Dynamically change columns in the report crosstab

[tradeex]

Hello -

I have created a list report which changes columns dynamically. It works fine and I used static controls. But the issue I am having is with measures. Instead of changing columns like customers and year to product and year, i want to change measure columns. Here is what I am looking for:
If  user select option A, than they will see sales, qty and variance
if a user selects option B, they will see sales, budget and variance

I can keep the base columns the same, product and year, just need to change the measures.

Any ideas how I would make that work?

Thanks!!

[Lynn]

You didn't mention if your package is relational or dimensional, so I am assuming relational.

Since sales and variance are there for both choices, you could use a prompt macro with type "token" as the expression for your qty/budget data item. Make the "use" values for your prompt control hold the appropriate data item expression for each.

If they pick option A then the use value would be something like:
[YourPackage].[YourQuerySubject].[qty]

whereas option B would have a use value like:
[YourPackage].[YourQuerySubject].[budget]

The dynamic data item in your query would contain the prompt macro something like this, assuming the default choice is qty:
#prompt('YourParameterName','token','[YourPackage].[YourQuerySubject].[qty]')#

If your source is dimensional then you'd use memberuniquename instead of token and of course the structure of your use value would have the member unique names for your measures.

[Robl]

I'd use Lynn's method.

However, a second method would be to add the following calculation into the qty/budget column.

Case
when ?userprompt? = 'QTY' then [qty]
when ?userprompt? = 'Budget' then [budget]
else (0)
end

They both come up with the same results.
The only time I'd use this method rather than the #prompt method is if you particularly wanted to keep the report looking simple.
While the #prompt isn't particularly complex to use it does confuse some people.

[tradeex]

Hi -

I see that may work but when the user chooses option A, they would need to see sales, QTY and variance not just sales. Would token and the option above still work? We have relational DB.

Thank you

[Robl]

Yes, either option would work.
You're just using the prompt to determine the value of the Data Item.

The sales columns will always be present.
A second column (lets call it FRED) will have a calculation based on the above posts.
Variance will be Sales-FRED.

Depending on the look of the report you might need to do the same sort of thing to create a variable column title for FRED.