CUSTOM PREDEFINED DATE RANGE FILTERS (WTD/MTD/QTD/YTD) in BO
CUSTOM PREDEFINED DATE RANGE FILTERS
In many financial reports, the requirement is to have date filters like user has to give start date and end date to see the data in the given time frame. But if user wants to run the report for a given period like for a month, quarter or year he will not be interested to calculate the start date of a week/month/quarter/year and give as input to the report. Instead he will be interested to say that he want data for a week/month/quarter/year.
These kind of implementations are done from many years but I have not found any document explaining the steps to implement this. So though it will be helpful for someone. As am new to documentation, please feel free to give your inputs to improve upon.
To achieve this, we generally follow the terminology as ‘WTD’,’MTD’,’QTD’, and ‘YTD’
WTD – Week to Date: Starting of Week to the current Date
MTD – Month to Date: Starting of Month to the current Date
QTD – Quarter to Date: Starting of Quarter to the current Date
YTD – Year to Date: Starting of Year to the current Date
Here the current date is the date on which the user is running the report.
Solution:
We can achieve this by using conditional object in the universe level. The syntax will change based on the type of data base which you are connecting to
Oracle:
- Table.DATE between case
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’WTD’ then TRUNC(sysdate, ‘DAY’)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’MTD’ then TRUNC(sysdate, ‘MONTH‘)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’QTD’ then TRUNC(sysdate, ‘Q’)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’YTD’ then TRUNC(sysdate, ‘YEAR’)
END
and sysdate
SQL Server:
- Table.DATE between case
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’WTD’ then DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’MTD’ then DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’QTD’ then DATEADD(q,DATEDIFF(q,0,GETDATE()),0)
when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’YTD’ then DATEADD(year,DATEDIFF(year,0,GETDATE()),0)
END
And getdate()
Here user will be prompted to select one of the value from list ‘WTD’,’MTD’,’QTD’,’YTD’ and based on the selection he will get the data for the given date range.
Note: for WTD the Important difference in Oracle and SQL is Oracle considers the starting day of the week as Sunday where SQL server takes it as Monday. So while calculating the WTD you need to keep this in mind and confirm with your business analyst about the requirement.
Other custom date range requirements will be like Last 3 months and Last 6 months.
You can achieve them as well in similar way but only thing is you need to tweak the start date of the date range based on the period.
For SQL Server:
Last 6 Months
Dateadd(month,-6,getdate()) And end date will be getdate()
Ex: If todays date is 10-March-2016 then the start date will be 10-Sep-2015 and date will be 10-March-2016
Last 3 Months
Dateadd(month,-3,getdate()) And end date will be getdate()
Ex: If todays date is 10-March-2016 then the start date will be 10-Dec-2015 and date will be 10-March-2016
For Oracle:
Last 6 Months
Start date will be equal to add_months(sysdate,-6)
And
End date will be sysdate
Last 3 Months
Start date will be equal to add_months(sysdate,-3)
And
End date will be sysdate
Last 6 Quarters
Option 1:
Start date will be add_months(sysdate,-18)
And
End date will be sysdate
Option 2:
table.year*100 + table.month between extract(year from sysdate)* 100+extract(month from sysdate)
and extract(year from add_months(sysdate,-18))*100+extract(month from add_months(sysdate,-18))
Last 6 Quarters Excluding current Quarter
Option 1: table.date between trunc(sysdate,’q’)-1 and add_months(trunc(sysdate,’q’),-18)
Option 2:
table.year*100 + table.month between
extract(year from trunc(sysdate,’q’)-1)*100+
extract(month from trunc(sysdate,’q’)-1)
and
extract(year from add_months(trunc(sysdate,’q’),-18))*100+
extract(month from add_months(trunc(sysdate,’q’),-18))
HI Divya,
Very Nice Explanation .Can you Explain more about @prompt Syntax like when user selects WTD . what will Hapen you mentioned TRUNC(Sys date,DAY)?
Could you please brief me about this i am confused .
Thanks,
Varun
Hi Varun,
If user selects WTD then we should show data from beginning of week to current date.
Ex if user runs the report today i.e. 30 th March 2016 then he should get data from 27th March (which is beginning day of the week) to todays date.
TRUNC(Sysdate,'DAY') gives the value 27th March 2016
Similarly TRUNC(Sysdate,'Month')gives start day of month etc
These oracle specific functions
Refer http://stackoverflow.com/questions/5515514/how-to-get-first-and-last-day-of-week-in-oracle
Hi Divya,
I understood your documentation completely and I have few clarifications,Kindly give your confirmation over my clarifications in a list wise and If you find any corrections update the same in the place where mistake occurs.
As am new to Web I , please check the clarifications mentioned below completely and give your inputs to improve upon.
Clarifications:
A) To achieve the WTD,MTD,QTD,YTD,do our report requires respective columns ?
For example, Year|Quarter|Month|Week|Product|Sales revenue|WTD-?
B)To achieve the requirement,use conditional object in universe level.
Is it the right procedure ?
Step :1.Project
|Classes
|Date(Conditional object)_in SQL Assistant(
Table.DATE between case
when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='WTD' then TRUNC(sysdate, 'DAY')
when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='MTD' then TRUNC(sysdate, 'MONTH')
when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='QTD' then TRUNC(sysdate, 'Q')
when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='YTD' then TRUNC(sysdate, 'YEAR')
END
and sysdate)
|Click on Validate.
2.Publish the universe to repository.
3.Launch Web I_Query Panel_
|Drag Year|Quarter|Month|Week|Product|Sales revenue in to Result object pane
and |Filter on Date(Conditional object)
Click on Run query.
Then user will be prompted to select one of the value from the list.
Note:
1.For WTD,which syntax is the right one ?
TRUNC(sysdate,'Day') or TRUNC(sysdate,'Week')
2.If TRUNC(sysdate,'Week') gives start day of week then what about current date ? As WTD stands for start day of week to the current date.
3.What is the reason behind mentioning case,? i.e., table.date between case.
C) with out creating conditional object in universe ,can we simply create WTD in report level.
If yes kindly let me know about the procedure.
D)In this document,you mentioned only oracle and SQL Server then what about other databases.
Thanks
Ram
No, not mandatory. You are filtering data for a week or month etc but you can show the data in the report by date or month wise.. whatever level you want
trunc(sysdate,'Day') --check it in your oracle db
Your missing the last part of the condition and sysdate) this gives the currentdate
case is to create the filter the data with dynamic values. here if user selects WTD then the start date will be the start date of the week , if he select MTD then it will be start date of month etc
Yes, but that is not the suggested option as you will be getting entire data into the report and filtering /showing for one week/month /year in the report
If some one faces issues in implementing this logic on other databases then i will update this document.More over this document is to give idea of implementing and can be used for reference and to be fine tuned based on the requirement. More over the logic of getting WTD /MTD/YTD parameter values will be database specific not BO specific. So better wait for the requirement.
hope i have answered all ur questions.
Thanks a lot Divya,
Kindly let me know the below procedure is correct or not.
1.Starts from creating conditional object till report creation.
Step :1.Business Layer
|Classes
|Date(Conditional object)_in SQL Assistant(
Table.DATE between case
when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='WTD' then TRUNC(sysdate, 'DAY')
when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='MTD' then TRUNC(sysdate, 'MONTH')
when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='QTD' then TRUNC(sysdate, 'Q')
when @Prompt('Enter value','A',{'WTD','MTD','QTD','YTD'},Mono,Free,Not_Persistent)='YTD' then TRUNC(sysdate, 'YEAR')
END
and sysdate)
|Click on Validate.
2.Publish the universe to repository.
3.Launch Web I_Query Panel_
|Drag Product|Sales revenue|Date in to Result object pane
and |Filter on Date(Conditional object)
|Click on Run query.
Then user will be prompted to select one of the value from the list.
2.As per your reply,Your missing the last part of the condition and sysdate) this gives the currentdate
Is Current date should be TRUNC(sysdate) or only sysdate
1) Yes, correct
2) Sysdate only and it is there in the original document. please refer it
One more thing is you need to understand the trunc function of oracle. please refer this
ROUND and TRUNC Date Functions
Thanks a lot Divya,
I have been observing lot of posts in Web I community but especially for 'date' related posts the way you are providing the best solutions is really helpful for the people like me.
I'm going to start my reporting work on Web I ,DB2 Database and took the training on that.
Divya,kindly add me as your follower so that when I face some complicate issues I can send a direct message to you.
Also,please send a DB2 based syntax document just like Oracle if you have any.
Thanks
Your Trainee
Ram
Hi Divya,
In Universe Design Tool, there are Reporting objects like Last7Days etc. How can we get the same in Information Design Tool. I need to schedule report automatically by email that should take data for last 7 days. Thanks in advance.
Warm Regards,
Samir
Hi Samir,
Just as an FYI - Universe 101:
Universe Design Tool (UDT) is the tool in which Universe.unv is created (its an old format, old technology if you will).
Information Design Tool (IDT) is a tool in which Universe.unx is created (its the latest and greatest format, if you will).
Keeping that in mind, the answer to your question is, you'll have to recreate those Pre-defined filters (Reporting Objects, as you said) in the new Universe.unx as well (in the IDT). If the backend database is same, then just create the filters by copy the filter content from the old Universe.unv (from the UDT and paste it in the new filter in new Universe.unx (in the IDT).
Look at this Tutorial, instead of country.country = 'US', you'll have the copy text from old universe and paste it here.
Information design tool 4.0: Create a pre-defined native filter
Let us know, if you've any issues.
Thanks,
Mahboob Mohammed
Thanks Mahboob for the information. I have tried copying the Reporting Objects from UDT to IDT as per your instructions. Firstly I made copy of the universe in UDT that had these reporting objects. Then converted this copy in IDT and copied the reporting objects to my customized universe. Once that was done, I created one new report and used one of the reporting objects(last 7 Days from Now). No problem till here. However when I tried to run this report it gave me errors as below: Database error: ORA-0904: "ANA_RPT_OBJECTS_VW"."LAST_07DAYS_FNW": invalid identifier. (IES 10901) (Error: INF ) Did I miss any step? Do I have to perform some step after pasting the reporting objects from one universe to another. Please note that the backend DB is same. Looking forward for your response. Thanks, Warm Regards, Samir
Hi Samir,
Would you be able to share a screenshot of that object/filter Last 7 Days from Now? I'm sure there's nothing confidential in it (except for table names). In the screenshot, I want to see the SQL in the Where clause and the Tables it refers to (you can see that right below Where clause).
If this is conversation is getting too long, you may want to think about creating a new Discussion for your issue here.
Thanks,
Mahboob Mohammed
Dear Mahboob,
Please find attached screenshot for the same as requested. I have also copied the table ANA_RPT_OBJECTS_VW in my customized universe.
Warm Regards,
Samir
Hi Samir,
That's almost close to the snapshot I asked for. Please check out the below, follow the steps and provide a snap is possible.
Also, that ORA is related to invalid table or column name, check this, I see that issue in the error message you sent earlier with double quotes for table and column name.
Apart from that, what's the value that this column has? I see it returned 1 row of data (in the image you attached).
Thanks,
Mahboob Mohammed
Dear Mahboob,
Please find screenshot attached. The column has values as shown in screenshot. Please let me know where I am going wrong.
Warm Regards,
Samir
Hi Samir,
You missed one of my questions, what does that column show? Date Time of current minus 7 days?
Check if the data type of the column in table is Date Time as you've specified for the object.
At this point, I think, it'd make sense to start a discussion instead of discussing in this blog post.
Thanks,
Mahboob Mohammed
Dear Mahboob,
Are you talking about my customized column or the Reporting Object Last 7 days from now? Yes, please lets start a new discussion instead of this blog post.
Warm Regards,
Samir
Hi Samir,
As we discussed, please create a new discussion, in this Webi Intelligence page. Also, what's the value in that Reporting Object Last 7 day column? Can you send a screenshot with show values?
Thanks,
Mahboob Mohammed
Hi Mahboob,
I have already started a new discussion "Using Reporting Objects in IDT-Would like to know how to include the Reporting Objects that are present in UDT in IDT?" the value for the Reporting Object Last 7 day column is as shown in screenshot attached.
Warm Regards,
Samir
Thanks Mahboob for your help however I have accomplished this myself after using the option of using custom script in BI which I found was very easy and fast.
Warm Regards,
Samir
Hi Samir,
Just a reminder (you may very well know it already), updating the SQL script by writing a custom script is not one of the best practices (at least when you have a universe in place), keep in mind, if and when anyone updates the query by adding new object(s), the custom script you wrote will be gone and you'll have to modify it again. If this is a report which is just scheduled and no one touches the query, then, that's fine.
Thanks,
Mahboob Mohammed
Hi Mahboob,
Thanks for the information. Yes, I am aware however I don't have any other option, do I? I have been asking for solution to so many people and I am not able to get anything for something simple like this. I cannot hold customer requirements forever.
Warm Regards,
Samir
Hi Samir,
How about this? Let your Customers use the report you have made and on the side, let's try to reverse engineer and find out why is the Reporting Object Last 7 Days filter failing when used in an object.
Let's try this - Create a totally new report with Reporting Date object (regular date object that you use to), Reporting Date Last 7 Days object just the way you have in the original report, how you were trying to filter (which was failing). From this new report, please provide the below 3:
Hopefully we'll be able to find the root cause and resolve it.
Thanks,
Mahboob Mohammed
Hi Divya,
Thanks for the detailed info.
Would you know how to write a filter in SQL server for Last Month or Last week to only get data between 6 AM to 8 PM for each day.
Thanks
Ashwin
Hello,
Has anyone implemented 'Custom Date Range(Select Start and End Dates)' where the user should be prompted to enter both start and end dates (when selected this lov)along with the above predefined date range filters.
i have tried something like below but it doesn't work. Appreciate any suggestions 🙂
Table.date_column between Case
when @Prompt('Select date Range:','A',{'Previous Day','WTD','MTD','YTD','Custom Date Range (Select Start & End Dates below)'},mono,constrained,not_persistent)= 'Previous Day' then TRUNC(CURRENT_DATE-1)
when @Prompt('Select date Range:','A',{'Previous Day','WTD','MTD','YTD','Custom Date Range (Select Start & End Dates below)'},mono,constrained,not_persistent)= 'WTD' then TRUNC(CURRENT_DATE,'iW')
when @Prompt('Select date Range:','A',{'Previous Day','WTD','MTD','YTD','Custom Date Range (Select Start & End Dates below)'},mono,constrained,not_persistent)= 'MTD' then TRUNC(CURRENT_DATE,'MM')
when @Prompt('Select date Range:','A',{'Previous Day','WTD','MTD','YTD','Custom Date Range (Select Start & End Dates below)'},mono,constrained,not_persistent)= 'YTD' then TRUNC(CURRENT_DATE,'yy')
END
and CURRENT_DATE
OR
Table.date_column between Case
when @Prompt('Select date Range:','A',{'Previous Day','WTD','MTD','YTD','Custom Date Range (Select Start & End Dates below)'},mono,constrained,not_persistent)= 'Custom Date Range (Select Start & End Dates below)' then
@Prompt('Enter Created Date(Start):', 'D',, Mono, Free, Not_Persistent)
and @Prompt('Enter Created Date(End):', 'D',, Mono, Free, Not_Persistent)
Thanks in advance,
Sindu