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>

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.
 

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.
  

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.