ScorecardAnalysis

Scorecard / Dashboard poor design

2010-09-21T19:52:00.001-04:00

Stay away from “Chart Junk” –
Chart junk refers to visual content that serves no analytical purpose. It’s just there for decoration. Your customers will quickly tire of this content (as it is typically static) and this space on the scorecard will quickly become useless. If you try to use this space at a later date for some important data your customers will have to retrain themselves to look at that space. The space is better left blank.
Example of "Chart Junk"

Ok...that's probably a little dramatic but here is an actual chart (data changed) that someone submitted to me and asked me to add to a scorecard I was designing.

Not as bad as the previous picture but still too much going on and emphasis given to the wrong things. It's in 3d (for no reason), the gray in the plot area and white gridlines need to go, and the font on the axis should be more muted. Here is the final version I finally did add to the scorecard.

Use “real estate” wisely –
The upper left corner of the screen is known as prime real estate for web developers and scorecard developers alike. One of the keys to good scorecard and dashboard development is to make it so your customers don’t have to scroll…everything is on one page. If they have to scroll you should make sure that they only have to scroll vertically (up and down) and not horizontally.

Keep it Simple –
Just because your software has 100’s of different kinds of charts doesn’t mean you need to use them all. There is a purpose for these 100’s of different charts but chances are you don’t need them. My guess is that you can do most of the visual display that you need to do with Bar, Line, and Column charts. Here (http://spreadsheet123.com/charttypes.html) is a quick explanation of types of charts and their general purposes. You can easily find more information about various chart types by googling the chart name. You don’t want the message of the data to get lost because people can’t understand and/or don’t want to take the time to understand the visual representation.

Pareto Chart template

2010-09-20T20:07:00.000-04:00

I've been gone a while working on a new template. This one is will allow you to import or enter data into an excel spreadsheet and create a pareto chart. I also wrote an article about how/when to use a pareto chart

Simply enter (or import) data into columns A and B. A should be your data to group by and B should be the values. No need to sum the data prior to importing it. The tool will do that for you. Please check it out. Let me know if you find it useful.

Easy analysis with fishbone diagram template (Ishikawa)

2010-08-30T22:22:00.009-04:00

I spent some time recently and created a fishbone diagram template . To view/use the diagram follow the link here.

Amazon.com Widgets

Here is a short article on fishbone diagrams and how/when to use them.
The output of will look like this

Here are the steps to produce the chart

1 Go to the data tab

2 Enter Effect in Cell I11
3 Modify Major Categories (if desired)
4 Select the Category from the drop down
5 Enter a cause (repeat until complete)
6 View diagram (button in upper right)
7 There are places to add additional columns of data that will not appear on the diagram

I am always working on ways to improve this fishbone diagram template so if you have any improvements please feel free to comment here or contact me by filling out my contact form.

MicroCharts in Excel

2010-08-23T19:33:00.005-04:00

I ran across this post the other day. It's an old post but a cool concept for anyone creating dashboards in Excel. You can actually use some of the concepts in Microsoft Reporting Services as well.

http://chandoo.org/wp/2008/09/05/microcharting-excel-howto/

These guys have a lot of great ideas. I just stumbled across them for micro charting but many great Excel charting solutions.

You can go here to read a little more about microcharts and how to use them.
Click here to download a template I created based on one of the charts. As it exists today it will allow for 50 lines of data. To add more lines follow the steps below.

1. Unhide columns C, D, E
2. Go to cell C54
3. Highlight over to cell E54
4. Copy the formula and paste it down adding as many rows as you need.

To get the charts to update;
1. Select cell B2 and press f2
2. Type in D and cell location for all of the cells you added seperated by the & symbol
(i.e. D55&D56, etc)
3. Follow step 2 for cell B3. The only difference is you'll change the D to an E

If the chart doesn't look correct you may have to resize the row height for rows 2 and 3.

How to use a Sharepoint list as a datasource for Microsoft Reporting Services

2010-08-18T21:28:00.000-04:00

Step 1:

Create Sharepoint List that contains the data you would like to report against.
Step 2: Once in SSRS:
     1. Create a new datasource
          a. Name – Name of datasource
          b. Type – XML
          c. Connection String – Home page of the Sharepoint site where the list exists.
               NOTE: After the name of the site remove the “default” and replace it with   <</_vti_bin/Lists.asmx>>
               http://<server>/<path>/_vti_bin/Lists.asmx

d. Use Windows Authentication

     2. Create a new dataset associated with the new Datasource.
          a. Name – Whatever you want to call your dataset
          b. Data source – Set this to be the connection you created in step 1.
          c. Query Type: Text
          d. Query – Copy and paste the following HTML code (replace <<Name of your list>> with the actual name of your list) –

<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
<Parameters>
<Parameter Name="listName">
<DefaultValue><<Name of your list>></DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>

RISKS / Things to be aware of:

          RISK: Regardless of list settings you will (by default) only return 100 records (even if your list is set to “return all records”).
          SOLUTION: You will need to go into the list setting for the All Items view and change the “Item Limit - Limit the total number of items returned to: “ to some unattainable number “999999”.

          RISK: You will only return columns where the first row of data is not null. This one can be frustrating.
          SOLUTION: You can always add a “dummy” record called DONOTUSE and put data in each column in order to get the query to return all items. Just be sure to filter this record out in your tables and charts.

Scorecard Analysis - an Introduction

2010-08-17T21:18:00.000-04:00

I decided to start a blog as an outlet for many of the things I am working on in my job right now. I am a data analyst. Recently I have taken on a new role where I am responsible for designing and implementing a scorecard for most of our IT department. My plan is to start small and attempt to get this model adopted by the entire IT department.

My intentions with this blog is to put out some How-tos, some general knowledge sharing, and a way to share the things I've learned and resources I've used with everyone else.