How to Build Dashboard Using Excel

34 

Full text

(1)

This paper will illustrate how to build Dashboard (reports, charts) from Excel Spreadsheet. The data file is also This paper will illustrate how to build Dashboard (reports, charts) from Excel Spreadsheet. The data file is also  provided so you can follow the

 provided so you can follow the steps.steps.

http://www.infocaptor.com

(2)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

How to build Dashboard (reports, charts) from Excel Spreadsheet...3

How to build Dashboard (reports, charts) from Excel Spreadsheet...3

This is This is the sample the sample Excel Spreadsheet tesExcel Spreadsheet test_data.xls...t_data.xls... ... 33 Launch Launch InfoCaptor...InfoCaptor... ... ... 44 Connect to the Excel file...5

Connect to the Excel file...5

Create Create your your first first Dashboard...Dashboard... ... ... 66 Add Add content tcontent to yo your new our new Dashboard...Dashboard... ... 77 Syntax for Querying Excel Worksheets...7

Syntax for Querying Excel Worksheets...7

Your first Portlet (Qlet )...7

Your first Portlet (Qlet )...7

Portlet Portlet Customizations...Customizations... ... ... 99 Create Create Summary Summary Portlets...Portlets... ... ... 1010 By By Region...Region... ... ... 1010 By Product Category...10 By Product Category...10 By Channel...11 By Channel...11

Create Pie Chart (Glet)...12

Create Pie Chart (Glet)...12

Add Parameters...18

Add Parameters...18

Year Parameter...18

Year Parameter...18

Link the Parameters to Portlet...22

Link the Parameters to Portlet...22

By By Region...Region... ... ... 2525 By Channel...25 By Channel...25 By Product Category...25 By Product Category...25 Top Countries...25 Top Countries...25 By By Period...Period... ... ... 2525 Dynamic Refr Dynamic Refresh of esh of Dashboard when Dashboard when changing Parameter...changing Parameter... 2626 The The Dashboard Dashboard So So Far...Far... ... ... 2929 Create Drills Create Drills – Drill – Drill from one portlet from one portlet to another...to another... ... 2929 Important Information...34

Important Information...34

Column Names...34

Column Names...34 Connection

Connection problems? problems? ... ... 3434

CopyRight Rudra

(3)

How to build Dashboard (reports, charts) from Excel Spreadsheet...3

How to build Dashboard (reports, charts) from Excel Spreadsheet...3

This is This is the sample the sample Excel Spreadsheet tesExcel Spreadsheet test_data.xls...t_data.xls... ... 33 Launch Launch InfoCaptor...InfoCaptor... ... ... 44 Connect to the Excel file...5

Connect to the Excel file...5

Create Create your your first first Dashboard...Dashboard... ... ... 66 Add Add content tcontent to yo your new our new Dashboard...Dashboard... ... 77 Syntax for Querying Excel Worksheets...7

Syntax for Querying Excel Worksheets...7

Your first Portlet (Qlet )...7

Your first Portlet (Qlet )...7

Portlet Portlet Customizations...Customizations... ... ... 99 Create Create Summary Summary Portlets...Portlets... ... ... 1010 By By Region...Region... ... ... 1010 By Product Category...10 By Product Category...10 By Channel...11 By Channel...11

Create Pie Chart (Glet)...12

Create Pie Chart (Glet)...12

Add Parameters...18

Add Parameters...18

Year Parameter...18

Year Parameter...18

Link the Parameters to Portlet...22

Link the Parameters to Portlet...22

By By Region...Region... ... ... 2525 By Channel...25 By Channel...25 By Product Category...25 By Product Category...25 Top Countries...25 Top Countries...25 By By Period...Period... ... ... 2525 Dynamic Refr Dynamic Refresh of esh of Dashboard when Dashboard when changing Parameter...changing Parameter... 2626 The The Dashboard Dashboard So So Far...Far... ... ... 2929 Create Drills Create Drills – Drill – Drill from one portlet from one portlet to another...to another... ... 2929 Important Information...34

Important Information...34

Column Names...34

Column Names...34 Connection

(4)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

How to build Dashboard

How to build Dashboard (reports, charts) from Excel Spreadsheet

(reports, charts) from Excel Spreadsheet

For practice, you can

For practice, you can download this excel spreadsheetdownload this excel spreadsheetand use the same SQL code in this document.and use the same SQL code in this document. All of the

All of the SQL code that is highlighted in Yellow can be selected and copied using the Acrobat select optionSQL code that is highlighted in Yellow can be selected and copied using the Acrobat select option Did you download

Did you download InfoCaptor Dashboard Designer InfoCaptor Dashboard Designer ? If not, then it is a go? If not, then it is a good idea to have od idea to have it installed so you canit installed so you can follow the steps easily.

follow the steps easily.

This is the sample Excel Spreadsheet test_data.xls

This is the sample Excel Spreadsheet test_data.xls

CopyRight Rudra

(5)
(6)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

Connect to the Excel file

If the path of the Excel file is “c:\files\data.xls” then use “\\” , result c:\\files\\data.xls In this case the file to be used is “d:\\test_data.xls”.

Select “Excel File – sun.jdbc.odbc.JdbcOdbcDriver” from the Driver list.

(7)

Create your first Dashboard 

• Click on the “New Dashboard” button (yellow highlight) • Enter the desired dashboard name (green highlight)

(8)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

 Add content to your new Dashboard 

Syntax for Querying Excel Worksheets

• Each worksheet in Excel file is treated like a SQL table • The worksheet is referenced as [worksheet name$] • You can perform normal SQL selects on this table

Your first Portlet (Qlet )

Lets create the first portlet

We will show the complete file in the portlet using below query “Select * from [detail_data$]

{detail data is one of the worksheet in the test_data.xls file}

(9)

• Click on the “New Qlet” button or select CreateQuery Portlet from Menu bar  • In the property window as shown above, enter the Qlet Name

• Select the Connection

• Enter you query in the text editor  • Click on the Apply button

(10)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

Portlet Customizations

Once your portlet is visible, you can change the cosmetic features such as colors and fonts, increase/decrease column widths, change the location of the portlet and resize the portlet.

We just changed the column colors.

(11)

Create Summary Portlets

Using the same data, we can create any slice of data by grouping the numbers by any column. Click on the new Qlet button and type the following query

By Region

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

“order by 3 desc” tells to sort by the third column in descending order. You can also mention the sort this way “order by sum(amount_sold) desc” or “order by sum(amount_sold) asc” for ascending

We will add few more portlets as shown below.

By Product Category

select prod_category, sum(quantity_sold) as qty_sold, sum(amount_sold) as amt_sold

from [detail_data$] group by prod_category

(12)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

By Channel

select channel_class, sum(quantity_sold) as qty_sold, sum(amount_sold) as amt_sold

from [detail_data$] group by channel_class

order by sum(amount_sold) desc

(13)

By Now the dashboard should be getting crowded

But what is a dashboard without charts? Lets add some colors to it with charts.

Create Pie Chart (Glet)

We will convert each of the above queries into Pie chart. For a pie chart we just need two columns, the first column becomes the base on which the pie sections are named after and the second column is the numeric column that decides the pie size for each section.

Since we have three columns in all of the above queries, we will pick the first and the second column.

• Click on the New Chart button • Enter the name of the Glet

• Select the Chart Type (2d Pie or 3d Pie) • Enter the following query

select country_region, sum(quantity_sold) as qty_sold from [detail_data$]

group by country_region order by 3 desc

(14)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

We don’t need the order by clause for a pie chart. You can select “pie” , “Pie 3d” or “Ring” from the drop down.

Here is the 3D pie version

Here is the “Ring” version

(15)

Similarly lets create other pie charts By Product Category Pie chart

select prod_category, sum(quantity_sold) as qty_sold from [detail_data$]

group by prod_category

By Channel Class

select channel_class, sum(quantity_sold) as qty_sold from [detail_data$]

(16)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

Your dashboard should look something like this

As you see the portlets are randomly placed and sized. We will take care of the placement and sizing later. Lets add few more stuff to the dashboard.

 Now we add a Bar chart Top Countries

select country_name, sum(quantity_sold) as qty_sold from [detail_data$]

group by country_name

order by sum(quantity_sold) desc

(17)

We select the “Chart Type = Bar”, enter the query and hit “Full Apply”

The bar chart is displayed but there are few ugly things. The labels on the X-Axis are overlapping, we need  proper names for the X and Y axes and what if we just need to see the “Top 5 Countries”?

As you see, we changed the Max Rows=5 which limits the bar chart to top 5 countries. We changed the X Axis label = Country, Y Axis label = Quantity Sold and finally we set the x Axis label angle to 30 degrees

Lets add another bar chart by Month By Period

select calendar_month_name, sum(quantity_sold) as qty_sold from [detail_data$]

(18)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

 Now we align and re-size the portlets

Click on MenuEdit Show Grid. Drag the borders of each portlet and resize them as you see fit

(19)

 Now that our dashboard is almost ready, we will add some parameters and make it more user friendly.

 Add Parameters

 Year Parameter 

(20)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

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

(21)

• 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 

(22)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

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.

(23)

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

(24)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

• 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 

(25)

• For the “Region” we include aLIKEoperator. 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’

(26)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

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.

(27)

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 pa rticular parameter change.

Dynamic Refresh of Dashboard when changing Parameter 

First take a note of the internal identifiers for each of the Graph portlet and Query portlets. When you click on any portlet, the internal ID is displayed at the bottom in the status bar 

The internal ID for ‘By Region’ portlet is = ‘q32’.

 Now we will add a trigger in the ‘Year’ parameter to refresh ‘By Region’ whenever the user changes or selects a year value

(28)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

As shown above, edit the ‘Year’ parameter (right click on ‘Year’ title, select ‘Edit’) Set “Run Dynamic Java Script” = ‘Y’

Click on the ‘Dynamic Java Script Editor’

Type the following command “//<REFRESH_LIST>q32</REFRESH_LIST> You can refresh more than one portlet by adding the portlet id separated by comma e.g. “//<REFRESH_LIST>q32,q34,g35</REFRESH_LIST>

 Now if you need to refresh all the portlets then there is an easy shortcut

Just type “//<REFRESH_LIST>ALL</REFRESH_LIST> NOTE: “ALL” is in Caps Similarly, add the trigger command to the ‘Region’ parameter 

(29)
(30)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

The Dashboard So Far 

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 (in this case pldesc) is character. If the filter is against a n umeric column then you don’t need the quotes.

(31)

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

(32)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

•  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

• Click on the ‘Add’ button. Enter ‘Drill Name’, Select ‘Drill Into’ as ‘By Region and Country’

(33)

• 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

(34)

How to build Dashboard using Excel data with InfoCaptor Dashboard Designer 

• Select ‘Get Details by Country’

• The child Qlet is refreshed and the drill value is displayed

• This completes the Drill definition and testing.

Figure

Updating...

References

Updating...

Related subjects :