COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: dssd on 10 Apr 2012 06:15:13 AM

Title: Dynamically choosing tables in FM
Post by: dssd on 10 Apr 2012 06:15:13 AM
I have a scenario where the users looks for data between two dates, start date and end date. The whole data is scattered across 2 databases, tables

So, for a certain part of the date range we need to query from one table and for the rest of the range need to pick the date from the other table. Is it possible to do so? I want to put it out as self service BI so everything needs to be in FM
Title: Re: Dynamically choosing tables in FM
Post by: blom0344 on 10 Apr 2012 07:35:19 AM
sure , no problem. Define a Cognos SQL subject against the tables using a union and predefined prompts within the SQL definition. Question is whether there is overlap between the 2 tables in terms of stored data. If not, then you do not have to use precautions.

Use syntax like:

CAST_TIMESTAMP(#prompt('Startdate')#)

to use in the SQL , to evaluate dates
Title: Re: Dynamically choosing tables in FM
Post by: barrysaab on 11 Apr 2012 11:59:37 AM
Sorry,blom0344,i didn't get you.Could you please it.Thanks
Title: Re: Dynamically choosing tables in FM
Post by: blom0344 on 11 Apr 2012 01:45:09 PM
Example:


SELECT
A,
B,
C
FROM [somedatasource].TABLE_A
where somedate between CAST_TIMESTAMP(#prompt('Startdate1')#)  and CAST_TIMESTAMP(#prompt('Enddate1')#)
UNION ALL
SELECT
D,
E,
F
FROM [somedatasource].TABLE_B
where somedate between CAST_TIMESTAMP(#prompt('Startdate2')#)  and CAST_TIMESTAMP(#prompt('Enddate2')#)



Title: Re: Dynamically choosing tables in FM
Post by: dssd on 11 Apr 2012 10:49:37 PM
Tihs would mean user would have 4 prompts. We want to present only two prompts to the user. I guess if the the flip over date of the first table is fixed it should be easy. If not then?
Title: Re: Dynamically choosing tables in FM
Post by: blom0344 on 12 Apr 2012 06:43:20 AM
Well, I just showed an example to demonstrate the possible approach. Change it to your requirements!!