level spanning plus striping for cross tab using DMR fuctions against cube

[xyz]

Hi All,

I have requirement where I need to use a cross tab against cube data source using DMR functions, where I need to span levels and also apply striping. I need a help on this as I am not much aware how the cross tab works against DMR functions.

Sample below
                                                                                                         Columns
Rows                                                                                               Task  Count
Country            State           Region        Sunday Monday Tuesday Wednesday Thursday Friday Saturday Total
X                       Y                 Z                      1        5                                              1                                      7
                                            B
                         C                 A
                                            D
Total                                                            1         5                                              1                                      7


1) How apply striping for the above cross tab, If I try to apply striping  using a variable then it is getting applied on the Total summary row as well, how to avoid it?

2) If I want the rows to be repeated for state to be repeated for region and country should be repeated for state, then if it is a list then we have 'Group Span' property option to change. But for cross tab I tried using concatenating the country plus state plus region and then add in front of country row and then hide, if I do that all null values are getting suppressed, how to achieve the same, without the nulls getting suppressed?

Thanks & Regards,
Xyz

[Francis aka khayman]

what is striping? -- isn't that the thing with a stage, lights... a little dark around? sorry :( can't help it

[CognosPaul]

Crosstabs behave better with dimensional functions than lists. You also have a much finer grain of control over the appearance.

By striping I assume you want alternating rows highlighted. Do you want highlighting on each individual row, or do you want alternating highlighting on a higher level, like state or country?

If it's only on alternating on the region, then it's a simple matter of setting a style variable on the region fact cells. Right click on the region node, select member fact cells, and apply the style using the expression:
mod(RowNumber(),2)=1

[xyz]

Hi Khayman and Paul,

Thanks for reply. Basically the cross tab should look like a list with alternate individual rows with different colors as well as level spanning of cross tab rows especially Country, State and Region to look like a list report. Please find the cross tab sample output and list sample output as expected.

Cross tab sample output below:

Sample below
                                                                                                         Columns
Rows                                                                                               Task  Count
Country            State           Region        Sunday Monday Tuesday Wednesday Thursday Friday Saturday Total
X                       Y                 Z                      1        5                                              1                                      7
                                            B
E                       C                 A
                                            D
Total                                                            1         5                                              1                                      7

Cross tab should look like a list, with expected output below.
                                                                                                         Task  Count
Country            State           Region        Sunday Monday Tuesday Wednesday Thursday Friday Saturday Total
X                       Y                 Z                      1        5                                              1                                      7
X                       Y                 B
E                       C                 A
E                       C                 D
Total                                                            1         5                                              1                                      7


Khayman - Basically striping is highlighting the alternate rows of data with two different colors, my requirement is I wan to highlight each individual row in the cross tab.

Paul - I am able to apply striping on alternate rows in cross tab, as the cross tab rows Country, State and Region has data grouped, striping is not getting applied properly on the cross tab rows.  I want striping in cross tab rows to be applied like list even to cross tab rows, in order to do that level spanning has to be applied on cross tab rows, there is no such property available for cross tab, but for cross tab I tried using concatenating the Country plus State plus Region and then add in front of Country row and then hide, if I do that all null values are getting suppressed, how to achieve the same, without the nulls getting suppressed?how will I go about it?


Thanks & Regards,
Xyz

[CognosPaul]

You're working too hard on this. Don't do any concatentation - or even any work on the query. This is an issue with presentation, so the solution should be in the presentation.

Build the crosstab as normal, nesting region in state in country. Right click on region, and select member fact cells. Set up an advanced conditional format with Mod(RowNumber(),2)=0. Make that gray. That will ensure that every row in the context of region (thus not including total rows) will be part of the style calculation.

Drag in three crosstab spaces to the left of the columns, and set Define Contents to yes. Unlock the report and drag in a layout calculation into the intersection, referencing the correct column.

Once that's done, select the crosstab rows (click on any of the nodes and press the ancestor button) and set the boxtype to none. Do the same to the corner.

Take a look at the attached report

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="he">
<modelPath>/content/folder[@name='Samples']/package[@name='sales and marketing']/model[@name='2013-05-02T10:57:02.761Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales and marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Product line" aggregate="none" rollupAggregate="none"><expression>[sales and marketing].[Products].[Products].[Product line]</expression></dataItem><dataItem name="Revenue"><expression>[sales and marketing].[Measures].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem name="Product type" aggregate="none" rollupAggregate="none"><expression>[sales and marketing].[Products].[Products].[Product type]</expression></dataItem><dataItem solveOrder="1" name="Summary(Product type)" aggregate="none" rollupAggregate="none"><expression>aggregate(currentMeasure within detail [Product type])</expression></dataItem><dataItem solveOrder="1" name="Summary(Product line)" aggregate="none" rollupAggregate="none"><expression>aggregate(currentMeasure within detail [Product line])</expression></dataItem><dataItem solveOrder="1" name="Summary(Year)" aggregate="none" rollupAggregate="none"><expression>aggregate(currentMeasure within detail [Year])</expression></dataItem></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">
<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></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>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product type" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><conditionalStyleRefs><conditionalStyleRef refConditionalStyle="Mod 2"/></conditionalStyleRefs></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Summary(Product type)" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="il"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Summary(Product type)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="iv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Summary(Product line)" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Summary(Product line)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><defaultMeasure refDataItem="Revenue"/><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabSpacer edgeLocation="s1">
<contents>
<textItem>
<dataSource>
<staticValue>Product Line </staticValue>
</dataSource>
</textItem>
</contents>
<factCell><contents><textItem><dataSource><reportExpression>[Query1].[Product line]</reportExpression></dataSource></textItem></contents><style><CSS value="text-align:left"/></style></factCell>
<style>
<defaultStyles>
<defaultStyle refStyle="xs"/>
</defaultStyles>
</style>
</crosstabSpacer></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabSpacer edgeLocation="s2">
<contents>
<textItem>
<dataSource>
<staticValue>Product Type </staticValue>
</dataSource>
</textItem>
</contents>
<factCell><contents><textItem><dataSource><reportExpression>[Query1].[Product type]</reportExpression></dataSource></textItem></contents><style><CSS value="text-align:left"/></style></factCell>
<style>
<defaultStyles>
<defaultStyle refStyle="xs"/>
</defaultStyles>
</style>
</crosstabSpacer></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Summary(Year)" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Summary(Year)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></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="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2013-05-02T10:57:02.807Z" output="no"/></XMLAttributes><namedConditionalStyles><advancedConditionalStyle name="Mod 2"><styleCases><styleCase><style><CSS value="background-color:silver"/></style><reportCondition>Mod(RowNumber(),2)=0</reportCondition></styleCase></styleCases><styleDefault/></advancedConditionalStyle></namedConditionalStyles></report>

[xyz]

Paul, you are awesome man!!!!!

Your solution worked like a piece of cake.

I have one query on top of that, actually I am displaying 'Day of Week' attribute, nested under 'Task Count' in cross tab columns. 'Day of Week' attribute is present under Date level, which is in Date Hierarchy.

Problem here I am facing is, I have a date prompt which displays Weeks, which is a multi select, If I select a single week, 'Day of Week' attribute is displaying days of week only once starting from Sunday to Saturday. If I select more than one week in date prompt then days of weeks are displaying more than once in cross tab columns. How will I make sure, even though I do multi select of weeks, days of week should display distinct or unique?

Your help will be much appreciated  :)

Thanks & Regards,
Xyz

[Francis aka khayman]

This might help... take note of the warning in the last sentence though.

http://cognosm.fm3online.com/dimensional/stripping-dimensionality-of-a-member/

[CognosPaul]

The best solution would be to create a day of week hierarchy. That way you can still slice by a range of weeks, but show aggregated by day of week.

[xyz]

Thank you very much for prompt reply Khayman and Paul.

Khayman - The link provided by you is not working!!! I guess the link will help me in setting up striping, which I have done it. Thanks for prompt reply.

Paul - Thank you very much for the suggestion.


Thanks & Regards,
Abdul

[Francis aka khayman]

its working for me... try this url:  cognosm.fm3online.com

the link is not about striping. its about the week getting displayed twice.

Thank you very much for prompt reply Khayman and Paul.

Khayman - The link provided by you is not working!!! I guess the link will help me in setting up striping, which I have done it. Thanks for prompt reply.

Paul - Thank you very much for the suggestion.


Thanks & Regards,
Abdul