In the dashboard, the user want to be able to have an option to filter
either by Month or by Year with a switch. In other words, the first
thing they want to do on a dashboard is to select the option of "Year"
or "Month". Once the option is selected, the next prompt will change to
either filter by "Year" value or by "Month" value.
The report that they are querying against is the same report that show be able to go either 'year' or 'month'.
The report that they are querying against is the same report that show be able to go either 'year' or 'month'.
The first thing that needs to be done is create this "switch" on the
dashboard. This is basically another dashboard prompt that should only
return 2 values, 'Year' and 'Month'. To do so, let's create a dashboard
prompt and call it 'Time'. In my case, I randomly pick "Fiscal Period"
column from table "Time" in Subject Area "Financials - AP
Transactions".
In the prompt creation list, go to 'Option' and set the "choice list
value" to 'Show SQL result'. In the box, enter the following code:
SELECT case when 0= 1 then "Time"."Fiscal Period" else 'Year' end FROM
"Financials - AP Transactions" union all SELECT case when 0= 1 then
"Time"."Fiscal Period" else 'Month' end FROM "Financials - AP
Transactions"
The code will generate 2 values 'Year' and 'Month' as a result of the
union of 2 select statement. Each select statement will return one of
the 2 values because the condition '0 = 1' is always false.
If you notice, at the bottom of the this window where "Set A Variable"
is filled with Presentation Variable. This is how to set a presentation
variable using Dashboard Prompt. This time, the variable is named
'Time'. My purpose is the pass this variable into the next prompt that I
am about to create.
The idea is that, the variable 'Time' will hold one of the 2 values of
'Year' or 'Month' depending on which is selected by the user. Then the
next prompt will use this variable to determine whether it should return
year column data or month column data.
Therefore, the next prompt that I am creating is called 'Period' with a 'between' operator.
So let's take a look at this next prompt 'Period':
In here, the first thing I do is edit the column formula. Since this
prompt is also created based on a randomly chosen column, I have to
define it's content. This time, the expression is:
case when '@{Time}' = 'Month' then "Time"."Fiscal Period" when '@{Time}' = 'Year' then "Time"."Fiscal Year" else 'N/A' end
As you can see, it is a condition saying that if Presentation Variable 'Time' happens to be 'Year', then this column should be "Fiscal Year"; if Presentation Variable 'Time' happens to be 'Month', then this column should be "Fiscal Period", otherwise it is 'N/A'.
Now back to the prompt
creation window. Again here we have to set the Choice value list to be
'Show SQL Result'. By doing so, the logical sql statement is
automatically generated with the above condition embedded into the
select statement.
Here as you can see, I set another presentation variable call 'Period'.
My goal is to use this variable as the filter condition for the report.
So let's preview these 2 prompts and see how they look like:
So as you can see from above. The prompt 'Time' has only 2 values 'Year'
and 'Month' in the drop down list. You can select either one and hit
'Apply', then the content of the drop down list of prompt 'Period' will
hold either years or month data. Just remember to hit 'Apply' after
picking 'Year' or 'Month' from the first prompt in order to see the
updated list of the second prompt.
So far so good. Now let's create a simple report with only 1 field for
testing purpose. Here I again randomly pick a column calling it 'Period'
from the subject area for this report, then I have to edit the column
formula to allow it to be either year column or month column depending
on the presentation variables. Same logic here as in the second prompt
'Period'.
The expression is again:
case when '@{Time}' = 'Month' then "Time"."Fiscal Period" when '@{Time}' = 'Year' then "Time"."Fiscal Year" else 'N/A' end
Then, we need to create a filter condition on this column so that it
will filter the values coming from the second prompt's presentation
value. In this case, the filter condition is 'equal to' the name of the
presentation variable 'Period':
Now, let's put everything together on a dashboard and see how everything works:
Let's pick 'Year' and click 'Apply'. Then you can see a list of years in the drop-down of the select prompt:
In this example, I select between 2003 to 2005 as my prompt value. Then I hit 'Apply' again to see the result of the report:
Now let's go back and try 'Month' and see what happen:
After selecting the period from BB-11 to JUL - 10 in the prompt, I hit 'Apply' and here we go:
