SAP BO ; User friendly Date Range Lov’s for prompt like ToDay , YesterDay ,ThisWeek

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:

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:

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.