| |
GeekInterview.com > Interview Questions > Data Warehousing > BO Designer
| Print | |
Question: AS OF DATE Filter to automate
Answer: Hello Guys,
I am creating a WEBI from my universe which is using ORACLE database as backend. I have the following query which i need to automate. For that i need to create a filter in the unvierse i don't know which statement i should write in the universe filters.
SELECT
WMB.DEPOSITS_TXN_DETAIL.AS_OF_DATE,
WMB.DEPOSITS_TXN_DETAIL.TXN_CODE,
WMB.DEPOSITS_TXN_DETAIL.TXN_POSTING_DATE,
WMB.DEPOSITS_TXN_DETAIL.SYS_SUB_PROD_CD,
WMB.DEPOSITS_TXN_DETAIL.SYS_PROD_CD,
count(distinct WMB.DEPOSITS_TXN_DETAIL.TXN_AMOUNT),
sum(WMB.DEPOSITS_TXN_DETAIL.TXN_AMOUNT)
FROM
WMB.DEPOSITS_TXN_DETAIL
WHERE
(
( WMB.DEPOSITS_TXN_DETAIL.AS_OF_DATE BETWEEN trunc (to_date(add_months(sysdate,-1)),'Month') AND last_day(add_months(sysdate,-1)) )
AND
WMB.DEPOSITS_TXN_DETAIL.TXN_POSTING_DATE BETWEEN '16-02-2008 00:00:00' AND '22-02-2008 00:00:00'
AND
WMB.DEPOSITS_TXN_DETAIL.TXN_CODE = 1105
AND
WMB.DEPOSITS_TXN_DETAIL.SYS_PROD_CD = 'DDA'
)
GROUP BY
WMB.DEPOSITS_TXN_DETAIL.AS_OF_DATE,
WMB.DEPOSITS_TXN_DETAIL.TXN_CODE,
WMB.DEPOSITS_TXN_DETAIL.TXN_POSTING_DATE,
WMB.DEPOSITS_TXN_DETAIL.SYS_SUB_PROD_CD,
WMB.DEPOSITS_TXN_DETAIL.SYS_PROD_CD
--AS_OF_DATE is a weekly upload but end of the month AS_OF_DATE become monthly data. and weekly no data available. My post date is the only source i can use but i don't know which code to write in a filter.
Any help would be appreciated!!!! |
| June 06, 2008 03:45:28 |
#1 |
| Balajikaadi |
Member Since: June 2008 Total Comments: 1 |
RE: AS OF DATE Filter to automate
|
Hi,
This can be handled as a condition object. Understand from the filter name (as of date) The condition object query should only contain the below statement. But of course.. you should have the associate objects in the class only then u can use them.
make sure that you have Objects --WMB.DEPOSITS_TXN_DETAIL.AS_OF_DATE and WMB.DEPOSITS_TXN_DETAIL.TXN_POSTING_DATE ..
your filter query will have these details.
( WMB.DEPOSITS_TXN_DETAIL.AS_OF_DATE BETWEEN trunc (to_date(add_months(sysdate,-1)),'Month') AND last_day(add_months(sysdate,-1)) ) AND WMB.DEPOSITS_TXN_DETAIL.TXN_POSTING_DATE BETWEEN '16-02-2008 00:00:00' AND '22-02-2008 00:00:00' AND |
| |
Back To Question | |
