Cognos promptmany and SSAS member unique name

When you want to supply prompt values to a Cognos Report Studio report via URL (or for any other reason it is only possible to pass through values as plain text ID's and not member unique names) dealing with the parameters might not be as straight forward as it seems in the beginning. As an example let us use CRM application that links customer ID to a sales report in Cognos BI. That means we would be running the Cognos report via a url that looks similar to this:

http://servername.yourcompany.com/ibmcognos/......&p_CustomerID=302156



It is quite an easy task to get this working with a prompt macro inside Report Studio when the prompt is single-select. All we would need to do is create a Data Item that looks similar to this:

#'[Cube].[Dimension].[Hierarchy].[Level]->:[M8].[[Dimension]].[Hierarchy]].[Level]].&[' + prompt('CustomerID', 'token') + ']]]'#

Here is what happens in this macro. Value of prompt CustomerID is received from the URL and concatenated with the preceding string and the succeeding square brackets. After parsing the macro this especially becomes the same as if our Data Item would contain only the MUN value without any quotes around it, like this:

[Cube].[Dimension].[Hierarchy].[Level]->:[M8].[[Dimension]].[Hierarchy]].[Level]].&[302156]]]

I should note that your MUN pattern might be different. It depends on which version of SSAS you are running. Just check one sample from within Report Studio to see that you are doing the right thing (i.e. in the model browser, navigate to any one of the customers, right-click, choose properties and there amongst all the other things you will find member unique name).

So, that solves the problem with a prompt that can not have multiple values submitted to it. When we want to submit multiple values to the prompt things get a bit more clumsy. Our URL looks like this (to pass multiple parameter values, just repeat the parameter name before each value):

http://servername.yourcompany.com/ibmcognos/......&p_CustomerID=302156&p_CustomerID=400002

To handle this we need a Data Item with a macro which looks really ugly:

#'set(' + 
csv(
substitute('$' , ']]]', 
substitute('^', '[Cube].[Dimension].[Hierarchy].[Level]->:[M8].[[Dimension]].[Hierarchy]].[Level]].&[', 
array(
split(';', 
promptmany('CustomerID', 'token', ' ', '')
)
)
)
)
, ',', '')
 + ')'#

promptmany allows multiple values to be passed on to the parameter. All of them are held in the parameter as one string, so we use split function to make them separate items. Use array to put them into an array. Then substitute the beginning of each value withing the array with the preceding MUN text and the succeeding square brackets. Then, convert the array into a csv list that is separated with commas (,) and has no quotes around each value (that is done by , ',', '') one line before the end of macro). Finally we wrap the whole thing into a set.

After parsing the two CustomerID values that we just passed via URL, our Data Item will look like this:

set([Cube].[Dimension].[Hierarchy].[Level]->:[M8].[[Dimension]].[Hierarchy]].[Level]].&[302156]]],[Cube].[Dimension].[Hierarchy].[Level]->:[M8].[[Dimension]].[Hierarchy]].[Level]].&[400002]]])

In other words, by using promptmany macro this Data Item becomes a nice set which we can use in any way we want to- generate crostab rows, slice with or anything else you might come up with for displaying information in a Cognos report that is based on SSAS cube.

Do you know of a smoother way to handle this?

No comments:

Post a Comment