Parameter Map syntax

[cognostechie]

I am trying to use a parameter map a little more than what it is normally used for so I need some help in the correct syntax.

The purpose is to prompt the user to provide a measure name and the parameter map will contain the MUN for that measure so that instead of creating one calculated column for every condition, I want to create a dynamic calculation that will work for every measure. As a first step, I created a column as follows and it is working:

If
(    [Business Layer].[Time Dimension].[Date] >= .... and [Business Layer].[Time Dimension].[Date] <= .... )
then
(
    Case
              when #prompt('Measure Name','string')# = 'Order Amt' then [Business Layer].[Orders].[Order Amt]
              when #prompt('Measure Name','string')# = 'Quantity'   then [Business Layer].[Orders].[Quantity]
    Else (0)
    End
)
Else (0)

Time dimension has an inner join with all Fact query subjects so this is working fine and it is being used to determine the value for a certain date range (let's say Month To Date). The user types 'Quantity' so this column provides result for MTD Quantity. If the user types 'Order Amt' then this gives result for MTD Order Amt.

Now if there are 200 measures, I don't want to type in 200 conditions in the case statement so the idea is to create a parameter map whch will have
2 columns as :

Quantity               [Business Layer].[Orders].[Quantity]
Order Amt            [Business Layer].[Orders].[Order Amt]

That way the user will just type Quantity and the parameter map will return the MUN for quantity.

So I tried this but it doesn't like the syntax:

If
(    [Business Layer].[Time Dimension].[Date] >= .... and [Business Layer].[Time Dimension].[Date] <= .... )
then
#$[Relative Time]{#prompt('Measure Name','string')#}#
Else (0)

What am I doing wrong?  Does it need 'sq' or some other function? I can also use a DB table instead of a parameter map if somebody
can come up with a solution.

[CognosPaul]

The # symbol delineates the boundaries of the macro. Once the macro marker is placed, you can use any macro function without needing to specify they are macros.

#$[Relative Time]{prompt('Measure Name','string')}#

Generally I try to use a single word for parameter maps. $relativeTime{}. Also in this case you can bypass the entire mechanism using token prompts.

Assuming the field name only is used:
#'[Business Layer].[Orders].['+prompt('Measure Name','token')+']'#

Or you can use a value prompt and hold the entire path:
#prompt('Measure','token')#

Display                 Use
Quantity               [Business Layer].[Orders].[Quantity]
Order Amt            [Business Layer].[Orders].[Order Amt]


I've done something similar before where I've built a dictionary table that describes all the structure of the tables. Table Name, Framework Field Path, Display Name, Field Type. I use that to populate a value prompt, and the parameter simply feeds the token prompt. This way I can quickly add or remove user-selectable fields from the report without actually having to go into it.

[navissar]

Paul's always right.
I would advocate for the macro prompt option Paul suggested, for two reasons:
1. It's less work. If you want to add/remove a measure, all you need to do s add/remove it from the prompt, plus you don't have to build the entire parameter map to begin with. Less work means better value for money for the customer.
2. Parameter map lookup is done locally. When the parameter map is big, it takes a noticeable toll on performance.

[cognostechie]

The # symbol delineates the boundaries of the macro. Once the macro marker is placed, you can use any macro function without needing to specify they are macros.

#$[Relative Time]{prompt('Measure Name','string')}#

Generally I try to use a single word for parameter maps. $relativeTime{}. Also in this case you can bypass the entire mechanism using token prompts.

Assuming the field name only is used:
#'[Business Layer].[Orders].['+prompt('Measure Name','token')+']'#

Or you can use a value prompt and hold the entire path:
#prompt('Measure','token')#

Display                 Use
Quantity               [Business Layer].[Orders].[Quantity]
Order Amt            [Business Layer].[Orders].[Order Amt]


I've done something similar before where I've built a dictionary table that describes all the structure of the tables. Table Name, Framework Field Path, Display Name, Field Type. I use that to populate a value prompt, and the parameter simply feeds the token prompt. This way I can quickly add or remove user-selectable fields from the report without actually having to go into it.

Thanks a lot Paul. I knew I was missing something ( the # indicates the beginning of the macro and another # is not required for another macro because the 2nd # will mean end of macro.) However I tried  #$[RelativeTime]{prompt('Measure Name','string')}#  and it didn;t like the syntax. I also named the parameter map as RelativeTime as one word but it still doesn't like the syntax.

I am still trying..

I forgot to mention that my requirement is for ad-hoc reporting. I had thought of a prompt with use value and display value but that is ony possible in report Studio.

[CognosPaul]

Try without brackets:

#$RelativeTime{prompt('Measure Name','string')}#

And if that doesn't work, try using a token prompt instead:

#$RelativeTime{prompt('Measure Name','token')}#

[cognostechie]

I tried both, no luck !

[CognosPaul]

You're getting a parsing error? Have you republished the model since changing the lookup name?

Create a new query and drop this in:

#sq($RelativeTime{prompt('Measure Name','string')})#

What do you get when you run it?

[cognostechie]

Thanks Paul. This one worked but somehow the data coming out is zero. I am working on that.

I also tried #sb('Business Layer') + '.' + sb('Orders') + '.' + sb(prompt('Measure Name','token') )#   and this worked perfectly.