Conditionally style left edge of dimensional crosstab (SOLVED)

[Corrigon]

Hi there, I have a crosstab based on a cube, and am working in report Studio 10.1.1. The left edge of the crosstab is based on the 2nd level of a three level hierarchy (Grandparents, Parents, Customers). So it displays the 'Parents' level in a data-item called 'Parents' which also contains some ordering and filtering expressions.

Drill-down is enabled in the report. When the report is run, the Parent level members are displayed. The User can drill-down into the Parent, and see the children (subsidiary Customers). Every Parent has at least one child member Customer (itself). Some also have many children (other Customers).

I want to conditionally format the Parents data item, so that if the Parent has more than one child Customer, then the cell will display differently, so that the user knows that there are subsidiary Customers to drill-down on and view.

I have been tinkering with a Measure [ChildMeasure] in the crosstab with Box Type set to None (so it is hidden)...

Code Select Expand

count(1 within set descendants(currentMember([Cube].[Hierarchy].[GP - P - C]), 1))

...and trying to format the Parents data item on that, but have had no success. I set the Conditional Style to ChildMeasure > 1 but when I run the report nothing happens, so obviously my approach is wrong.

Does anyone know how this could be accomplished?

Thanks in advance,
Dave

[MFGF]

Hi there, I have a crosstab based on a cube, and am working in report Studio 10.1.1. The left edge of the crosstab is based on the 2nd level of a three level hierarchy (Grandparents, Parents, Customers). So it displays the 'Parents' level in a data-item called 'Parents' which also contains some ordering and filtering expressions.

Drill-down is enabled in the report. When the report is run, the Parent level members are displayed. The User can drill-down into the Parent, and see the children (subsidiary Customers). Every Parent has at least one child member Customer (itself). Some also have many children (other Customers).

I want to conditionally format the Parents data item, so that if the Parent has more than one child Customer, then the cell will display differently, so that the user knows that there are subsidiary Customers to drill-down on and view.

I have been tinkering with a Measure [ChildMeasure] in the crosstab with Box Type set to None (so it is hidden)...

Code Select Expand

count(1 within set descendants(currentMember([Cube].[Hierarchy].[GP - P - C]), 1))

...and trying to format the Parents data item on that, but have had no success. I set the Conditional Style to ChildMeasure > 1 but when I run the report nothing happens, so obviously my approach is wrong.

Does anyone know how this could be accomplished?

Thanks in advance,
Dave

Hi,

That seems like the right approach, although you wouldn't need to hide [ChildMeasure]. Instead, remove it from your crosstab, select the row edge set, and in the Properties property, check ChildMeasure. You should then be able to define an Advanced Conditional Style for the row edge members based on values of ChildMeasure.

Here's a simple example based on the great_outdoors_sales_en powercube:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/12.0/" useStyleVersion="10" expressionLocale="en-gb">
<modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;PowerPlay&apos;]/package[@name=&apos;great_outdoors_sales_en&apos;]/model[@name=&apos;2010-06-18T15:38:44.109Z&apos;]</modelPath>
<drillBehavior/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItemLevelSet name="Year"><dmLevel><LUN>[great_outdoors_sales_en].[Years].[Years].[Year]</LUN><itemCaption>Year</itemCaption></dmLevel><dmDimension><DUN>[great_outdoors_sales_en].[Years]</DUN><itemCaption>Years</itemCaption></dmDimension><dmHierarchy><HUN>[great_outdoors_sales_en].[Years].[Years]</HUN><itemCaption>Years</itemCaption></dmHierarchy></dataItemLevelSet><dataItemMeasure name="Revenue"><dmMember><MUN>[great_outdoors_sales_en].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[great_outdoors_sales_en].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="9"/></XMLAttributes></dataItemMeasure><dataItemLevelSet name="Product line"><dmLevel><LUN>[great_outdoors_sales_en].[Products].[Products].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel><dmDimension><DUN>[great_outdoors_sales_en].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[great_outdoors_sales_en].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemLevelSet><dataItem name="NoOfChildren"><expression>count(1 within set children(currentMember([great_outdoors_sales_en].[Products].[Products])))</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>
<crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Revenue"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><propertyList><propertyItem refDataItem="NoOfChildren"/></propertyList><conditionalStyleRefs><conditionalStyleRef refConditionalStyle="Conditional Style 1"/></conditionalStyleRefs></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></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 output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=","/><XMLAttribute output="no" name="RS_modelModificationTime" value="2010-06-18T19:38:44.127Z"/></XMLAttributes><namedConditionalStyles><advancedConditionalStyle name="Conditional Style 1"><styleCases><styleCase><style><defaultStyles><defaultStyle refStyle="pd_4"/></defaultStyles></style><reportCondition>[Query1].[NoOfChildren] &lt; 4</reportCondition></styleCase><styleCase><style><defaultStyles><defaultStyle refStyle="pd_2"/></defaultStyles></style><reportCondition>[Query1].[NoOfChildren] &gt; 4</reportCondition></styleCase></styleCases><styleDefault/></advancedConditionalStyle></namedConditionalStyles></report>

Cheers!

MF.

[Corrigon]

MFGF - it worked exactly as you said it would. Outstanding!

Thank you very much!