This is the second in a series of three articles highlighting the Report Solution Patterns and Recipes from Chapter 7 of Professional SQL Server 2005 Reporting Services.
As we have endeavored to solve various business problems, we’ve learned to do some interesting things with Reporting Services. On consulting engagements, I often find myself in front of a client who is asking questions like “Can you do this or that?” Almost inevitably, the answer is “yes,” but the question becomes what the best method would be to meet the requirement. With a little outside-the-box thinking, a lot of interesting things are possible. This may involve some custom programming, embedding report items or using customer application components in concert with Reporting Services.
In the following article, I’ve compiled a description of reporting challenges and solutions we’ve encountered when developing reports for our clients. For each “solution recipe,” I provide a brief list of skills, techniques, and resources needed to apply the report feature. This should give you a good idea about how prepared you may be to use the techniques based on your skill set and the level of complexity. Some of these are easy to duplicate and others require more advanced skills, which may include Transact-SQL and Visual Basic programming. These are not intended to be exercises or step-by-step instructions. I have made a point to provide enough information to demonstrate the concepts and techniques.
Creating a Business Scorecard
Microsoft Office Business Scorecard Manager is a Back Office product that integrates with SharePoint Portal Server and may be used to bridge the features of several Microsoft products, such as SQL Server Analysis Services, Excel, Visio, and Office Web Components. BSM is primarily designed for the nonprogrammer, power-user and requires little technical expertise to create dashboard-style reports. Using Reporting Services and a little programming savvy, it’s possible to duplicate some of the BSM features without the product.
This type of reporting scenario has quickly become a mainstay in enterprise business applications. Also known as executive dashboards, business scorecards provide summary level progress and success status information for business leaders.
You’ll need the following:
- A query expression with data-based or calculated target, budget, variance and actual values
- A multigroup table with drill-down features
- Small images for use as progress indicators
- An expression used to translate KPI and target values to indicator images
To understand and appreciate the value of this type of reporting interface, you need to walk in the shoes of corporate business leaders. A typical corporate officer deals with a lot of people and a variety of information in a day, and often needs to make immediate decisions based on this information. Moving from meeting to meeting, transaction-level details are too granular for most decisions. Business leaders need to know how the business is performing overall and whether there are areas of concern or notable success. I’ve sat in a lot of meetings with a General Manager or Director sitting on one side of the table and subject experts on another. The officer begins by saying, “So, how are we doing?” The subject expert gives a lengthy presentation, stepping through PowerPoint slides, charts, graphs, and diagrams that depict trends and variances based on mountains of data. After the presentation, the officer concludes with the question: “So, how are we doing?” Scorecards and dashboards answer this all important question using succinct summary values and simple graphical, symbolic progress indicators.
Although simplification is a key concept, scorecards go beyond just keeping reports simple. Trends and success indicators should be clear and easy to understand but should provide an avenue to discover more detail and to view related trends and summaries. These objectives are easily achieved using drill-down and drill-through report features.
Targets and KPIs
These are the fundamental concepts behind business scorecards. For any given measurement, a target is simply an objective value. Targets are often data-driven values like a Budget, Quota, Baseline, or Goal. A KPI, or Key Progress Indicator, is a set of thresholds used to measure actual values with the target. KPIs may define banding indicators that to signify a range of variances like poor, acceptable and exceptional performance. KPI thresholds may be a single point, corresponding to the target, percentage, or fixed increment offsets with any number of indicator bands.
When considering longer-term trends, you may want to recognize the difference between a positive trend using a KPI and whether or not a value represents a successful outcome, as a KSI (Key Success Indicator.) For example, sales for a particular product may have been unprofitable since it went on the market. If sales are rising, a KPI would show positive sales growth while a KSI would indicate that the company is still in the red. We might simply define two targets, one to measure short-term progress and the other to measure overall profitability.