XPath: An Introduction

XPath is a query language used to navigate through an XML structure. In other words, assume you have the following folders on your C drive: Folder 1, Folder 2 that is inside Folder 1, Folder 3 that is stored inside Folder 2. Now to access Folder 3, you would probably type C:\Folder 1\Folder 2\Folder 3 in your run command. Likewise, XPath can be considered as a language that would help you navigate your XML document and locate specific XML sections in your document.

Let us consider a simple XML structure as below:

<Products>
  <ProductLine name="Product Line A">
      <Product type="Product Type A">A</Product>
      <Product type="Product Type A">B</Product>
      <Product type="Product Type A">C</Product>
  </ProductLine>
  <ProductLine name="Product Line B">
     <Product type="Product Type B">D</Product>
     <Product type="Product Type B">E</Product>
     <Product type="Product Type B">F</Product>
 </ProductLine>
 <ProductLine name="Product Line C">
    <Product type="Product Type C">G</Product>
 </ProductLine>
</Products>

Using XPath you can traverse the structure using the following XPath Expressions:

node - Select all the nodes with the name "node" Example: Products would return all nodes with node name Product.
/ - Select the root node. In our example XML this would return the root Products node.
// - Select all the nodes in the document from the current node that match the defined condition. //ProductLine would return all ProductLine nodes assuming we are at the root node.
@ - Select attributes. ProductLine[@name = 'Product Line A'] would return Product Line nodes with name value set to Product Line A.

XPath Predicates: XPath Predicates allow you to select nodes containing defined values.

node[1] - Select the first node of type node. ProductLine[1] would return the first Product Line node.
node[@type='Type 1'] - Select nodes that have type attribute with value Type 1.
node[@value > 100] - Select nodes that have value attribute with value > 100.

There are many more such expressions and predicates that you can use. But not all are supported by CMS. Only a subset of the XPath expressions and predicates are supported by CMS.

In my next article I will write about how to get the XML of the data generated by Cognos.Note, this XML is not the same as the report XML. The report XML defines the report specification while the XML we are dealing with in CMS defines the report output or rather the report data along with the styles that go with the data.

CMS XPath Examples:

Consider a report that has 2 pages (Yearly Revenue Page, Revenue Detail Page). The Yearly Revenue Page displays a revenue chart and the Revenue Detail Page displays 2 lists - Sales Region Revenue and Product Revenue.





To get all the nodes of type list.

http://localhost:81/cognos84/cgi-bin/cognos.cgi/rds/reportData/report/i754384CF51C94873A34DABCF36108B16?xpath=//lst



To get all the column titles of the list nodes.

http://localhost:81/cognos84/cgi-bin/cognos.cgi/rds/reportData/report/i754384CF51C94873A34DABCF36108B16?xpath=//lst/colTitle/item/txt



To access the Region Sales list on Page 2.

http://localhost:81/cognos84/cgi-bin/cognos.cgi/rds/reportData/report/i754384CF51C94873A34DABCF36108B16?xpath=/document/page[2]/body/item[1]



To select Row 1 of Group 1 (2004) of Region Sales list.

http://localhost:81/cognos84/cgi-bin/cognos.cgi/rds/reportData/report/i754384CF51C94873A34DABCF36108B16?xpath=/document/page[2]/body/item[1]/lst/group/grp[1]/row[1]





To get all Asia Pacific rows of Region Sales list.

http://localhost:81/cognos84/cgi-bin/cognos.cgi/rds/reportData/report/i754384CF51C94873A34DABCF36108B16?xpath=/document/page[2]/body/item[1]/lst/group/grp/row[cell/item/txt/val='Asia Pacific']



To get row 2 in each group of Region Sales list.

http://localhost:81/cognos84/cgi-bin/cognos.cgi/rds/reportData/report/i754384CF51C94873A34DABCF36108B16?xpath=/document/page[2]/body/item[1]/lst/group/grp/row[pos()=2]


 

Playing around with CMS

CMS or Cognos Mashup Services is a Cognos component available starting from Cognos 8.4.1 as part of your Cognos SDK software. CMS exposes Cognos objects like reports as web services and thus allows you to integrate them into other environments. There are multiple formats and interfaces through which these web services can be exposed namely SOAP and REST but I am not going to bore you with jargon here. I am not going to try and explain the differences between using SDK and CMS in this article either. This article is just to give you a glimpse of what one can do with CMS and to demonstrate one of the many CMS features.

I have been playing around with the REST interface of CMS and am going to begin by showing you what I have been able to do using this.Technical information on CMS, REST and SOAP will follow later :-)

There are a number of ways you can use the REST interface. I have been playing around with just one of those ways that I will be demonstrating in this article.

To retrieve report data using CMS you create a URL as below:

http://localhost:81/cognos84/cgi-bin/cognos.cgi/rds/reportData/path/Public%20Folders/Reports/Sample%20Report%201?fmt=HTML



The URL consists of the Cognos application detail, and the path of the report along with the fmt in which you wish to see the data. The formats supported are XML, HTML or JSON.

To access one of the objects in the report you need to use the name of the object in the report.

Example: We have a report that includes a list and chart object.



To access the chart object alone:

http://localhost:81/cognos84/cgi-bin/cognos.cgi/rds/reportData/path/Public%20Folders/Reports/Sample%20Report%203?selection=CombinationChart1&fmt=HTML

where CombinationChart1 is the name of the Chart object.



Though you can access report content through URLs using CMS as well, this technique differs from parametrized URL method in that you cannot append prompt values to the URL and also, the above URL will work in an environment where anonymous access is allowed or if you try this in a browser in which you are already logged into Cognos thus re-using the existing Cognos session.

To provide authentication details and prompt values the CMS REST interface needs to be used through a programming language where in you will invoke the CMS authentication web services.The URLs need to be used through the programming application. I will be providing examples on the same in upcoming articles.

Using XPaths in your requests:

XPath is a language to navigate through an XML document. More on XPaths in another article only if folks are interested.

When you use XPaths in your request the output is always returned in XML format. XPaths allow you to get data from very specific sections in your report like say the group header or the first column in your report etc.

Example: To retrieve the data from a single column in your report say you want to view the PRODUCTTYPE_ID column data displayed in Img 1 of the post:

http://localhost:81/cognos84/cgi-bin/cognos.cgi/rds/reportData/report/i1AD2E77046F74BFD8C3B916CF4FD66C2?xpath=/document/page/body/item/lst/group/row/cell[1]/item/txt/val




Now you can use this XML output retrieved in your programming application and display/integrate the data as required in your external application.
 

Adding Comments to Saved Report Output Versions

This is a new feature that I came across in Cognos 8.4. This is not to be confused with adding comments on on-demand/interactive run reports that developers keep asking for (this feature is available with Cognos 10 though).

Requirement: Create a Sales report and schedule it to run once every month and save the output to Cognos Directory with a burst label identifying the month the report was executed for. Allow users to add comments to the burst output.

Step 1: Create the required report (not going into the details of this unless someone wants me to).

Step 2: From Cognos Connection, select the "Set Properties" action. On the report tab, click on Advanced options and check the "Enable comments in saved output versions" option.



Step 3: Burst the report.



 

Step 4: Open the report output version, notice the "Add Comments" icon at the far right-end of the screen.



Step 5: Set comments and close the report output version.



Step 6: Re-open the report output version, notice the comment added earlier.

Caching Prompts

Started the new year with a Cognos job failure for refreshing the prompt cache due to server issues. I am now trying to think of an effective failure/re-start strategy.

Caching Prompt values makes sense when you have huge dimensions in your warehouse that change slowly over a period of time. In my case we have dimensions that have SCD Type 2 implemented in them requiring us to cache prompts.

But one needs to keep in mind that the data you see in your prompts are not live data and are not security oriented. One also needs to monitor the prompt cache refresh jobs to ensure they run successfully else users may end up seeing old data in the reports.

To cache report prompts you need to create a Cognos job. Select the report whose prompt values need to be cached.Under report options, choose "Refresh the report cache" option for "Run the Report to" property. The clear cache option is available under the report properties section. You can also set properties to clear the cache in x days.