BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Take control of your CMS with Infolytic (Opens a new window)  

General Notice: No events within the next 45 days.

Relative Dates - Webi reports for Publication and Infoview
1 members found this topic helpful

 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
twebber
Principal Member
Principal Member



Joined: 23 Mar 2009

Posts: 142
Location: Victoria BC



PostPosted: Mon Jun 20, 2011 1:36 am 
Post subject: Relative Dates - Webi reports for Publication and Infoview

I need a report to have query prompts that allows users to set start and end dates. I have this already. I also need the same report to be published and scheduled nightly so that it can deliver "yesterdays" data to people. I am totally fine with the SQL for this. My question is how best to do this with the universe/webi report tools?

The key is that users can still select their date range and I can schedule a publication based on a "prompt" - Yesterday in my case but they will likely want "this week" and "last week" and so on. From what i can see, prompts are the way that you can get a publication to be filtered. Any suggestions are appreciated.

I have hunted around a bit and seen all the relative date stickys. Its not the SQL. Its really how to have one report that can be published so that it gets yesterdays data and also allows users to select date ranges in InfoView. Thanks!
Back to top
anorak
Forum Enthusiast
Forum Enthusiast



Joined: 13 Sep 2002

Posts: 1045
Location: U.K.


flag
PostPosted: Mon Jun 20, 2011 1:55 am 
Post subject: Re: Relative Dates - Webi reports for Publication and Infovi

twebber wrote:
I need a report to have query prompts that allows users to set start and end dates. I have this already. I also need the same report to be published and scheduled nightly so that it can deliver "yesterdays" data to people. I am totally fine with the SQL for this. My question is how best to do this with the universe/webi report tools?

The key is that users can still select their date range and I can schedule a publication based on a "prompt" - Yesterday in my case but they will likely want "this week" and "last week" and so on. From what i can see, prompts are the way that you can get a publication to be filtered. Any suggestions are appreciated.

I have hunted around a bit and seen all the relative date stickys. Its not the SQL. Its really how to have one report that can be published so that it gets yesterdays data and also allows users to select date ranges in InfoView. Thanks!


Some examples:

An Oracle one:

Code:
ACCOUNT_MONTHLY_DISCOUNTS.DISCOUNT_MONTH  =  CASE
WHEN @prompt('Enter "Previous Month" or month (format YYYYMM)','A',,,) = 'Previous Month'
THEN substr(trunc(add_months(sysdate,-1),'mm'),7,4)||substr(trunc(add_months(sysdate,-1),'mm'),4,2)
ELSE @prompt('Enter "Previous Month" or month (format YYYYMM)','A',,,)
END


Here the scheduled version uses a prompt value of "Previous Month", but users can run ad hoc versions for any month by entering a month end date in the format "YYYYMM".

A Teradata one:

Code:
BO_EDW_VIEWS.P_CMC_MONTHLY_CALENDAR.LAST_DAY_OF_MONTH =
(CASE @prompt('Enter "Yesterday" or date (dd/mm/yyyy)','A',,,)
WHEN 'Yesterday' THEN date
ELSE cast(@prompt('Enter "Yesterday" or date (dd/mm/yyyy)','A',,,) as date format 'dd/mm/yyyy') END)
-
extract(day from (CASE @prompt('Enter "Yesterday" or date (dd/mm/yyyy)','A',,,)
WHEN 'Yesterday' THEN date
ELSE cast(@prompt('Enter "Yesterday" or date (dd/mm/yyyy)','A',,,) as date format 'dd/mm/yyyy') END))+1-1


Similarly, the scheduled version returns the previous month based upon a prompt value of "Yesterday" (i.e. current date), but users can enter any date and the query will return data for the month previous to the date entered.

Hope that this makes sense.
Back to top
twebber
Principal Member
Principal Member



Joined: 23 Mar 2009

Posts: 142
Location: Victoria BC



PostPosted: Mon Jun 20, 2011 11:10 am 
Post subject: Re: Relative Dates - Webi reports for Publication and Infovi

I tried the month example and it works as designed - thankyou. However, I need to provide a date range with a list of values and then the static example like your "Previous Month". For the date range, i currently use a query filter something like this:

ENTERPRISE_DATE.CAL_DATE between @Prompt('Please select a start date for you query','D','Enterprise Start Date\Cal Date',mono,free,not_persistent) and @Prompt('Please select an end date for you query','D','Enterprise Start Date\Cal Date',mono,free,not_persistent)


This prompt asks for a start date and an end date. Using your example, i think i would have to have "dynamic" prompts! What I mean by that is if I could first prompt a user to select either "Yesterday" or "Custom" then
1) just run the query if they select Yesterday or
2) further prompt them to enter a custom date range (as above) if they Choose Custom. I dont think BO supports this?

One other thought I had was provide two sets of prompts. THe one i have above and another with just "Yesterday" for example. Let the user select one "or" the other. THere is an optional check box when you develop the prompts in webi that allows you make your prompt optional - the key bit here is that I think if its optional and a user leaves it empty, its not included in the SQL. I dont see anywhere in the syntax for prompts how you can make it optional at the universe level. I'll play around but any more suggestions along this line would be great!
Back to top
twebber
Principal Member
Principal Member



Joined: 23 Mar 2009

Posts: 142
Location: Victoria BC



PostPosted: Mon Jun 20, 2011 4:06 pm 
Post subject: Re: Relative Dates - Webi reports for Publication and Infovi

OK, so I can confirm that the following method will work. If you need to have a date such as Yesterday for the purposes of scheduling a publication but also want to allow end users to be able to select dates for themselves.

1) create a query filter for your dates. I used the "between" function because i needed a start and end date. I "think" you must create this query filter in Webi (report level) because you need to set this prompt to be optional. there is a wee bitty ICON beside the drop down where you select "prompt" that allows more settings for the prompt. If someone knows how to make a universe query filter optional - please do share

2) create a universe query filter for Yesterday. For me, the where clause SQL was like:
trunc(MyTable.MyDate) = trunc(sysdate - 1)

3)Make the operator "OR" between the two prompt sets.

Now if you run the report, you can clear the values for the selectable date query filter and the report will then use you "Yesterday". If the selectable date filter has values, it will also work fine. Just occured to me that one thing might not be great with this! I suspect with this design that I will always get a value for yesterday unless I either make the Yesterday filter also at the report level and make it optional or figure out a way to make query filters optional.

Getting there.
Back to top
Markdaul
Forum Member
Forum Member



Joined: 27 Feb 2013

Posts: 4
Location: France



PostPosted: Tue Nov 19, 2013 9:03 am 
Post subject: Re: Relative Dates - Webi reports for Publication and Infovi

Hi everybody,

I think I have found the most relevant post, thank you Twebber !
(I'm under BO XI Webi R3)
In order to automatically publish some reports at the end of the ongoing month (for events happened between the first day of the ongoing month until the last day on the ongoing month), I would need to schedule a publication but also allow end users to be able to select the dates for themselves for day-to-day use.

My problem is that I cannot create an universe query filter as I have no access to the designer (BO powered by supplier).
Nevertheless, I have the access to the personal SQL window, and I am asking myself if it would be possible to reach the same result than Twebber by directly changing the SQL code with something approaching this :

Quote:
SELECT

...

WHERE
...
AND ( TABLE.DATE_FIELD<= @Prompt('Date fin historique','D',,MONO,FREE,PERSISTENT,last_day(sysdate)) AND TABLE.DATE_FIELD>= @Prompt('Date debut historique','D',,MONO,FREE,PERSISTENT,relative date for the first day of the month) )

...


I have tried like this (and several other syntax) but while running the report, it doesn't work (no date printed by default in the dates fields "first day" and "last day" of the month).

What could be the correct SQL/Oracle syntax ? is it just possible to proceed like that ?
Thank you in advance for your help !
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0548 seconds using 17 queries. (SQL 0.0116 Parse 0.0344 Other 0.0088)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo