Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Menu

Skip to content
  • Home
  • My Books
  • Presentations
  • Best Practice Resources: Blogs & Books from the Experts
  • Video Tutorials
  • COVID-19 Daily Updates Report
  • Visualizations
  • About/Bio
  • Paul’s Bio
  • Note to SPAMers

Tag Archives: Business scorecard

Creating a Business Scorecard

March 11, 2010 by Paul Turley

0

Report Recipes

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

Executive Dashboards

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.

Indicators

Indicators are graphical icons, representing the state of an actual value with respect to a KPI band. On the scorecard, corresponding indicator icons might be red, yellow, and green symbols. Indicators are typically common symbolic metaphors like traffic lights, colored shapes, progress bars, gauges, and directional arrows. Figure 1 shows some common indicator graphics embedded in a sample report.

Figure 1
Figure 1

Calculating Variance

Variance is the difference between an actual and target value. If time variances will be used extensively, the queries used to make these calculations can be very intensive. Aggregating and calculating sales totals, for example, for a given month over last month, quarter, or year can require some heavy-duty query processing (even with a modest number of detail rows.) Ideally this type of data should be stored in a data mart or data warehouse with precalculated variance values stored in the database. The AdventureWorksDW database contains some preaggregated summary values, but as you can see, even for this simple report with only year-over-year variances, the query is fairly complex.

SELECT         
    ThisYearSales.SalesTerritoryRegion
  , ThisYearSales.SalesTerritoryKey
  , ThisYearSales.CalendarYear
  , ThisYearSales.LastName
  , ThisYearSales.FirstName
  , ThisYearSales.EmployeeName
  , SUM(ThisYearSales.ExtendedAmount) AS ExtendedAmountSum
  , SUM(ThisYearSales.SalesAmountQuota) AS SalesAmountQuotaSum
  , SUM(LastYearSales.ExtendedAmountSum) AS ExtendedAmountSumLastYear
  FROM (
     SELECT 
         DimSalesTerritory.SalesTerritoryRegion
       , DimSalesTerritory.SalesTerritoryKey
       , DimTime.CalendarYear
       , DimEmployee.LastName
       , DimEmployee.FirstName
       , DimEmployee.EmployeeKey
       , DimEmployee.FirstName + ' ' + DimEmployee.LastName AS EmployeeName
       , FactResellerSales.ExtendedAmount
       , FactSalesQuota.SalesAmountQuota
     FROM DimEmployee INNER JOIN FactSalesQuota 
         ON DimEmployee.EmployeeKey = FactSalesQuota.EmployeeKey
         INNER JOIN DimTime ON FactSalesQuota.TimeKey = DimTime.TimeKey 
         INNER JOIN FactResellerSales 
         ON DimEmployee.EmployeeKey = FactResellerSales.EmployeeKey
            AND DimTime.TimeKey = FactResellerSales.OrderDateKey 
         INNER JOIN DimSalesTerritory 
         ON DimSalesTerritory.SalesTerritoryKey =  
            FactResellerSales.SalesTerritoryKey) AS ThisYearSales 
         INNER JOIN 
           ( SELECT 
                 FactResellerSales.EmployeeKey
               , DimTime.CalendarYear
               , DimSalesTerritory.SalesTerritoryKey
               , DimSalesTerritory.SalesTerritoryRegion
               , FactResellerSales.ExtendedAmount AS ExtendedAmountSum
             FROM FactResellerSales 
                 INNER JOIN DimTime 
                 ON FactResellerSales.OrderDateKey = DimTime.TimeKey 
                 INNER JOIN DimSalesTerritory 
                 ON DimSalesTerritory.SalesTerritoryKey = 
                    FactResellerSales.SalesTerritoryKey
            ) AS LastYearSales 
     ON LastYearSales.CalendarYear = ThisYearSales.CalendarYear - 1 
        AND ThisYearSales.EmployeeKey = LastYearSales.EmployeeKey 
        AND ThisYearSales.SalesTerritoryKey = LastYearSales.SalesTerritoryKey
GROUP BY ThisYearSales.SalesTerritoryRegion, ThisYearSales.SalesTerritoryKey
  , ThisYearSales.CalendarYear, ThisYearSales.LastName, ThisYearSales.FirstName
  , ThisYearSales.EmployeeName
ORDER BY ThisYearSales.SalesTerritoryRegion, ThisYearSales.CalendarYear
  , ThisYearSales.LastName, ThisYearSales.FirstName

When running complex queries like this one, you may need to increase the default connection timeout setting on the data source. The default setting is 15 seconds, which may not be sufficient for this query on all hardware. In a production application with data volumes greater than the sample database, I would recommend testing query performance and possibly using an Analysis Services database with cubes and precalculated aggregates. To populate the data warehouse, you will use queries similar to this one and store the results for later retrieval.

Figure 2 shows a simple table with two groups: on the SalesTerritory and CalendarYear fields. This table is much like several previous examples. The detail row is hidden by default allowing for drill-down using the SalesTerritoryRegion textbox. Two more images will serve as indicators. These are based on expressions used to change the indicator image.

Figure 2
Figure 2

You will notice that the images have a white background even though I’ve used background colors to separate the rows. I’ve only done this to simplify this example. I have simply added the images to the cells in the table header. If you want to use transparent images over a colored or shaded background, you will need to add rectangles to the header cells and then place images in the rectangles. This way, you can set the BackgroundColor property for each rectangle and take advantage of the image transparency. The final example, shown in Figure 6 (on the final page of the article), uses this technique to fill the background color behind the scorecard indicator images.

Looking at the columns with text headers, the first column contains the SalesTerritoryRegion field in the first group header and the CalendarYear field in the detail row.

The second column contains the EmployeeName in the detail row.

The third text column is for the SalesAmountQuota field. The header uses the SUM() function to aggregate the details for the sales territory.

The fourth text column contains total sales values, using the ExtendedAmount field.

The last column of textboxes, labeled Yr. Variance, calculates the total sales amount annual variance. In the header row, the expression uses the SUM() function. In the detail row, the SUM() function is omitted.

=1-(Sum(Fields!ExtendedAmountSumLastYear.Value)/Sum(Fields!ExtendedAmountSum.Value))

The expression for the sales first set of indicators (the images column after total sales column) calls a Visual Basic function to apply the KPI threshold banding. Figure 3 shows this custom code.

Figure 3
Figure 3

Since the image names for the green, yellow, and red indicators are Green, Yellow, and Red, these values are simply returned in the Value property of the image item using the following expression:

=Code.SalesQuotaVarianceIndicator(Sum(Fields!ExtendedAmountSum.Value), 
Sum(Fields!SalesAmountQuotaSum.Value))

For variety, I’ve resolved the second indicator column images using only an in-line expression, rather than using a custom function. This is the expression for the header row. The detail row expression is the same but excluding the SUM() function. As a rule, after I’ve decided to use custom code, I’ll typically continue to use custom functions for all but the simplest expressions so I can keep business logic in one place.

The following is intended as all one line but will not display that way with this page width online.

=IIF(Sum(Fields!ExtendedAmountSum.Value) / 
	Sum(Fields!ExtendedAmountSumLastYear.Value) < 
	.8, "exclamation_small", "nothing")

This expression returns the name of an exclamation mark icon image when this year’s sales amount is less than 80 percent of last year’s. I created an image file called “nothing,” which is a blank icon with a white background. Using this image effectively displays nothing in the image cell.

Synchronizing Charts and Related Report Items

One of the great advantages to the scorecard approach is that all the information is presented in a concise page. In order to make the best use of screen space, I can use a separate report item to show content related to the item selected in the scorecard.

Figure 4 shows the table and chart items. When I select a sales territory, by clicking on the small pie chart icon in the first column, I want to see sales trend information in a column chart. I’ve placed a chart to the right of the scorecard and have configured it as a column chart. I’ve also simplified the chart by removing the legend.

Figure 4
Figure 4

The chart content is synchronized using a report parameter. The SalesTerritoryKey parameter is used to filter the dataset providing data to the chart. The SalesTerritoryregion parameter is used to provide a title value for the textbox above the chart. Figure 5 shows the Navigation properties for the pie icon used to synchronize the chart. Note that the Jump to report property is set to navigate back to this report, re-rendering the same report with the new parameter values.

Figure 5
Figure 5

Figure 6 shows the rendered report with some region sections drilled open. I’ve clicked the pie icon next to the Southwest region to synchronize the chart and view sales trend details for the southwest region. Again, note the background color fill behind the scorecard indicator images using the technique I mentioned earlier.

Figure 6
Figure 6

One of the first things you’ll probably notice is that the quota and total sales values are not very close at all. These values have actually changed in different editions of the AdventureWorksDW sample database as it progressed through beta testing cycles. For this report example to be more realistic, you can manually change some of the SalesAmountQuota values in the FactSalesQuota table. The completed sample code on the book’s support site, at P2P.WROX.COM, contains a script to update all of the quota values.

This article is the second in a series of three articles from Paul Turley excerpted from the 90-page chapter, “Report Solution Patterns and Recipes” from the book Professional SQL Server 2005 Reporting Services, written by Paul Turley, Dave DuVarney, James Counihan, and Todd Bryant (Wrox, 2006, ISBN: 0-7645-8497-9). The full chapter contains a deep analysis of successful report projects, including project profiles, success factors, user discussions, and scope management. Templates are provided to assist requirement gathering and management. The Report Recipes section of this chapter includes 19 specific examples of advanced report designs that implement custom coding and other techniques taught in previous chapters. This series of articles demonstrates three of these examples. The first article was Greenbar Reports and the final installment will be Creating Sparklines. Hitachi Consulting has business and IT consulting offices throughout the world. Paul works for Hitachi’s Pacific Northwest Business Intelligence practice in Seattle and may be contacted at pturley@hitachiconsulting.com.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in SQL Syndication, SSRS Design. Tagged Business scorecard, KPI indicator

Post navigation

Blog Stats

  • 1,386,585 hits

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 5,075 other subscribers

Recent Posts

  • Doing Power BI the Right Way: 4. Power Query in Dataflows or Power BI Desktop
  • A First Look at Gen 2 Composite Models with Live Power BI Datasets
  • Power BI: The Week in Graphics
  • Doing Power BI the Right Way: 5. Data Modeling Essentials & Best Practices (2 of 2)
  • Doing Power BI the Right Way: 5. Data Modeling Essentials & Best Practices (1 of 2)
  • Composite Models Gen 2 and DirectQuery over Power BI Datasets
  • Power BI Expert Resources
  • Doing Power BI the Right Way: 7. Planning for separation – data models and reports
  • Doing Power BI the Right Way: 6. Validating data model results – Part 2
  • Doing Power BI the Right Way: 6. Validating data model results – Part 1

Category Cloud

BI Industry BI Projects Business Analytics Microsoft BI Platform MVP Community PASS Power BI PowerPivot Power View Self-service BI SolidQ SQL Server SQLServerPedia SQL Server Pro Magazine SQL Syndication SSAS Administration SSAS Design SSRS Administration SSRS Design Tabular Models

Archives

  • January 2021 (3)
  • December 2020 (4)
  • November 2020 (1)
  • October 2020 (2)
  • September 2020 (1)
  • August 2020 (1)
  • July 2020 (4)
  • May 2020 (1)
  • April 2020 (3)
  • March 2020 (3)
  • February 2020 (1)
  • January 2020 (1)
  • December 2019 (2)
  • November 2019 (5)
  • October 2019 (1)
  • September 2019 (1)
  • August 2019 (2)
  • July 2019 (5)
  • May 2019 (1)
  • April 2019 (3)
  • March 2019 (1)
  • February 2019 (2)
  • December 2018 (3)
  • November 2018 (1)
  • October 2018 (1)
  • September 2018 (3)
  • July 2018 (5)
  • May 2018 (2)
  • April 2018 (2)
  • March 2018 (3)
  • February 2018 (3)
  • January 2018 (3)
  • December 2017 (3)
  • November 2017 (4)
  • October 2017 (1)
  • September 2017 (2)
  • August 2017 (1)
  • July 2017 (1)
  • June 2017 (4)
  • April 2017 (8)
  • March 2017 (1)
  • February 2017 (2)
  • January 2017 (8)
  • December 2016 (4)
  • November 2016 (3)
  • October 2016 (2)
  • September 2016 (1)
  • July 2016 (3)
  • June 2016 (3)
  • May 2016 (3)
  • March 2016 (6)
  • February 2016 (5)
  • January 2016 (2)
  • December 2015 (4)
  • November 2015 (3)
  • October 2015 (2)
  • September 2015 (2)
  • August 2015 (3)
  • July 2015 (6)
  • June 2015 (1)
  • May 2015 (5)
  • April 2015 (4)
  • March 2015 (1)
  • February 2015 (2)
  • January 2015 (4)
  • December 2014 (3)
  • November 2014 (1)
  • October 2014 (4)
  • September 2014 (1)
  • August 2014 (2)
  • July 2014 (5)
  • June 2014 (4)
  • May 2014 (2)
  • April 2014 (6)
  • March 2014 (3)
  • February 2014 (7)
  • January 2014 (5)
  • December 2013 (2)
  • November 2013 (1)
  • October 2013 (1)
  • September 2013 (2)
  • July 2013 (4)
  • June 2013 (5)
  • April 2013 (1)
  • March 2013 (4)
  • February 2013 (3)
  • January 2013 (1)
  • December 2012 (4)
  • November 2012 (4)
  • October 2012 (3)
  • September 2012 (3)
  • August 2012 (2)
  • July 2012 (2)
  • June 2012 (2)
  • May 2012 (3)
  • March 2012 (2)
  • February 2012 (3)
  • December 2011 (1)
  • November 2011 (3)
  • October 2011 (11)
  • September 2011 (7)
  • August 2011 (4)
  • July 2011 (2)
  • June 2011 (4)
  • May 2011 (5)
  • April 2011 (5)
  • March 2011 (4)
  • February 2011 (2)
  • January 2011 (4)
  • December 2010 (4)
  • November 2010 (4)
  • October 2010 (1)
  • September 2010 (1)
  • August 2010 (2)
  • June 2010 (1)
  • May 2010 (2)
  • April 2010 (1)
  • March 2010 (19)
  • December 2009 (1)
  • June 2009 (1)

Tag Cloud

" & Workspace and Database Recovery Techniques Aaron Nelson Ad-hoc reporting Add columns Add controls Albert Ferrari Alternate row colors Analysis Services Operations Guide Apple Are There Rules for Tabular Model Design? Article Assemblies Azure Azure Reporting Azure SQL Database BARC Survey best practices BI BI Center of Excellence BI COE BI Conference Bill Gates Birds-of-a-Feather BI Roles and Team Composition BISM BI Survey 10 Blogging Breakcrumb links Browser settings Build career Business Intelligence Business Intelligence for Visual Studio 2012 Business scorecard Can I Use Reporting Services with Tabular & PowerPivot Models? Checkbox in report Checkbox list Check mark Chris Webb Cloud computing Column chart Community Conditional formatting Conference presentation Conference review Conference session Conference Session Topics Cortana Power BI Integration Custom code Custom coding reports Custom Functions Dashboard design Dashboard standards Database Lifecycle Management Data Modeling 101 for Tabular Models Data Quality Services Dataset filter nulls Datazen Datazen control selection Date parameters DAX DAX: Essential Concepts DAX: Some of the Most Interesting Functions DAX: Some of the Most Useful Functions DAX functions DAX reference DAX syntax Demo scenario Denali CTP3 DevTeach DLM Do I Write MDX or DAX Queries to Report on Tabular Data? Do We Need to Have SharePoint to Use Tabular Models? Drill-down Drill-through Drillthrough Dynamic column visibility Dynamics CRM Dynamics reporting Embedded formatting ENterprise SSAS Errors Estimating BI European PASS Filter by user Formula Firewall Funnel charts Garner Magic Quadrant Microsoft BI Getting Started with DAX Calculations Global Summit Live Feeds Greenbar report Grocery shopping demo Hans Rosling Happy Birthday Power BI Hide columns Hitachi Consulting How Do You Design a Tabular Model for a Large Volume of Data? How Do You Secure a Tabular Model? How to Deploy and Manage a Tabular Model SSAS Database How to Promote a Business-created PowerPivot Model to an IT-managed SSAS Tabular Model HTML text integrated mode Interview Interviews Isn’t a Tabular Model Just Another Name for a Cube? James Phillips Julie Koesmarno King of Spain KPI indicator Licensing Login prompt Manually starting subscription Map Visualization Marco RUsso Master-detail report Master Data Management MDM MDX datasets MDX queries Microsoft Architecture Journal Microsoft humour Microsoft MVP Microsoft news Mobile Reporting Mobile Reports MVP community MVP Deep Dives 2 MVPs support the community MVP Summit navigation Nested tables Null filter Olivier Matrat Olympia WA Oracle vs Microsoft in the movies Oregon SQL Saturday Parameter controls Parameterize Parameters PASS 2012 PASS BAC Blog Feed PASS community leaders PASS Conference PASS Global Summit 2012 PASS Keynotes PASS Summit PASS Summit 2017 PASS Summit 2018 PASS Summit Announcements Paul te Braak PDF image distortion dithering fonts PerformancePoint Pinal Dave Poll About Product Usage Poll Results Pop-up window; Java script Portland OR Power BI Administration Power BI Best Visuals Contest Power BI DAX Power BI Partner Showcase Power BI Premium Power BI Pro Power BI Training Power BI World Tour Power Pivot PowerPivot Power Pivot DAX Power Query Power Query Training Power View Power View multidimensional cubes Preparing Data for a Tabular Model Project Phoenix Recipes Redmond SQL Saturday Reed Jacobson Remove columns Repeating list Report controls report dependencies Report deployment Reporting Services 2016 Reporting Services Training Report navigation Report parameters Report recipe book Reports for MDX Return specific row Rob Collie DAX Book Robert Bruckner Scheduled Refresh Scripting Tabular Model Measures Self-service reporting Seth Bauer SharePoint SharePoint 2012 SharePoint integration Simplifying and Automating Tabular Model Design Tasks SolidQ SolidQ Journal Solid Quality Mentors Spatial queries; happy holidays; Merry Christmas SQLAuthority SQLCAT SQL Saturday SQL Saturday 446 SQL Saturday Portland Oregon SQL Server SQL Server 2012 Upgrade Guide SQL Server community SQL Server Data Tools – Business Intelligence for Visual Studio 2012 SQL Server Denali SQL Server Denali; Self-service reporting SQL Server Denali CTP3 SQL Server MVP SQL Server Optimization SQL Server Pro Magazine SQL Teach SSAS SSAS Performance Logger SSAS Tabular SSAS Tools BI Development Tools SSDT BI SSRS 2016 SSRS dynamic columns SSRS PowerShell SSRS version control standards Start subscription Steve Jobs StreamInsight Strip line style Subscription Survival Tips for Using the Tabular Model Design Environment Tabular DAX Tabular Model & " Tabular Model Common Errors and Remedies Tabular Model Design Tabular Model Design Checklist Tabular Modeling Tabular models Tabular report design TechEd TechEd 2011 Sessions TechSmith Snagit Pro themes Threshold line Top values Training clsses Unconference User-related report content User authentication User prompted to login Using DAX to Solve real-World Business Scenarios Vancouver BC Vern Rabe Visualisation Visualization Visual Report Design Volunteers Weather and Climate Web.Contents Web API What About Multidimensional – Will Tabular Replace It? What are the Naming Conventions for Tabular Model Objects? What Do You Teach Non-technical Business Users About PowerPivot and Tabular Models? What’s the Best Business User Tool for Browsing & Analyzing Business Data with Tabular Models? What’s the Best IT Tool for Reporting on Tabular Models? What’s the Difference Between Calculated Columns & Measures? What’s the Difference Between PowerPivot and Tabular Models? Why Tabular? Wrox book
RSS
RSS Feed
Powered by WordPress.com.
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
%d bloggers like this: