Integration Software

Add additional metadata to your publications

The basics

When reports are being saved to disk by the IBM Cognos Service, a *_desc.xml file is provided along with the report. This file contains metadata about the report you were running such as the Cognos searchpath, the burstkey, prompts you selected, etc.

This image shows an example of a Cognos _desc.xml metadata file contents and how it is exposed in the CoRePublisher publication wizard for use in your publication configuration:

Add additional metadata to your publications desc file in crp small

In the *_desc.xml file you can only control and manage the contents of the burstkey and the report prompt elements. In some cases you want to map more metadata about a report than there is available in the *_desc.xml file. This blogpost describes some options to add or map all the additional metadata you want in your target system where the reports are being published to. Ideally, the required metadata should be made available in the Cognos XML metadata file; this is the out-of-the-box way. As an alternative, a lookup can be executed based on an external CSV or XML file or even a DB.

Extending your burstkey

Suppose you have a Sales report which uses the Sales Rep ID as a burst group. In your target SharePoint Document Library you want to make this Cognos report available and map custom SharePoint metadata fields such as the Sales Rep Name, his telephone number and e-mail. If this extra metadata is available in for example the Sales Rep dimension in your Data Warehouse, you could extend the burstkey by concatenating this extra Sales Rep information into a single column (a new, custom data item in your report) and use it as burst grouping. We used the URL parameter principle in order to create key-value pairs, which we can parse using a generic VBScript function.

Add additional metadata to your publications add metadata concatenated burst grouping small     Add additional metadata to your publications add metadata burst options small

This burst grouping eventually results in the burstkey, which is provided in the *_desc.xml file, as shown in this screenshot:

Add additional metadata to your publications add metadata burstkey result small

When configuring the publication, you can use VBScript to split the burstkey and use the additional metadata in your target mapping or configuration:

Add additional metadata to your publications add metadata crp expr editor small

Code snippet for parsing the burstkey using a small VBScript function:

'function for parsing the burstkey based on the URL parameters principle
Function getBurstkeyValue(burstkey, key)
	Dim parameters
	Dim valuePair
	Dim i
	'parse the parameters based on the ampersand
	parameters = Split(burstkey, "&")
	For i = 0 To UBound(parameters)
		If Trim(parameters(i)) <> "" And InStr(parameters(i), "=") > 0 Then
			'parse the key-value pair based on the is equal character
			valuePair = Split(parameters(i), "=")
			If valuePair(0) = key Then
				getBurstkeyValue = valuePair(1)
				Exit Function
			End If
		End If
	Next
End Function

This custom VBScript function can be added to the publication by checking in the first step of the publication wizard the option “Use include.vbs for extended VBScript functionality”. This way, you can use the functions and procedures from the include.vbs file in the various expressions when configuring the conditions and the target parameters for the publication.

Add additional metadata to your publications add metadata include vbs small

Adding an external lookup file

In case your report is not bursted (and hence there is no burstkey) and you cannot use prompts to pass the metadata you need in the Cognos metadata xml file and into your publication, you can add custom VBScript functions to perform a lookup in an external file. The same way as described above, in the first step of the publication wizard the option “Use include.vbs for extended VBScript functionality” allows you to include custom VBScript functions or procedures and to use those calls in the expressions for configuring the conditions and the target parameters for the publication.

Add additional metadata to your publications add metadata include csv lkp small     Add additional metadata to your publications add metadata crp expr editor lkp small

You can download the full VBScript lookup functionality here: download sample VBScript code

CoRePublisher allows you to use 1 include.vbs file per Common Home Directory (to be stored in the root of the CRP Common Home Dir). The lookup files can be CSV files, XML files or any type of file you can access by means of VBScript in order to extract the data from. In the example provided in this post, we will use a CSV file and store it in a folder called “_LookupFiles”, in the root of the CRP Common Home Dir.

The example given, uses key-value pairs to perform the lookup in order to reduce maintenance and to provide maximum re-use of your code. For example, next expression:

lookup("sales_lkp", "12345", "sales_id", "sales_name")

will, based on the include.vbs file as shown above, perform a lookup in the csv file sales_lkp.csv in the folder E:\CoRePublisher\Sales_Dep_HomeDir\_LookupFiles where it will search for sales_id 12345 and return the sales_name value matching the id.

The lookup CSV file could look like this (sample CSV is included in the download):

sales_id,sales_name,sales_tel_nbr,sales_email
12343,John Smith,+1 478 7888 785,john@company.com
12344,Michael Pescoe,+1 478 7999 785,michael@company.com
12345,Roger Suarez,+1 478 7666 785,roger@company.com

The result would be “Roger Suarez”, which is the sales rep name for id 12345.

Use Cognos and CoRePublisher to generate and refresh the lookup files

The CSV file (or other data file) needs to be generated and refreshed one way or another. In case the additional metadata you need, is available in your Data Warehouse (and thus available in your Cognos reporting environment) you can create a simple Cognos list report containing the keys and values, save it to disk in CSV or XML output format and have it published via CoRePublisher into the lookup directory. Using scheduling in Cognos you could refresh this lookup data on a daily basis and keep it up to date this way.

Adding an external database lookup

Using VBScript you can not only access files, but you could also connect to a database (natively or via ODBC) in order to perform a lookup. For bulk publications of Cognos reports, there will be a performance drawback, for smaller sets of publications this can be a useful option. Also note that you will need to have the DB client tools or required ODBC drivers installed on the host running the CRP service.

Leave a Reply

News

Blog

Twitter Feed

Contact us


7 − three =