Strange Scenario in Prompt

[Sundaramoorthy]

Hi All,

We are in a position to design the prompts in such a way like below

1.Department

Instead of single column getting assigned to prompt, it should display all existing columns from department table as check box option. Also it should have provision to check some columns by default.

2.Employee

Same scenario as above.

3.Sales

Same scenario as above.

By this approach during run time, user may get privilege to include the necessary columns in the layout.Once expected columns got selected from above prompts, All these columns need to append simultaneously in another single consolidated prompt & based on columns available there, report need to get render.

Please advise whether is it feasible in cognos 10.2.2. Thanks in Advance..

[BigChris]

Essentially you need to create variables for each column that you want to conditionally render. The variable needs to use the IN operator since you're going to be allowing the user to select multiple columns. Then you apply the render variable to each column...and if you're putting these prompts at the top of the report page don' forget to include a Finish button.

[Sundaramoorthy]

Thanks for your valuable information..

I have got one proven practice link(http://www.ibm.com/developerworks/data/library/cognos/reporting/dynamic_report_design/page570.html) in IBM portal for this scenario..Now i have another issue like whatever column i selected from the prompt, it got rendering in the layout but in the background irrespective of whether that column selected or not, query is getting executed for all available columns in that query.

Is there any possibility to have the selected columns alone can hit the DB for fetching the records?

[BigChris]

Not that I'm aware of - you're just controlling the rendering of the output. The only that I can think of to produce that behaviour would be to have a query for each scenario...and if you've got several selectable columns that would mean a LOT of queries...something like 2 ^ number of columns. Then for each combination you'd need a conditional block, which would show the output for the relevant query

So, logically possible, but you really don't want to go there!

[Sundaramoorthy]

Chris,

Thanks.i implemented the logic by using the combination of static prompt,render variable & prompt macro..