SSRS Sparkline: NOW and TREND

The SSRS sparkline is a powerful little chart that shows a trend at a glance. Google Finance, for example, uses the sparkline to show the price of a share over the day, while the number you see shows the real-time price. That is often what you want to show as well: the latest value of a certain figure together with a trend line of the past period. This article shows how to build a setup like that.

Usually you use a sparkline inside a cell, although you can also use it outside one. In a table/matrix you can use a sparkline only at group level, not on the detail row. Say you want to show the revenue Y-T-D in a trend line, but as the number in the table you only want to show the revenue of the last month. How do you solve this?

Your first thought would be to put a filter on the table that keeps only the revenue of the last month. This does not work, because then you also filter out the revenue of the previous months, and the sparkline does not work properly. A table inside a table cell does not work well either. With the steps below you build the trend line quickly:

Step 1 - Create the dataset

Create a dataset with the following data:

1
SELECT 50 AS Omzet, 1 AS Maand, 'Sjaak' AS Verkoper
2
UNION
3
SELECT 100 AS Omzet, 2 AS Maand, 'Jan' AS Verkoper
4
UNION
5
SELECT 100 AS Omzet, 2 AS Maand, 'Sjaak' AS Verkoper
6
UNION
7
SELECT 150 AS Omzet, 3 AS Maand, 'Sjaak' AS Verkoper
8
UNION
9
SELECT 300 AS Omzet, 4 AS Maand, 'Sjaak' AS Verkoper
10
UNION
11
SELECT 600 AS Omzet, 5 AS Maand, 'Jan' AS Verkoper
12
UNION
13
SELECT 900 AS Omzet, 6 AS Maand, 'Sjaak' AS Verkoper
14
UNION
15
SELECT 900 AS Omzet, 7 AS Maand, 'Sjaak' AS Verkoper
16
UNION
17
SELECT 150 AS Omzet, 8 AS Maand, 'Sjaak' AS Verkoper
18
UNION
19
SELECT 385 AS Omzet, 9 AS Maand, 'Sjaak' AS Verkoper
20
UNION
21
SELECT 100 AS Omzet, 10 AS Maand, 'Sjaak' AS Verkoper
22
UNION
23
SELECT 506 AS Omzet, 11 AS Maand, 'Sjaak' AS Verkoper
24
UNION
25
SELECT 92 AS Omzet, 12 AS Maand, 'Sjaak' AS Verkoper
26

27
ORDER BY 2,3

Step 2 - Insert a table with a sparkline

Insert a table in SSRS. Create a group “Verkoper” (salesperson), a column “Omzet” (revenue) and a column trend. The sparkline goes in the last one. Delete the details group. Add a sparkline to the last cell with the following settings:

Your SSRS report looks like this:

The groupings look like this:

If you now preview the report, you get the following result:

What we want now is to show only the revenue of December, but the trend YTD.

Step 3 - Expression on the table cell

The trick is to set the filter on the revenue value. In it we filter out all months before December. The filter then applies only to the revenue cell and not to the trend. Of course this is now hard-coded for December (=12), but you can make this fully flexible. Note that the SUM() must go around the expression, not the other way around:

=SUM(IIF(Fields!Maand.Value<12,0,Fields!Omzet.Value))

If you preview again now:

This also matches the dataset: only Sjaak has revenue of 92 euros in December, and Jan has none:

This way you see a nice trend, while the figure shows the last value.