Need to use Prompt with LOV to get a Calculated Range.

building reports desktopintelligence
1

Hi,

Here’s the issue…

I have this table where data is loaded a couple of times a week and it is Reported on every monday with filter criteria as:

TABLENAME.LOAD_DATE  >  trunc(next_day(sysdate - 14, 'FRIDAY'))

so the report displays the loaded data only for the previous week.

Now the requirement is this that a Prompt be produced with DATE Lov’s before running the report and upon selection the same Value be passed in to a variable which would filter TABLENAME.LOAD_DATE in the expression above and the Report generated on the basis of the same.

I’m confused regards this and I thought a condition object would help but its not.

Could anyone please help me get a workaround tho this problem!

Help would be greatly appreciated.

Thanks

Ash

ash_for_bo (BOB member since 2005-08-29)

2

Previous week? Doesn’t this include the Friday from 00:00?

I’m not sure if I understand your question correctly, but creating a prompt with a Date-LOV isn’t that hard a deal? And passing it in your report to a report-filter isn’t either (using UserResponse())? Or maybe I’m just not sure what you’re asking.

jobjoris :netherlands: (BOB member since 2004-06-30)

3

Hi… firstly thanks a ton for the suggestion… the UserResponse() is something I havn’t toyed around with yet.

To make the Scenario more clearer…


trunc(next_day(sysdate - 14, 'FRIDAY'))

yes the date would be from Friday 00:00

What we’re intending to do is… Take for Example FEB 2006 - if I were running this Query on Monday 20th, it would give me exactly the data greater than FRIDAY the 10th up till 17th which is Exactly what I would be looking for:


TUE    WED   THU   FRI   SAT    SUN    MON    TUE    WED    THU    FRI    SAT    SUN    MON   TUE 
  7      8     9    10    11     12     13     14     15     16     17     18     19     20    21

I need to get the Date from the User… Pass it to the Filter above ie REPLACE “SYSDATE” with the PROMPT date… and Instead of using > than… I would be using a BETWEEN -14 and -7. But How do I get the PROMPT Value to this Between Filter is a issue.

I have never used the UserResponse Function before so I am going to check out its syntax now, thanks so much again… I might just come up with more questions just in case…

Thanks

Ash

ash_for_bo (BOB member since 2005-08-29)

4

The UserResponse() function can only be used in local (report-level) data. You can add a filter by clicking “format”–>“Filters…” in your menu-bar and add your filter to a report-tab or a table in your report-tab. By choosing “define…” you can use the BO-function syntax to filter out your dates.

But maybe you can filter it out in your query, usin the @prompt-functionality?

jobjoris :netherlands: (BOB member since 2004-06-30)

5

So are we talking Filters (in the report) or Conditions (in the query panel)?

I have a feeling you are talking about Conditions that you can use in the query panel to restrict the number of rows returned in your report.

So try and build a Pre-defined Condition in Designer such as:

TABLENAME.LOAD_DATE  between  trunc(next_day(@Prompt('Enter Date','D',,,) - 14, 'FRIDAY')) and trunc(next_day(@Prompt('Enter Date','D',,,) - 7, 'FRIDAY'))

Then when building your report you can add the Pre-defined Condition (with a little yellow filter icon) into the Conditions part of the query and it will add the code above to the Where clause of the SQL.

Hope this helps.

Beck

beckster :uk: (BOB member since 2003-02-18)

6

Hi Beckster and JobJoris… thank you so much for your valuable advice. Its been of great help. At least I feel I’m moving in some proper direction now. I guess I would like and experiment with both of your suggestions for learning’s sake and well have the more suitable one in this case, implemented. With regard to the Report level filter. I managed to get hold of the syntax and even managed to test with a String Prompt which worked great… now a Date Field with a calc seems to have a problem. Follwoing is what I’ve used… and It gives me a

in the Field. So the variable is getting created and parsing but not actually showing up… the backend database is Oracle 9i :frowning:


=(LastDayOfWeek(ToDate(UserResponse("Query 1 with MYUNV" ,"Enter Date for Previous Week's INFO") ,"mm/dd/yyyy") - 14)) -1

ash_for_bo (BOB member since 2005-08-29)

7

Well, your syntax seems in order, maybe you didn’t enter the date prompt with the “/”'s but did you use “-” instead?

jobjoris :netherlands: (BOB member since 2004-06-30)

8

Hi Jobjoris, Beckster


=(LastDayOfWeek(ToDate(UserResponse("Query 1 with MYUNV" ,"Enter Date for Previous Week's INFO") ,"mm/dd/yyyy") - 14)) -1

No, The date is being picked up from the date LOV that would pass out the date as (‘mm/dd/yyyy 00:00:00’) the format I see in the LOV. hope that helps you get a clearer picture to better enable you advise on this.

Now as per Becksters recommendation I tried creating a Dimension for the same using the following in the “Where” part of the Object definition…

ORPHANS.HEADER_DT BETWEEN next_day(@Prompt('Enter Date','D','Orphans\Header Dt',MONO,FREE) - 14, 'FRIDAY') AND next_day(@Prompt('Enter Date','D','Orphans\Header DT',MONO,FREE) - 7, 'FRIDAY')

I keep getting a Parse Failed: ORA-00932: inconsistent datatypes messgbox upon trying to parse the statement. I dont think I’m using Incompatible data types here… I’m just using a Date Field and filtering on it. Besides can I use a UserResponse() out here? Kindly advise.

Thanks in anticipation

Ash[/code]

ash_for_bo (BOB member since 2005-08-29)

9

Ash,

Don’t put it in the where-part of an object, built a predefined condition (as Beckster actually recommended). You hardly ever use the where-part as using such an object can mess up your query pretty good.

Inconsistent datatypes: Are you sure your ORPHANS.HEADER_DT is an actual DATE-field?

jobjoris :netherlands: (BOB member since 2004-06-30)

10

Can I just clarify the two ways of restricting the data shown in your report

  1. Using a FILTER in the report based on a UserResponse value as jobjoris suggested

  2. Using a CONDITION in the query which restricts the data returned from the database, as I suggested.

First you need to be clear which of these you are trying to achieve. Option 1 returns more rows then you hide some in a particular table, but you may want to show those hidden rows in a different table or perform a calculation based on them. Option 2 removes the rows you want to filter so they will not be available in the report, it means the report is smaller and the query may run quicker, but the filtered rows are not available for any other purpose like calculations.

OK, now assuming you want to go with option 2. Firstly I would suggest NOT to put it in the Where clause of the object, because you will always get this prompt when using that object, and it may conflict with other joins in your schema. It is far better to create a Condition object (yellow filter icon) which the user can then include in the report if they want that prompt restriction.

So in your trial of this method (albeit using the Where clause) you got ‘inconsistent datatypes’ message. I have just tested and the bit it does not like is

Prompt('Enter Date','D','Orphans\Header Dt',MONO,FREE) - 14

which is odd - I assumed it would work. Anyway I think the following (or something similar) can be made to work:

ORPHANS.HEADER_DT BETWEEN next_day(@Prompt('Enter Date','D','Orphans\Header Dt',MONO,FREE), 'FRIDAY')-14 AND next_day(@Prompt('Enter Date','D','Orphans\Header DT',MONO,FREE), 'FRIDAY')-7

So you end up finding the next friday from the users date and subtracting 14 and 7 days from this.

Let us know how you get on.

beckster :uk: (BOB member since 2003-02-18)

11

Beckster, Jobjoris,

My Apologies for the delay in responding. I was down with a viral, the usual stuffy nose kind… :roll_eyes: . Thank you both so much for your suggestions and thanks for pointing out where I was actually going wrong. I guess I’m making headway with this report except for the Date formats which seem to be acting rather weird.

Now as per Becksters Suggestion I create a Filter in the universe and use it to Filter my report. Now I need to Display the From Date and To Date

So I create a Variable “VAR1” using the following

=Replace(UserResponse("Query 1 with SHPRML" , "Enter Date") , "12:00:00 AM" ,"")

And This is what I get when I Display the same on the Report The same date that was Chosen at the the Prompt.

2/3/2006

without the Replace(,) I get “2/3/2006 12:00:00 AM”

And I create another Variable where I just use

=VAR1

The Following is the date I see.

01/00/1900

I try and Convert this prompt value which is now in Char either in the Variable “VAR1” or using a New variable to a Date Format “mm/dd/yyyy” using the ToDate(,) I get a

#ERROR

Thanks again… for bearing with me, for your kindness and and patience… :slight_smile:

Ash[/code]

ash_for_bo (BOB member since 2005-08-29)

12

The #Error part is correct, as there is no such date with a monthnumber “0”. The UserResponse() function always is a char so you’ll have to change it by ToDate to a date-field for use in date-related filters. But… Could you please explain what exactly the problem is you’re having or what the functionality is you’re looking for because the only thing I get is that it’s about a prompted date. Or maybe attach the report so we can look in to it more easily.

jobjoris :netherlands: (BOB member since 2004-06-30)

13

Hi JobJoris,

Thanks again for your invaluable advice and help.

I’m afraid I cannot attach the report here as I access the same from Citrix and I cannot download it to my desktop. however I’ve reproduced the WHERE part of the SQL below…

WHERE
  (ORPH.EMPL_ID=FND.ORPH_VW.EMP_ID(+)  )
  AND  (
  ( ORPH.HEAD_DATE > next_day(to_date(@Prompt('Enter Date','D','IP Orph\Head Date',MONO,FREE)) -14 ,'FRIDAY') AND ORPH.HEAD_DATE <=  next_day(to_date(@Prompt('Enter Date','D','IP Orph\Head Date',MONO,FREE)) -7 ,'FRIDAY')  )
  )

and with this have the report working as desired, but the issue that’s bothering me is that This time after having put this Formula,

=LastDayOfWeek(ToDate(Replace(UserResponse("Query 1 with SHPRML" ,"Enter Date") ,"12:00:00 AM" ,"") ,"mm/dd/yyyy HH24:mI:ss") - 14) -2

^ This is for <START_DATE>

the Date Shows up when the Report is RUN, but it shows #ERROR upon Purge because the report should appear clean when the user OPENS the report. This Date is used only for the TITLE Display… i.e. "REPORT BETWEEN DATES <START_DATE> TILL <END_DATE>

Your suggestions and Becksters helped greatly and I Incorporated the Filter at the universe which is actually fetching information for Dates between two Previous Fridays.

Kindly help me with this.

Thanks.

ash_for_bo (BOB member since 2005-08-29)

14

Can’t you use

=LastDayOfWeek(ToDate(Left(UserResponse("Query 1 with SHPRML" ,"Enter Date"),10) ,"dd/mm/yyyy"))

for the display of this date?

jobjoris :netherlands: (BOB member since 2004-06-30)