Excel Dashboard Software

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

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

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

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

We are releasing the web component for Excel dashboards in few days and it is going to solve a lot of pain.

You have your data in Excel, you build a dashboard in Excel Dashboard designer and you can share and distribute the dashboard as PDF or live dynamic dashboard using the “Dashboard Viewer”. This is all fine and perfect.

But how about this scenario, what if you could hit a button and publish the dashboard on your website or intranet or your local webserver?

This web based Excel dashboard is not just a static html page but a real dynamic dashboard. You can access the dashboard using a URL on your browser (Internet Explorer,Chrome, Firefox etc) and view it from any part of the world. It would be as dynamic as you see it when you are designing it in Excel Dashboard Software.

Sounds interesting? so watch out for our web based dashboarding solution for Excel which should be out in few days.

So why bother buying an ebook or a pdf tutorial which tells you how to build a dashboard in Excel when you can get the leading Dashboard software just for the same price and you easily deploy it on your website.

Data governance is a set of processes that ensures that important data assets are formally managed throughout the enterprise. Data governance ensures that data can be trusted and that people can be made accountable for any adverse event that happens because of low data quality. It is about putting people in charge of fixing and preventing issues with data so that the enterprise can become more efficient.

Data governance also describes an evolutionary process for a company, altering the company’s way of thinking and setting up the processes to handle information so that it may be utilized by the entire organization. It’s about using technology when necessary in many forms to help aid the process. When companies desire, or are required, to gain control of their data, they empower their people, set up processes and get help from technology to do it

In general, most organizations have proliferated Excel use. When looking at business intelligence, many people use Excel to manage information and to analyze data. The issue arises when people use Excel as the key entry point of information access, meaning that individuals control, edit, and make changes to data used to plan and identify business performance and/or opportunities.

Because of the lack of validation within Excel, the ability to develop analyses based on incorrect assumptions and data manipulation are quite large. Consequently, organizations require the ability to use Excel and to interact with data, while still being able to validate the data being used to ensure proper data governance and overall compliance.

Excel Dashboard allows users to drill through to the original information without having to access a database or have an understanding of where the data resides. The overall ability to access and validate original data provides an asset to business users by allowing them to understand the context of information and how it interrelates with overall business information. When looking at compliance specifically, the ability to meet regulations and to maintain data integrity can have legal implications. Therefore, organizations require ways they can correlate Excel based analyses with original operational data to confirm that information has not been changed or compromised.