How to use the "Range Prompt" - some undocumented features

The TextField prompt has an interesting feature: it can be used to get two values instead of one by setting its ``Range'' property to YES.

This is handy when you want to filter by a range that has a lower and/or an upper value.

The extra that the prompt gives you is that you can have open intervals ie. cases where the user only specifies and upper or a lower value.

e.g. filter lines where some ratio is

• less than 80%
• beteen 60% and 80%
• greater than 80%

It saves you from having to do some sort of javascript voodoo with radio buttons etc.

The trick is that you want to set the prompt as optional (= not required) because otherwise it would be just two textfields, which is not any better than using two textfield from the first place.

Now... since the prompt is optional the user does not have to input values.
If the user leaves both the lower and the upper values unspecified... the prompt does not return anything.
And this is a problem...
...because you are trying to use the prompt in an expression using the in_range operator that looks something like this

filter(my_dimension, my_measure in_range ?pRange?)

and this blows up!
After the prompt is evalued it becomes the following expression:

filter(my_dimension, my_measure in_range )

...that's right, the prompt did not return absolutely anything

To fix this you need to use the long form of the prompt macro and set the datatype to ``range''.
This is undocumented as of 8.3 ... but it works

to fix the expression specify a default range that the prompt should return when the user left both lower and upper values unspecified. e.g. can be an open range starting with 0

filter(my_dimension, my_measure in_range #prompt('pRange', 'range', '{0:}')#)

[updated] Relative Package Names

It is possible to have a report be built on a package that is specified with a relative (to the report) location.
It is not possible to set relative package names through the UI in ReportStudio but you can copy the XML source into an editor, make the changes and paste it back to RS.
The reference to the package is right at the beginning of the XML in the <modelPath> tag.
I tested with relative path on 8.3 and it worked.

This is great for keeping multiple environments on the same Cognos box.
The report age can be the same. There is no need to relink the report with a different package. The package can be redeployed into different folders (new feature in 8.3) with the same name. The packages would be practically the same only they would use different datasources. Changing the datasource is easy... just run a sed script on the model's XML and publish.
If you need to maintain a lot of environments eg. QA, staging, production, support on the same Cognos then you will find this very useful!

Update
Damn, it doesn't work. Report Studio axccepts the relative path but before saving the report it substitutes it with an absolute path.
the good news is that in 8.3 you can relink a report to another package through the portal by setting the properties... there's no need to open up the report in RS.

Crosstabs with Column Headers

aka How to make Crosstabs that look like lists

                     | sales
------+--------------+---------
branch| sales person | <1234>
      +--------------+---------
      | sales person | <1234>

will look like

                     | sales 
-----------+---------+--------
1st Avenue | Aaron A | 1,000
           +---------+--------
           | Betty B | 2,000
-----------+---------+--------
2nd Street | Clare C | 1,500 
...

If it was a list it would have column headers and would look like this:

branch     | sales person | sales 
-----------+--------------+--------
1st Avenue | Aaron A      | 1,000
           +--------------+--------
           | Betty B      | 2,000
-----------+--------------+--------
2nd Street | Clare C      | 1,500 
...

To achieve this using a crosstab you need to add extra Text Items and hide some cells by setting their box type to ``none''

    HIDE             | "branch" | "sales person" | sales
---------------------+----------+----------------+------
branch| sales person |  HIDE    |   HIDE         | <1234>
      +--------------+----------+----------------+-------
      | sales person |  HIDE    |   HIDE         | <1234>

Hiding the top left corner will pull the extra Text Items to the left so that the "headers" will nicely align with their columns.
But adding these new text item still creates extra empty columns in the crosstab... these have to be hidden as well.

The resulting crosstab will look just like a list, displaying column headers.

You may need to play around with styling it to get everything in the right color....

Difference in dimensional expressions between RS8.2 and 8.3 - part two

I found another one

code in 8.2

set( emptySet([My Dimension].[My Hierarchy]) )

code in 8.3

set( item(emptySet([My Dimension].[My Hierarchy]),0) )

without the use of item() you get an error
Invalid coercion from 'memberSet' to 'member, measure'
even though the expression validates.

This is important when you want to create prompts that return MUNs but are optional.
As a recap from my older posts optional for a prompt means that it has a default value. The default is ... no members selected
...in other words: empty set

Difference in dimensional expressions between RS8.2 and 8.3

We are just upgrading to Cognos 8.3 (I know, I know...) and I've just found this difference in the use of the filter() function:


I think it actually makes more sense in 8.3... probably that's why they fixed it.

Code in 8.2

filter(
[My Dimension].[My Hierarchy].[My Level],
roleValue('_businessKey', currentMember([My Dimension].[My Hierarchy])) contains ?Param1?
)


Code in 8.3

filter(
[My Dimension].[My Hierarchy].[My Level],
roleValue('_businessKey', [My Dimension].[My Hierarchy].[My Level]) contains ?Param1?
)

In 8.2 one had to use the currentMember() function to refer to the member being filtered.
In 8.3 it is not needed. Instead one can just use the level.

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