Sort of Dynamic Crosstab off a Powercube

[Arsenal]

Hey Guys,

Working on cubes after almost 2 years, so needed some brush up perhaps.  ;D
I searched for similar topics here and found one half solution which I'll mention below when discussing the issue

So, basically the deal is that the cube has about 6 dimensions with around 10 levels each. These 6 dimensions will make up the rows part of the crosstab i.e. the user would like to be presented with 6 different prompts and each of these prompts should display the 10 levels of the dimension it is based on and when the user selects levels the children of those levels should be displayed in the rows of the crosstab. So, the user may select Level 4 from Prompt1 based on Dimension 1 and Level 6 from Prompt4 based on Dimension 4 and the rows should display the children of these levels.

I know I have to create static choices in value prompts for each dimension and there will be 10 choices (with display set to smething like level 1, level 2 etc. and use set to each of those levels within the powercube dimension). But, how should I use a prompt macro to then dispslay the choices made, especially if those choices are coming from different dimensions?

Also, the columns part of the crosstab should be made up of similarly chosen dynamic measures. static choice prompts come up again, but how do I go about the report automatically calculating the number of columns needed to dispklay the chosen measures instead of me having to create a column for each of the measures and then possibly using boolean variables to control rendering. Just trying to think of a more elegant solution.

help will be deeply appreciated

cough

[Arsenal]

Further, is it possible to display each level chosen as a separate row? So for example, if 3 levels from dimension1 were chosen and 2 from Dimension 2, then the report should have 5 rows of level information.
Am open to a list report instead of a crosstab as well.

am getting more and more confused   if at all there will be a solution:-\

[CognosPaul]

You can do quite a lot with macros and render variables.

We'll start with the columns.

For this you really only need a single query item. Call it MeasureSet. The expression should be:

Code Select Expand

set(#promptmany('Measures','memberuniquename','[Cube].[Measures].[DefaultMeasure])#)
When combined with the static choice prompt your users can select which measures they want to see. If they don't choose anything, they'll see whatever is in the third parameter, DefaultMeasure.

The rows are a bit more complicated and I'll need more information before I can help.

When you say that you are expecting 3 levels, how do you want it to be displayed?
These are two of the ways I'm visualizing what the final output might be:

                   |M1|M2|M3|
--------------------------
Hier1Level1        |01|01|01|
Hier1Level2        |01|01|01|
Hier1Level3        |01|01|01|
Hier2Level1        |01|01|01|
Hier2Level2        |01|01|01|


or maybe....


                                      |M1|M2|M3|
------------------------------------------------
Hier1Level1 Total                     |01|01|01|
------------------------------------------------
Hier1Level1  |Hier1Level2 Total       |01|01|01|
             |----------------------------------
             |Hier1Level2 |Hier1Level3|01|01|01|
------------------------------------------------
Hier2Level1 Total                     |01|01|01|
------------------------------------------------
Hier2Level1  |Hier2Level2 |           |01|01|01|

[Arsenal]

First of all Paul, thanks MUCH for your reply. What you said about the measures prompt makes sense. I just didn't know about the promptmany thing  ;)
I am wondering if the default choice measure needs to be there. Sort of looks like it evaluates to an ELSE condition in SQL so I am thinking you can't NOT have it.

Now, for the rows..I think the first option you presented would be the most ideal but with a caveat - somehow I'll need to figure out a way to insert "labels" before the start of each successive row so the users know at which point the row is "breaking" so to speak - i.e., the row is changing from one level to another either in the same dimension or a different dimension so they know which level and which dimension the next row is coming from. So to use your example, some sort of an indicator beween Hier1Level1 and Hier1Level2 and similarly between HierLevel2 and Hierlevel3 and so on

[Arsenal]

Or hmmm, if the measures are in the row section and the dimension(s) level(s) in the column section, then perhaps the column name can be manipulated to have the levelname+category name because the levels in the cube are named according to the dimension in which they occur so then that would inform the users the dimension it is coming from

[Arsenal]

I am trying the method with a static column and dynamic measure row but the log shows an error stating there is no search path....

I am noticing something else in the cube. Aggregated measure seems to be missing for all the levels in dimension except for the source level - which is the key column on the fact side basically. Measure scope though shows yellow for all the levels in each of the dimensions. The allocation tab is greyed out as well which shows that the relationship between dimension and fact that Transformer expects is  there. Really funny. I've never come across such a problem
Any ideas?

Thanks

[CognosPaul]

Before I continue I just want to point out that you're replicating the behavior of Analysis Studio here. You may want to try pushing that before doing any more work on this. 

Now onwards... I just the measure set myself. set(#promptmany('Measures','memberuniquename')#) doesn't seem to work. Replace it with set(#promptmany('Measures','token')#).

The default value is to ensure that it is an optional prompt. Without a default value the report will prompt the user for a value, one that he can't possibly enter. You could leave the prompt macro default blank and use the default value in the value prompt.

The next bit is a bit difficult. I'll reply tomorrow with a solution.

[Arsenal]

That token part did the trick. It works now. THANKS

Figured out why the levels don't have data. Except the source level, I had not used the key column on the fact side to construct the additional levels. So, only the lowest level had data.  Can't believe I forgot that. But I still can't figure out why if I construct the levels with source as the key column from the fact table and label as one of the description columns from the dimension table, the categories don't use the label and the names used are the source value names  :-X

[Arsenal]

Fixed the cube.

For the crosstab, I'm thinking that what if one huge ass value prompt with static choices that includes all levels in all the dimensions was made and then a data item similar to the one for measures was made which grabbed the values from the prompt. And then this data item would be dropped into the crosstab column.
Don't know if that will work

[Arsenal]

man, I'm thinking this can't be done. I've tried every which way..using set, filter and what not. Either the methods don't allow multiple selection from static choice prompt or it the chosen method throws a parsing error when more than 2 levels (either same dimension or different dimensions) are chosen. Seems like I've been trying for the whole day today without any success. IBM support site lists mainly prompt macro methods but they all give the same parsing error issue when applied to this situation.

I am beginning to think that short of making tree prompts off the actual dimensions and having the users select from there directly instead of static choices, this probably can't be achieved  :-\

[CognosPaul]

 :D Impossible you say! :D
Difficult, time consuming and lots of dirty boring work. But it is definitely possible. I'm in a talkative mood, so skip ahead if I start boring you.

Let's think about this in an orderly way.
Goal: a multiselect value prompt that allows you to select specific levels to render. The levels can be from any dimension.

Problems: There are no dimensional functions suitable for this purpose. Set will only work with individual members, and then only from within the same hierarchy. Unions will only work within the same hierarchy, and will only work with two parameters. If you select 3 or more levels you're SOL.  A case switch won't work as that's a relational concept and has no place in a cube. There also needs to be a separator between each level. Also, render variables are not available on individual crosstab nodes.

Therefor: Each level has to go into it's own data item. Since you can't use a render variable you'll have to use style variable with boxtype = none. The only hard part then becomes finding a way of pulling the level from the prompt resultset in such a way as it doesn't try to generate the entire level only for it to be hidden at runtime.

I'll give you a hint and a quick example report xml.

Hint: macro functions grep, split, join, substitute.

Make sure you have the samples installed. The following uses the Go Sales (Analysis) package. This

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="he">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (analysis)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Region"><expression>#

substitute(']''All''',']',
join('',
grep('\[Sales \(analysis\)]\.\[Retailer site]\.\[Retailer site]\.\[Region]',
split(';',
   promptmany('Levels','token')
   )

)) + sq('All'))

#</expression></dataItem><dataItemMeasure name="Quantity"><dmMember><MUN>[Sales (analysis)].[Sales].[Quantity]</MUN><itemCaption>Quantity</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales]</DUN><itemCaption>Sales</itemCaption></dmDimension></dataItemMeasure><dataItem name="Product Line"><expression>#

substitute(']''All''',']',
join('',
grep('\[Sales \(analysis\)]\.\[Product]\.\[Product]\.\[Product line]',
split(';',
   promptmany('Levels','token')
   )

)) + sq('All'))

#

</expression></dataItem><dataItem name="Product type"><expression>#

substitute(']''All''',']',
join('',
grep('\[Sales \(analysis\)]\.\[Product]\.\[Product]\.\[Product type]',
split(';',
   promptmany('Levels','token')
   )

)) + sq('All'))

#

</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>
<selectValue parameter="Levels" required="false" multiSelect="true" selectValueUI="listBox"><selectOptions><selectOption useValue="[Sales (analysis)].[Product].[Product].[Product line]"><displayValue>Product Line</displayValue></selectOption><selectOption useValue="[Sales (analysis)].[Product].[Product].[Product type]"><displayValue>Product Type</displayValue></selectOption><selectOption useValue="[Sales (analysis)].[Retailer site].[Retailer site].[Region]"><displayValue>Region</displayValue></selectOption></selectOptions><defaultSelections><defaultSimpleSelection>[Sales (analysis)].[Retailer site].[Retailer site].[Region]</defaultSimpleSelection></defaultSelections></selectValue><promptButton type="reprompt">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton><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>
<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabSpacer edgeLocation="s1">
<contents>

<textItem><dataSource><staticValue>Region</staticValue></dataSource></textItem></contents>

<style>
<defaultStyles>
<defaultStyle refStyle="xs"/>
</defaultStyles>
</style>
<conditionalStyles><conditionalStyleCases refVariable="renderRegion"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></crosstabSpacer></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Region" edgeLocation="e1"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></factCell><conditionalStyles><conditionalStyleCases refVariable="renderRegion"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles><CSS value="padding-left:10px"/></style></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabSpacer edgeLocation="s3">
<contents>

<textItem><dataSource><staticValue>Product Line</staticValue></dataSource></textItem></contents>

<style>
<defaultStyles>
<defaultStyle refStyle="xs"/>
</defaultStyles>
</style>
<conditionalStyles><conditionalStyleCases refVariable="renderProductLine"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></crosstabSpacer></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product Line" edgeLocation="e3"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></factCell><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles><CSS value="padding-left:10px"/></style><conditionalStyles><conditionalStyleCases refVariable="renderProductLine"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabSpacer edgeLocation="s2">
<contents>

<textItem><dataSource><staticValue>Product Type</staticValue></dataSource></textItem></contents>

<style>
<defaultStyles>
<defaultStyle refStyle="xs"/>
</defaultStyles>
</style>
<conditionalStyles><conditionalStyleCases refVariable="renderProductType"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></crosstabSpacer></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product type" edgeLocation="e4"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></factCell><conditionalStyles><conditionalStyleCases refVariable="renderProductType"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles><CSS value="padding-left:10px"/></style></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabIntersections><crosstabIntersection row="e1" column="e2"><conditionalStyles><conditionalStyleCases refVariable="renderRegion"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></crosstabIntersection><crosstabIntersection row="e3" column="e2"><conditionalStyles><conditionalStyleCases refVariable="renderProductLine"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></crosstabIntersection><crosstabIntersection row="e4" column="e2"><conditionalStyles><conditionalStyleCases refVariable="renderProductType"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></crosstabIntersection><crosstabIntersection row="s1" column="e2"><conditionalStyles><conditionalStyleCases refVariable="renderRegion"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></crosstabIntersection><crosstabIntersection row="s2" column="e2"><conditionalStyles><conditionalStyleCases refVariable="renderProductType"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></crosstabIntersection><crosstabIntersection row="s3" column="e2"><conditionalStyles><conditionalStyleCases refVariable="renderProductLine"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></crosstabIntersection></crosstabIntersections></crosstab>
</contents>
</pageBody>


</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes><reportVariables><reportVariable type="boolean" name="renderRegion">
<reportExpression>ParamValue('Levels') contains '[Sales (analysis)].[Retailer site].[Retailer site].[Region]'</reportExpression>
<variableValues>
<variableValue value="1"/>
</variableValues>
</reportVariable><reportVariable type="boolean" name="renderProductType">
<reportExpression>ParamValue('Levels') contains '[Sales (analysis)].[Product].[Product].[Product type]'</reportExpression>
<variableValues>
<variableValue value="1"/>
</variableValues>
</reportVariable><reportVariable type="boolean" name="renderProductLine">
<reportExpression>ParamValue('Levels') contains '[Sales (analysis)].[Product].[Product].[Product line]'</reportExpression>
<variableValues>
<variableValue value="1"/>
</variableValues>
</reportVariable></reportVariables></report>

It's worth mentioning that this code splits the prompt resultset into an array with a semicolon delimiter. This means you cannot have a semicolon anywhere in the level name!

It's also worth mentioning that this method will also for relational reports.

[Arsenal]

Outstanding! Brilliant!!!! Take tomorrow off, Paul  ;D
I might have to start calling ya MFGF II  ;)

Your "chatiness" actually helped me understand the solution so thanks for it. This was a paticularly complex requirement which without your kind help I would not have been able to solve, mate

I'm thinking that a similar approach using filter, but multiple prompts, might also work. I gotta try that when I have some time

Thanks again

[eliza_jane]

That is a great solution Paul. Could you explain why we would need to concatenate 'All' and substitute the same?

[MFGF]

Outstanding! Brilliant!!!! Take tomorrow off, Paul  ;D
I might have to start calling ya MFGF II  ;)

Steady on!  You surely don't want to offend Paul by likening him to a grumpy, slightly senile old muppet?  ;)

I'm in full agreement with your sentiment though - Paul is a very clever and talented man, and we're very fortunate to have the benefit of his wisdom on this site.

MF.

[CognosPaul]

Eliza, I'll go through the function step by step. It is a bit complicated and took me a while to get it just right.

To begin with we have the output of the promptmany: promptmany('Levels','token')

Code Select Expand

[NS].[Dim].[Hier].[Level];[NS].[Dim].[Hier].[Level2];[NS].[Dim].[Hier2].[Level];[NS].[Dim].[Hier2].[Level2]

The goal of each data item is to find the particular level from that output. The only way to do that is to grep the resultset and for that we need an array:

Code Select Expand

split(';',promptmany('Levels','token'))

This will return the array:

Code Select Expand

[NS].[Dim].[Hier].[Level]
[NS].[Dim].[Hier].[Level2]
[NS].[Dim].[Hier2].[Level]
[NS].[Dim].[Hier2].[Level2]

Now we can do the grep, but grep doesn't like certain characters (().[ and probably a few others) so we have to escape them with \:
grep(
   '\[NS]\.\[Dim]\.\[Hier]\.\[Level]',
   split(';',promptmany('Levels','token'))
)

If the resultset array has that level it will return it as an array. We can join that into a string. Join will take all of the elements of an array and join them as a string with the specified delimiter. In this case we want the delimiter to be nothing:
join(
   '',
   grep(
      '\[NS]\.\[Dim]\.\[Hier]\.\[Level]',
      split(';',promptmany('Levels','token'))
   )
)
And we get [NS].[Dim].[Hier].[Level] Perfect!

BUT! What happens if the resultset doesn't have the level we're looking for? Cognos will return the error "Cannot parse an empty expression" (or something like that). We need a way of saying "if null then add a default value". Sadly at the time of this writing there is no conditional statement in macro functions. So we have to cheat.

We'll take the join result and add 'All' to the end of it. It will now either be:

Code Select Expand

[NS].[Dim].[Hier].[Level]'All' OR

Code Select Expand

'All'

So if the user doesn't select the value we'll get 'All' returned to the data item, but if he does it won't work! So now we need a way of getting rid of that 'All' if there is a value.

We'll substitute out the ]'All' and replace it with ]

Code Select Expand

[NS].[Dim].[Hier].[Level]'All' then becomes

Code Select Expand

[NS].[Dim].[Hier].[Level] and

Code Select Expand

'All' stays the same because the substitute function can't find ]'All'

So the final macro expression is:

#substitute(']''All''',']',
join('',
grep('\[Sales \(analysis\)]\.\[Product]\.\[Product]\.\[Product type]',
split(';',
   promptmany('Levels','token')
   )

)) + sq('All'))#

MF, just call me Waldorf  ;D

[eliza_jane]

That was awesome....Thanks a lot Paul for explaining this in detail..