Excel Dashboards: Add Dashboard parameter
In the previous articles we added a Pie chart (Create Pie chart from Excel Data) and a Bar Chart (Create Bar chart from Excel Data). Now we add parameters to the dashboard
Add Dashboard Parameters or List of drop down values
Year Parameter
Click on the new parameter button or from the menu select Create -> Parameter
· Enter “Year” as the parameter name
· In the “Display Column” type “distinct fiscal_year”
· In the text editor type the worksheet name “FROM [detail_data$]”
· Click on “Fully Apply”
· The above is the result list of values
· The list of value is showing a decimal value, this is because of the Excel formatting.
· Change the query to add a condition as below. We will use the TRIM function (if you list of values shows a null value then also add a where condition to remove null value e.g “where fiscal_year <> ‘null’ “)
Similarly lets add the region parameter
Region Parameter
For this parameter we have enabled the “All Values”. We also added ‘country_region’ in the (ID1) Internal Column1 property. This is needed in order for ‘All Values’ to work.
Now we need to wire all the portlets to the above two parameters.
Link the Parameters to Portlet
Right now the parameters and the portlets are independent. In order to link the tables and charts to the parameter we need to modify the SQL queries for each portlet (table and chart). Select the ‘By Region’ and Edit it.
· The current query is
select country_region, sum(quantity_sold) as qty_sold,
sum(amount_sold) as amt_sold
from [detail_data$]
group by country_region
order by 3 desc
· Just after [detail_data$], hit ‘Enter or return key’ to create a new line below
· Type and fiscal_year = , then right click just after the ‘=’ sign
· A list of all ‘Parameters’ is made visible.
· Select the ‘Year (p33) parameter, a sub-menu will popup
· Select ‘p33: Get Display >/*distinct TRIM(fiscal_year)*/
· p33 is the internal Identifier for the ‘Year’ parameter
·
· Once you select ‘p33: Get Display >/*distinct TRIM(fiscal_year)*/’, the query should be as shown below
select country_region, sum(quantity_sold) as qty_sold,
sum(amount_sold) as amt_sold
from [detail_data$]
where fiscal_year = ‘G_PARAM<p33:Display>’ /*distincttrim(fiscal_year)*/
group by country_region
order by 3 desc
· Since “Year” is a numeric value, we will remove the quotes around G_PARAM<p33:Display> and remove the string /*distinct TRIM(fiscal_year)*/. So the final query looks as shown below
select country_region, sum(quantity_sold) as qty_sold,
sum(amount_sold) as amt_sold
from [detail_data$]
where fiscal_year = G_PARAM<p33:Display>
group by country_region
order by 3 desc
· The code G_PARAM<p33:Display> is an instruction to pick the current value of the “Year” parameter identified by id = p33 and get the Display Value
· When you click on ‘Full Apply’, the SQL query is dynamically reconstructed at run time to use the parameter value. If you go to menu Run à Display Log, click on ‘Log Enabled’ button, refresh the chart and go back to the Display Log, you will see the modified query as it is submitted.
· Similarly we add the condition for the ‘Region’ parameter
· For the “Region” we include a LIKE operator. Since we have enabled ‘All Values’ in the region parameter, the ‘All Values’ internally passes a ‘%’ character in the ‘ID1′ field, i.e if you select ‘All Values’ in the region parameter then the ‘G_PARAM<p34:ID1>’ will contain a value of ‘%’, in all other cases it will contain the actual country region.
· Here is the log display with ‘All Values’
· We will apply the above conditions to all the portlets. The resultant queries are as below
By Region
select country_region, sum(quantity_sold) as qty_sold,
sum(amount_sold) as amt_sold
from [detail_data$]
where fiscal_year = G_PARAM<p33:Display>
and country_region like ‘G_PARAM<p34:ID1>’
group by country_region
order by 3 desc
By Channel
select channel_class, sum(quantity_sold) as qty_sold
from [detail_data$]
where fiscal_year = G_PARAM<p33:Display>
and country_region like ‘G_PARAM<p34:ID1>’
group by channel_class
By Product Category
select prod_category, sum(quantity_sold) as qty_sold,
sum(amount_sold) as amt_sold
from [detail_data$]
where fiscal_year = G_PARAM<p33:Display>
and country_region like ‘G_PARAM<p34:ID1>’
group by prod_category
order by sum(amount_sold) desc
Top Countries
select country_name, sum(quantity_sold) as qty_sold
from [detail_data$]
where fiscal_year = G_PARAM<p33:Display>
and country_region like ‘G_PARAM<p34:ID1>’
group by country_name
order by sum(quantity_sold) desc
By Period
select calendar_month_name, sum(quantity_sold) as qty_sold
from [detail_data$]
where fiscal_year = G_PARAM<p33:Display>
and country_region like ‘G_PARAM<p34:ID1>’
group by calendar_month_name
· When you change any of the parameters, you need to click on the ‘Refresh Dashboard’ button. This action refreshes the full dashboard.
Having to click on the ‘Refresh button’ everytime you change the parameter is little tedious. So is there a way where the dashboard refreshes automatically whenever there is a change in parameter?
Absolutely! Infact, you can control which portlet gets refreshed for any particular parameter change.
2 Trackback(s)