Changing measures based on user selection

[Manu0521]

My crosstab will have a measure which shows the revenue , i wanted to give user an option to select a different measure like sales.

How can I achieve this. Should I use prompts and how do i add these both measure values in it . Please let me know how it can be achieved.

Thanks,

[Manu0521]

Value prompt doesnt allows me to add measure values. How can I achieve this, Can I add static values and how do i relate it to my measeure data item.

Appreciate your help

Thanks,

[MFGF]

Hi,

Is this with your dimensional package or are you using a relational package for this?

The answer will dictate the approach we recommend...

Cheers!

MF.

[Manu0521]

Its dimensional package !

[MFGF]

Hi,

Ok - thanks. The best approach with a dimensional package is to use a prompt macro to drive the measure in your crosstab.

1. Remove the current default measure value from the crosstab.
2. In your package tree, expand the measures folder and right-click on one of the desired measures. Copy the Path of the measure and paste it into Notepad. You will see something similar to [great_outdoors_sales_en].[Measures].[Revenue]
3. Drag a query calculation to the measures area of your crosstab. Call it Revenue (or whatever the name of this measure is). Code the expression as #'[great_outdoors_sales_en].[Measures].[' + prompt('MeasureParam','token','Revenue') + ']'#
      Hint - the first part of the expression will match the first part of the Measure Path you copied, and the Revenue part will be the name of the measure - also from the Path.
4. Add a Value Prompt to your report. Base it on the existing parameter MeasureParam. Don't click Next - just hit Finish immediately.
5. Select the prompt and go to the Static Choices property. Add Use Values for each measure you want to use - hint: use the exact measure names from your package and note that they may be case sensitive.
6. Set the Auto Submit property of the prompt to "Yes"

Cheers!

MF.

[Manu0521]

Hi MFGF,

I think I am making a mistake in my macro expression,

These are the 2 values of measures
[Sales].[Measures].[Sales]
[Sales].[Measures].[Revenue]

I added a query item and should i add both these values in my expression ?

My Expression :#'[Sales].[Measures].[' + prompt(''MeasureParam'','token','Sales') + ']'#
what is the 'MeasureParam' here and what is 3 rd parameter... I have 2 measures right ? so will it be sales or revenue.


what does base it on existing parameter MeasureParam mean ? I added a value prompt and it asks for parameter name , what should I give and will the use values be Sales and Revenue - 2 measures.

I ran the report. but i dont see any values on my measure on selection.

Thanks,

[MFGF]

Hi,

I added a query item and should i add both these values in my expression ?

Code the expression just for one of the measures - exactly as you appear to be doing.

what is the 'MeasureParam' here and what is 3 rd parameter... I have 2 measures right ? so will it be sales or revenue.

MeasureParam is the parameter the prompt places a result into - just like ?MeasureParam? would in a "normal" expression. You can call this whatever you like. The third parameter is the default that gets used if the prompt is unfulfilled, so it needs to be either Sales or Revenue in your case.

what does base it on existing parameter MeasureParam mean ?

When you drag a value prompt into your report, the first question you get asked is whether to base it on a new parameter or on an existing parameter. Select the 'Existing' option and choose the MeasureParam parameter (or whatever you used as the first argument in your prompt macro).

Cheers!

MF.

[Manu0521]

Hi MFGF,

That worked !! Thanks a lot.But with one issue as mentioned below in point 2.

1) It worked only if I wrote that prompt expression on a query calculation for "other expression" , but if I write the same code in the calculated measure it doesnt show any values for the measure. What is the difference in other expression and calculated measure/

2) If I add suppress rows and column for the cross tab , it gives me an error , i think since we change the measures using prompt , its not able to filter them initially , is there a way to get rid of the below error and make work with supression
"Found an internal error: '!mapSuccess - reportName=ABCD - edgeName=31 - edgeOrdinal=0 - tuple=([Months].[Months].[@MEMBER].[Prior Month], [Measures].[Sales Measures].[@MEMBER].[Sales]).'."

Thanks,