If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

The FASTEST (peforming) way to nest rows in a crosstab

Started by JGirl, 16 Feb 2011 09:32:57 PM

Previous topic - Next topic

JGirl

Hi All,

Im reporting from a ragged dimension in TM1 with no published levels.  I need to nest 8 levels of a single dimension in the rows, and provide summary lines after the detail (so not in hierarchical order).  Some levels will be filtered by descriptions or MUNs to provide me with asymetric levels of nesting (I'm building financial style reports).  All my aggregates are done in the TM1 cube, so i'm not using any aggregate expressions in my report.  The dimension to be nested consists of a total of 600 members.

So at the moment, ive got 8 different level query items (or rather "set expressions" and 7 different summary line "set expressions".
     [HierarchyRootMember] = set ( item ( rootMembers ([Cube].[Dim1].[Dim1]),0) )
     [L1 Items] = descendants([HierarchyRootMember],1)
     [L2 Items] = descendants([L1 Items],1)
     [L3 Items] = descendants([L2 Items],1)
     ...
     [L8 Items] = descenants([L7 Items,1])


Then to give me the appearance of a summary line after each parent group, i've got for L1-L7
     [L1 Summary] = currentMember (hierarchy ([L1 Items))
     [L2 Summary] = currentMember (hierarchy ([L2 Items))
     ...
     [L7 Summary] = currentMember (hierarchy ([L7 Items))


Finally, I've got a crosstab with nested rows like the following:

     +----------------------------------------------------------------+
     |L1 Items | L2 Items   | L3 Items        | L4 Items              |
     |   *     |   *        |         *       +-----------------------+
     |         |            |                 | L3 Summary Line       |
     |         |            +-----------------------------------------+
     |         |            | L2 Summary Line                         |
     |         +------------------------------------------------------+
     |         | L1 Summary Line                                      |
     +----------------------------------------------------------------+


The cells with the * are then hidden and have labels removed from the crosstab to provide a statement style report in almost reverse-hierarchical order (so the first member of the hierarhcy appears last on the page).

So - The Question Is....
The behaviour and appearance of this is correct at present, but it adds over a minute to the report execution time, so I feel like there must be a better way (or better functions to use) than the ones that I am using.   What are they?

There are other options I'm looking into, such as flags and sort orders (as attributes) in the cube, but for the moment, I'm wondering what is the most efficient way writing the requirements above (ie.  8 levels of nesting)?