RESOLVED BUT NOT SOLVED: Creating a Calculated Crosstab Member

[adam_mc]

I have a report that looks similar to below (Crosstab over Transformer Cube):
                                                                                                  WTD Measures          MTD Measures ........................
Product Line          Rank Band Group          Rank Band       
A                           A 1 - 100                          A 1 - 20
                                                                     A 51 - 100
                                                         Subtotal A 1 - 100   
                             A 101 - 500                     A 101 - 200
                                                                     A 201 - 300
                                                                     A 301 - 500
                                                         Subtotal A 101 - 500
                             A 501 - 1000                    A 501 - 750
                                                                     A 750 - 1000
                                                         Subtotal A 501- 1000

These levels are all defined within the cube.
There are many more Product Lines, Rank Band Groups, and Rank Bands.
And, the Rank Bands are not the same based on Product Line.

What I need to do is create additional Group Subtotals based on Grouping the Rank Band Groups as follows:

                                                                                                                          WTD Measures          MTD Measures ........................
Product Line    New Subtotal      Rank Band Group          Rank Band       
A                     A 1 - 500              A 1 - 100                          A 1 - 20
                                                                                        A 51 - 100
                                                                            Subtotal A 1 - 100   
                                                   A 101 - 500                 A 101 - 200
                                                                                      A 201 - 300
                                                                                      A 301 - 500
                                                                        Subtotal A 101 - 500

                                                                    New Subtotal A 1 - 500

                      A 501 - 2000        A 501 - 1000                A 501 - 750
                                                                                    A 750 - 1000
                                                                       Subtotal A 501- 1000


I would like to do these in Report Studio rather than in cube generation as this will allow me greater flexibility in creating versions of this reports as Rank Bands will surely change.
The New Subtotals, will be based on either the current member of the Rank Band Group or Rank Band.

I've done this before, but I'm having difficulty recreating.

Any thoughts will be greatly appreciated.
Thanks in advance,
Adam.
             

[CognosPaul]

The normal way of creating calculated members is to define a value (usually using a summary function) and assigning it a key, caption, and a hierarchy.

For example:

Code Select Expand

member(
  total(currentMeasure within set set([RankBandGroupA1-100],[RankBandGroupA101-500]))
  , 'A 1-500'
  , 'A 1-500'
  , [Cube].[Dimension].[Hierarchy]
)

There's no reason for you not to stick a series of those into a set function:

Code Select Expand

set(
member(
  total(currentMeasure within set set([RankBandGroupA1-100],[RankBandGroupA101-500]))
  , 'A 1-500'
  , 'A 1-500'
  , [Cube].[Dimension].[Hierarchy]
)
, member(
  total(currentMeasure within set set([RankBandGroupA501-1000],[RankBandGroupA1001-1500],[RankBandGroupA1501-2000]))
  , 'A 500-2000'
  , 'A 500-2000'
  , [Cube].[Dimension].[Hierarchy]
)
)

In the crosstab you would then stick that new data item between the Product Line and Rank Band Group nodes. Just click on the sigma button with that new node selected to automatically add the total row.

[adam_mc]

Paul...

Thanks for your feedback, but I am still having issues...

I have set the calculation for the "new" calculated member to be:

member(
   aggregate (currentMeasure within set set([A 1-50],[A 51-200],[A 201-1000],[A 1001-3000]))
    , '1-3000'
    , '1-3000'
    , [New TY_LY Stockouts by Rank Band WTD].[Rank Band Hierarchy].[Rank Band Hierarchy]
)

I used aggregate rather than totals as the measures contain %'s.

It is placed in the appropriate location between the Product Line and next to those Rank Band Groups.

I just did this one subtotal to see if I was headed in the right direction.
However, when I run the report it completes, but this section of the report is not included and only the other Rank Band Groups/Rank Bands that don't have a Group Subtotal added show on the report.

I haven't got the summary row added yet as I would expect all the detail to show!

What am I missing?

Thanks in advance,
Adam.

[CognosPaul]

It looks like I need to eat my words. I've been trying a few different variations on this, all without success.

The closest I've gotten to the goal is also my least favorite. By explicitly defining every single group as a separate data item, you can total them and put a crosstab space to the left. Unfortunately the "aggregate" summary function does return null for Revenue but does work correctly on Profit margin.

Report:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/11.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Region" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Retailers].[Retailers].[Region]</expression></dataItem><dataItem name="Revenue" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Measures].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem name="A Group 1" aggregate="none" rollupAggregate="none"><expression>set([sales_and_marketing].[Retailers].[Retailers].[Retailer country]-&gt;:[PC].[@MEMBER].[90002],[sales_and_marketing].[Retailers].[Retailers].[Retailer country]-&gt;:[PC].[@MEMBER].[90001])</expression></dataItem><dataItem solveOrder="1" name="A Group 1 Total" aggregate="none" rollupAggregate="none"><expression>total(currentMeasure within set [A Group 1])</expression></dataItem><dataItem name="A Group 2" aggregate="none" rollupAggregate="none"><expression>set([sales_and_marketing].[Retailers].[Retailers].[Retailer country]-&gt;:[PC].[@MEMBER].[90003],[sales_and_marketing].[Retailers].[Retailers].[Retailer country]-&gt;:[PC].[@MEMBER].[90004])</expression></dataItem><dataItem name="A Group 2 Aggregate" aggregate="none" rollupAggregate="none"><expression>aggregate(currentMeasure within set [A Group 2])</expression></dataItem><dataItem name="Profit margin"><expression>[sales_and_marketing].[Measures].[Profit margin]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></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>


<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="A Group 1" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="A Group 1 Total" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabSpacer edgeLocation="s2">
<contents>
<textItem>
<dataSource>
<staticValue>Group A1</staticValue>
</dataSource>
</textItem>
</contents>

<style>
<defaultStyles>
<defaultStyle refStyle="xs"/>
</defaultStyles>
</style>
</crosstabSpacer></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="A Group 2" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="A Group 2"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="A Group 2 Aggregate" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="A Group 2 Aggregate"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabSpacer edgeLocation="s3">
<contents>
<textItem>
<dataSource>
<staticValue>Group A2 </staticValue>
</dataSource>
</textItem>
</contents>

<style>
<defaultStyles>
<defaultStyle refStyle="xs"/>
</defaultStyles>
</style>
</crosstabSpacer></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Region" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Profit margin" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab>
</contents>
</pageBody>


</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes></report>


It may be worth trying to embed this in the cube creation process. Depending how you build the cube, it shouldn't be too hard. Maybe another table that has Rank Band and Rank Band Group? Updating the list would be a simple update: "update table set table.rankBandGroup = 'Another value' where table.rankBand='ABC'"

[adam_mc]

Paul....

Thanks for your feedback!

As much as I don't want to, I think I'm just going to have to add these directly to the Cube.
It'll make things easier for this report, but I worry about the long term maintenance as I know our business community changes how to measure things so often.
But, I guess, that's why I have a job!

Thanks again,
Adam.