How to get the user friendly Date Range Lov's for prompt like ToDay , YesterDay ,ThisWeek etc
Requirement: some business users may ask for prompt Lov’s like ToDay , YesterDay ,ThisWeek etc rather than going for date selection .
Solution: You can achieve above requirement by following below steps.
1. Create View at database level or Derived Table at universe level By using below Query .
SQL for View /Derived Table :
SELECT 'Last 7 Days' AS Date_Range,CONVERT(smalldatetime,{ fn curDATE() }) - 6 AS Begin_Date,CONVERT(smalldatetime,{ fn curDATE() }) AS End_Date
FROM dbo.syscolumns
UNION
SELECT 'Today' AS Date_Range, CONVERT(smalldatetime, { fn curDATE() }) AS Begin_Date, CONVERT(smalldatetime, { fn curDATE() }) AS End_Date
FROM dbo.syscolumns
UNION
SELECT 'Current YTD’ AS Date_Range, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS Begin_Date, CONVERT(smalldatetime, { fn curDATE() }) AS End_Date
FROM dbo.syscolumns
UNION
SELECT'Yesterday' AS Date_Range, CONVERT(smalldatetime, { fn curDATE() }) - 1 AS Begin_Date, CONVERT(smalldatetime, { fn curDATE() }) - 1 AS End_Date
FROM dbo.syscolumns
Union
-------
Output:
Date_Range | Begin_Date | End_Date |
Last 7 Days | 12/04/2012 | 18/04/2012 |
Today | 18/04/2012 | 18/04/2012 |
Current YTD | 17/04/2011 | 18/04/2012 |
Yesterday | 17/04/2012 | 17/04/2012 |
------------- | ----------------- | ------------- |
Note: a. SQL may vary depend on Database.
b. Begin_Date and End_Date Values will be varying depending on SQL run date.
c. better create derived table rather than view to maintenance.
- Define the join between desirable date column (order date, created date, dispatch date) and Begin_Date, End Date in universe level
- Create prompt on Date_Range at report level.
Scenario:
Current Data:
Dispatch Date | DispatchQuantity |
11/04/2012 | 100 |
12/04/2012 | 50 |
13/04/2012 | 25 |
14/04/2012 | 14 |
15/04/2102 | 12 |
16/04/2012 | 10 |
17/04/2012 | 75 |
By using above data business user want see the report like Dispatch Quantity by today or by yester day or by Last 7 days.
1. Universe Level :Define the Join between Dispatch Detail and Derived Table at universe level
Dispatch date Between Begin_Date and End_Date
2. Create prompt for Date_Range in report level.
