BusinessObjects Board

Issue trying to hard code a current Date Prompt Query

building reports webintelligence
#1

Below is a query that uses a prompt to select the “As of Date” and number of days to retrieve the data for. I would like to remove the prompt and hard code the query so that the report is run automatically each day and is sent to the users e-mail. I am having a problem hard coding the prompt. The “As of Date” equals the prior date if the day of the week is Tues, Wed, Thurs, Fri, if the days of the week is Sat or Sunday then we don’t want the report to run (or it can run “As of Date” Fri), and if the day of the week is Monday then we want the “As of Date” to be the previous Friday date. The number of days that we want the data returned for is 5 days. Any suggestions? Thanks in advance!!

SELECT
V_TM_DIMENSION.AS_OF_DT,
V_RFS_RGN_HIER_SM.GLOBAL_RGN_DESC,
DECODE(V_ODS_PROD_ACCS.ACCT_PROD_FLAG,‘Y’,V_ODS_PROD_ACCS.PROD_GRP_DESC),
SUM(NVL(V_POSN_ACCS_SM.MKT_VAL_AMT_USD,0))
FROM
V_TM_DIMENSION,
V_ODS_PROD_ACCS,
V_RFS_RGN_HIER_SM,
V_POSN_ACCS_SM
WHERE
( V_POSN_ACCS_SM.MONTH_END_DT=V_TM_DIMENSION.AS_OF_DT )
AND ( V_POSN_ACCS_SM.UNIV_ACCT_PROD_CD=V_ODS_PROD_ACCS.PROD_CD )
AND ( V_POSN_ACCS_SM.ORG_CD=V_RFS_RGN_HIER_SM.ORG_CD )
AND ( V_POSN_ACCS_SM.CWRK_LOGIN_ID(+) = @Variable(‘BOUSER’) )
AND ( V_RFS_RGN_HIER_SM.CWRK_LOGIN_ID(+) = @Variable(‘BOUSER’) )
AND ( V_TM_DIMENSION.AS_OF_DT=V_TM_DIMENSION.AS_OF_DT and V_TM_DIMENSION.USED_FLAG = ‘Y’ )
AND
( V_TM_DIMENSION.AS_OF_DT BETWEEN max(to_date,‘MM/DD/YYYY’)-((‘Number of Days’)=5))
GROUP BY
V_TM_DIMENSION.AS_OF_DT,
V_RFS_RGN_HIER_SM.GLOBAL_RGN_DESC,
DECODE(V_ODS_PROD_ACCS.ACCT_PROD_FLAG,‘Y’,V_ODS_PROD_ACCS.PROD_GRP_DESC)

Alise (BOB member since 2010-12-08)

#2

Hi,

I see that there is no prompt used in the above query.
You wanted the data to be for 5 days or just the previous day?
will the asof date column contains the sat and sun dates also?

sandeepmunagala :us: (BOB member since 2010-09-07)

#3

The data is only available for week days and it is never available on the same day (i.e. Monday’s data is available on Tuesday, Friday’s data is available Saturday).

Here is the original code with the prompts:

SELECT
V_TM_DIMENSION.AS_OF_DT,
V_RFS_RGN_HIER_SM.GLOBAL_RGN_DESC,
DECODE(V_ODS_PROD_ACCS.ACCT_PROD_FLAG,‘Y’,V_ODS_PROD_ACCS.PROD_GRP_DESC),
SUM(NVL(V_POSN_ACCS_SM.MKT_VAL_AMT_USD,0))
FROM
V_TM_DIMENSION,
V_ODS_PROD_ACCS,
V_RFS_RGN_HIER_SM,
V_POSN_ACCS_SM
WHERE
( V_POSN_ACCS_SM.MONTH_END_DT=V_TM_DIMENSION.AS_OF_DT )
AND ( V_POSN_ACCS_SM.UNIV_ACCT_PROD_CD=V_ODS_PROD_ACCS.PROD_CD )
AND ( V_POSN_ACCS_SM.ORG_CD=V_RFS_RGN_HIER_SM.ORG_CD )
AND ( V_POSN_ACCS_SM.CWRK_LOGIN_ID(+) = @Variable(‘BOUSER’) )
AND ( V_RFS_RGN_HIER_SM.CWRK_LOGIN_ID(+) = @Variable(‘BOUSER’) )
AND ( V_TM_DIMENSION.AS_OF_DT=V_TM_DIMENSION.AS_OF_DT and V_TM_DIMENSION.USED_FLAG = ‘Y’ )
AND
( V_TM_DIMENSION.AS_OF_DT BETWEEN (to_date(@variable(‘Select End of Period’),‘DD-MM-YYYY HH24:MI:SS’)-(@variable(‘Number of Days’))) AND @Prompt(‘Select End of Period’,‘D’,‘Time Dimension\As of Day’,MONO,CONSTRAINED) )
GROUP BY
V_TM_DIMENSION.AS_OF_DT,
V_RFS_RGN_HIER_SM.GLOBAL_RGN_DESC,
DECODE(V_ODS_PROD_ACCS.ACCT_PROD_FLAG,‘Y’,V_ODS_PROD_ACCS.PROD_GRP_DESC)

Alise (BOB member since 2010-12-08)

#4

Yes, but first let me explain how the prompts currently work. The user runs the report and then selects the date. The date he/she selects is always the last available date of the data. If we ran the report today the last date available would be yesterday (12-07-2010). They also select the number of days “5”. This means that they want the results to include 12-07-2010, 12-06-2010, 12-03-2010, 12-2-2010, and 12-1-2010 (keeping in mind that there are no results for weekend dates).

Alise (BOB member since 2010-12-08)

#5

Alise,

If i am correct you wanted to get the previous 5 days data to the report and the data should depend on the date when it runs?

Can you check whether you have any column which says the current date?
Not As of date.

sandeepmunagala :us: (BOB member since 2010-09-07)

#6

It’s not an option to select in BI Web Intelligence. I added a line in the

Where clause…

 AND (V_TM_DIMENSION.AS_OF_DT=Current_Date -1)

It returned no results. I can access the BI tables on Toad, but not sure where to look for “Current_Date”.

Then I changed the line above to this:

 AND (V_TM_DIMENSION.AS_OF_DT=SYSDATE -1)

It didn’t return any results either.

Alise (BOB member since 2010-12-08)

#7

There is one alternate for this…

You can use the between option for the date in the prompt so that it would be easy. The users can enter the range of dates which they are looking for. This will not include the sat and sun dates…

sandeepmunagala :us: (BOB member since 2010-09-07)

#8

Actually that wouldn’t work because the whole issue is that I want to remove the prompt altogether and have the report run automatically and have the results sent to the user via e-mail. That is why I am looking a way to change the current code (which includes a prompt) and hard code it with a function to determine the As_of_Date and data results of 5 days including and back from that date.

Alise (BOB member since 2010-12-08)

#9

Hi Alise,

This link will help you get started :slight_smile: :-

Please look at your various Oracle date / char / conversion functions, Case logic e.t.c. available to you, along with this and then post any problems you have.

Cheers,

Mark.

Mak 1 :uk: (BOB member since 2005-01-06)

#10

Sandeep - do you have more suggestions/ideas?

Mark - I am not sure how those will work with what I specifically need. Can you elaborate?

Thanks!

Alise (BOB member since 2010-12-08)

#11

I have an idea. I am not sure whether it works or not.

In the universe level create a filter

[As of Date] BETWEEN (sysdate - 1) AND (sysdate-7)

this should work. as you have the previous dates in the as of date column we are using sysdate - 1 and as you want the data fro 5 days we are using sysdate -7. don’t wry abt the sat and sun as you dont have the dates of the non-business days in the as of date.

Let me know if you have any questions.

Last thing is you need to work around if any of the users run the report on sunday and monday. try it if not i will help.

sandeepmunagala :us: (BOB member since 2010-09-07)

#12

I had tried this in the past. For some reason it is pulling is dates for more than just the 7 days. However, your suggestion gave me an idea and I tried
V_TM_DIMENSION.AS_OF_DT >= sysdate -8

This worked and gave me the 5 days of results that I need.

Thanks so much for all of your help.

Alise (BOB member since 2010-12-08)