Monday, February 14, 2011

Cognos 10 - Scheduling Features

Came across an IBM Supportlink article mentioning a new scheduling feature available with Cognos 10 that I think is really helpful.

There are many times when you wish to schedule your report to run once every x hours but on certain days in the week and only between certain time range on those days. This was so far not possible up until Cognos 10. Cognos objects could only be scheduled to run once every x hours or only once on certain days of the week, a combination of hours on week days was not possible.

With Cognos 10, users can now schedule their reports to run once every x hours on certain days of the week between certain time range on those days. You can read up more on this on IBM support link article.
 

Friday, January 28, 2011

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]


 

Thursday, January 20, 2011

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.
 

Tuesday, January 11, 2011

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.

Thursday, January 6, 2011

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.
 

Friday, December 24, 2010

Happy Holidays!!!

As the year comes to an end, wanted to take a moment to thank you all for keeping this blog going.

When I started this, didn't think that it would last for more than a few months or I would even have enough data to write about for more than 10 articles..It is with your continued interest and insights that I have been able to keep this going.

Wishing you all a Happy and Prosperous New Year...Happy Holidays!!!

Cheers,
Zephyr
 

Thursday, December 16, 2010

Calculations in Dimensional Query Subjects

A long time back while working on a relational ad-hoc package request I ran into an issue. I have been unable to explain the cause for it and the solution involved a work-aound that I am unhappy with. I am posting it here hoping that someone might be able to offer a better solution or even validate my reasoning on the issue.

Let us assume the ad-hoc package involves the query subjects - Product, Revenue and Forecast. Product behaves like a dimension due to being on the 1 side of the 1-N relationship to the other 2 query subjects. I have created a calculation say Calc A in the Product Query subject. The package also has a stand-alone calculation say Calc B defined as Revenue / calc A.

In Query Studio, when you drag Product and Calc B the data is off. The SQL generated by Cognos involves a min on Calc A even if aggregates are set to Total or Sum.Including the function Total inside of Calc A also doesn't help. No determinants are involved as the fact tables are at the same granularity and join to the dimensions at the same granularity.

My assumption on this issue now is that Cognos treats Product as a Dimension and hence includes a Min on the dimensional data item.

My work-around to the above issue was to create a shortcut to Product and join it to Product with a cardinality to make this behave like a fact. This has the downside of the table being queried twice and involves a stitched query when calc B is pulled into the query. Since the Dimension table in my case was not huge there wasn't much performance impact.

I will otherwise have to manipulate cardinalities to make Cognos treat Product as a fact which is not advisable as it impacts other queries and still will not remove the stitched query. Any thoughts on this?
  

Tuesday, December 7, 2010

Column Pagination

This tip is something probably everyone is aware of. When dealing with wide reports, you can split the report horizontally across pages. You can set which columns need to appear across each of those horizontal pages using the pagination property of the List Column.
 

Moving Aggregate Calculations on Dimensional Model

Here's a requirement to use moving aggregate calculations against a dimensional model and the solution to the same. The requirement is based on dates and hence may not be applicable to all scenarios.

Requirement: Display Quantity Sold, Revenue and Margin which is calculated as Revenue / Quantity for each year in a crosstab. Show the Margin Differential or moving-difference Calculation which is the (current year quantity - previous year quantity) / Margin of previous year.

Solution:

Step 1: Create a crosstab report with Years, Quantity Sold, Revenue dragged into it.

Step 2: Create a calculation to get Previous Year - lag(currentmember([Great Outdoors].[Years].[Years]),1). Let us call this as Prev Year.

Step 3: Create a Calculation called Quantity Diff - [Quantity Sold] - aggregate([Quantity Sold] with set [Prev Year])

Step 4: Create the Margin Diff Calc as [Qty Diff] / aggregate([Margin] within set [Prev Year]).







User Comments [Paul]: You could use the periodsToDate function for a running - aggregate([Quantity old] within set periodsToDate([All Member],currentMember([Great Outdoors].[Years].[Years])))
 

Wednesday, December 1, 2010

Creating Effective Dashboards

Here's a look at how to create effective Dashboards using Portal feature of Cognos.

Requirement: Create a dashboard showing Product Name, Sales in a list and chart report as tabbed reports with a prompt on Product ID.

Solution:

Step 1: Create folders - Dashboard Reports, Dashboard Portals.

 



Step 2: Create the prompt report under the Dashboard Reports folder. Let us name this as Dashboard - Prompt. Create an optional Product value prompt.





 









Step 3: Create a list report and a chart report under the Dashboard Reports folder. Let us name this as Dashboard - List, Dashboard - Chart. Include optional Produt filters in both the reports.

Step 4: Create a Dashboard Portal page for each of the reports under the Dashboard Portal folder. Name these as Dashboard - List Portal Page and Dashboard - Chart Portal Page. Set the content to Cognos Viewer.


 


Step 5: Edit the Portal Pages and set the Entry to the Dashboard Reports.Hide the title bars in the portal pages.

Step 6: Set the Portlet Communication options under Report Properties for each of the Dasboard portal pages. Provide the channel name for this property. All portlets sharing the same channel name will then be able to communicate with each other and can pass parameter values between them.




 






















Step 7: Create the main Portal page (Dashboard) that integrates the prompt report and the reports portal pages.Set the content to use Cognos Viewer for the prompt section and multipage object for the tabbed report output.









Step 8: For the prompt report section, set the Portlet Communication options under Report Properties.
 









Tuesday, November 23, 2010

Auto Correct option in Report Studio

I came across this option in the Tools menu of Report Studio and found it really useful.

When you run into validation errors while working on complex reports that involve a lot of data items and query objects, identifying the data item that is causing the error becomes difficult especially if its a complex syntax that is used across the data items. In such cases click on the Auto Correct option under Tools Menu to quickly identify all data items that have errors. This is much easier than going through the validation errors and trying to figure out the data items that have issues. But do not click on the OK button of the dialog box as this would remove the erroneous data items from the report.

Lets say we have a report that displays Product Line, Revenue, Margin, Quantity. Now the requirement is to display the values in the records only if quantity is more than 10000 else the records should display NA. So we include a case statement in all the metrics as "Case when [Sales(query)].[Sales].[Quantity] > 10000 then..". Now lets assume we made a mistake while copy-pasting this expression across the data items.Validating the report would throw the following error:













 From the error, its difficult to make out the data item that caused this error. Lets select Auto Correct option from the Tools menu:

















Though this feature is useful it has some limitations like the dialog box only displays the data item name. So if there are multiple data items across query objects with the same name its difficult to identify the query that has the error.
 

Monday, November 15, 2010

Parameterized URLs for Retrieving Burst Outputs

Many of you must be aware of using URLs for inserting Cognos content in external applications. How do you frame the URL for retrieving bursted report outputs?

To retrieve burst output of the report view TEST stored in the folder TEST under Public Folders with burst key 123:

http://< cognos environment>?b_action=cognosViewer&ui.action=view&ui.object=/content/folder[@name='TEST']/reportView[@name='TEST REPORT']/reportVersion[starts-with(@name,'20')]/output[contains(@burstKey,'123')]

Note that the above URL will look through all burst outputs generated in the year starting with "20" i.e. 2000 - 2999.

You can use starts-with or contains for the reportVersion and output options.

Example: We have a report Burst PoC with Burst outputs.





The URL to retrieve the burst output for Americas would be:



Related Articles:
 

Wednesday, November 10, 2010

Teradata Unions - Numeric Overflow Error

Requirement: There are 2 tables Sales and Revenue with columns Date, Product, Sales and Date Product, Revenue. Union data from these 2 tables and display Sales / Revenue value.

Create 2 query objects with the data items Date, Product, Sales and Revenue with Sales set to 0 in query 1 and Revenue set to 0 in query 2. You may run into numeric overflow errors with Teradata cause in Query 1 Sales is set to 0. So Teradata considers data type of Sales to be integer rather than decimal thus causing the error. Cast the 0's to appropriate data types and the issue gets resolved.
 

Wednesday, November 3, 2010

Avoiding Meta data Callbacks due to Hard-Coded SQLs

You would probably have noticed that Cognos creates meta data call-backs when you have reports based on Query Subjects that have SQLs hard-coded in them. To avoid such call-backs, import all the tables referenced by your query and leave them untouched. Cognos will reference the meta data from these tables while generating the SQL hard-coded in the query subject.

I would suggest it best to avoid hard-coding SQLs in query subjects but instead to create them as views in the DB and reference them through Cognos. This would help ensure better maintenance of objects and in easier impact-analysis of DB changes. Hard-Coded SQLs in query subjects should only be used when dynamicity is required through the usage of Cognos macros in the queries.
  

Thursday, October 14, 2010

Macro Prompts in FM Model for Adhoc Packages

Working on creating an Adhoc Package for use in Query Studio. The package needs to include base metrics and compound metrics and users need to be prompted for Date range and other Dimensions on pulling any of the metrics.

For the prompts, I included filters in the model query subject and built of the metrics. But what I noticed was that, only the base metrics prompt the users and if you drag a compound metric you are not prompted the first time in Query Studio until you hit the re-run button.

Work-around is to use macro filters rather than ?parameter? filters.
.

Tuesday, October 5, 2010

FM and Teradata Stored Procedures

I have been working on using Stored Procedures to update tables through Event Studio based on a condition. The back end DB is Teradata.

With Teradata if you have a separate schema to create Stored Procedures, the schema will not show up in FM when you use the run meta data wizard unless it has at least 1 table or view. To overcome this issue, a dummy table was created in the schema after which the schema showed up in FM. I then imported the SP. The SP is a data modification SP. Teradata SPs cannot be run through FM or through Event Studio because Cognos fires a "CALL SPName" statement rather than an "EXEC SPName" statement.

To work around the above issue create macros and use them through FM or Event Studio. This is only for Teradata. Oracle Stored Procedures do not have any issues.
 

Tuesday, September 28, 2010

Creating a "Top 10 + Others" dimensional report

Couldn't figure out a better title :-( .

The requirement is to display Top x Products and group all the other products into the "Others" group. If you were using a relational model, you could rank the Products based on Revenue and then use a case statement to identify the Products that need to go into the Others group. But with a dimensional model you would run into issues using a case statement or Rank function.

Steps:

Step 1: Create a Top Prod data item to order the Products based on Revenue and to retrieve the Top 2 Products.

head(order([Sales and Marketing].[Products].[Products].[Product line],[Revenue],desc ),2)

Step 2: Create a Bottom Prod data item to retrieve all the other Products.

except([Sales and Marketing].[Products].[Products].[Product line],[Top Prod])

Step 3: Drag the Revenue data item. Create a Bottom Value data item to calculate the Revenue for "Others" category.

total(currentMeasure within set [Bottom Prod])

Step 4: Create a member for Others that has the Bottom value associated with the Products Dimension.

member([Bottom Value],'Others','Others',[Sales and Marketing].[Products].[Products])

Step 5: Create a Products data item that now includes Top 2 Products and Others.

union([Top Prod],[Others])



Report XML 10.1.1 against Go Sales:

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us"> <modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (analysis)']/model[@name='model']</modelPath> <drillBehavior modelBasedDrillThru="true"/> <queries> <query name="Query1"> <source> <model/> </source> <selection><dataItem name="Top Prods"><expression>head(order([Sales (analysis)].[Products].[Products].[Product line],[Revenue],desc),2)</expression></dataItem><dataItem name="Bottom Prod"><expression>except([Sales (analysis)].[Products].[Products].[Product line],[Top Prods])</expression></dataItem><dataItem name="Bottom Prod value"><expression>total(currentMeasure within set [Bottom Prod]) </expression></dataItem><dataItem name="Others"><expression>member([Bottom Prod value],'Others','Others',[Sales (analysis)].[Products].[Products]) </expression></dataItem><dataItem name="Products"><expression>union([Top Prods],[Others])</expression></dataItem><dataItemMeasure name="Revenue"><dmMember><MUN>[Sales (analysis)].[Sales].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales]</DUN><itemCaption>Sales</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/></XMLAttributes></dataItemMeasure></selection> </query> </queries> <layouts> <layout> <reportPages> <page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style> <pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style> <contents> <crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1"> <noDataHandler> <contents> <block> <contents> <textItem> <dataSource> <staticValue>No Data Available</staticValue> </dataSource> <style> <CSS value="padding:10px 18px;"/> </style> </textItem> </contents> </block> </contents> </noDataHandler> <style> <defaultStyles> <defaultStyle refStyle="xt"/> </defaultStyles> <CSS value="border-collapse:collapse"/> </style> <crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Products" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab> </contents> </pageBody> <pageHeader> <contents> <block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style> <contents> <textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style> <dataSource> <staticValue/> </dataSource> </textItem> </contents> </block> </contents> <style> <defaultStyles> <defaultStyle refStyle="ph"/> </defaultStyles> <CSS value="padding-bottom:10px"/> </style> </pageHeader> <pageFooter> <contents> <table> <tableRows> <tableRow> <tableCells> <tableCell> <contents> <date> <style> <dataFormat> <dateFormat/> </dataFormat> </style> </date> </contents> <style> <CSS value="vertical-align:top;text-align:left;width:25%"/> </style> </tableCell> <tableCell> <contents> <pageNumber/> </contents> <style> <CSS value="vertical-align:top;text-align:center;width:50%"/> </style> </tableCell> <tableCell> <contents> <time> <style> <dataFormat> <timeFormat/> </dataFormat> </style> </time> </contents> <style> <CSS value="vertical-align:top;text-align:right;width:25%"/> </style> </tableCell> </tableCells> </tableRow> </tableRows> <style> <defaultStyles> <defaultStyle refStyle="tb"/> </defaultStyles> <CSS value="border-collapse:collapse;width:100%"/> </style> </table> </contents> <style> <defaultStyles> <defaultStyle refStyle="pf"/> </defaultStyles> <CSS value="padding-top:10px"/> </style> </pageFooter> </page> </reportPages> </layout> </layouts> <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-06-09T13:51:23.483Z" output="no"/></XMLAttributes></report>


Monday, September 20, 2010

Drill Through Pointers for Deployment

Recently, I updated and moved a drill through target report. Soon after, the Source reports started throwing drill-through links error. It was later that I figured out that the target report was moved to another folder prior to import for back-up reasons. This caused the drill through in the source reports to break.

A drill through source and target report are linked to each other through their search path and ID. When a target report is moved around the source report specification gets updated with the new path without having to make any modifications.

This can prove to be a boon to some and a head-ache to others while doing deployment. The administrator doing deployments should keep the above point in mind while doing deployments involving drill through reports else it will result in broken links.

Let us consider a scenario where we have a Source Report and a Target report in Folder 1.

Now the Target Report is moved to My Folders and a newer version of the Target Report is imported into Folder 1. The Source report link to the target report breaks.

The reason is when the Target report was moved to My Folders the Source report re-pointed to this new path. Even when a newer version of the report with the same name is pasted in the same path the Source report's spec points to the new path and the Source Report ID is linked to the older Target report ID. Hence the links from the Source report break.

Until the ID connection between the Source and Target Report is lost, deployments need to be done carefully. An ID association is lost when Target report is deleted or the Target report is imported separate from the source report.
 

Friday, September 10, 2010

Multiple Queries for different data containers

Why does Cognos generate Multiple Queries even when you have the different objects pointed to the same Query?

Now before I answer that, here's a tip to all the newbies on seeing the SQLs generated by Cognos. To identify the SQLs that Cognos fires against the DB, you would need to use the Generated SQL/MDX option from the Tools Menu and not the one available for each individual Query Object in Cognos. This will display the various SQLs that Cognos would fire against the DB and here's where you would notice that even if you have multiple objects pointing to the same Query Object, Cognos would still fire multiple queries.

Now if you have say multiple lists all pointing to the same Query Object, then unless the columns pulled in, the sort order, group by clauses match, Cognos would fire multiple queries. To make the columns pulled in to match you could use the Properties property of the lists and select all those columns that are missing from each list to match the other lists based out of the same Query Subject.

In case you have different data containers say like a list and crosstab or a bar graph and crosstab pointing to the same query subject Cognos would fire multiple queries as that's how its designed. So no luck over there.
  

Friday, September 3, 2010

New Dynamic Sorting Controls from IBM Cognos

Cognos seems to have come up with new controls / method for dynamic column sorting that doesn't involve the drill through methods.

Here's the link to the article.

http://www.ibm.com/developerworks/data/library/cognos/reporting/scripting_techniques/page515.html?ca=drs-

Sounds cool!! Hope this ends the report re-run issues we have while using the drill-through method for sorting.

I will check this method out and post my comments later.

Let me know your thoughts on this method though.

Here's an update from PaulM about applying this method on Cognos 10 environment:

[User comments: PaulM]
There are some differences between the XML files from Cognos 8 to Cognos 10. The general structure of the files are the same, so making the correct updates is not difficult.

First you need to use a decent text editor, one with comparison capabilities. Notepad++ is excellent for this.

Open both the C10 version and the version from the download. They should be in different tabs in Notepad++. Click on the plugins menu and select Compare --> Compare.

Find and copy the differences related to this technique only. Don't try to copy deprecated objects over. All of the related fragments have "orderCol" somewhere in the name.