Creating a Personal Report Card with SQL Server Reporting Services

Creating a Personal Report Card

This recipe creates a personal report card report that displays an individual’s performance compared with the best performer for the same performance metrics over a specific period of time. In this case, you are going to compare sales employees who are at the same hierarchical level to see how well they have performed compared to their allocated sales quota. This technique can be used to create personal report cards for any performance metrics.

Product Versions

  • Reporting Services 2005

  • Reporting Services 2008

What You’ll Need

  • A connection to Adventure Works cube

  • A column chart with two data series

  • A basic understanding of Analysis Services and MDX queries

Designing the Report

This example uses the Adventure Works cube for SQL Server 2005 or 2008 sample database.

To make this recipe easier to understand, it is broken into two parts: personal performance over a period of time and personal performance compared to a best performer over a period of time. The first part creates a report displaying a selected employee’s sales performance over a period of time where performance is measured by the difference between sales amount and target sales quota. The second part expands the first scenario by adding the best performer to the mix so you can measure the selected employee’s performance against the best performer (in our case, sales employees from the same hierarchical level). Figure P3-14 shows the finished report.

Figure P3-14

For the first part of the report, let’s create a simple MDX query to return the selected employee’s reseller sales amount and sales target over a period of calendar years and quarters. This example uses Report Builder 2.0.

1. Start by creating a data source for the Adventure Works DW Analysis Services OLAP database.

2. Create a dataset MDX query using the Query Designer. Drag the following items from the left-most metadata pane to the query grid and drop them into columns from left to right:

  • Employees attribute from the Employee dimension.

  • Calendar Year from the Date dimension.

  • Calendar Quarter of Year from the Date dimension.

  • Reseller Sales Amount measure.

  • Sales Amount Quota measure.

3. Add a filter using the Filter pane at the top of the Query Designer.

  • Drag the Department Name hierarchy from the Employee dimension to the first row of the Filter pane.

  • Verify that the Equal operator is used.

  • For the Filter Expression, select the Sales department to restrict data to sales employees only.

4. Add a second filter for the Employees hierarchy. This filter will also be used as a parameter.

  • Drag and drop the Employees hierarchy from the Employee dimension to the second row in the Filter pane.

  • Verify that the Equal operator is used.

  • Leave the Filter Expression blank.

  • Check the Parameter checkbox.

The parameter will be used to select the employee when a user runs the report. After you’ve completed these steps, the Query Designer should look like Figure P3-15.

Figure P3-15

Now that the query is designed, let’s start creating the first part of the report.

1. Add a Chart report item to the report body and select Column chart type. Resize the chart to your liking.

2. Drag the Calendar_Year and Calendar_Quarter_of_Year fields to the category field area of the chart. This will create category groups of Calendar Year and Calendar Quarter on the X-axis.

3. Drag Reseller_Sales_Amount and Sales_Amount_Quota to the data field area of the chart. This will create two data series.

At this point, the chart simply plots reseller sales amount and sales target quota data series as columns, and calendar year and calendar quarter on the X-axis. In order to display the performance of the selected employee in a more useful manner, you will change the chart type of the sales target quota data series to Line with Markers and dynamically color the reseller sales amount columns depending on their variance from the sales target. For example, if the percentage variance between reseller sales amount and sales target quota is positive (that is,  reseller sales amount >= sales target quota), the data column will be colored lime; on the other hand, if the percentage variance is less than negative twenty-five percent, the column will be colored yellow; otherwise it will be colored red.

Next, update the chart to reflect the changes just mentioned. But before you begin, the report designer should look like Figure P3-16.

Figure P3-16

1. Right-click on the Sales_Amount_Quota data series and select Change Chart Type.

2. Select Line with Markers chart type.

3. Right-click on the Reseller_Sales_Amount data series and select Series Properties.

4. Select Fill page of the Series Properties dialog box and enter the following expression for Color (see Figure P3-17):

=switch ((Fields!Reseller_Sales_Amount.Value – Fields!Sales_Amount_Quota.Value)

/Fields!Reseller_Sales_Amount.Value > 0, "Lime",

(Fields!Reseller_Sales_Amount.Value – Fields!Sales_Amount_Quota.Value)

/ Fields!Reseller_Sales_Amount.Value < -0.25, "Red",

(Fields!Reseller_Sales_Amount.Value – Fields!Sales_Amount_Quota.Value)

/ Fields!Reseller_Sales_Amount.Value > -0.25, "Yellow")

Figure P3-17

NOTE: In order to change the chart type for Sales_Amount_Quota data series to line with markers in SSRS 2005, go to Data Series Properties and select the Appearance tab. In the Appearance tab, check both the “Show Markers” and “Plot data as line” checkboxes. Also, in order to update the Fill property for Reseller_Sales_Amount data series in SSRS 2005, go to the data series properties and select the Appearance tab. From the Appearance tab, select the Series Style button and go to the Fill tab. From the Fill tab, select the expression button for Color and enter the expression.

Preview the report to test the results. Select employee Amy E. Alberts. As you can see in Figure P3-18, the reseller sales amount data columns change color depending on the percent variance between sales amount and sales target quota. Also, the sales target quota data series is plotted as a line with markers.

Figure P3-18

So, the first part of this recipe was pretty straightforward. Now, for the second part, you want to compare the performance of the selected employee with the best performer at the same level as the selected employee. In most environments, peer-to-peer comparison can be done at any level as long as the comparison is fair. In this example, you are going to compare the selected employee to the best performer on the percentage variance between reseller sales amount and sales target quota. It wouldn’t be fair to compare the employees on just reseller sales amount.

For simplicity’s sake, you are just going to create another dataset and chart to fulfill the requirements for this part of the recipe. The most important element of this part is creating the MDX statement for the dataset. So, let’s review the MDX statement you are going to use:

WITH

  MEMBER [Measures].[TargetPctVariance] AS

      (([Measures].[Reseller Sales Amount])-([Measures].[Sales Amount Quota]))

    /

      ([Measures].[Reseller Sales Amount])

    ,Format_String="Percent"

  MEMBER [Measures].[BestPerformerAtSameLevel] AS

    Max

    (

      {

        StrToMember

        (@EmployeeEmployees

         ,CONSTRAINED

        ).Siblings

      }

      , ([Measures].[TargetPctVariance])

    )

SELECT

  NON EMPTY

    {

      ([Measures].[Reseller Sales Amount])

      ,([Measures].[Sales Amount Quota])

      ,([Measures].[TargetPctVariance])

      ,([Measures].[BestPerformerAtSameLevel])

    } ON COLUMNS

  ,NON EMPTY

      STRTOSET

      (@EmployeeEmployees

       , CONSTRAINED

      )*

      {

        [Date].[Calendar Year].[Calendar Year].ALLMEMBERS

      }*

      {

        [Date].[Calendar Quarter of Year].[Calendar Quarter of Year].ALLMEMBERS

      } ON ROWS

FROM [Adventure Works]

In the MDX statement, you are creating two members: TargetPctVariance and BestPerformerAtSameLevel. TargetPctVariance is the percentage variance between reseller sales amount and sales target quota. BestPerformerAtSameLevel finds the best TargetPctVariance among the employees at the same level as the selected employee using the Max MDX function. Employees at the same level are selected using the Siblings MDX member function.

After the two members are created, the select MDX statement is pretty straightforward. Along with the two members just created, you add measures Reseller Sales Amount and Sales Amount Quota to the column axis and the selected Employee (use the STRTOSET function in order to convert the employee parameter string to a set), Calendar Year, and Calendar Quarter of Year to the row axis.

Now that you have reviewed the MDX statement, let’s create the dataset.

1. Right-click on the data source and select Add Dataset.

2. From the Dataset Properties dialog box, click the Query Designer button and toggle the Design Mode to text mode by clicking the Design Mode icon.

3. Copy and paste the MDX you just reviewed.

4. Add a query parameter by clicking the Query Parameter icon.

5. Enter a parameter name of EmployeeEmployees and select a Default Value of Amy E. Alberts. Leave everything else blank and click OK (see Figure P3-19).

6. Verify the MDX query by executing it in the Query Designer and click OK to close the Query Designer.

7. Click OK to close the Dataset Properties dialog box.

Figure P3-19

Now that you have created another dataset, you create a chart to display to the results.

1. Add a Chart report item to the report body and select Column chart type. Resize the chart to your liking.

2. Drag the Calendar_Year and Calendar_Quarter_of_Year fields from the second dataset to the category field area of the chart. This will create category groups of Calendar Year and Calendar Quarter on the X-axis.

3. Drag TargetPctVariance and BestPerformerAtSameLevel to the data field area of the chart.

4. Format the Y-Axis to display a percentage scale by right-clicking on the Y-Axis and selecting Axis Properties.  

5. Select Number on the Axis Properties dialog box and select Percentage category and click OK (see Figure P3-20).

Figure P3-20

The report designer should look like Figure P3-21.

Figure P3-21

NOTE: In order to display percentage scale on Y-Axis in SSRS 2005, go to chart properties. From the Chart Properties dialog box, go to Y-Axis tab and enter P2 in Format Code. Adjust the scale minimum and maximum values and major gridlines interval value according to the data.

Preview the report to test the results and select employee David R. Campbell. As you can see in Figure P3-22, both charts are displayed with the first chart showing David’s individual performance while the second chart compares David’s performance with the best performer at the same level as David.

Figure P3-22

Final Thoughts

You can further enhance this report by adding series data labels, changing column patterns, and combining two charts into one using dual Y-axis. Combining charts may look cluttered, but in some cases, if the performance metric is common between the two charts, it may be beneficial to do so.

The technique used in this example can be used to create personal report card report that displays an individual’s performance compared with the best performer for the same performance metrics over a specific period of time. The important concept in this example is to understand the MDX that finds the best performer for the performance measure you are tracking.

This article is excerpted from Part III "Chart and Gauge Reports" of the book "Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports" by Paul Turley, Robert M. Bruckner (ISBN: 978-0-470-56311-3, Wrox, 2010, Copyright Wiley Publishing Inc.)

Tags:

Comments

One response to “Creating a Personal Report Card with SQL Server Reporting Services”

  1. Anonymous says:

    very interesting and useful

Leave a Reply to Anonymous Cancel reply

Your email address will not be published. Required fields are marked *