Use of macro to retrieve the table name

[pkrishnaq]

Hello gurus,
I would like to know if there is a way to capture the prompt selection made from the users and then use the table related to that.
E.g:

Prompt is to select the date : So, if the user selects 05112012
the table cc05112012_matrix is then chosen for the query.

there are many tables with the corresponding dates in it (cc06112012_matrix,cc071120212_matrix,etc)

So ,how would I do this? Thanks in advance

[blom0344]

If the table are identical in structure, then you can define a SQL subject against them:

SELECT 'cc05112012_matrix' AS TYPE,T.* FROM cc05112012_matrix T
UNION ALL
SELECT 'cc06112012_matrix' AS TYPE,T.* FROM cc05112012_matrix T
UNION ALL
SELECT 'cc07112012_matrix' AS TYPE,T.* FROM cc05112012_matrix T
...
...

Then use the dataitem that stores TYPE for the selection..

[pkrishnaq]

thanks for the response blom..That is a great idea!!!
but we have some 50 tables and it will be dynamic (in the sense that there will be more tables coming  in the future as they are updated monthly )...
Is there any other alternative?

Thanks again. \m/

[pkrishnaq]

Moreover, I jsut found out that the column counts are separate in different tables. ugh

[blom0344]

If you're talking about relatively small tables, then why not use a stored prodecure that writes data to a reporting table ? Even if column counts differ, there will be a subset of columns you need for reporting.  If not, then you have a major isssue with defining the metadata model (framework) against such structures.  So, an additional ETL step could resolve your problem..

[pkrishnaq]

Thanks...

I shall talk with the ETL folks and find out since I feel i have exhausted all options in Cognos.