Dynamic column seletor

[anirudha]

Hello All,

I want to create simple list report which has 3-4 fixed fact columns as well as 5-6 attribute columns.

Users want to select amongst these 5-6 attributes which will be displyed on list, before they really run the report. They should see a prompt page with a multi-select checkbox prompt with these columns listed in it. They should be able to pick a few from them or all of them that they want to have in the list report.

Any clue?

Thanks in advance,
-Anirudha

[almeids]

This is a pretty straightforward/common technique you should be able to find many details on in this and other forums...in brief:

Define a data item for each column to return a boolean value (1/0 or Y/N) based on the prompt selection value - you'll need to use some sort of instring/contains function so make sure none of your prompt USE values is a substring of another - then define boolean conditional variables each based on one of the data items and conditionally hide each column/heading based on its associated variable.

It may be possible to do without the data items, just with variables, but sometimes it's necessary to put all the logic in data items and keep the variables simple.

[anirudha]

Hi Almeids,

I have already devised a prototype using the technique you mentioned. The problem with it is if the user does not want to see the lowest grain in the list then there are multiple rows with the same attributes, only measure value is different on the each row i.e. it does not regroup the columns.

Thanks for the reply.
Anirudha

[almeids]

Anirudha,
   I'm afraid I don't follow your second post regarding grain and regrouping of columns...I thought you just wanted to turn on and off columns based on a static prompt selection.  Provide some more details as to your report structure and how you'd like it to behave and maybe I can help.
Steve

[anirudha]

Almeids,

I could figure that out. Thanks for your reply though.

Anirudha

[wyconian]

Hi

I had a similar problem.  It sounds like your users want to be able to select which attribute columns they see on the report and then want the measures to roll up based on what they selected (is that right?).

Almeids is right in that you need to use some conditional rendering (check out the report studio user guide for details on how to do that).  In addition to that though I had to use case statements on the attribute columns along the lines of

case when ?show_column? = 'Yes' then [column] else  null

That means that you can't have the column select prompts in a single check box list but you can make it look like a single list by using a table with a text label and value selection for each column.  You can then have a different parameter and boolean variable for each column which makes the conditional rendering easier (in my opinion).

You may also need to play around with the aggregation and roll up properties for the measure columns.

Hope that helps.