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

Posted by

7 comments:

  1. madhukarMarch 8, 2010 at 5:28 AM

    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. Srinivas MalyalaMarch 13, 2010 at 7:54 AM

    @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. UnknownJune 10, 2010 at 2:23 PM

    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. Srinivas MalyalaJune 10, 2010 at 7:03 PM

    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. UnknownJune 11, 2010 at 10:09 AM

    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. UnknownJune 16, 2010 at 10:04 AM

    Srinivas

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

    Thank you
    S

    ReplyDelete
  7. Srinivas MalyalaJuly 3, 2010 at 9:01 AM

    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

Subscribe to: Post Comments (Atom)