% of total Crosstab

[kenrisen]

Dear All cognos Gurus...

can we create a crosstab report with a % of total
for example I have 1 column let say amount and i have a total in the below, i want create 1 column to get the value from amount/total amount *100 (please see my attachement)

please help..

[kenrisen]

hi all..

A and A1 is not a hierarchy and my source is a cube.

if i use amount/aggregate(amount within set total)*100 or amount/total *100, i always got 100. it's always divide with the same value.

does someone have any experience to solve this issue?

[kenrisen]

does somebody have experience for this?confused with cognos behavior..

[pricter]

Which is the data item that brings you A and B.

I suppose it is a set of members right?

[kenrisen]

Hi pricter,

Because I use a level item,cognos will give a set of member.

do you have any solution for my issue?

thx

[pricter]

For the calculation of the total you should use the set of the members and not the total

If for example the members A , B came from the data item [Level1]

then you used the following exressiong

Code Select Expand

total([amount] within set [Leve1])

Another option for what are you trying to achieve is to use member summary "percentage"

[kenrisen]

hi Pricter,

When I use : total([Amount] within set [Splitting AOP Level 02])
I got XQE-GEN-0002 error -> An unexpected exception occurred: Java heap space

do you know how to solve this error?

[pricter]

I have no idea about the error that throws to you

Below is a sample report based on great_outdoors_sales_en sample cube

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/7.0/" useStyleVersion="10" expressionLocale="el-gr">
<modelPath>/content/package[@name='great outdoor sales']/model[@name='2012-07-30T13:43:30.093Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItemLevelSet name="Product line"><dmLevel><LUN>[great outdoor sales].[Products].[Products].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel><dmDimension><DUN>[great outdoor sales].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[great outdoor sales].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemLevelSet><dataItemLevelSet name="Year"><dmLevel><LUN>[great outdoor sales].[Years].[Years].[Year]</LUN><itemCaption>Year</itemCaption></dmLevel><dmDimension><DUN>[great outdoor sales].[Years]</DUN><itemCaption>Years</itemCaption></dmDimension><dmHierarchy><HUN>[great outdoor sales].[Years].[Years]</HUN><itemCaption>Years</itemCaption></dmHierarchy></dataItemLevelSet><dataItemMeasure name="Revenue"><dmMember><MUN>[great outdoor sales].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[great outdoor sales].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItemMeasure><dataItem name="Percentage"><expression>percentage ([Revenue] within set [Product line];[Sales region])</expression></dataItem><dataItemDimensionalEdgeSummary solveOrder="1" refDataItem="Year" name="Total(Year)" label="Total" aggregateMethod="aggregate"><dmDimension><DUN>[great outdoor sales].[Years]</DUN><itemCaption>Years</itemCaption></dmDimension><dmHierarchy><HUN>[great outdoor sales].[Years].[Years]</HUN><itemCaption>Years</itemCaption></dmHierarchy></dataItemDimensionalEdgeSummary><dataItemDimensionalEdgeSummary solveOrder="1" refDataItem="Product line" aggregateMethod="total" name="Total(Product line)" label="Total"><dmDimension><DUN>[great outdoor sales].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[great outdoor sales].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemDimensionalEdgeSummary><dataItemLevelSet name="Sales region"><dmLevel><LUN>[great outdoor sales].[Sales regions].[Sales regions].[Sales region]</LUN><itemCaption>Sales region</itemCaption></dmLevel><dmDimension><DUN>[great outdoor sales].[Sales regions]</DUN><itemCaption>Sales regions</itemCaption></dmDimension><dmHierarchy><HUN>[great outdoor sales].[Sales regions].[Sales regions]</HUN><itemCaption>Sales regions</itemCaption></dmHierarchy></dataItemLevelSet><dataItem name="Calc Percent"><expression>[Revenue]/total([Revenue] within set [Product line];[Sales region])</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/></crosstabCorner>


<style>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Sales region" edgeLocation="e9"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><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="e6"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Total(Product line)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Percentage" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Calc Percent" edgeLocation="e10"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Percentage" edgeLocation="e8"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Year)" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Total(Year)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabIntersections><crosstabIntersection row="e9" column="e10"><style><dataFormat><percentFormat decimalSize="2"/></dataFormat></style></crosstabIntersection><crosstabIntersection row="e6" column="e10"><style><dataFormat><percentFormat decimalSize="2"/></dataFormat></style></crosstabIntersection></crosstabIntersections><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></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="true" output="no"/><XMLAttribute name="listSeparator" value=";" output="no"/></XMLAttributes></report>

[RKMI]

Thx for this solution.   8)