how to display table names in a value Prompt

[Shailaja0520]

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.

[Gopinath]

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.

[Shailaja0520]

Thanks

Actually we have around 20 tables.can we have another soln .

[blom0344]

Use SQL to read the required 20 objects from the proper DBA view, like for SQL Server:

Code Select Expand


SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN (..............)


[Shailaja0520]

Thanks.


  do u mean to use Tabular Sql or--
can u  pls tell  a detailed procedure to do this.

thanks

[blom0344]

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

[Shailaja0520]

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.

[blom0344]

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:

Code Select Expand


SELECT TABLE_NAME AS TABLE1,COLUMN_NAME AS COLUMN1 FROM INFORMATION_SCHEMA.COLUMNS



The promptquery would be:

Code Select Expand



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.