how to select dynamic columns based on selected query?

[bvk.cognoise]

Hi Folks,
i need your help.i have a requirement?

1)assume that i have ten query subjects in my package.is it possible to select particular query subject in prompt.i need another prompt which shows columns which is related to selected query subject in previous prompt.after that my client will select required columns in second prompt(more than one column) if he click finish my report should show that selected columns only.please help me in this.

2)i have 20 columns in one query subject.i dont want to show any thing in report page in report studio.but when i run the report i want prompt with multi selct option to select any of these 20 columns.i have tried using new query calculation using static choices.but its only showing any one column even i enabled multi select property to yes.its not allowing me to select more than one column.

Folks please help in this.this scenarios very close to my requirement.it will help me alot .

thanks in advance.

thanks
bvk

[blom0344]

In general terms:  You can reference the PARAMETER value in a calculated dataitem to dynamically assign a set of model dataitems to the calculated one:

CASE ?myparameter?
WHEN (1) THEN [dataitemA]
WHEN (2) THEN [dataitemB]
...
...
ELSE null
END

Just as example..

It should give you a first step?

[bvk.cognoise]

Hi blom,

>thank you very much for your reply.but i tried this process before its only allowing to select one column.even i enabled multi select option.how can i select multiple columns in one query.?any other way?

>one more question this solution only to select dynamic columns.is it possible to select dynamically a query subject in prompt(not query item?)?

thanks
bvk

[Lynn]

Look into using a prompt macro with "token" data type.

There are a number of examples on the IBM site. Search "prompt macro token". Here is one:

https://www-304.ibm.com/support/docview.wss?uid=swg21340445

[bvk.cognoise]

Hi Lynn,
sorry if i am not clear .this is not my requirement.i will  explain once again.

assume  that i have  following columns in my query .
a
b
c
d
e
f
i want to create a prompt which allows me to select more than one column means.
abc(or)
abcd(or)
bef(or)
eda(or)

like this randomly i want select columns . dynamically i want to show these columns in report based on my prompt selection.


thanks
bvk

[Lynn]

I realize the specific article link I posted isn't exactly what you're after, but the technique of prompt macros with tokens is (I believe) what you need to achieve your requirement.

You might want to familiarize yourself with the concept and then see how to apply it for your situation. A promptmany macro in your case would be the way to go.

[bvk.cognoise]

HI Lynn,

thank you very much for your reply.i tried different methods for this requirement but still i am not able to achieve this.if any one know different method please suggest me :(


thanks
bvk

[Lynn]

How about conditional rendering? Include all the fields in your layout, then create a render variable for each column so it is only rendered per the user's prompt responses.

[bvk.cognoise]

Hi Lynn,

i dont want to show those data items in report layout.and i want a prompt which allows me to select more than 2 columns  dynamically whenever i run the report.(even if we use render variable is it possible to select multiple columns in prompt?)



thanks
bvk

[Lynn]

Assuming this is a list report:

• Create a multi-select prompt with static choices a, b, c, etc.
• Create a boolean variable for column "a": ParamValue contains ('a')
• Repeat step 2 so you have one boolean variable for each column
• Put all your columns in the list
• Select column "a" and specify the render variable as the one you created for column "a"
• Repeat step 5 for the other columns.
• Even though all columns are in the layout, they will only render based on what the user answers for the prompt

[PRIT AMRIT]

Otherwise, if you have just few columns say 5, then create a Static Prompt and reports with all possible combinations.

Then can use a conditional block to toggle between the reports based on the Prompt selection. I know it's weird though  ;)