Dashboards – Free Dashboard Designer – Examples and Tutorials

How to build Excel Dashboards – 3

Previous step : Excel Dashboards – 2

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

Posted in Excel, How-to | No Comments »

How to build Excel Dashboards – 2

Previous Step: Excel Dashboards – Part 1

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

Posted in Excel, How-to | No Comments »

How to build Excel Dashboards – 1

Databases are often under lock-n-key of the IT Department and not possible to connect directly due to security reasons. So if you plan to build a Dashboard prototype or a Dashboard Mockup it makes sense to export the data to Excel and build it.

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.

Posted in Excel, How-to | No Comments »

Totals and Sub-Totals in Dashboard

Data Model manipulation

If you are accustomed to the data manipulation in Excel then the following features will instantly enable you to change your table appearance and data manipulation ability to the next level.

//you can add blank rows and columns and also Grand total using the following API
// data.addColumn(”XYZ”,4); /* to add a new column at number 4 with title XYZ*/
// data.addRow(6); /* adds a blank row at number 6 */
// data.addRowTotal(”Grand Total”, 1, -1 ) ; /* The first input is the total label, second is the column position where the label appears, the third is to indicate which columns to total => -1 to total all numeric columns

//you can also perform calculations on the result from the Query
//You can access columns by col[column_number], rows by row[row_number] and each cell by cell[row, col]
//each instruction should end with a semi colon “;”
//all instructions should be enclosed within the <calc> …</calc> statements, it can span multiple lines. Following is just an example

// <calc>
//
// col[3]=col[2];
// rowl[4]=row[2]+row[3];
// col[5]=col[2] + col[3] + col[4];
// cell[2,3] = cell[3,3] * cell[5,6];
//
// </calc>
// Please delete the above instructions once you are familiar with them as it may affect performance

data.addSubTotal(”Person #DATA subTotall”, 0, -1 ) ;
data.addRowTotal(”Grand Total”, 1, -1 ) ;

Posted in Excel, How-to | No Comments »

Excel Date Functions

In the information jungle for Excel, when it comes to find certain “How-to” with Excel, it is like finding a “needle in Mount Everest”.

Here is the list of functions that you can use when using ODBC + SQL + Excel

Again, the following stuff applies only when using SQL with Excel over ODBC connection.

One of the important function is Dateserial which converts to a date type given the year, month and day.

Posted in Excel, How-to | No Comments »

Hurray! Web Dashboards with InfoCaptor

I am on the final stages of releasing the beta for our web based dashboard engine. This was a long time coming. The dashboard engine is completely PHP based with Flash charting engine. We have licensed the Flash charting engine and embedded as part of the server dashboard engine.

Flash charts are beautiful and I wanted the simplicity of PHP as the backbone. Apart from this, there are so many wonderful open source tools that are being leveraged in this product namely jQuery for Ajax, jqGrid for Table portlets that it really helps to keep the cost down.

The best part is there will be a “publish to web” menu and a button in the InfoCaptor Desktop Dashboard Designer which will let users to simply publish to PHP based engine. I am also planning to include a light weight web server so that you will see the web dashboard in action everytime. No need to contact any IT person and if you keep your PC on, you may even forward the dashboard URL to your colleagues within your network (be sure that you are inside your VPN or network).

The look and feel will be different because the desktop version is Java based and it uses jFreechart engine for charting whereas for the web based we are using a Flash charting engine.

InfoCaptor has already expanded into the desktop market by channeling into documentation tools and small business dashboard solution. What do you do if you are just a 1 or 2 person company? You certainly don’t need a web server when you can view the reports and dashboards on your PC.

But as you grow, it makes sense to have a true web based version and this is what InfoCaptor web based version will allow you to achieve.

I am excited with this release as it opens up so many possibilities, joint ventures and dashboards as SaaS, and OEM Dashboards

So stay tuned, the big release is coming!

-Nilesh

Posted in Announcement, news | No Comments »

Easy Excel Dashboards

Excel dashboard Designer is a all purpose reporting and dashboard development application.

Here is a brief process of how you build dashboard with Excel data

1. Open Excel File ( create as many connections to multiple excel files)
2. Visually join all worksheets or query single worksheet
3. Create parameters (pivot tables)
4. Launch the chart factory
5. Create as many charts
6. Repeat the process from step 2 to build another set of charts

Check this video

Visit Excel Dashboards . This is a dedicated website for Excel Dashboarding process.

Posted in Announcement | 1 Comment »

Foxpro Dashboard connection

Tags: foxpro, odbc, visual foxpro

Posted in How-to | No Comments »

Dashboard Chart Factory

We will be introducing a chart factory wizard with the Dashboard Designer. With this chart wizard, it will be a breeze to create different chart objects from a tabular output.

Here is a brief flow of how the task can be accomplished.

1. Connect to the Database or Excel file
2. InfoCaptor presents a wizard that allows you to build a blank dashboard or build a query on existing dashboard
3. Once you have the dashboard created you launch the query builder, drag all the tables you need, join them accordingly and create a valid SQL query.
4. The above SQL query is now your starting point
5. You create a tabular portlet or Qlet
6. From this Qlet you add all the needed parameters
7. It is necessary to decide all your parameters because it updates the table query accordingly which will form the basis of all the Charts
8. Once you are ready, click on the Qlet title and launch the chart factory.
9. Pick a chart type
10. Pick the columns
11. Pick the aggregations
12. Click on create chart
13. This presents you with the chart editor and shows you how the chart SQL looks like. You can rename the chart title or change other properties you may wish at this point (you can leave it as it is and update them later)
14. Click Apply
15. You have your chart created.

Stay tuned for detailed steps with screenshots and videos

Posted in Announcement, news | 1 Comment »

SQL Server named instance connection

If you are trying to connect InfoCaptor (Dashboard Designer) to a named instance of SQL Server then follow the procedure below.

NOTE: This is a work around until we enhance the connection wizard to include instance name

To connect to SQL Server, we use the jtds driver http://jtds.sourceforge.net/faq.html#instanceName

and the default jdbc url with InfoCaptor does not support additional properties.

In order to connect with properties the URL is as listed below

jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]

Assuming that your normal connection to SQL server through Microsoft tools (such as SQL Server Studio) is

e.g Myhost\MyInstance

1. Goto InfoCaptor directory and there is a file called drivermap.txt

2. Edit the file in a notepad

3. Add a new line

“SQL Server Named Instance – net.sourceforge.jtds.jdbc.Driver”, jdbc:jtds:sqlserver://<HOST>:<PORT>/<DB>;instance=MyInstance“;

Where MyInstance is your named instance.

4. Save the file and restart InfoCaptor.

5. Now goto connection wizard and select ‘SQL Server Named Instance …’ from the driver list

6. Enter the user, password, host, port and the database name.

Posted in How-to | 2 Comments »