Subscribe to RSSArchive for the ‘infocaptor’ Category:
How to Create Excel Dashboards – 4
Previous Part Build Excel Dashboards – 3
We will Add Calculations and Formulas
Add Calculations
Since this is a raw extract of actual transactions, it may need to add additional calculations and columns to create useful metrics.
For e.g in our sample file, we have the “Unit Price” and “Quantity” columns. We will create calculation to derive the “Amount” column
Right click on the Quantity Column, Navigate to Popup Menu
Create ‘ –>Create Calculation
It shows a calculation wizard.
1. Click on “Unit Price”
2. Click on * multiplication operator
3. Click on Quantity
4. Rename the column to “Amount”
5. Click on “Use this Formula”
As you see, the “Amount” column is added right next to the Quantity column
Next – Add Date Hierarchy Columns – Excel Dashboards – 5
Excel Dashboards for Web
Put your Excel Dashboards on the web
Create your excel dashboards on Desktop and then easily put them on the web or intranet.
Download the web dashboard files
The documentation is a complete printable reference guide for converting desktop to web dashboard and has a reference section for chart properties to create advanced charts.
Download web dashboard software
- The link to the web dashboard software is provided within the above documentation link.
- download web dashboard software
Excel ODBC – Access ODBC
>Alternative Video Tutorial or Follow the steps below
Sometimes, due to some improper setup of Microsoft Excel or incomplete components for Microsoft Office, direct connection to Excel files may not be possible.
This tutorial provides an alternative method to connect to the Excel Files. This method also applies to any new format of Excel Files or Microsoft Access databases (ie. mdb or .accdb)
Steps to Create DSN for ODBC connection
1. Click on the Start menu and click on the Control Panel
2. Click on the “Administrative Tools”.
NOTE: You may need to “Switch to Classic View” in windows Vista or newer version of your windows operating system
3. Click on the “Data Sources (ODBC)” setup icon
4. Click on “Add”
5. Select .xls, .xlsx .xlsm .xlsb option if you are using the latest Excel version and format of Excel
6.We will select the normal .XLS version since this northwind.xls file was created using old format
7. Give a short and simple name to this DSN entry
8. Now select and point this DSN name to the appropriate Excel file
9. 
10. select the file and click OK. Close all the dialogs.
11. Now go back to InfoCaptor connection wizard
12. Select the “ODBC” option now instead of the “Excel” option
13. Give some simple name to this connection so that InfoCaptor can easily remember it
14. Type the ODBC name that was defined in the ODBC DSN setup screen previously
15. 
Click on the Connect button and then you can proceed the remaining of this tutorial after the connection part
Free Dashboard PDF Tutorial
How to build Excel Dashboards – 12
Previous – Excel Dashboard Tutorial – 11
How do you share your Excel Dashboard with users?
Now that you have built the dashboard, you can share the dashboard in multiple ways.
” Create a PDF output of the dashboard and send as email attachment.
Click on the PDF icon on the toolbar as shown below and it will instantly create a PDF output
PDF output
” Create Static HTML output. Click on the HTML icon on the toolbar. This option creates a static html page that you can put in on the web. Users cannot interact with the dashboard. It is just a snapshot image of the dashboard at the time the output was generated. It does not refresh automatically. You will need to regerate the output again when the data is refreshed.
” Desktop Sharing. You can put the dashboard definition file (icv) on a file share directory and other users can view the dashboard through the Dashboard viewer. You may need separate license for dashboard viewers for desktop.
Go to Menu Create ‘ Publish to web and it will generate a file that you can put it where the dashboard web server is and it will render the output. The look and feel is different with this option
The web version is using a flash charting engine to the desktop Java chart engine.
Details of converting the desktop to web version are not covered in the document. Please refer online at any of our websites for more details
You can download the complete tutorial as PDF
” Web Sharing: In this option, you can publish the dashboard to web and the dashboard is available real time with all the dashboard parameters and refresh actions
How to build Excel Dashboards – 11
Previous – Excel Dashboard Tutorial
Summary
We built the above dashboard completely from scratch.
- We defined Calculations
- We defined date hierarchies
- We built Date Filters
- We built Date Parameters
- We built Bar Charts
- We built Pie Charts
- We cleaned the dashboard layout
Next – Share Excel Dashboard with other Users
How to build Excel Dashboards – 10
Previous – Excel Dashboard Tutorial – 9
Cleanup – Resize and Organize
Give appropriate names to each portlet.
During the chart creation if we did not provide names, now is the time to provide them.
Right click on each title and click on “Edit”
Scale down the Size
Note, you can drag the corners of each portlet to resize the frames and drag the title name to place them on the canvas
Screen real-estate is very important when designing any dashboard or web application. For better space utilization, we will convert the standard pie to a 3D view
Also, while arranging the objects, keep the most important charts on the top and left area. Put all the detailed portlets at the bottom.
Avoid horizontal scrolling
Remember, do not put any objects on the right side of the scroll bar. Always, keep the scrolling to vertical as it is convienient for viewing.
The above layout will cause for horizontal scrolling and is not a good practice.
Once you do the basic layout of placing the portlets so that they are distinctly visible, it becomes easy to use the resizing options within the tool.
Navigate to Edit ‘ Resize Mode
Once in the resize mode, you can select each object and they are enabled for group resize and placement
Select all the parameters on the top and we will align the top edges with respect to the first one.
As you see, each plet is shown in red with a bar on top indicating it is selected for alignment
One more, thing, you can also turn the Grid mode to see a guide for your alignment and placement
Goto Edit ‘ Show Grid
Note: Once you are done with resize and align with one set of objects, make sure to deselect them before working on the next set of objects
It is best practice to first resize all the objects. All objects on the same horizontal line should have the same height. Select the optimum height of any object on that particular horizontal line and then right click on the title and resize the height of all the selected ones. Then apply the alignment -> Top , deselect the objects and start with the objects in the next horizontal line.
This completes our basic dashboard which is dynamic and extremely informative.
If you need to further customize the look and feel please refer to our online tutorial and reference section
http://www.infocaptor.com/user_help/bi-dashboard-help.htm
Next – Excel Dashboard – Complete Order Management Dashboard
How to build Excel Dashboards – 9
Previous – Excel Dashboard Tutorial – 8
Finish the Dashboard Design and Development
By now, your dashboard is quite chaotic. You have objects all around your dashboard canvas with different sizes. We will start the process of finishing our design.
Before proceeding, we save a copy of the dashboard.
Navigate to menu File ‘ Save As
Give a name to the file so that you can identify the raw dashboard definition file.
Click on Save.
Next, go to menu File => Close All
Note that there are no dashboards to view.
If you need more backup copies to be created or create different versions of the same dashboard definition, then go to the File Explorer, select the file and type – Ctrl-C, and then Ctrl-V
Now, Click on the File ‘ Open
Select the file “Order Management Analytics.icv”
Note that we are not touching the – Initial Copy file.
Click on Open
Get rid of objects you don’t need
We don’t need the initial table portlet, so we will delete that.
Select the “Name” portlet and then click on the “Scissor’ icon on the toolbar
Click “Yes” to confirm.
Note, we created the backup copy of the dashboard so don’t worry if you mess up the dashboard. You can always go back to the original and start again.
Similarly remove all objects that you don’t need it.
Next – Excel Dashboard Tutorial – Arrange objects in sizing and alignment
How to build Excel Dashboards – 8
Previous – Excel Dashboard Tutorial – 7
Create Pie Chart
We will now create a pie chart by product name
It is nice to know that the revenue distribution is not dominated by a single product.
We can now change the “Category name parameter” to see the product distribution for other category
By Category = Beverages
Summary of steps so far
” Create a foundation table portlet (Qlet)
” Add Calculations and any derived columns
” Add Date hierarchy columns as needed
” Create Date parameters if needed
” Create drop down list of values as parameters
” Create charts using the base Qlet
” Delete the foundation portlet when not needed and you are done with the dashboard design
” Arrange the remaining portlets in desired format.
Once you are done with all the Qlets (query/table portlet) and Glets (graphic portlets), it is advisable to save the dashboard file, copy the file and work on the new file for finishing and polishing the dashboard.
Next – Excel Dashboard Tutorial – 9
How to build Excel Dashboards – 7
Previous – Excel Dashboard Tutorial – 6
Create Bar Chart
Bar charts reveal important trend in your data. The very important reason we split the date into year, quarter, month, week and day components is to see if there is a trend in the sales or order information.
Right click on the Qlet title (”Name” ), and then click on the “Create Chart”
It opens a “Chart Factory Wizard”
1. The top drop down contains a list of all the chart types that are supported.
2. The X-Axis drop down has all the available fields from the Qlet
3. The Y- Axis drop down has all the available fields from the Qlet
4. The Y- Aggregation has (SUM, COUNT, MAX, MIN,AVG) values
The process is to pick the base column that will form the X-axis, the Y-axis is the numerical value column that will be used to plot the bar or appropriate chart type. The y-aggregation is required, usually we should pick “SUM” but you could use “AVG” or other aggregations as you see fit.
In our case, we want to see a “year over year trend for our sales growth”
1. Select “Bar” as the chart type
2. Select “Or Year’ as the X- Axis
3. Select “Amount” as the Y- axis
4. Select ‘Sum’ as the aggregation (by selecting sum, we are telling it to sum the amount and group it by year value)
5. Click on the “Create Chart” button
6. It shows the Glet Editor for your review which is a pre-built SQL query.
We change the Glet Name, and click on “full apply” button
It creates a bar chart as shown above, you can drag the borders and expand the view, it will scale automatically.
We just dragged the corners and expanded the view. Do not close the chart wizard yet, we will create other views like a rubber stamp.
By Quarter
Go back to the chart factory and only change the X-Axis column to ‘quarter’ field
Click ‘Create Chart’
Change the name to “By Quarter” and click “Full Apply”
Do not worry about the size and the placement. Just drag by the title “By quarter” and place it in a position where you can see it. At the end we can re-arrange the portlets on the dashboard canvas.
The column “Or_Qtr_Disp” has Qtr, 2 digit year and the last digit indicates the quarter number. The reason we chose this value is so that we can order the quarter across multiple years.
As you see, we can clearly see a trend emerging from our data. The Qtr-98-1 has the tallest bar. Now lets create a month view so we can nail down, which month contributed to that spike
By Month
We go back to the chart factory and select “Or_Mth_Yr_NN” which contains Year, month number and month name(3 letter).
So far the tallest periods are March and April of 1998. Why is that? Did you do special promotion, did you get extra word of mouth or publicity?
Similarly you can create slices by Week, or each Day. Note that as you go more granular, the chart may look more congested.
Now let us see which products are doing better.
Click on apply
Change the name to “By Product” and in the SQL query below, type, “Order by 2 Desc” which will sort the products by the revenue in the descending order
You have now found your winning product line.
But lets see how each of the products fair well within their own category.
Next – Excel Dashboard Tutorial – 8
How to build Excel Dashboards – 6
Previous – Excel Dashboards – 5
Create Year and Category Parameters
Remember, we have not built any charts yet. It is very important that we build our foundation Qlet and its corresponding parameters so that all of the drop down parameters can be leveraged in the charts.
It definitely helps to plan your dashboard development. Since the dashboard tool is easy to work with, we should still make an attempt to always plan so that we don’t duplicate our effort.
During the development, it may happen that you will get lots of ideas on implementing different ways. I would suggest to save the base dashboard definition once you have frozen all the parameters and Qlets. Before you start building charts, create a copy of the dashboard file as backup.
In our data, we have details about items that are sold on a particular date. We have the unit price, and quantity information. We also have the category for each item that they belong to. So it makes sense to include a parameter for the item category so that we can select only the items belonging to a particular category.
Right click on the “Category Name”, navigate to Create ==> Create Parameter
It creates a new parameter named “Category Name”
You may place this anywhere you feel appropriate.
Similarly, right click on the “Or Year” column and create the parameter
NOTE: Often it does not make sense to have a year drop down and a date range parameter.
Because if the year value and the date ranges fall apart or do not intersect then you will not see any data output. In this tutorial we provided the year and date ranges only for demonstration so if you need only one, do not use the other.
Next – Create Excel Dashboard – 7