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>
