Excel Dash-board : Create Drills

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
from [detail_data$]
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.

Image

· Once you click 'Apply', it prompts you to enter the Filter value

Image

· 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.

Image

· Now focus on the 'By Region' Qlet
· Right click on the first column i.e. on any cell in the 'Country Region' column

Image

· Switch to the Drill Settings Tab as shown here

Image

· Click on the 'Add' button. Enter 'Drill Name', Select 'Drill Into' as 'By Region and Country'
Image

· 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
Image

· Right click on any cell on the "Country Region" Column

Image

· Select 'Get Details by Country'
· The child Qlet is refreshed and the drill value is displayed

Image

· This completes the Drill definition and testing.

Important Information
Column Names
· 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$]

Connection problems?
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

1 , 2 , 3 , 4 , 5 , 67 , 8 , 9

Join InfoCaptor and Get Free Dashboard Software

Posted in Excel | Tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


Would you like to build your dashboard now?

If so, click to buy now, including 60 minutes of dashboard development for free!