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.


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>


Lidya said...

Hi Zephyr,
I have tried the steps that have been posted.
But I got this error :
Invalid parameter '[Top Prod]' provided for 'except' at position '2' for 'dataItem="Bottom Prod"'. This parameter is 'memberSet' type but is expected to be one of the following: 'memberSet'.

Could you help me to solve this problem? Thanks :)

Zephyr said...

Hi Lidya, Can you start from step 1and create each data item and validate them in each of the steps and let me know in which particular step you are receiving the error?

Lidya said...

Hi Zephyr,
Because I want make top 10 and others group.. First, I created data item in Query 1,named [Top Prod], I do exactly as in Step 1 --> head(order([Sales and Marketing].[Products].[Products].[Product line],[Amount],desc ),10)
And then, I created new data item in Query 1, named Bottom Prod, like Step 2. except([Sales and Marketing].[Products].[Products].[Product line],[Top Prod])

Then, in Page 1, I drag Bottom Prod into the list in report. And then run the report.

After that I got that error.
Thanks before :)

Zephyr said...

Hi Lidya, Sorry for the delay in responding.

I have attached the report xml for reference. This is against 10.1.1

RJohnson said...

Hello and thanks for the information your providing. I'm new to this and it's been helpful.
In your example, are you using a list report or crosstab? I have a need to display the top customers but I can not get this method to work in a crosstab report. I'm able to get the appropriate number of customers (top 10) but they are not the top customers, the results are scattered.
Thanks for the info!

Zephyr said...

Hi RJohnson, You can simplify this by using the topCount expression as well.


This will return the Top 5 Products based on Revenue.

Anonymous said...

How can we have this on multiple amount column.

Rick J said...

Excellent work