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
