If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Using prompt values as table and column names in the query?

Started by DrDamn, 12 Jul 2011 10:48:14 AM

Previous topic - Next topic

DrDamn

Is there a clever and cunning way to use prompt values from a users as the column and table names in the report query?

I have a large database with lots of fact tables. I want to create a "generic" report which will plot a selection of fact column values based on the user input of the table name and say 1, 2 or 3 column names. Assuming the table names and column names are available in a table themselves so this can be used to populate prompt values.

rockytopmark

Yes, the dynamic Table choice at runtime can be done with Prompt macros, and using the TOKEN prompt type.  See an example of a model query item's expression, from the single query in the Presentation Layer, below.

[Business Layer].#sb(prompt('pTableName','token','Presentation Layer Template'))#.[COL_TXT_01_LBL]

My implementation is 3 layers:
1). I have brought in all the tables into the Import Layer (Data Source Queries)
2). In the Business Layer, I model each and every table into a generic format that the single ultimate presentation layer query subject will be based on, with expressions like the example above.
3). Model the single Model Query subject in the Presentation Layer, using the Prompt Macros for each and every query item.


To allow the use to limit the columns to be seen, you'd have to implement prompts for each and every column and then use a Render Variable for each possible column.

GL... M