Showing Facts as the user wish. How to?

[ratnalein]

Hallo everyone,

I have a modelled a package with 8 Facts(Grade facts).  I created a list report like this:

Name Math_Grade Physic_Grade English_Grade Biology_Grade Music_Grade Sozial_Grade Japanese_Grade Sport_Grade
--------------------------------------------------------------------------------------------------------------------------------------------------------
William     1.0                1.0                    1.0                 3.0                  2.0                 2.0                     3.0                   1.0
Hudson    1.0                2.0                    2.0                 1.0                  2.0                 1.0                     3.0                   1.0
...
...
...

I would like to author the report, so that only the desired fact columns popping out as the user wishes to see.  Normally, this is possible by using "output variable".  It means, we create a variable, which allows the user to decide if the user wants that dimension or not. 

But now the problem is, I can not create a FACT prompt, can I?  I mean, how to let the user decide, which fact columns he wishes to see?

Thank you for your help.

Regards,

Ratna :)

[Satheesh]

Hi,
   
By Using Value Prompt Static variable and Render Variables you can achieve your output.

[ratnalein]

Hallo Satheesh,

thank you for the reply.  I am facing difficulty to create the Value Prompt variable, because the columns the user should be able to choose are FACT columns.  All of the tutorials I found in google regarding this are about choosing dimension columns.

Please help, thank you..

Regards,

Ratna

[wyconian]

Hi

You should still be able to use a render variable on a measure column.  The other alternative is to use a case statement to display the required measure.

To do that you'll need a prompt with some static choices so the users can specify which column (s) to see then you'll need a case statement on the report something like

CASE WHEN ?PROMPT? = VALUE1 THEN MEASURE1
WHEN ?PROMPT? = VALUE2 THEN MEASURE2
ELSE 0 END

You'll need to do that for all possible values.

It's possibly easier/more efficient to use a render variable on the columns

Good luck

[ratnalein]

Hallo wyconian,

thanks for the reply.  If I am choosing to use render variable:

I would need to create a prompt, if I am not mistake.  But how can I create the promt?

If I am choosing to use case statement:
The case statement would be my filter, if I correctly understand your advice.  Still I would have to create a prompt for the filter interface, how can I create this prompt?

Thank you for your help.

Regards,

Ratna

[wyconian]

Hi

You'll need the same prompt for both scenarios.

Add a value prompt to your prompt page, add some static choices (this is an option in the prompt properties).  You will need a static value for each option you want to give the users i.e. if you want them to choose from 3 measures you will need 3 values.  Each static value has a use and display value.  The use value is what will be used in the report, the display value is shown to the user.  The values can be the same but I find it better if the use value is something simple but the display value has to be good enough to let the users make a decision.

If you want to use the render variable do this;
Once you have your prompt and static values set up you will need a string variable.  The derivation of the variable should be something like paramValue(?prompt?).  You will need to add the possible values for the prompt. These should be the use values from the prompt.

In the report page you will need a column for each measure.  In the properties for each column you can add a render variable.  Choose the variable you just added and select which values to render the column for.

If you decide to use the case statements
Add a data item to the report (you don't need to have any columns for the measures).  The derivation for the data item should be something like
CASE WHEN ?PROMPT? = value1 THEN measure1
WHEN ?PROMPT? = value2 THEN measure2
END

You don't need to add any filters for this to work.

Good luck

[ratnalein]

Hallo wyconian,

thank you very much for the detailed explanation.  It worked, I get what I wanted now. :)

Regards,

Ratna