Skip to Content

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.

  1. Define the join between desirable date column (order date, created date, dispatch date) and Begin_Date, End Date in universe level
  2. 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.

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply