HI,
i have a value prompt for a list report.
i want to display some table names in value prompt.
suppose we have tables like company , material, etc
and when we select a particular table , the columns of that table to be dislayed in a list report.
can u suugest the soln as a best design.
Appreciate ur help.
Add the table names as static choices for the prompt.
Add all the fields from those tables in the list.
Create a conditional variable (String) based on the prompt value.
Display and hide the fields in the list based on the prompt value.
Thanks
Actually we have around 20 tables.can we have another soln .
Use SQL to read the required 20 objects from the proper DBA view, like for SQL Server:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN (..............)
Thanks.
do u mean to use Tabular Sql or--
can u pls tell a detailed procedure to do this.
thanks
1. SQL syntax : native
2. Select the proper datasource
3. Add the SQL expression
4. When dragging in the SQL icon from Query Explorer Cognos will automatically add a Query to the SQL
5. Make sure that aggregate properties are correctly set as Cognos cannot do this automatically as it can when using a normal package..
Thanks....
My req is
i want to pass the 20 tables in a prompt and based on the selection of the value prompt---columns of that particluar table to be displayed in different list objects.
and can we pass the parameter to the sql query.
For SQL server this would mean addressing the view:
INFORMATION_SCHEMA.COLUMNS
This view stores all columns for every table (and a lot more)
Main query:
SELECT TABLE_NAME AS TABLE1,COLUMN_NAME AS COLUMN1 FROM INFORMATION_SCHEMA.COLUMNS
The promptquery would be:
SELECT TABLE_NAME AS TABLE1 FROM INFORMATION_SCHEMA.TABLES
Use the 2nd query for a value prompt. Assign parameter 'table1' to this prompt
Then filter the main query with a filter that addresses this parameter.