This is on Cognos 10.1.1 with the GO Data Warehouse (query) package.
I was asked a somewhat difficult question. On a relational model the report shows a crosstab years with another attribute nested in rows and measures in columns. How can the growth % of the years from the previous year to the current year be displayed?
Normally I would create data items for each measure for each year that needs to be included in the expression. Something along the lines of
case when [Year] = extract(‘year’,current_date) then [Measure] else 0 end
The issue with this is that this is a crosstab – how can you make it accommodate different measures on different edges?
A better solution would be one that mimics dimensional functionality. I want to divide 2011 by 2010, with all of the measures acting accordingly.
In a dimensional query I would simply do [Current Year]/[Previous Year]. If I didn’t have any calculated members in the cube, I might try something like this:
item(filter([Year Level],[Year Caption] = #sq(timestampMask(‘yyyy’,$current_timestamp))#),0)
The filter returns a set of all of the years that match the current year, and the item takes that set and returns the first member in it.
Because this is a relational model, OLAP functions shouldn’t work.
(item(filter([Year],[Year]=2007),0)/item(filter([Year],[Year]=2006),0))-1
But they do.
My guess is that Cognos is implicitly converting the [Year] field into a level, like it would be in a DMR model. The odd thing is, that while it works, I don’t see it reflected in the SQL of the xtab.
Cognos SQL:
select
SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN as Order_method_typekey,
GO_TIME_DIM.CURRENT_YEAR as Yearkey,
XSUM(SLS_SALES_FACT.QUANTITY for SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,GO_TIME_DIM.CURRENT_YEAR ) as Quantity,
XSUM(SLS_SALES_FACT.SALE_TOTAL for SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,GO_TIME_DIM.CURRENT_YEAR ) as Revenue,
XSUM(SLS_SALES_FACT.GROSS_PROFIT for SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,GO_TIME_DIM.CURRENT_YEAR ) as Gross_profit
from
great_outdoors_warehouse..GOSALESDW.SLS_ORDER_METHOD_DIM SLS_ORDER_METHOD_DIM,
great_outdoors_warehouse..GOSALESDW.GO_TIME_DIM GO_TIME_DIM,
great_outdoors_warehouse..GOSALESDW.SLS_SALES_FACT SLS_SALES_FACT
where
(GO_TIME_DIM.CURRENT_YEAR in (2006,2007)) and
(SLS_SALES_FACT.ORDER_METHOD_KEY = SLS_ORDER_METHOD_DIM.ORDER_METHOD_KEY) and
(SLS_SALES_FACT.ORDER_DAY_KEY = GO_TIME_DIM.DAY_KEY)
group by
SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,
GO_TIME_DIM.CURRENT_YEAR
Running a trace in SQL Server shows that it’s not doing anything different:
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,NULL,N'select "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_EN" AS "Order_method_typekey", "GO_TIME_DIM"."CURRENT_YEAR" AS "Yearkey", sum("SLS_SALES_FACT"."QUANTITY") AS "Quantity", sum("SLS_SALES_FACT"."SALE_TOTAL") AS "Revenue", sum("SLS_SALES_FACT"."GROSS_PROFIT") AS "Gross_profit" from "GOSALESDW"."SLS_ORDER_METHOD_DIM" "SLS_ORDER_METHOD_DIM", "GOSALESDW"."GO_TIME_DIM" "GO_TIME_DIM", "GOSALESDW"."SLS_SALES_FACT" "SLS_SALES_FACT" where "GO_TIME_DIM"."CURRENT_YEAR" in (2006, 2007) and "SLS_SALES_FACT"."ORDER_METHOD_KEY" = "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY" and "SLS_SALES_FACT"."ORDER_DAY_KEY" = "GO_TIME_DIM"."DAY_KEY" group by "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_EN", "GO_TIME_DIM"."CURRENT_YEAR"',1
select @p1
So it must be doing the calculation locally. Local processing is frowned upon, so you should try to find another way to handle this. Ideally building a real cube and using real OLAP functionality.
Report XML:
<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 Data Warehouse (query)']/model[@name='model']</modelPath> <drillBehavior modelBasedDrillThru="true"/> <layouts> <layout> <reportPages> <page name="Page1"> <style> <defaultStyles> <defaultStyle refStyle="pg"/> </defaultStyles> </style> <pageBody> <style> <defaultStyles> <defaultStyle refStyle="pb"/> </defaultStyles> </style> <contents><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1" rowsPerPage="999999"> <crosstabCorner> <contents/> <style> <defaultStyles> <defaultStyle refStyle="xm"/> </defaultStyles> </style> </crosstabCorner> <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> <CSS value="border-collapse:collapse"/> <defaultStyles> <defaultStyle refStyle="xt"/> </defaultStyles> </style> <crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order method type" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="2007 %" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="2007 %"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles><dataFormat><percentFormat/></dataFormat></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order method type" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Gross profit" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab></contents> </pageBody> </page> </reportPages> </layout> </layouts> <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-06-09T13:50:33.233Z" output="no"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Order method type" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Order method].[Order method type]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales fact].[Quantity]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Revenue" aggregate="total"><expression>[Sales (query)].[Sales fact].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Gross profit" aggregate="total"><expression>[Sales (query)].[Sales fact].[Gross profit]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="2007 %"><expression>(item(filter([Year],[Year]=2007),0)/item(filter([Year],[Year]=2006),0))-1</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_relationalExpression" value="true" output="no"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterExpression>[Year] in (2006,2007,2008)</filterExpression></detailFilter></detailFilters></query></queries><reportName>test</reportName></report>
cognos…thy mistery of life in a nutshell!
Hi Paul
I have a requirment where I need to highlight difference in data between “one” Cognos report run at different dates in a month. My question is — Can this be achieved only within Cognos? Is there a way we can highlight what data has been changed between the report outputs generated with run on the first of the month and then on the 15th of the same month?
Please let me know if this is possible and kindly let me know if you need more clarity on the requirement which I have.
Thank you for the assistance.
Regards
Anil
I think both BSP and Motio have tools that can do this. If you don’t want to go through them, there are a number of tools available online for comparing PDF and Excel documents. You’ll need to have the reports saved in the content store then compare them manually.
Hi,
Cognos treats any crosstab as if it was built on dimensional data. That’s why you can also use the “measure within set” syntax in crab (and in fact, Cognos produces this syntax when adding aggregates automatically). This is indeed done locally (which is why master detail relationships are so slow with crosstabs). In 10.1.1 it would be wise to use the DQM engine for such reports, to get faster results using the Hybrid Query.
Hi Paul,
Thank you for sharing your discovery!
Though seemingly logical (according to Nimrod’s comment), I doubt if anyone has been using it. I don’t think it is a documented functionality.
However, it really works …
Up to now I used to tell customers the difference between the relational and dimensional reporting style – in term of the dimensional style you can report on selected members.
And opposite, when using relational package, one could make only simple crosstabs, always reporting througout the whole dimension, not possible to filter out some members.
Now, thanks to your technique, I can prepare the underlying data query in the report (including custom joins, unions and aggregations), and make the desired crosstab over it. Such workflow might be similar to many Excel reporters đŸ˜‰
Some examples:
– to create a subset of members:
filter ([Internal Rating Result] , [Internal Rating Result] in ( ‘5A’ , ‘5B’ , ‘5C’ , ‘5D’ , ‘5E’) )
– and then calculate their total:
total (currentMeasure within set [Ratings 5A – 5E] )
The filter function seems to work as a ‘converter’ to dimensional sets:
Example:
if
(item(filter([Report Date], 1=1 ),1) is null)
then (0)
else (item(filter([Report Date], 1=1 ),1) )
–
if
(item(filter([Report Date], 1=1 ),0) is null)
then (0)
else (item(filter([Report Date], 1=1 ),0) )
In this case we can use the filter function with an always-true condition to convert to a dimensional set , and then using Item function pick up a desired member to get the difference (also contains checks for null values).
To set sorts for dimension members we can also set the Override Dimension Info query property to display the Dimension Info and explicitly define dimensions in the crosstab.