I came across a requirement where a client wanted to see specific records that had end dates before 30, 60, or 90 days from now. In other words the client wanted to dynamically change the date range to include records with a date less than Today’s Date + (30,60,90 days).
So the first step was to create prompt that allowed the user to select 30, 60 or 90 days.NEW ->DASHBOARD PROMPT
Once you see the window below click on the + (NEW) and select COLUMN PROMPT.
After clicking on the COLUMN PROMPT, you should be able to select a column from a table, as you see below. In this case, I chose INDIRECT COST RATE because the data type is numeric, but it doesn’t really matter which column we selected, as we plan on changing the list of values anyway. Click OK
Once you select a column the window below will pop up and in here the first thing you want to do is:
- Enter a new column name next to LABEL. We called this “Expiring Within”
- In OPTIONS, Choice List Values: SQL RESULT
- Enter a SQL statement to generate the list of values.
- Set a variable: Presentation Variable
- Name the presentation variable. I created OFFSET_DAYS as my variable.
Click OK
And you’re done with setting the prompt.
Next, create a new analysis ( NEW -> ANALYSIS ) and from the Subject Areas select a column (in my requirement the column name is PROJECT END DATE) that you want to use to limit the criteria.
Go to EDIT FORMULA.
Window below will pop up, by using Oracle TIMESTAMPADD funcation and a PERSENTATION VARIABLE you can figure out TODAY’S DATE + 30,60 AND 90 DAYS.
Once you click on Presentation variable the window below will pop up and you can enter a VARIABLE EXPRESSION and DEFAULT VALUE. Click Ok
Once you are done with the variable you will need to finish up the SQL, as you could see below. Click OK
Create a new filter, OPERATOR (is less than or equal to) and the click on Add More Options >> REPOSITORY VARIABLE. Once you click on the REPOSITORY VARIABLE you will have the option to enter a variable name as you could see below I used CURRENT_DATE as my repository variable. Click OK and you’re done.
Blog author: Bipul Kuri
Bipul is a contributing blog author on the Business Intelligence Practice team at BizTech.
