What is the best practice to display CA Service Desk Manager (CA SDM) custom column values in a CA Business Intelligence (CABI) Report?

Document ID : KB000049311
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Most installations of CA SDM have custom extensions to the out of the box MDB tables that CA SDM installs.

Consider this scenario:

  • Table cr (Call Request) has customer: cr.customer
  • Table customer was extended to have an SREL against table zBPType: customer.zBPType
  • Table zBPType has details about zBPType (ex: name, description etc.)

This document documents how best to report the Call Request's Customer's ZBPType details without heavily customizing the CA SDM CABI Universe.

Solution:

The solution is to create an Attribute Alias within SDM via the Service Desk Administration tab.

As described in the scenario above, the intention is to display the zBPType's Name of a given Customer for a given Call Request record. We need to create an attribute alias for that value and let CA SDM automatically create the necessary joins.

Each attribute alias defines a pseudo column that corresponds to a column of a joined table. The current CA SDM CABI ODBC driver allows an unlimited number of attribute alias columns to be added to each object.

Perform the following steps:

Create attribute aliases in CA Service Desk Manager

  1. Log in to CA Service Desk Manager as an Administrator.
  2. Click the Administration tab.
  3. Expand Service Desk, Application Data, Codes and browse to the Attribute Alias node.
  4. Click Create New to add a new alias and use below values based on the example scenario above:

    Object Name = cr
    Alias Name = Use a name that you can easily qualify this when using reporting. For example, zcustomer_zBPType_Name
    Alias Value = customer.zBPType.name

Update CABI Universe:

  1. Create or update the Derived CABI Universe by adding a new Object under the Request -> Request Details class.
  2. Create a new object under Request Details with a name zCustomerBPTypeName and in the Select section type: cr.zcustomer_zBPType_Name
  3. Click on Validate to ensure syntax is correct.
  4. Save and export the universe.

Report:

Create a new report with other Request Details based fields alongside the newly added object zCustomerBPTypeName within the Request Details class. For each Call Request record now you will see the customer's zBPType Name value automatically within the report.

The benefit of using the attribute alias approach is that it eliminates the need to create and maintain table joins within the CABI Universe.

Note:

If there is Replicated Database/offline reporting involved:

  1. Create the above alias on the Main CA SDM Server.
  2. Let the Database replication process update this added record to the Reporting MDB Database. You can verify this by checking the record in Attrib_Alias table (via your Database Client tools) on the Reporting MDB Database.
  3. Once the above is in place, on the CA SDM Reporting instance, open a command line and type the following command: pdm_cache_refresh -t attr_alias
  4. Verify that the universe now sees the newly added column on the cr table.

For more information on Attribute Aliases, refer to Online Bookshelf / Help with in CA SDM Administrator interface or the CA SDM Administrator Guide.