Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sam Vanga

Sam Vanga helps business people solve their data problems and enables them make data-driven-decisions. His specialties include data integration, data warehouse and database development, and BI design. Sam blogs at http://SamuelVanga.com.

Sparklines and Conditional Formating in SSRS

When creating a SSRS report, you want to add lines that display trends. You want to show trends for more than one data point. And you want to conditionally format the data point.

In this example, I use Sparklines and a simple expression to create a sparkling report!

Below is how the Sparkline with multiple data points and conditional formatting applied to them will look like. Columns represent sales by month and line represents sales quota by month. Column is in green when sales exceeds the quota for a given month and red when sales fall below the quota.

Create dataset:

After opening report server project and creating a data source, create a data set. Right click on shared datasets and choose add new dataset. Following is the query I am using for this example which runs against AdventureWorksDW2008R2 sample database.

  1. SELECT T.CalendarYear
  2.     , T.CalendarQuarter
  3.     , T.MonthNumberOfYear
  4.     , SUM(S.ExtendedAmount) AS Sales
  5.     , COALESCE(SUM(Q.SalesAmountQuota) / COUNT(SalesAmountQuota), SUM(Q.SalesAmountQuota) / COUNT(SalesAmountQuota)) / 3 AS Quota
  6.     , E.FirstName + ' ' + E.LastName AS Employee
  7. FROM FactResellerSales AS S
  8. LEFT OUTER JOIN dbo.dimDate T ON S.orderdatekey = DateKey
  9. JOIN dbo.DimEmployee E ON S.EmployeeKey = E.EmployeeKey
  10. LEFT OUTER JOIN dbo.FactSalesQuota Q ON S.EmployeeKey = Q.EmployeeKey
  11.     AND T.CalendarYear = Q.CalendarYear
  12.     AND T.CalendarQuarter = Q.CalendarQuarter
  13. WHERE S.EmployeeKey IN (
  14.         SELECT TOP 10 EmployeeKey
  15.         FROM FactResellerSales
  16.         GROUP BY factResellerSales.EmployeeKey
  17.         ORDER BY SUM(ExtendedAmount) DESC
  18.         )
  19. GROUP BY T.CalendarYear
  20.     , T.CalendarQuarter
  21.     , T.MonthNumberOfYear
  22.     , E.FirstName
  23.     , E.LastName
  24.     , E.EmployeeKey
  25. ORDER BY Employee
  26.     , 1

Insert table:

We want to insert a table grouped on Employee so one row for each employee is shown. Sparklines will later be added to this table.

Drag and drop table report item from the toolbox to report body. Next, drag Employee field from dataset and place it in the details row.

Right click on the employee text box and insert a row group by choosing row group, group properties. In the group properties window choose Employee from the drop down for group on property.

Insert Sparkline:

We will configure a Sparkline with two data points, apply conditional formatting, and add Sparkline to the above table.
Drag and drop Sparkline from toolbox on to the report body. Select column as the Sparkline type and click ok.
Add sales, Quota from dataset to values and CalendarYear, CalendarQuarter to category groups.

Click on Quota from values and choose change Sparkline type. From select Sparkline type page, choose smooth line and click ok.

Click on Sales from values and choose series properties.

From the series properties page, choose fill and type the following expression
iif(Fields!Sales.Value<Fields!Quota.Value,“red”,”green”)

Finally, drag and drop Sparkline to a cell next to Employee in the table we created earlier.

PS: I originally wrote this post on my old blog. Since I started blogging on LessThanDot.com I intend to selectively move content from my previous blog to LTD.

Comments

Leave a comment on the original post [blogs.lessthandot.com, opens in a new window]

Loading comments...