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])))
Popular Posts
Showing posts with label Dimensional Model. Show all posts
Showing posts with label Dimensional Model. Show all posts
Tuesday, December 7, 2010
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:
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> |
Tuesday, August 17, 2010
Single Select Tree Prompt on a Relational Model
You can base Tree prompts not just on Dimensional Model but on relational Model as well. This involves using macros and decoding MUNs. This has been covered in article on IBM's support site but I am covering the same here for the benefit of those who use this blog as a one-stop-site for all Cognos needs.
The below solution works for single select Tree Prompt. I am working on multi-select Tree Prompts and would update this article once I have the solution.
Requirement: Create a Date Dimension Tree Prompt based on a Relational Date Dimension Table and filter the report to retrieve data for the selected dates.
Solution:
Step 1: In the FM Model, create a stand alone Date Regular Dimension based on the existing Date Dimension query subject in the Presentation Layer View.
This Dimension would be used for creating the Tree Prompt.
Step 2: In the report create a tree prompt based on Date Dimension.
Step 3: Create a filter as below:
[Date] = #csv(substitute(']','',grep(']',split('].', grep('all',split('->',prompt('dte','memberuniquename','[Presentation Layer].[Date Dimension].[YEAR].[DATE]->[all].[2008].[Q1 2008].[Jan 2008].
[Wk 1].[2008-01-01]')))))))#
The above filter works as explained:
Thus [Date] is compared with 2008-01-01 and the filter works as expected.
The below solution works for single select Tree Prompt. I am working on multi-select Tree Prompts and would update this article once I have the solution.
Requirement: Create a Date Dimension Tree Prompt based on a Relational Date Dimension Table and filter the report to retrieve data for the selected dates.
Solution:
Step 1: In the FM Model, create a stand alone Date Regular Dimension based on the existing Date Dimension query subject in the Presentation Layer View.
This Dimension would be used for creating the Tree Prompt.
Step 2: In the report create a tree prompt based on Date Dimension.
Step 3: Create a filter as below:
[Date] = #csv(substitute(']','',grep(']',split('].', grep('all',split('->',prompt('dte','memberuniquename','[Presentation Layer].[Date Dimension].[YEAR].[DATE]->[all].[2008].[Q1 2008].[Jan 2008].
[Wk 1].[2008-01-01]')))))))#
The above filter works as explained:
- First the MUN for the selected Tree Prompt value is retrieved.
In the above Example: [Presentation Layer].[Date Dimension].[YEAR].[DATE]->[all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01] - Then we split the retrieved MUN into 2 parts based on the symbol "->".
In the above Example: The MUN is split into [Presentation Layer].[Date Dimension].[YEAR].[DATE], [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01] - We then select the part containing "all".
In the above Example: [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01] is selected. - Then split the part again into multiple parts based on the symbol "].[".
In the above Example: [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01] is split into [all, 2008, Q1 2008, Jan 2008, Wk 1, 2008-01-01] - Retrieve the part containing "]".
In the above Example: 2008-01-01] is selected. - Replace "]" with "".
In the above Example: 2008-01-01] is converted to 2008-01-01.
Thus [Date] is compared with 2008-01-01 and the filter works as expected.
Thursday, June 24, 2010
Implementing Dynamic Sorting with Dimension Model??
I am trying to implement dynamic sorting but using a dimensional model. Not having much luck here. Written to various Cognos forums as well but no help there.
Any help would be greatly appreciated.
Any help would be greatly appreciated.
Tuesday, March 23, 2010
Top or Bottom Selling Products against Dimensional Model
If you have tried creating a report that should display either Top or Bottom 5 products based on Revenue against a dimensional model in Cognos version 8.4, you would have noticed that using a case statement seems to cause validation issue.
The reason for the same has been explained in the article : Case Statement Issues in 8.4 with Dimensional Model
To work around this issue, the steps have been explained below:
Step 1: Create the following 2 Set Expression
TopCount Products - filter(topcount([Cube].[Products].[Products].[Product],5,[Revenue]), ?TopBottom? = 1)
BottomCount Products - filter(bottomcount([Cube].[Products].[Products].[Product],5,[Revenue]), ?TopBottom? = 2)
Where ?TopBottom? is a prompt that accepts as its values - 1 (Top Count) or 2 (Bottom Count)
Step 2: Create the following Union Set Expression
Products - union([TopCount Products],[BottomCount Products])
Step 3: In the list report, drag Products and Revenue measure.
Here if you notice, I have avoided using Case statement as they seem to be unsupported or do not work as expected in 8.4.
The report now works as expected.
The reason for the same has been explained in the article : Case Statement Issues in 8.4 with Dimensional Model
To work around this issue, the steps have been explained below:
Step 1: Create the following 2 Set Expression
TopCount Products - filter(topcount([Cube].[Products].[Products].[Product],5,[Revenue]), ?TopBottom? = 1)
BottomCount Products - filter(bottomcount([Cube].[Products].[Products].[Product],5,[Revenue]), ?TopBottom? = 2)
Where ?TopBottom? is a prompt that accepts as its values - 1 (Top Count) or 2 (Bottom Count)
Step 2: Create the following Union Set Expression
Products - union([TopCount Products],[BottomCount Products])
Step 3: In the list report, drag Products and Revenue measure.
Here if you notice, I have avoided using Case statement as they seem to be unsupported or do not work as expected in 8.4.
The report now works as expected.
Monday, March 22, 2010
Case Statement Issues in 8.4 with Dimensional Model
This particular issue deals with Coercion errors. Your report works fine in 8.1 and when you have converted it to 8.4.1 you get an error that looks similar to the following:
QE-DEF-0478 Invalid coercion from 'value' to 'measure' for 'when ?Measure? = 'R' then [Revenue] when ?Measure? = 'Q' then
[Quantity] ' in 'case when ?Measure? = 'R' then [Revenue] when ?Measure? = 'Q' then [Quantity] end'.
This is due to the usage of Case statements against dimensional models. Cognos had earlier warned against usage of this against Dimensional Models now they seem to have started restricting the same.
Try using an If Then Else to work around this. But Cognos definitely needs to come up with a similar construct for Dimensional Models as well as most work revolves around the usage of Case statements.
QE-DEF-0478 Invalid coercion from 'value' to 'measure' for 'when ?Measure? = 'R' then [Revenue] when ?Measure? = 'Q' then
[Quantity] ' in 'case when ?Measure? = 'R' then [Revenue] when ?Measure? = 'Q' then [Quantity] end'.
This is due to the usage of Case statements against dimensional models. Cognos had earlier warned against usage of this against Dimensional Models now they seem to have started restricting the same.
Try using an If Then Else to work around this. But Cognos definitely needs to come up with a similar construct for Dimensional Models as well as most work revolves around the usage of Case statements.
Subscribe to:
Posts (Atom)