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:

  1. 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:

  1. 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))


To report this post you need to login first.

22 Comments

You must be Logged on to comment or reply to a post.

  1. varun garaga

    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

    (0) 
    1. DIVYA P Post author

      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

      (0) 
      1. Ramanaidu Kolasani

        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

        (0) 
        1. DIVYA P Post author

          A) To achieve the WTD,MTD,QTD,YTD,do our report requires respective columns ?

          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

          1.For WTD,which syntax is the right one ?

          TRUNC(sysdate,’Day’) or TRUNC(sysdate,’Week’)

          trunc(sysdate,’Day’) –check it in your oracle db

          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.

          Your missing the last part of the condition and sysdate) this gives the currentdate

          3.What is the reason behind mentioning case,? i.e., table.date between case.

          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

          C) with out creating conditional object in universe ,can we simply create WTD in report level.

          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

          D)In this document,you mentioned only oracle and SQL Server then what aboutother databases

          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.

          (0) 
          1. Ramanaidu Kolasani

            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

            (0) 
              1. Ramanaidu Kolasani

                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

                (0) 
          2. samir kazi

            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

            (0) 
            1. Mahboob Mohammed

              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

              (0) 
              1. samir kazi

                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

                (0) 
                1. Mahboob Mohammed

                  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

                  (0) 
                  1. samir kazi

                    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,
                    SamirLast & Days SQL.jpg

                    (0) 
                    1. Mahboob Mohammed

                      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.

                      Snap 01.png

                      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

                      (0) 
                      1. samir kazi

                        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/wp-content/uploads/2016/08/screenshot_1014886.jpg

                        (0) 
                        1. Mahboob Mohammed

                          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

                          (0) 
                          1. samir kazi

                            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

                            (0) 
                            1. Mahboob Mohammed

                              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

                              (0) 
                              1. samir kazi

                                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,

                                SamirLast7Days Column-Show Values.jpg

                                (0) 
                                1. samir kazi

                                  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

                                  (0) 
                                  1. Mahboob Mohammed

                                    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

                                    (0) 
                                    1. samir kazi

                                      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

                                      (0) 
                                      1. Mahboob Mohammed

                                        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:

                                        1. Screenshot of the query panel
                                        2. SQL script generated by the query (which would give an error, as the issue wasn’t resolved) and
                                        3. Custom SQL script after you’ve modified it to make it work (as you did to the original report which works now)

                                        Hopefully we’ll be able to find the root cause and resolve it.

                                        Thanks,
                                        Mahboob Mohammed

                                        (0) 

Leave a Reply