Dynamically choosing tables in FM

[dssd]

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

[blom0344]

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

[barrysaab]

Sorry,blom0344,i didn't get you.Could you please it.Thanks

[blom0344]

Example:

Code Select Expand


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')#)


[dssd]

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?

[blom0344]

Well, I just showed an example to demonstrate the possible approach. Change it to your requirements!!