Cognos 11.1.7 Framework Manager Change The Table For The Query Subject, Type Data

Using Cognos 11.1.7 and the database is DB2 on IBMi DQM Trying to figure out how to get Framework manager to change SQL definition on the fly, based on a value in the database This

Solution 1:

This is my understanding of the situation. One problem I'm having is that I've needed to guess a bit.

You have two data bases, each with the same structure of metadata. You update the dbs continually.

You have a table which keeps the status of both DBs and there is {something} which you can use as the indicator of the most recently update DB.

It isn't clear how this is done or how many records are in this table.

You might want to try something along these lines. I have not tried it, so the details involve a considerable degree of hand-waving.

You can use parameter map macros in the data source connection properties in your FM model.

You would need to import the table which you are using to track the DB status. You would create a filter in the query subject to find something like the maximum (if you are using time stamps) or filter for the value in the relevant column of the table which tells you which DB is current. This would mean that only one row, representing the current db, would be returned.

You would create a parameter map which would pick up whatever value is returned for the relevant column in the query subject. Put that macro into the appropriate property of the data source connection.

Key          Value
Sales        DB1's name
AnotherThing DB2's name

Solution 2:

You might be able to use the QueryValue macro function to dynamically build up the FROM clause. The example below will change the FROM claus to GOSALES. Camping Equipment. (This will fail, given its not an actual table_name) In your case you would replace #dq(queryValue ('[Sales (query)].[Products].[Product line]'))# with the database column which contains the table_name to be used.

SELECT"PRODUCT_LINE"."PRODUCT_LINE_CODE"AS"Product_line_code", 
   "PRODUCT_LINE"."PRODUCT_LINE_EN"AS"Product_line"FROM"GOSALES".#dq(queryValue ('[Sales (query)].[Products].[Product line]'))# "PRODUCT_LINE"