AS OF DATE Filter to automate

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!!!!

Questions by jd_usnj   answers by jd_usnj

Showing Answers 1 - 1 of 1 Answers

Balajikaadi

  • Jun 11th, 2008
 

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

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions