In Between Calendar Control with Sliding dates

I would like to share how I use to implement sliding days and dynamic date range with in reports.

Problem: Many times we come across requirement to have recent orders or activities etc. Here recent may be 1 day or 1 week or 1 month depends on different users.

Solution: To accommodate all above, I will pull report with latest 7 days i.e
Between CURRENT_DATE-7 and CURRENT_DATE and also I will give controls to user to choose between dates from page prompt so that user can get what they need.

Example: Today(July 24) report will pull recent 7 days as default(July 17 to July 24) and if I access same report tomorrow , report will pull July 18 to July 25 data as default. Same time user provided with page prompt control to choose dates. (Exp. July 17 to July 30).
Here are step by step to do that.
At first we need to have dynamic variables so create Server Variable’s for your requirement in my case startDate_ServerVariable with week ago date (SYSDATE - 7) and endDate_ServerVariable as Current date (SYSDATE).
1. To do that, in RPD navigate to repository – initialization block
2. Create new initialization block
3. Edit Data Source
4. Select Data Source Type as Database
5. Edit default initialization string with “select sysdate, sysdate-7 from dual”
6. Assign Connection Pool and also choose refresh rate of these variables.
7. Click on test to see proper values populated and say okay
8. Now click on Edit Data Target button
9. Create server variables
10. Click okay; close Variable Manager, Save RPD.


Now create prompts with Date-Calendar control and assign default value with server variable and also create new presentation variables as shown below.
Create report with filter on date column using between syntax will be like,
DateColumn is between @{startDate_PVariable}{1900-01-01 12:00:00} and @{endDate_PVariable}{1900-01-01 12:00:00}

For more detailed information follow these articles.
Venkat posted very good article on between, John has posted very good solution on Calendar Control.

Possible error:
If you have not specified proper default values for presenation variables exp "Date”.CreatedDate" is between @{startDate_PVariable} and @{endDate_PVariable} then you will end up with following error.
Error getting drill information: SELECT "Date”.CreatedDate" saw_0 FROM subjectarea WHERE "Date”.CreatedDate" BETWEEN date '0000-00-00' AND date '0000-00-00'
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46048] Datetime Month value 0 from 0000-00-00 is out of range. (HY000)

Make sure proper default value has provided for presentation variable.

--Next post on how to manage/switch between multiple environments on single machine

7 comments:

  1. Another Alternative:

    Chose SQl Results from Default drop down and write the below queries:

    1.Select Case when 1=0 then Calendar.Date else CURRENT_DATE-7 from SubjectArea.name
    2.Select Case when 1=0 then Calendar.Date else CURRENT_DATE from SubjectArea.name

    ReplyDelete
  2. @Madhukar,

    Thanks for visiting my post and commenting here.

    Nice alternative, this will work for static report, if user wants by default week days and same time ability to get for his specified data range in that case how we can accommodate?

    Thanks,Srinivas

    ReplyDelete
  3. Srinivas

    I have a requirement for prompt to have

    Account Expiration Date – dropdown – 30, 60, 90 days from current date

    When i Select 30 it should display the results for Current date to 30 Days. and so on

    Any help on this will be appreciated....

    Thank You
    Satish

    ReplyDelete
  4. Hi Satish,

    Try creating a column something like following.
    ROUND(((DAYOFYEAR("DateDimension".Date)*YEAR("DateDimension".Date) -DAYOFYEAR(CURRENT_DATE)*YEAR(CURRENT_DATE))/30), 0) * 30

    Let me know will this solves your problem.

    Thanks,
    Srinivas

    ReplyDelete
  5. Srinivas
    Nice to see your reply,

    I tried using your Code as

    SELECT ROUND(((DAYOFYEAR(Account."Expiration Date")*YEAR(Account."Expiration Date") -DAYOFYEAR(CURRENT_DATE)*YEAR(CURRENT_DATE))/30), 0) * 30 FROM "Subject Area"

    Here in above code i the column i am using is on "Expiration Date" from "Account" Dimension.


    in the Prompt Show - SQL RESULTS option but i am getting something wiered ... like "MM/DD/YYYY" and "-MM/DD/YYYY" in the drop down and its repeating .

    I do know if i have explained you correctly, all i wanted in the drop down is to display 3 values as 30 Days, 60 days , 90 Days and when i select 30 it should select the range from current date to 30 days and when i select 60 it should display date range from current days to 60 days and when i select 90 Days it should display me current date to 90 days.

    Thank You
    Satish

    ReplyDelete
  6. Srinivas

    I havn't heard back from you on the date Drop down....

    Thank you
    S

    ReplyDelete
  7. Hi Satish,

    You need to create this column in BMM or in presentation. Not place at prompt sql-results.

    Thanks,Srinivas

    ReplyDelete

Please give me your feed back