Cross-tab Calculation !

[vsudaya]

hi,

Find the attachment.

Please find the fields marked in red. Let’s take an example for “2007/05/30” i.e. “Ist UW Date”.

Here, since I have applied the “aggregated/Total” to get “Sub Total”, each field is getting summarized.

Instead under “1st UW ratio” we need to have the result as: 4/11=.363636, currently it is displaying as 1.33 which is a submission of all the “ratio” for that particular date.

I tried by replacing the “Sub Total” cell with a new “Data Item” by defining the logic but didn’t get any result. Also tried with various combinations in the properties panel and facing the same.

It's quite urgent since we need to move the package to the UAT.

Would appreciate your kind of help.

thanks in advance,
Amrit.

[goose]

Try the following:

1. Make sure the "Aggregate Function" and "Rollup Aggregate Function" properties are set to "Calculated" for 1st UW ratio

2. Change the data item used for the sub total from

total(currentMeasure within detail [data-item])

aggregate(currentMeasure within detail [item-item])

Using aggregate combined with the calculated settings done in step 1 lets congos know you dont want to add up the values

Good Luck!

[vsudaya]

Hi,

We tried with the above approach, but if we change the expression from total to Aggregate, the values are missing for subtotals.

regards
udaya

[goose]

Thats weird, well here is a sample using aggregate function for the GO Sales & Retailers package.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/2.0/" expressionLocale="en-za"><!--RS:8.1-->
<modelPath>/content/package[@name='GO Sales and Retailers']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Order month" aggregate="none"><expression>[gosales_goretailers].[Orders].[Order month]</expression></dataItem><dataItem name="Order year" aggregate="none"><expression>[gosales_goretailers].[Orders].[Order year]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[gosales_goretailers].[Orders].[Quantity]</expression></dataItem><dataItem name="Unit Cost" aggregate="total"><expression>[gosales_goretailers].[Orders].[Unit cost]</expression></dataItem><dataItem name="Unit Cost / Quantity" aggregate="calculated" rollupAggregate="calculated"><expression>[Unit Cost] / [Quantity]</expression></dataItem><dataItem name="Order method" aggregate="none"><expression>[gosales_goretailers].[Orders].[Order method]</expression></dataItem><dataItem name="Aggregate(Order method)"><expression>aggregate(currentMeasure within detail [Order method])</expression></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page1">
<pageBody class="pb">
<contents>
<crosstab class="xt" refQuery="Query1">
<crosstabCorner class="xm"><contents/></crosstabCorner>


<style>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order method" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Aggregate(Order method)" class="il" solveOrder="1"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell class="iv"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Order month" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Unit Cost" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Unit Cost / Quantity" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Order year" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabFactCell class="mv"><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents></crosstabFactCell></crosstab>
</contents>
</pageBody>
<pageHeader class="ph">
<contents>
<block class="ta">
<contents>
<textItem class="tt">
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter class="pf">
<contents>
<table class="tb">
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfDate()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<staticValue>- </staticValue>
</dataSource>
</textItem>
<textItem>
<dataSource>
<reportExpression>PageNumber()</reportExpression>
</dataSource>
</textItem>
<textItem>
<dataSource>
<staticValue> -</staticValue>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfTime()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
</report>

[vsudaya]

hi Miller,

would you mind to be more specific to the XML specification you have mentioned.

I didn't get much from it.

Please shed some more light on the same.

would appreciate your help.

thanks,
amrit.

[goose]

The xml is a report spec for the GO Sales and Retailers sample package that ships with cognos. If you have the samples installed then you can create a new blank report in RS, copy the xml to the clipboard, then in RS select Tools -> Open Report from Clipboard.

You will now have a working sample that uses the aggregate function maybe this will help you come right with your problem.

[vsudaya]

hi Miller,

thanks for your response.

Unfortunately we haven't installed the sample. That's why we are finding bit difficulties in understanding the XML specification that how it really works.

The issue is not yet resolved. we are trying to nail it down.

Please let me know if you come across with certain ideas.


Thanks,
amrit.

[goose]

Good luck! I have never come across your scenario personally but these things usually turn out to be something small hopefully

[MDXpressor]

We tried with the above approach, but if we change the expression from total to Aggregate, the values are missing for subtotals.

Check your Solve Order property for the subtotal members.  If they are not a higher number than the detail members they can cause nulls and incorrect results.