All settings are done in the connection_methods.php

Windows 7 and Excel 2007

(support for .xls, .xlsx, .xlsm, .xlsb)


$db_url="DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};FIL=Excel 12.0;DriverID=1046;Dbq={$excelFile};DefaultDir={$excelDir};ImportMixedTypes=Text";


Windows XP and older Excel (just .xls)


$db_url="Driver={Microsoft Excel Driver (*.{$fileinfo['extension']})} ;DriverId=790;Dbq={$excelFile};DefaultDir={$excelDir};ImportMixedTypes=Text";


How do you get Fiscal Quarter

Let say your fiscal year begins from 1st July then

First Quarter = { 7 , 8 , 9}

Second Quarter = { 10, 11, 12}

Third Quarter = {1, 2, 3}

Fourth Quarter = { 4, 5, 6 }

So with the above logic let us create a formula

• Right click on the date column
• Select “Create Calculation”
• 
• Type the following formula
• switch(
  month(“Order Date”) in (7,8,9) , ‘Qtr1′ ,
  month(“Order Date”) in (10,11,12) , ‘Qtr2′ ,
  month(“Order Date”) in (1,2,3) , ‘Qtr3′ ,
  month(“Order Date”) in (4,5,6) , ‘Qtr4′
  )
• Click on ‘Use This Formula’

Here is the Fiscal Quarter

Note: You can change the Quarter text to anything like ‘Q1′ instead of ‘Qtr1′

Fiscal Month

Similarly, if you need fiscal month, we can use the following formula

switch(
month(“Order Date”) >= 7 , month(“Order Date”)-6 ,
month(“Order Date”) < 7 , month(“Order Date”) + 6
)

Fiscal Year

switch(
month(“Order Date”) >= 7 , year(“Order Date”) ,
month(“Order Date”) < 7 , year(“Order Date”) – 1
)

If you have a different Fiscal year begining then replace the numbers accordingly.

Launch Dashboard Software and Connect

Launch InfoCaptor Dashboard Software on your Desktop and click on the connect button on the toolbar

The connection wizard has items that simplify connecting to different data sources.

For Excel Files, Click on the Excel button

Once you click, it automatically selects the appropriate Driver for you

Click on the “Access/Excel File” button.

Select the order_raw.xls file. It populates the connect name for you. But you can change this name. Connect Name is for your reference

NOTE: Make sure that the order_raw.xls file is not open in MS Excel. The Excel file is locked once you connect to it. If the file is open in MS Excel, you may get connected but not able to query and may get unpredictable results.

Now, click on the Connect button
Upon successful connection, the connection wizard disappears and a new action wizard pops up.

Next : How to build Dashboards – 3

In this example we are using the northwind sample database

Click on connect

Once connected it will open the Option wizard

Since there is no dashboard open, the “Build New Query” is disabled.

Click on the “New Blank Dashboard”

Enter some name for the dashboard “Northwind Dashboard”

Click OK

This Action immediately launches the Query Builder interface

Expand the node on the left

This displays a list of available tables

Double click on any table and it brings the table in the Query Builder window

You can now select columns and join then

To create joins, just drag the mouse on a column in the orders table. Once you see the join link appear you may release the mouse button. Now go to the second table and click on the column to which it needs to be joined.

Click on the SQL tab and you can see the SQL query that is automatically built for you

Now click on the “Create Table” button

This action creates a Qlet and places it in the dashboard

You can now right click on any column and create a drop down parameter

The parameter now automatically refreshes the qlet when you change the value

Step by Step Dashboard Video, Watch Now

Modular Dashboard Design
Lot of the online tutorials and Dashboard e-books teach you to build dashboard in Excel file. They provide you with templates, you fill in the information and build the dashboard using Excel charting techniques.

If you use Excel Templates for your real dashboarding needs then you may end up with any of the below problems.

” Big Bulky and bloated excel files
” Undocumented Macros
” Zero verification on the data authencity
” No way to refresh the dashboard (lost productivity)

“Best Practise for Dashboard Design”
Modular Design – Keep Data and Presentation Separate
How? – Use Excel only for storing your data. Nothing more.

Do not build any visualization in the same place where your Data resides.

Benefits of this approach
” Your dashboard or presenation layer is independent – Dashboard definition is stored separately
” Increased productivity – When you have fresh data just refresh the dashboard, no redevelopment needed
” No Messy Excel Macros or coding knowledge required – Just drag and drop tables and visually build Tables, Charts, Speedometer, Gauges, Dials, Thermometers and more charts
” Simplified Distribution and Presentation – You can Export the Dashboard to PDF or HTML and just send them as attachments
” Save Time – Just build the dashboard presentation layer only once and automate the refreshes.

Lets begin Building Dashboard

Data Preparation

Export your transaction data to Excel file by running a report or requesting your IT department.

There are certain rules to be followed when you get the data in Excel files.

Here is a sample Excel dump of Order Entry data.
The first row is always treated as the column names.

If you inherit an Excel file with pivots and charts all over the places then try to copy just the raw data in another worksheet in the same file or create another file. If it is not possible to keep the first row as the column names then you could define “named ranges”. In the dashboard designer it is possible to access the data using Cell ranges but then future updates to the Excel file could break the dashboard, for e.g if the data goes beyond the Cell range.

NOTE: Make sure column names do not have any funky characters. Just keep it to simple alpha-numeric column names.

Create Dashboard and Table portlet

Once connected, it will show you a list of action items to pick from

We will click on “New Blank Dashboard“

In the Dashboard Name, we type in “Order Management Analytics”

Click OK.

It will present you with a table browser showing all the available worksheets as tables

Expand the node to see all the worksheets.

In this example file, we just have one worksheet named “order_data”. Note how, it attaches a $ sign at the end, which indicates this is an Excel Worksheet.

So make sure you do not have any dollar sign when naming the Excel worksheet and no other special characters and no white or blank characters. Replace blank characters with “_” underscore character

Double Click on the “order_data$” node

Click on the “* alias=ord” item at the very top to select all the items

You may change the name of any column or any other property such as sort and group by.

We will leave it as it is and click on the “Create Table” button.

This action creates a Table Portlet (Qlet) in the Dashboard canvas

We will use this Qlet as our basic building block for the following exercise

Next : How to build Excel Dashboards – 4

We will also create few bar trending charts.

Consider the below sample data

Source File: You can download the excel file here

The above excel file tracks few metrics such as safety, quality and revenue. This is just a sample data and may not make sense in actual world but just imagine a manufacturing company that wants to track its overall safety and quality score and also track its total revenue. Now the company may use its own method at deriving the individual safety and quality scores.

They may have a dedicated team to collect safety violations and product defects and then use some formulae to convert those data into final score for any given month. We are not concerned with any of the methods on how those scores are generated. You are the dashboard person and the company has provided you the metrics by each month and now your job is produce a nice dashboard that shows relevant charts for the given data.

Since the data is tracked for each month, it makes sense to show a line or bar chart trending for the metrics.

Dials and Speedometers are relevant when we need to show performance of a single value. So in this case, since we have 12 month data, how do we show single value on the meter chart?

One solution is to show an ‘Average’ value for the safety and Quality metrics or we could show a dial that shows the current month values.

So the GOAL for this exercise is: Show bar chart for Safety trending 12 months and show average and current month value on the dial chart.

Dial chart requirement: For the dial chart, we are measuring the performance, whether we are on track or missed the goal. In order to achieve that, we create three new columns for safety such as ‘safety_bad’, ‘safety_ok’, safety_good’. These values need to be decided by the company management. If you are not sure, then look at previous year data and decide what should be good and bad values. The dials are used to guide the company to perform better or atleast improve that portion of the company operation represented by the metric.

First let us create the date hierarchy columns from the Period column. You need to make sure that the Period column is an actual Excel date column, else the hierarchy will not be generated

Right click on the period column, select Create -> Add Year, Quarter, Month columns

As you see we added the year and month columns. We may not use all of the above columns but having them ready gives us the option during the chart building.

Now right click on the “Name” and select ‘Create Chart”

We selected the ‘Bar’ chart type

For the x-axis we select the month column and for the y – axis we select the Safety metric

Next, we click on the “Create Chart” button

This action takes us to the below dialog

We rename the chart to ‘Safety’ and then click on ‘Fully Apply’

This action create the below chart (You may need to move the screens and dialog around to see the chart behind)

Now having the chart wizard open, we change the selection to ‘Dial’ chart

Check the Dial chart options below

We modify the options as below

• You can type the low and high range values for each range on the dial.
• In our case, we have defined the safety bad, ok and good values in our spreadsheet so when our goals change, we just change the spreadsheet and the dials will automatically take care. Also, what if we needed different goals for each month so rather than hard coding the range in the chart, we get them from the excel file

We first change the label sequence for the “Region Name” from “Bad” -> “Warning” -> to “Good”. For the dial chart the labels are not used so make sure that the colors are selected appropriately.

You may click on the color icon to change the colors of your choice

Next, we select the range boundary values

The safety_bad value from our spreadsheet represents the lower boundary of our bad region, “safety_ok” represents the upper boundary of our bad region, similarly ‘safety_ok’ is the lower bound for our ‘ok’ region and so on. For the ‘Good’ upper bound we simply typed a value but as general practice we should have one more column for the upper bound of the final region. In our case the final region is ‘Safety Good’.

Next, for the pointer value, select the column that you want to show on the dial chart. In our case we select ‘Safety’ and aggregation as ‘Avg’

Next, we click on “Create chart”. This action shows the dialog below

We change the chart title to ‘Average Safety’

NOTE: remove the double quotes in the SQL statement for the group by. This will cause an error.

Click on ‘Fully Apply’

Here is our final Dial Chart

Next, create a dial chart to show current month value

We will change some conditions on the main Qlet. Right click on the “Name” and select ‘Duplicate’

It creates an exact copy of the qlet table. We change the name to ‘Current Month’

Right click on the ‘Period’ column and select ‘Create’ -> Create Date Filter

This action shows a dialog as shown below

First change the operator to ‘Between’

Next select ‘First Day of Month’ for the from and ‘Last Day of Month’ for the To val.

Click on ‘Use This Filter’

This action applies the filter to the Qlet and it shows only currrent month row

Next, right click on ‘Current Month’ and select ‘Create Chart’

All of the settings remain the same except the ‘Aggregation’, make sure to select ‘NONE’

Once all the three charts are done, we arrange them as below

Similarly, you may create the charts for “Quality” and “Revenue”

The below article will explain how to drill from a bar or pie chart to another chart or table.

From the tutorial above, we need to prepare the child portlet in order to receive the parameters for drill purpose.

This is true for charts or tables. The key to preparing a “child” or the “drill into” portlet is the use of the filters on the column.

You define filters using the “param” variable.

Consider the following Chart and the detail table

At this point both of the portlets are independent. Let us assume we need to drill from the above bar chart to the table below.

So when the user clicks on any of the bars it should be able to send the country name that it clicked and send that to the detail portlet.

Drilling into Bar or Pie chart involves few more steps than the just drilling between table to table.

1. Prepare the “Drill Into” or the child portlet.

We edit the Details portlet and add a filter condition for the country name

We added a condition to the existing SQL

“and country_name=’param<country,x>’

Click apply and it will show no records as it is now dependent portlet.

2. Note down the portlet id for the child portlet

When you select any portlet, the status bar at the bottom shows the Qlet ID. in our case, the qlet id is “q31″, for charts it starts with a “g”

3. Define the drill link from the parent chart

Edit the chart properties

There is the property “Drill into Portlet”

The default value will be ‘NO_DRILL’

Replace the value with the child’s ID followed by number 2,1

So in our case the string would be {q31,2,1}

Explanation of the format: When you click on the bar chart, the bar transmits two values, one is the actual number value (y axis) and the second is the country name (x axis). The number 2 represents the X-axis value and since in most of the drill you need the X axis value we tell it to transmit the second value first.

click apply and your drill is set.

When you click on the bar, the child portlet will automatically align just below the bar chart.

Similarly you can drill from a Pie chart as well. Other charts are not drillable.

Read how MockupTiger fixed the font rendering issue. You will also notice that with the fix, it is now possible to add any of your favourite font for wireframes. Why just limit to Comic Sans or Arial. Try the Google web fonts for instance.

Build beautiful Dashboard prototypes with MockupTiger Wireframes

Goodbye Excel! now I can have true charting prototypes with Wireframes and mockups from MockupTiger

MockupTiger when released had Clickable prototyping feature using the Text widgets

Now all the text widgets, icons can be linked. There is a new element called hotspot that you can overlay on any kind of object/widget to simulate Clicking or interactivity

Read more about building Clickable Prototypes using Mockups