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.
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