In the previous article we saw how to Enable Automatic Dashboard Refresh
Now we add a very powerful Dashboard feature: Drills
Create Drills - Drill from one portlet to another
We will create a drill from the "By Region" portlet to a detail level by 'By Region and Country'. So this detail level will tell us How is the Sales distributed among different countries for each 'Region'
We need to create a new Qlet with the following SQL query
select country_region, country_name,sum(quantity_sold) as qty_sold,
sum(amount_sold) as amt_sold
where fiscal_year = G_PARAM<p33:Display>
and country_region = 'param<Region Name,Asia>'
group by country_region,country_name
order by 3 desc
In the above code, we introduced a new element which is called as Portlet Filters. Each portlet filter is defined using the following syntax ['param<Filter name, default filter value>']. The quotes at the beginning and end of the filter definition are needed if the column is character. If the filter is against a numeric column then you don't need the quotes.
· Once you click 'Apply', it prompts you to enter the Filter value
· We can type any value in the 'Region Name' or just accept the default 'Asia' value.
· Click on 'Apply'. The portlet is refreshed and the results are as shown below.
· Now focus on the 'By Region' Qlet
· Right click on the first column i.e. on any cell in the 'Country Region' column
· Switch to the Drill Settings Tab as shown here
· Once you select the 'Drill Into' value as 'By Region and Country', the Parameter region will show the available parameters.
· Click on the values region and a drop down will appear. This is the list of available columns from the 'By Region' Qlet which is the parent Qlet.
· Select the 'Country_region' value
· Click on 'Done and Close'
· Click on 'Close' on the Drill Settings tab
· Refresh the 'By Regions' Qlet and you will see the 'Drill' indicator on the 'Country Region' column
· Right click on any cell on the "Country Region" Column
· Select 'Get Details by Country'
· The child Qlet is refreshed and the drill value is displayed
· This completes the Drill definition and testing.
· The first row of the Excel file is assumed to be the Column names.
· Its good idea to not have spaces in your column names. For e.g If possible convert a column name like Account Balance to "Account_Balance". This way you can directly refer the column name in your SQL query. Alternatively, If you have blank spaces in between words in your Column name then put them in double quotes "Account Balance" , For e.g. Select sum("Account Balance") from [sheet1$]
If your excel file "C:test_data.xls" is open in Excel, you cannot connect using InfoCaptor. Similarly if your Excel file is connected through InfoCaptor then you may not open it in Excel, you may lose your connection
- Launch Dashboard Designer and Connect to Excel
- Create your first excel based dashboard
- Create summary view portlets from Excel Data
- Create Pie chart from Excel Data
- Create Bar chart from Excel Data
- Create dashboard Parameter
- Enable Automatic Dashboard Refresh
- Create Dashboard Drills