by Susan Hernandez, July 22, 2009

In an earlier blog post, I stressed the need for Data Visualization, or Business Intelligence Dashboards, to be summarized, actionable, graphical, and personalized.  In this post, we’ll be diving more into actionable dashboards.

Drill-Downs
One of the ways to create a dashboard that is actionable is by implementing Drill-Downs.  For example, let’s say you have created a dashboard using a Data View web part showing “KPI’s” – or Key Performance Indicators – for all of your sales this year. 

You see that June has a Red indicator, indicating that the sales were below average.  You would want to click on that record and be redirected to another view of sales, for only June, perhaps broken out by Region.  Once you determine the Region that failed to perform adequately, you should be able to click on that record and get a list of all salespeople in that region and their sales for June.

By implementing a drill-down, you have effectively targeted that Red indicator – Sales for June – down to a specific list of people responsible for the dip in sales.

Related Information
You can also add Related Information to a page.  Following our previous example, let’s say that you have drilled down to Sales for June, in the Eastern Region, and you see that Joe and Tom have low sales for this month.  On the same page, you might have a list of notes or status reports from the sales people, specifically filtered on June.  When you click on a radio button next to Joe, the web part showing status reports should just show Joe’s Status reports for June.  When you open that status report, you notice that a top client of Joe’s had cancelled a long standing order due to poor customer satisfaction with the product.

This is just one example of related information.  You may also have items like News Feeds, Weather reports for June (in case weather affects shipping, for example), or you may simply have contact information for Joe and Tom so you can call them and ask them what happened to Sales in June.

In short, you need to provide enough information that a manager can take action on a statistic shown that makes sense to the business.

How Do I?
How do we implement some of the processes we’ve covered in the examples?  You will typically want one Web Part Page for each of the views that you are displaying – for example, one for All Sales for the Year, one for Sales by Month, and one for Sales by Region.  Each of these web part pages, except for the first one, will accept a query string value to filter on the desired data item.  We will cover details for this in a later paragraph.

Tip:  To get your quick launch bar back on your web part page, see this post.

The hardest part will be implementing the Drill-Down from a Graphical interface, i.e. a chart or graph.  Taking a step back, you can show charts or graphs using SQL Server Reporting Services, CorasWorks Workplace Suite, Bamboo Charting Web parts, or Dundas Charting Web Parts, just to name a few.  I have found that Dundas covers my needs best, as it provides you with a clickable drill-down capability – i.e. clicking on one part of a pie chart to go to the information just for that pie slice.  You can actually specify a URL to go to, and insert data elements into the query string so that you have the correct filter.

If you have more of a list-driven interface, you can set up the list as you would show it, and then open up the page in SharePoint Designer.  If you then switch the web part to a Data View Web Part, you can then add hyperlinks to the rows with query string information – parameters to pass to the next page – in order to filter on just that information.

Once you get to the next page by clicking on a link, you would by default show everything, not the filtered information.  We will want to use a QueryString Filter Web Part to limit the number of records that are returned to ONLY the data from the previous page that you clicked on.  You will use the Connections of the web parts to connect the filter to the list that you are populating.

Tip:  If you want another graph on the second page instead of a list, filtered by the QueryString Filter Web Part, Dundas is again a good third party tool for this, as it accepts data connections from other web parts.

You may also wish to show both a Chart and a list in the second and/or third drill down pages – a graphical and a textual representation of the data.  For example, on the second page in our example, which shows Sales for June, you may wish to show a graph of Sales per Region, grouped by SalesPerson, and a List of Sales per Region, with KPI indicators that show the health of the Region.

Tip: You create KPI indicators for a list by changing the list to a Data View using SharePoint Designer, adding a column, adding 3 images to the column (red, yellow, green), and setting conditional formatting on the graphical images to hide or show them in certain cases.  You can use _layouts/images/kpidefault-0.gif, _layouts/images/kpidefault-1.gif, and _layouts/images/kpidefault-2.gif for the default stoplight KPI images.

When you get to the final detail page, in our example the Sales by SalesPerson for the Eastern Region for June, you will want to connect your list web parts to your related data web parts, if there is a relationship.  For example, if you have a list of all salespersons for the Eastern Region for June and their sales, you would connect that web part to a web part of all Status Reports for all sales persons for June (the status report web part would also be limited by the QueryString Filter Web Part to only items for June for that Region).  Once you connect the 2 web parts, you will get a radio button next to each data row of salespersons.  When you click on the radio button for one of the salespersons, it will further filter the Status Reports web part to status reports for June, for that Region, and then for only that SalesPerson.

Summary
In summary, you can use Drill-Downs and related information on your web part pages to provide an action that the user can take to retrieve more information about what the data means.  You can use third party products like Dundas Charting, or you can use Lists and Data View Web Parts, in conjunction with QueryString Filter Web Parts and web part data connections, to filter down and drill into your information.