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..
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?
does somebody have experience for this?confused with cognos behavior..
Which is the data item that brings you A and B.
I suppose it is a set of members right?
Hi pricter,
Because I use a level item,cognos will give a set of member.
do you have any solution for my issue?
thx
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
total([amount] within set [Leve1])
Another option for what are you trying to achieve is to use member summary "percentage"
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?
I have no idea about the error that throws to you
Below is a sample report based on great_outdoors_sales_en sample cube
<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>
Thx for this solution. 8)