In this blog you will learn how to design and configure choosing date filter dynamically and apply relative data values on top of the chosen date filter object. This is a typical challenge that we face in most of the web-intelligence reporting tool as this is not something that the underlying database or meta-layer can generate directly. This would help a developer / power user trained in web intelligence / IDT (Information Design Tool) to create an effective web-intelligence reporting as always date prompt plays a vital role in any reporting.
This functionality can be leveraged against any back-end relative database, for this blog I have used HANA as the backend database.
Problem Statement: Consider a scenario where a web-intelligence report needed to be built based on both Ex: “Decision Date” Or “Notification Date” (based on User prompt) and at the same time, flexible enough to pull the data for any Start and End Date frame (again based on User Prompt). For Example, I have taken below samples. This can be easily extended to any number of scenarios based on individual reporting needs.
Solution : This is a multi-step process that I layout below step by step in both Information Design Tool (IDT) as well as in web-intelligence tool.
IDT Steps: Open IDT Tool and create below LOV/Parameters/Variables & Filters using below steps.
- Create a static LOV named “Relative_Date1” & “Relative_Date2” either at Data Foundation layer or at Business Layer (see below) and this is where you can add multiple date objects as needed for your web intelligence If your report demands a single date filter, then you only need to create “Relative_Date1” and can skip creating “Relative_Date2.”
Relative_Date1: Static LOV which would feed
Start_Date filter
Relative_Date2: Static LOV which would feed
End_Date filter
2. Create ‘Date option’ LOV variable (for dynamic selection of dates for multiple data options). You would need this if your reporting demands dynamic date filters based on multiple dates. You can add as many date objects to the LOV as needed. In this example, I have added 2 date objects i.e., Decision Date & Notification Date
3. Create the following 3 parameters. One for the different
date options and other 2 for the
Start Date and
End Date Label them as they need to appear on the web intelligence report.
4. Create following universe variables vStart and vEnd Dates (see below). Below code is based on HANA Sql syntax.
Note: If you are using other relative database, please change the code according to the database syntax.
For custom range, please use “mm/dd/yyyy” syntax as it is mentioned in above screen shot.
vStart Date:
Case when @Prompt(Start Date) LIKE '%/%' Then to_date(@Prompt(Start Date), 'MM/DD/YYYY')
Else Case when upper(substr_before(@Prompt(Start Date),'-'))='T' Then add_days(current_date,-to_number(substr_after(@Prompt(Start Date),'-')))
when upper(substr_before(@Prompt(Start Date),'-')) = 'W' Then add_days(current_date,-7*to_number(substr_after(@Prompt(Start Date),'-')))
when upper(substr_before(@Prompt(Start Date),'-')) = 'M' Then add_months(current_date,-to_number(substr_after(@Prompt(Start Date),'-')))
when upper(substr_before(@Prompt(Start Date),'-')) = 'Y' Then add_months(current_date,-12*to_number(substr_after(@Prompt(Start Date),'-')))
when upper(substr_before(@Prompt(Start Date),'-')) = 'WB' Then add_days(current_date,(weekday(current_date)*-1 )- 1-(7*to_number(substr_after(@Prompt(Start Date),'-'))))
when upper(substr_before(@Prompt(Start Date),'-')) = 'MB' Then add_months(add_days(current_date,- extract(day
from current_date)+ 1),-to_number(substr_after(@Prompt(Start Date),'-')))
when upper(substr_before(@Prompt(Start Date),'-')) = 'QB' Then ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),(to_number(right(quarter(current_date),1))-to_number(substr_after(@Prompt(Start Date),'-'))-1)*3)
when upper(substr_before(@Prompt(Start Date),'-')) = 'YB' Then to_date('01-01-'||extract(year
from current_date)-to_number(substr_after(@Prompt(Start Date),'-')),'MM-DD-YYYY')
End
End
vEnd Date:
Case when @Prompt(End Date) LIKE '%/%' Then to_date(@Prompt(End Date), 'MM/DD/YYYY')
Else Case when upper(substr_before(@Prompt(End Date),'-'))='T' Then add_days(current_date,-to_number(substr_after(@Prompt(End Date),'-')))
when upper(substr_before(@Prompt(End Date),'-')) = 'W' Then add_days(current_date,-7*to_number(substr_after(@Prompt(End Date),'-')))
when upper(substr_before(@Prompt(End Date),'-')) = 'M' Then add_months(current_date,-to_number(substr_after(@Prompt(End Date),'-')))
when upper(substr_before(@Prompt(End Date),'-')) = 'Y' Then add_months(current_date,-12*to_number(substr_after(@Prompt(End Date),'-')))
when upper(substr_before(@Prompt(End Date),'-')) = 'WE' Then add_days(current_date,(WEEKDAY(current_date)*-1 ) -2-(7*(to_number(substr_after(@Prompt(End Date),'-'))-1)))
when upper(substr_before(@Prompt(End Date),'-')) = 'ME' Then ADD_DAYS(ADD_MONTHS(ADD_DAYS(current_date,- extract(day
from current_date) + 1),-(to_number(substr_after(@Prompt(End Date),'-'))-1)),-1)
when upper(substr_before(@Prompt(End Date),'-')) = 'QE' Then ADD_DAYS(ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE), 'YYYY'),(to_number(right(quarter(current_date),1))-to_number(substr_after(@Prompt(End Date),'-')))*3),-1)
when upper(substr_before(@Prompt(End Date),'-')) = 'YE' Then to_date('12-31-'||extract(year
from current_date)-to_number(substr_after(@Prompt(End Date),'-')),'MM-DD-YYYY')
End
End
5. Create below 2 filters:
Start Date:
Case When @Prompt(Date Based On) = 'Decision Date' Then @Select(Dates\Decision Date) Else @Select(Dates\Written Notification Date) End >= @Select(Dates\vStart Date)
End Date:
Case When @Prompt(Date Based On) = 'Decision Date' Then @Select(Dates\Decision Date) Else @Select(Dates\Written Notification Date) End <= @Select(Dates\vEnd Date)
Caution: You won’t be able to do “Integrity Check” on
vStart Date and
vEnd Date as there is NO table associated to it.
Once you publish the universe to the repository, follow below steps in Web Intelligence Tool.
Web Intelligence Steps:
- Open up web intelligence tool, using the universe publish to repository, fetch the necessary objects needed for reporting along with 2 filters i.e. “Start Date” & “End Date” dragged into “Query Filters” (see below)
2. Click “Run Query”. See below screen on what to expect.
3. Click Start and End Date prompt and you would see below screen
4. For custom range, please use the following syntax as explained in the previous steps in IDT tool.
5. Execute the report and see how dates comes out in reporting layer.
Conclusion:
You can use this solution towards any web intelligence reporting which involves data fetch based on dynamic date filter objects with any number of relative date scenarios (ie., weeks, months, year ago etc). This just a template and it can be extended or custom tailored to any specific relational database and for any number of date scenarios.