Dynamic Dates in Web Intelligence
Explore various solutions to overcome hard-coded dates and scheduling issues in Web Intelligence reports. Learn how to utilize variables, SQL, and calendar universes effectively. Discover tips for date prompts and comparisons to enhance report flexibility and efficiency while working with dynamic date ranges.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
DYNAMIC DATES IN WEB INTELLIGENCE
ABOUT ME Noel Scheaffer - https://www.linkedin.com/in/noelscheaffer BusinessObjects Admin, Universe Designer, Web Intelligence Report Writer BusinessObjects since 2012 (started on 4.0; currently on 4.2 SP07 Patch 4)
BACKGROUND https://answers.sap.com/questions/13470598/variable-to-calculate-the-period-of-the-previous-m.html
THE PROBLEM Hard-coded dates need to be changed and the report cannot be scheduled.
THE PROBLEM - CONTINUED Prompting for the dates is better, but the report still cannot be scheduled. Tip You can set the prompt order in the Query Properties.
DATA My base data set will be the number of transactions per day between 07/01/2021 and 12/31/2021. Tip You can use the format suggestion in the date prompt to convert the prompt value (always a string) to a date.
SOLUTIONS There are a number of ways to satisfy the requirement. I will show three solutions with some helpful tips along the way. Web Intelligence variables Free-hand SQL Calendar universe
WEB INTELLIGENCE VARIABLES Var Reference Date =ToDate("09/03/2021"; "MM/dd/yyyy") Var First Day of Previous Month =ToDate(FormatNumber(MonthNumberOfYear(RelativeDate([Var Reference Date];-1; MonthPeriod)); "#0") + "/1/" + FormatNumber(Year(RelativeDate([Var Reference Date];-1; MonthPeriod)); "0000"); "M/d/yyyy") Var Last Day of Previous Month =RelativeDate(RelativeDate([Var First Day of Previous Month]; 1; MonthPeriod); -1; DayPeriod) Var Previous Month Date Range Match =If([Txn Effective Date] Between ([Var First Day of Previous Month]; [Var Last Day of Previous Month]); 1; 0) Tip You cannot compare objects in a filter. Instead create a variable that does the comparison and filter on that.
WEB INTELLIGENCE REPORT FILTER Tip You can define a custom format if you want something other than the default or other available formats.
FREE-HAND SQL RAW QUERY https://irfansworld.wordpress.com/2011/01/24/sql-server-dates-part-1-year/ Tip You can copy the @Prompt function syntax details from the query script of a universe query and then paste it into a free-hand SQL query.
FREE-HAND SQL QUERY FILTER Tip When using Results from another query you cannot use Between ; you must use Greater than and Less than .
CALENDAR UNIVERSE https://blogs.sap.com/2014/02/11/first-day-of-previous-week-with-a-twist/ Tip You can add the pre-defined Query Summary cell to get an objective view of your queries such as execution time and number of rows returned.
BONUS COVERAGE Use the TimeDim() function to show missing dates (i.e. weekends, holidays, etc.) =TimeDim([Filtered by Calendar Universe].[Financial Trans Date])
BONUS COVERAGE CONTINUED Create a query with all of the dates in your range. Merge it with your primary data. Tip You can generate a date table dynamically with a common table expression (CTE) in a free-hand SQL query.
CONCLUSION Hard-coded date values need to be changed all the time Prompted date values cannot be scheduled Variables and report filters inefficient returning more data than necessary Free-hand SQL can be tricky to write, but gets the job done and efficient Calendar Universe hard work is in the universe development, easy query and report development with same efficiency as the free-hand SQL approach
TIP SUMMARY Set the prompt order in the Query Properties. Use the format suggestion in the date prompt to convert the prompt value (always a string) to a date. You cannot compare objects in a filter. Instead create a variable that does the comparison and filter on that. Define a custom format for something other than the default or other available formats. Copy the @Prompt function syntax details from the query script of a universe query and then paste it into a free-hand SQL query. When using Results from another query cannot use Between ; must use Greater than and Less than . Add the pre-defined Query Summary cell to get an objective view of the queries such as execution time and number of rows returned. Generate a date table dynamically with a common table expression (CTE) in a free-hand SQL query.
