Show data of past 4 quarters

The requirement is to show the past 4 quarters when user selects a quarter from the drop down list.

Steps-

Create one Control report :

For ex : for the Quarter 2000-04

LL1 – lower level -1999-04

UL1- upper level – 2000-03

( Because we are going to show past 4 quarters - LL1 = [2000/04 - 4months] and 

UL1=[2000/04 -1month] )

Look at the column formulae –

Column formula of LL1

CASE WHEN SUBSTRING(TIMES.CALENDAR_QUARTER_DESC,7,1 ) ='1' THEN CAST((CAST(TIMES.CALENDAR_YEAR AS INT)-1) AS CHAR(4))||'-'||'01'  WHEN SUBSTRING(TIMES.CALENDAR_QUARTER_DESC,7,1 ) ='2'  THEN CAST((CAST(TIMES.CALENDAR_YEAR AS INT)-1) AS CHAR(4))||'-'||'02' WHEN SUBSTRING(TIMES.CALENDAR_QUARTER_DESC,7,1 ) ='3' THEN CAST((CAST(TIMES.CALENDAR_YEAR AS INT)-1) AS CHAR(4))||'-'||'03' ELSE CAST((CAST(TIMES.CALENDAR_YEAR AS INT)-1) AS CHAR(4))||'-'||'04' END

Column formula of UL1

CASE WHEN SUBSTRING(TIMES.CALENDAR_QUARTER_DESC,7,1 ) ='1' THEN CAST((CAST(TIMES.CALENDAR_YEAR AS INT)-1) AS CHAR(4))||'-'||'04' WHEN SUBSTRING(TIMES.CALENDAR_QUARTER_DESC,7,1 ) ='2' THEN CAST(TIMES.CALENDAR_YEAR AS CHAR(4))||'-'||'01' WHEN SUBSTRING(TIMES.CALENDAR_QUARTER_DESC,7,1 ) ='3' THEN CAST(TIMES.CALENDAR_YEAR AS CHAR(4))||'-'||'02' ELSE CAST(TIMES.CALENDAR_YEAR AS CHAR(4))||'-'||'03' END

We now have to call the control report. This is Main report. ( as shown below )

Filter in this main report is based on Control report.

Look at the prompt -

Look at the end result on the dashboard when the Main report and the prompt are put on place.

 I have shown here 2 examples .

Good day !!!!