Crosstab total issue

[ravindravoggu]

Hi All,

I have a cross tab report and using dimensional data. Iam taking currencies hierarchy in the column, it will display columns like AR, AL, BR,CA,EU,GB,Bl,US etc... I have rows like Income Local, Income US, Income ex US only, Income Local Current Month, Variance, %Variance.
Variance=Income ex US only - Income Local Current Month. Below is the output format.

                                                   AR      AL     BR      CA   EU   GB    Bl     US     total(currencies)
Income Local
Income US
Income ex US only
Income Local Current Month
Variance
%Variance

calculation for total(currencies) = total(currentMeasure within set[currencies]). When I run the report it is adding all the currencies.but my user don't want to display the amounts for USD column and variance row(intersection of USD and Variance). I am hiding this value by keeping foreground color as white as it is not taking any condition to make zero . So it is adding up to total but it should not  as per our requirement. Is there any way to achieve this please let me know.

Thanks in Advance.

Ravindra.

[Gollapudi]

Instead of changing foreground color, why don't you make that box type none.

[ravindravoggu]

Hi Gollapudi,

If we make boxtype none the next cell will be moved to left so there will be another value in place of USD rite.
Thanks .

Regards,
Ravindra..

[CognosPaul]

This is certainly doable. The solution I found is not the best, but it does work. It's a little bit complicated, so let me know if you have any problems.

Step 1.
Create a new query item. Call it total(currencies) ex US. The code should be

Code Select Expand

total(currentMeasure within set except([currencies],[US.MEMBER])) Drag that to the right of total(currencies).
Step 2.
Select the header and all of the intersections of that new column, except for the income ex us only, and set box type to none.
Step 3.
click on the intersection of total(currencies) and income ex us only and set box type to none.

This will give you the correct results for the total column, but the data is still in the US column. You can hide it by changing the color, but it still being rendered.

Step 1. Unlock the report. Click on the intersection of 'US' and 'Income ex US only' and set Define Contents to Yes. Drag in a text item, leave it blank, set the text source to Cell Value.
Step 2. Create a bool variable called NotUS. The code should be (assuming your query is query1)

Code Select Expand

[query1].[currencies]<>'US'

Step 3. use that variable as a render variable on the text item in the intersection.


Below is the xml against the sales and marketing cube.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="he">
<modelPath>/content/package[@name='Sales and Marketing']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItemLevelSet name="Product line"><dmDimension><DUN>[Sales and Marketing].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[Sales and Marketing].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy><dmLevel><LUN>[Sales and Marketing].[Products].[Products].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel></dataItemLevelSet><dataItemCalculatedMember name="Total(Product line)" solveOrder="1"><expression>total(currentMeasure within set [Product line])</expression><dmDimension><DUN>[Sales and Marketing].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[Sales and Marketing].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemCalculatedMember><dataItemMember name="Asia Pacific"><dmMember><MUN>[Sales and Marketing].[Retailers].[Retailers].[Region]-&gt;:[PC].[@MEMBER].[740]</MUN><itemCaption>Asia Pacific</itemCaption></dmMember><dmDimension><DUN>[Sales and Marketing].[Retailers]</DUN><itemCaption>Retailers</itemCaption></dmDimension><dmHierarchy><HUN>[Sales and Marketing].[Retailers].[Retailers]</HUN><itemCaption>Retailers</itemCaption></dmHierarchy></dataItemMember><dataItemMember name="Sales visit"><dmMember><MUN>[Sales and Marketing].[Order method].[Order method].[Order method type]-&gt;:[PC].[@MEMBER].[606]</MUN><itemCaption>Sales visit</itemCaption></dmMember><dmDimension><DUN>[Sales and Marketing].[Order method]</DUN><itemCaption>Order method</itemCaption></dmDimension><dmHierarchy><HUN>[Sales and Marketing].[Order method].[Order method]</HUN><itemCaption>Order method</itemCaption></dmHierarchy></dataItemMember><dataItemMember name="Special"><dmMember><MUN>[Sales and Marketing].[Order method].[Order method].[Order method type]-&gt;:[PC].[@MEMBER].[607]</MUN><itemCaption>Special</itemCaption></dmMember><dmDimension><DUN>[Sales and Marketing].[Order method]</DUN><itemCaption>Order method</itemCaption></dmDimension><dmHierarchy><HUN>[Sales and Marketing].[Order method].[Order method]</HUN><itemCaption>Order method</itemCaption></dmHierarchy></dataItemMember><dataItemMeasure name="Revenue"><dmMember><MUN>[Sales and Marketing].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[Sales and Marketing].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension></dataItemMeasure><dataItemCalculatedMember name="Total(Product line) - no mountain" solveOrder="1"><expression>total(currentMeasure within set except([Product line],[Sales and Marketing].[Products].[Products].[Product line]-&gt;:[PC].[@MEMBER].[992]))</expression><dmDimension><DUN>[Sales and Marketing].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[Sales and Marketing].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemCalculatedMember></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>


<style>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Product line)" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Product line) - no mountain" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles><CSS value="display:none"/></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Asia Pacific" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Sales visit" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Special" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabIntersections><crosstabIntersection row="e4" column="e1"><contents><textItem><dataSource><cellValue/></dataSource><conditionalRender refVariable="NotSalesandMountaineering"><renderFor refVariableValue="1"/></conditionalRender></textItem></contents></crosstabIntersection><crosstabIntersection row="e4" column="e2"><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><CSS value="display:none"/></style></crosstabIntersection><crosstabIntersection row="e3" column="e6"><style><CSS value="display:none"/></style></crosstabIntersection><crosstabIntersection row="e4" column="e6"><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></crosstabIntersection><crosstabIntersection row="e5" column="e6"><style><CSS value="display:none"/></style></crosstabIntersection></crosstabIntersections><defaultMeasure refDataItem="Revenue"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab>
</contents>
</pageBody>


</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes><reportVariables><reportVariable type="boolean" name="NotSalesandMountaineering">
<reportExpression>[Query1].[Product line]&lt;&gt;'Mountaineering Equipment'</reportExpression>
<variableValues>
<variableValue value="1"/>
</variableValues>
</reportVariable></reportVariables></report>