How to Provide Column Subtotals

[martinpaul]

Hi Everyone,
I am creating a crosstab report in Report Studio.  The columns on the report have two "levels" (product and year).  At the end of the report, I would like to create column that provides a subtotal for each year; unfortunately, I can only seem to get a total that summarizes ALL of the years.

Here's an example of what I want and what I am actually getting.   (I hope the rudimentary example I'm providing format correctly in the post.)
Any suggestions are appreciated. 
Thank you.

What I Want:
                            Product A                Product B                 Total
                         2009      2008          2009     2008         2009     2008

Customer 1         10         20              30        40             40         60

Customer 2          5          10              15        20             20         30



What I'm Getting:
                             Product A               Product B             Total
                         2009      2008          2009     2008

Customer 1          10         20              30        40             100

Customer 2          5           10              15        20              50

[CognosPaul]

Your crosstab should be set up as follows:


Crosstab |  Product Level |  Crosstab Space add a text item for Total
Corner    |   Year Set      |       Year Set
-----------------------------------------------------------
Customer|    [Measure]
  Level    |

[martinpaul]

Thanks, PaulM for the feedback.  Unfortunately, I neglected to mention that this is a Dimensional report.  So, as I drill down into the report, the values in the Total column on the right side would need to change.  Your solution worked in as much as the totals DID appear, but when I drill down, the total figures in that column didn't change.  (I am drilling down from Product to Subproduct, etc.)
Thanks for the suggestion, though.

[CognosPaul]

That's not a problem. Replace the crosstab space with a query calculation with the following formula

Code Select Expand

total(currentMeasure within set [Product Level])

Set the solve order to 1.

It looks like there's still a problem attaching documents, so the example XML is below. It's using the Go Sales (Analysis) package.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (analysis)']/model[@name='model']</modelPath>

<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1">
<crosstabCorner>
<contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabColumns><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="set(2005, 2006)" edgeLocation="e3"><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><CSS value="text-align:center"/></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="set(2005, 2006)" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="QueryCalculationTotal" edgeLocation="e7"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></factCell><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles><CSS value="text-align:center"/></style></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Retailer type" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><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><queries><query name="Query1"><source><model/></source><selection><dataItemLevelSet name="Product line"><dmDimension><DUN>[Sales (analysis)].[Product]</DUN><itemCaption>Product</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Product].[Product]</HUN><itemCaption>Product</itemCaption></dmHierarchy><dmLevel><LUN>[Sales (analysis)].[Product].[Product].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel></dataItemLevelSet><dataItemLevelSet name="Retailer type"><dmDimension><DUN>[Sales (analysis)].[Retailer]</DUN><itemCaption>Retailer</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Retailer].[Retailer]</HUN><itemCaption>Retailer</itemCaption></dmHierarchy><dmLevel><LUN>[Sales (analysis)].[Retailer].[Retailer].[Retailer type]</LUN><itemCaption>Retailer type</itemCaption></dmLevel></dataItemLevelSet><dataItemMeasure name="Revenue"><dmMember><MUN>[Sales (analysis)].[Sales].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales]</DUN><itemCaption>Sales</itemCaption></dmDimension></dataItemMeasure><dataItemSet name="set(2005, 2006)"><dmMembers><dmMember><MUN>[Sales (analysis)].[Time dimension].[Time dimension].[Year]-&gt;[all].[2005]</MUN><itemCaption>2005</itemCaption></dmMember><dmMember><MUN>[Sales (analysis)].[Time dimension].[Time dimension].[Year]-&gt;[all].[2006]</MUN><itemCaption>2006</itemCaption></dmMember></dmMembers><dmDimension><DUN>[Sales (analysis)].[Time dimension]</DUN><itemCaption>Time dimension</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Time dimension].[Time dimension]</HUN><itemCaption>Time dimension</itemCaption></dmHierarchy></dataItemSet><dataItem name="QueryCalculationTotal" solveOrder="1"><expression>total(currentMeasure within set [Product line])</expression></dataItem></selection></query></queries><drillBehavior drillUpDown="true" modelBasedDrillThru="true"/></report>

[martinpaul]

Hi PaulM,
That worked!  What a great solution!  I went back into my framework model and created a time dimension and then implemented the solution you suggested.  It seems to be working beautifully.  Thank you for taking the time to respond.  Much appreciated!
--Marty