I came across a requirement, where user has to select a value from the month prompt(Ex:- DEC-1981) and report should display the data for the past 4 months (DEC-1981, NOV-1981,OCT-1981 and SEP-1981) .
Here month prompt is derived from the date column.
Results should look like this:-
Steps followed:-
--> Create a dashboard prompt and assign a presentation variable.
--> Create a helper report to restrict the dates
--> Create the base report to see data and filter dates based on helper report
Create a dashboard prompt and assign a presentation variable.
EVALUATE('TO_CHAR(%1,%2)' AS CHAR,"Scott_emp"."HIREDATE",'MON-YYYY')
Create a helper report to restrict the dates
Helper report is used to list the dates that fall in the last 4 months.This is filtered with the selected month value and gives you the starting and ending date for the four months.
For First Day:-
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( TIMESTAMPADD(SQL_TSI_MONTH,-4,"Scott_emp"."HIREDATE")) * -(1) + 1, TIMESTAMPADD(SQL_TSI_MONTH,-4,"Scott_emp"."HIREDATE")))
For Last day:-
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( "Scott_emp"."HIREDATE") * -(1) + 1, "Scott_emp"."HIREDATE")))
Create the base report to see data and filter dates based on helper report
Create report with the required columns and the filters to restrict the report with the dates.
Add a filter on the date column as below
Add a filter on the date column as below
Save all the reports and add them to the dashboard page and output looks like this
