Cognos Analytics

We have new Data marts created for each year. Each Data Mart table has a date stamp at the end of the name.

Can we write pass through SQL or point our Cognos reports to the latest data mart table with use of Session parameters from Cognos to Oracle DB.

or is there a way Cognos can dynamically point to the latest tables?

thanks

------------------------------
Swaraj Kundu
------------------------------

#CognosAnalyticswithWatson

You could write your own SQL and define it as Pass-Through SQL. But this has some disadvantages.

I recommend to use query macros, in particular the 'token' type. It might be valuable to combine them with parameter maps.
The macro passes - or to phrase it differently - extend your table name with the current date stamp.

The release 10.x manual contains a sample for 'token' macros which you may find useful:

Creating Prompts with Query Macros -> Search for "Syntax Substitution"

#CognosAnalyticswithWatson

Hi Swaraj,

... you can use a prompt macro in framework manager database query subject. E.g. "Select * from table_name#some macro function#"​

regards, Frederik

------------------------------
Frederik Spang
------------------------------

Original Message:
Sent: Thu October 03, 2019 01:51 PM
From: Swaraj Kundu
Subject: Dynamically pointing to different Oracle Tables

We have new Data marts created for each year. Each Data Mart table has a date stamp at the end of the name.

Can we write pass through SQL or point our Cognos reports to the latest data mart table with use of Session parameters from Cognos to Oracle DB.

or is there a way Cognos can dynamically point to the latest tables?

thanks

------------------------------
Swaraj Kundu
------------------------------

#CognosAnalyticswithWatson