SSRS Sparkline NU en TREND

Een sparkline gebruik je meestal binnen een cel, hoewel gebruik erbuiten ook mogelijk is. Een sparkline kun je alleen op groepsniveau binnen een tabel/matrix gebruiken en niet op de detailregel. Stel dat je de omzet Y-T-D in een trendlijn wilt tonen maar als getal in de tabel wil je alleen de omzet van de laatste maand tonen. Hoe ga je dit oplossen?

In eerste instantie zou je denken een filter te zetten op de tabel waarbij je de omzet van de laatste maand filtert. Dit werk niet omdat je dan ook de omzet van de voorliggende maanden filtert en daardoor de sparkline niet goed werkt. Een tabel in een tabelcel werkt ook niet goed. Door onderstaande stappen te volgen is een trendlijn zo gerealiseerd:

Stap 1 – Dataset aanmaken

Maak een dataset aan met de volgende data:

SELECT 50 AS Omzet, 1 AS Maand, 'Sjaak' AS Verkoper
UNION
SELECT 100 AS Omzet, 2 AS Maand, 'Jan' AS Verkoper
UNION
SELECT 100 AS Omzet, 2 AS Maand, 'Sjaak' AS Verkoper
UNION
SELECT 150 AS Omzet, 3 AS Maand, 'Sjaak' AS Verkoper
UNION
SELECT 300 AS Omzet, 4 AS Maand, 'Sjaak' AS Verkoper
UNION
SELECT 600 AS Omzet, 5 AS Maand, 'Jan' AS Verkoper
UNION
SELECT 900 AS Omzet, 6 AS Maand, 'Sjaak' AS Verkoper
UNION
SELECT 900 AS Omzet, 7 AS Maand, 'Sjaak' AS Verkoper
UNION
SELECT 150 AS Omzet, 8 AS Maand, 'Sjaak' AS Verkoper
UNION
SELECT 385 AS Omzet, 9 AS Maand, 'Sjaak' AS Verkoper
UNION
SELECT 100 AS Omzet, 10 AS Maand, 'Sjaak' AS Verkoper
UNION
SELECT 506 AS Omzet, 11 AS Maand, 'Sjaak' AS Verkoper
UNION
SELECT 92 AS Omzet, 12 AS Maand, 'Sjaak' AS Verkoper
 
ORDER BY 2,3

Stap 2 – Tabel invoegen met sparkline

Voeg een tabel in in SSRS. Maak een groep aan “Verkoper”, een kolom “Omzet” en een kolom trend. In de laatste komt desparkline. Verwijder de detailgroep.  Voeg een sparkline in de laatste cell toe met de volgende instellingen:

Je SSRS rapport ziet er zo uit:

De groeperingen zien er zo uit:

Als je nu een preview doet van het rapport dan krijg je het volgende resultaat:

Wat we nu willen is alleen de omzet tonen van december maar de trend YTD.

Stap 3 – Expressie op tabelcel

De truuk is nu om de filter op de omzetwaarde in te stellen. Daarbij filteren we alle maanden voor december weg. De filter is dan alleen van toepassing op de omzetcell en niet op de trend. Uiteraard is dit nu hard-coded voor december (=12) maar dit kun je helemaal flexibel inregelen. Let op dat de SUM() om de expressie heen moet en niet andersom:

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

Als je nu weer een preview doet:

Dit klopt ook met de dataset, immers alleen Sjaak heeft een omzet van 92 euro in december en Jan niet:

Op deze manier zie je een mooie trend terwijl de stand de laatste waarde toont.