Het maximum van groepstotalen bepalen in SSRS

Onlangs werd mij gevraagd hoe je in Reporting Services het maximum van een groepstotaal kunt bepalen. De vraag kwam eigenlijk neer op het volgende voorbeeld: gegeven de AdventureWorks database, bepaal het aantal orders per subcategorie en highlight het totaal met de hoogste waarde binnen de bijbehorende categorie. In onderstaande afbeelding, was het gewenste gedrag dat de waarden bij Tires and Tubes en Road Bikes rood op zouden lichten, omdat het de hoogste waarden binnen de categorieën Accessories resp. Bikes zijn.

Om de conditionele opmaak van de betreffende cellen in te stellen, werd in eerste instantie de volgende expressie gebruikt:

Dat deze expressie niet het gewenste resultaat geeft, wordt snel duidelijk wanneer je deze expressie in een extra kolom in het rapport weergeeft. Om te begrijpen waarom deze expressie niet het gewenste  resultaat geeft, moet je begrijpen hoe SSRS iedere expressie binnen een bepaalde scope uitvoert. De scope is dat deel van een dataset waarvoor een expressie wordt uitgevoerd. Dat kan zijn de hele dataset, een data region (waarop eventueel filters zijn gedefinieerd), een groepering of een detailregel. Als je binnen een expressie een aggregatiefunctie gebruikt, heb je de keuze de te gebruiken scope expliciet te benoemen. Doe je dat niet, dan wordt de default scope gebruikt. Binnen de group header van de categorie groepering in onderstaand voorbeeld, is de default scope de set van records die bij een bepaalde categorie hoort. Voor de group header van de subcategorie groeperingen, is dat de set van records die bij die subcategorie hoort. Over die set van records wordt in dit voorbeeld de maximum waarde bepaald.

Zoals met de pijlen is aangegeven, wordt daarom voor beide groeperingen het maximum bepaald van de individuele producten die binnen de scope vallen. Omdat het individuele product met het hoogste aantal orders de Water Bottle – 30 oz. is die zowel binnen de set van records van de Bottles and Cages valt, maar ook binnen de grotere set van records van de Accessories, is de uitkomst van deze expressie voor beide groeperingen op verschillend niveau gelijk aan het aantal orders voor dat product.

De gewenste uitkomst van de expressie binnen de categorie header is eigenlijk de hoogst voorkomende waarde binnen de subcategorieën. Iets waarvoor we in SQL Server Reporting Services 2008 R2 dankbaar gebruik kunnen maken van de nieuwe feature ‘Aggregates of Aggregates’. Door de expressie te herschrijven in:

krijgen we wel het gewenste resultaat. Nu wordt eerst op subcategorie niveau het totaal van de orders bepaald. Daarvan wordt vervolgens op categorie niveau de hoogste waarde bepaald. Passen we deze expressie toe in de conditionele opmaak van de cellen in de group header van de subcategorie groepering, krijgen we wel het gewenste resultaat.

Helaas gaat dit goed todat we van de tabel een matrix maken:

Je ziet nu opnieuw de hoogste categorie totalen, maar omdat we hiervoor de scope in de SUM en MAX functies expliciet hebben benoemd, is de default scope vervangen door deze named scope waardoor de jaren buiten beeld zijn geraakt. De 17.332 die je voor de Accessories in bovenstaand voorbeeld ziet, is immers het hoogste order aantal van alle subcategoriën onder deze categorie in alle jaren…

Juist in dit soort situaties is het goed te weten dat Report Designer en Report Builder je een zogenaamde ‘visual clue’ geven over de default evaluatie context van een expressie. Zodra je een cel in een tablix selecteert, laten de tools je middels maiskleurige handles zien wat de ‘innermost row en column groups’ zijn waartoe de geselecteerde cel behoort. Dat bepaald de default evaluatie context.

Terug naar ons probleem. Helaas is het niet mogelijk de context voor twee verschillende, niet gerelateerde groeperingen expliciet te benoemen. Daarom gaat in dit complexere voorbeeld het gebruik van geneste aggregatiefuncties niet meer op en zul je terug moeten vallen op een oplossing die je ook in SQL Server Reporting Services 2008 en ouder zult moeten toepassen, bijvoorbeeld oplossen in de dataset. In onderstaand voorbeeld heb ik een extra veld opgenomen in de dataset waarvoor in de query op het laagste niveau is bepaald wat het maximum orderaantaal bij de bijbehorende productcategorie is. Dat veld kun je vervolgens simpelweg laten zien of gebruiken in expressies voor conditionele opmaak.

Conclusie

Werk je met SQL Server Reporting Services 2008 R2, vergeet dan bij ‘eenvoudige’ problemen de kracht van de ‘Agregates of Agregates’ niet! Werk je nog niet met de laatste versie van SQL Server Reporting Services of heb je te maken met zowel een rij als kolom context als gevolg van groeperingen in matrices, zul je de oplossing van dit soort problemen moeten zoeken in bijvoorbeeld de dataset. Ook het schrijven van code om in dit specifieke voorbeeld het maximum van waarden in een group header te bepalen, behoort tot de mogelijke oplossingen.