Can we Use Parameter Map in Prompt # Macro

[Kiran Kandavalli]

Team,

Can we Use Parameter Map in Prompt # Macro.

Requirement

1) I have around 180 measures in the Fact Table.

2) I Need to pick some of the Columns based on Prompt Parameter Selections. ( 4 Prompts - Use Values)

3) My Idea is I wanted to create a Unique Key with all the 4 different Prompt Selection.

4) While creating Parameter Map I wanted to give the Unique Key (point 3) as a Key and in the Value I wanted to give the Full Path of the Measure.

    EX:

   Key  Value

  ABCD     [Presentaion Layer].[Fact Table].[Measure]


5) when The Report Run - Based on Selection of all the 4 Prompts, I need to pick the right Column mentioned in Parameter Map based on the Key Value.

6) I am taking this Approach to avoid Case Statements in Report Studio to improve the performance.


Please suggest me on this or any other approach to implement.

Thanks!
Kiran

[Lynn]

Team,

Can we Use Parameter Map in Prompt # Macro.

Requirement

1) I have around 180 measures in the Fact Table.

2) I Need to pick some of the Columns based on Prompt Parameter Selections. ( 4 Prompts - Use Values)

3) My Idea is I wanted to create a Unique Key with all the 4 different Prompt Selection.

4) While creating Parameter Map I wanted to give the Unique Key (point 3) as a Key and in the Value I wanted to give the Full Path of the Measure.

    EX:

   Key  Value

  ABCD     [Presentaion Layer].[Fact Table].[Measure]


5) when The Report Run - Based on Selection of all the 4 Prompts, I need to pick the right Column mentioned in Parameter Map based on the Key Value.

6) I am taking this Approach to avoid Case Statements in Report Studio to improve the performance.


Please suggest me on this or any other approach to implement.

Thanks!
Kiran

You could do a similar thing using a prompt macro with type "token" and skip any parameter map. Set your prompt control to have "display" values as the name of the measure (e.g., ABCD) and have the "use" value as the fully qualified expression (e.g., [Presentaion Layer].[Fact Table].[Measure]).

Then create a query item to include in your layout with the expression as:

Code Select Expand


#prompt ( 'Choose Measure' , 'token', '[Presentaion Layer].[Fact Table].[Measure]' )#


The first element in the prompt macro is the parameter name. The second element is the data type. The third element is the default value.

[Kiran Kandavalli]

Thanks Lynn for your response!

Sorry I may not be clear. The measure is driven based on the Combination of 4 Prompts selection.

Lets Say-

Prompt 1 has Use Values - A,B,C,D
Prompt 2 has Use Values - W,X,Y,Z
Prompt 3 has Use Values - E,F,G,H
Prompt 4 has Use Values - I,J,K,L

1) if the user selects A, W, E, I in the above prompts [Measure 1] is shown
2) if the user selects B, W, E, I in the above prompts [Measure 2] is shown
3) if the user selects C, W, E, I in the above prompts [Measure 3] is shown
4) if the user selects D, W, E, I in the above prompts [Measure 4] is shown
5) if the user selects A, X, E, I in the above prompts [Measure 5] is shown
6)  if the user selects A, Y, E, I in the above prompts [Measure 6] is shown
...
...
...
...
Until all the Possible Selections are completed.

As of now I am writing Case Statement in the Report, So I wanted use #Prompt# Macro to avoid the Case statement.

Thanks!
Kiran

[MFGF]

Thanks Lynn for your response!

Sorry I may not be clear. The measure is driven based on the Combination of 4 Prompts selection.

Lets Say-

Prompt 1 has Use Values - A,B,C,D
Prompt 2 has Use Values - W,X,Y,Z
Prompt 3 has Use Values - E,F,G,H
Prompt 4 has Use Values - I,J,K,L

1) if the user selects A, W, E, I in the above prompts [Measure 1] is shown
2) if the user selects B, W, E, I in the above prompts [Measure 2] is shown
3) if the user selects C, W, E, I in the above prompts [Measure 3] is shown
4) if the user selects D, W, E, I in the above prompts [Measure 4] is shown
5) if the user selects A, X, E, I in the above prompts [Measure 5] is shown
6)  if the user selects A, Y, E, I in the above prompts [Measure 6] is shown
...
...
...
...
Until all the Possible Selections are completed.

As of now I am writing Case Statement in the Report, So I wanted use #Prompt# Macro to avoid the Case statement.

Thanks!
Kiran

Hi,

Wouldn't you just concatenate the prompt functions within your macro?

eg #$Your_Parameter_Map{prompt('Prompt1','token') + prompt('Prompt2','token') + prompt('Prompt3','token') + prompt('Prompt4','token')}#

Am I missing something?

MF.

[Kiran Kandavalli]

Thank you so much MFGF!

Seems like it's working - will let you know once I implement for all the conditions in my Report.

Thanks again,
Kiran

[Kiran Kandavalli]

Hi MFGF,

I have 8 columns in the Report and so I created 8 Parameter Maps.

1) First Col - Currency
2) First Col - Units
3) Second Col - Currency
4) Second Col - Units
...
...
7) Eighth Col - Currency
8- Eighth Col - Units

in My first Prompt if I select as 'Currency' the Currency Columns are Valid and Units Columns are throwing Syntax Error (unexpected end of expression "" found.) . My understanding as I did not select Units in the prompt, so the Units Columns are throwing Error.

Code Select Expand

#$ActualColMOUnits{prompt('pMeasure','token') + prompt('pCurrency','token') + prompt('pReportFilter2','token')}#

This is the Same syntax I have given for Currency Columns and they are working. But units column Expression shows Invalid.

I tired to put an if Condition (or) Case Statement - but then Expression is not accepting the macro #.

Is there a way that I can handle this, please suggest!

Thanks!
Kiran

[CognosPaul]

The syntax there looks like it should work.

My suggestion is to run the following in a tabular in a new query:

Code Select Expand

#sq(prompt('pMeasure','token') + prompt('pCurrency','token') + prompt('pReportFilter2','token'))#

Check that against the parameter map. Do you have a default value in the pmap? My guess is that the combination is returning a value that's not expected in the map.

Another solution you might consider would be to use the prompts to generate the reference directly. Make the data items for each of the combinations, and call it with:

Code Select Expand

#'[Presentaion Layer].[Fact Table].['
+ prompt('pMeasure','token')
+ prompt('pCurrency','token')
+ prompt('pReportFilter2','token')
+']'#

No case statement involved, but the problem with this would be getting the label to appear correctly.

[Kiran Kandavalli]

My Bad, I have set the Default value as Key Code, it should be the Value shown in Value Columns.

Thanks to everyone for all your help!