Version 2 is Coming ;-)

My second baby is about to be born this week.
You won't see much blogging for the next couple of weeks.
When I come back I'll write about our experiences switching the DMR model to OLAP.
We're evaluating both Cognos PowerPlay and MS Analysis Services...

See generated MDX when using DMR model

When you use a DMR model you'd expect that Cognos Report Studio generates MDx expressions, since the framework model is supposed to look just like any other cube from a report author's perspective.
Yet, when you click on a query and select "Geenrated SQL/MDX" from the properties you can only see Native and Cognos SQL.
I've recently learned that there is a way to see MDX...
Click "Tools"/"Show Generated SQL/MDX" and you'll have the option to see Native MDX.
Why you cannot see the same when you go from the properties... ask Cognos.

Another interesting thing is that the generated MDx ends with something like this:
FROM [F:/Cognos8.2\./bin\../temp\dmbTemp_5324_0004_12142423860.dmc]

Looks like the generated SQL will result in a query that gets fetched into a temporary cache (or cube???) that can then process MDX expressions.

ps I tried this on 8.2, I wonder if there are any changes in 8.3

Advanced TreePrompt

I have played around with Treeprompts lately and would like to show you tow different usage of this handy tool.

Treeprompt provides a hierarchical selection tool.
you just point it to the top of the hierarchy tree that you want to select from and it lets the user make selections at any level.


Actually you don't have much control over it, it goes down to the leaf nodes (lowest granularity) whether you want it or not.

e.g. there is no way to "stop" this treeprompt aty the product line level...
it will always allow the selection of products.














I find Treeprompts very useful in two scenarios:

As a replacement for a list of subsequent combo boxes (having "cascading" master-detail relationships.

This provides the user with fine grained selection in an easy way.

The treeprompt return the member selected, so in this case we want to automagically "expand" that member if it's at a higher level to include all lowest level members.

e.g.

user makes a selection at the product item level but we still want to display all "selected" products.


Selection: Cooking Gear, Rope

























We can see what the prompt return by dropping a Text Item, setting it's Source Type to "Report Expression" and setting the Expression to ParamValue('pProduct')
Where pProduct is how I named the prompt's parameter.

This will return:
[Sales].[Product].[Product].[Product type]->[all].[1].[1], [Sales].[Product].[Product].[Product type]->[all].[2].[6]

In a crosstab, summary kind of report we want to see all products that the user selected:

in the crosstab's query we need to get the descendants of every member returned by the prompt at the product level:

union( descendants( set( # join(',', split(';', promptmany('pProduct', 'MUN')) )# ), [Sales].[Product].[Product].[Product]) , emptySet([Sales].[Product].[Product]) )








Charts

If we are dealing with charts treeprompts are also very handy because we can allow the user to have full control over how many series to display in the chart.

In this case we do not want to "expand" the selection.


The expression to use here is more simple:

set( #promptmany('pProduct', 'MUN')#
)