Viele Controller kennen und lieben sie aus Excel: die Trendlinie. Mal eben die Umsätze der letzten Quartale als Balkendiagramm betrachtet und mit zwei Klicks den Trend visualisiert.
Um so verwirrter guckt dann dieser Controller, wenn man ihm versucht zu erklären, dass das mit den Reporting Services nicht möglich ist. Zum Glück stimmt das auch nicht ganz.
Mit Hilfe von Dundas Chart für Reporting Services ist dies fast so einfach wie in Excel. Der Haken liegt dann nur noch bei den Lizenzkosten.
Aber auch ohne zusätzliche Software sind einfache Trendlinien mit SSRS möglich, denn genaugenommen ist das ja nichts weiter als ein „bisschen“ Mathematik.
Grundlage für die oben gezeigte gerade Trendlinie ist die einfache lineare Regression. Und dafür gibt es in MDX Funktionen, die sich nutzen lassen, um diesen Trend zu berechnen. Die Grundlagen dieser MDX Funktionen und der mathematischen Konzepte dahinter hat Mosha bereits vor einigen Jahren in seinem Blog veröffentlicht.
Nun aber zurück zum eigentlichen Thema, das Erzeugen der oben gezeigten Grafik in den Reporting Services:
Mit dem Assistenten erstellen wir einen neuen Bericht auf den Adventure Works Cube aus der Adventure Works DW Datenbank. Nach dem Umschalten des Designmodes kommt für das Dataset folgender Quellcode zum Vorschein:
SELECT
NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY { ([Date].[Calendar].[Calendar Quarter].ALLMEMBERS) } ON ROWS
FROM [Adventure Works]
Um später die Lesbarkeit des Codes zu erhöhen, erstellen wir uns als erstes ein Set, dass alle Quartale enthält, die wir auch der Achse anzeigen wollen.
WITH
SET
myMonate AS ([Date].[Calendar].[Calendar Quarter].ALLMEMBERS)
Nun müssen wir für jede Zeile den Wert ermitteln, der für das jeweilige Quartal auf der Trendlinie liegt. Dazu nutzen wir die MDX-Funktion: LinRegPoint.
LinRegPoint(Slice_Expression_x, Set_Expression, Numeric_Expression_y [ ,Numeric_Expression_x ] )
Slice_Expression_x: Hier wird ein numerischer Ausdruck erwartet, der als x-Wert genutzt werden soll. Da die Quartale nicht numerisch sind, können wir diese hier nicht direkt benutzen. Wir können jedoch die RANK-Funktion nutzen, um die Position des jeweiligen Quartales auf der X-Achse zu ermitteln. Diesen merken wir uns in einem eigenen Member:
MEMBER
Measures.myX AS Rank([Date].[Calendar].CurrentMember, [Date].[Calendar].CURRENTMEMBER.LEVEL.MEMBERS)
Set_Expression: An dieser Stelle wird definiert, über welches Set der Trend-Wert berechnet werden soll. Hier könnte z.B. die letzten 10 Perioden gewählt werden, oder ein Set, das von Berichtsparametern abhängig ist, genutzt werden. In unserem Fall wollen wir einfach alle Quartale zur Berechnung heranziehen und nutzen deshalb das eben erstellte Set myMonate.
Numeric_Expression_y: Der gewünschte y-Wert ist einleuchtend: [Measures].[Sales Amount], dafür machen wir das ganze ja schließlich.
Numeric_Expression_x: Hier wird die Formel erwartet, die genutzt werden kann, um die x-Werte der anderen Punkte „vorauszusagen“. Wir können hier einfach den gleichen Ausdruck benutzen, wie bei Slice_Expression_x: myX
Wenn wir das alles einsetzen und dem Ganzen dann noch einen Namen geben, erhalten wir folgenden Ausdruck:
MEMBER
Measures.[Sales Trend] AS LinRegPoint(Measures.myX, myMonate, [Measures].[Sales Amount], Measures.myX)
Unsere Quelle für das Berichts-Dataset sieht nun also folgendermaßen aus:
WITH
SET
myMonate AS ([Date].[Calendar].[Calendar Quarter].ALLMEMBERS)
MEMBER
Measures.myX AS Rank([Date].[Calendar].CurrentMember, [Date].[Calendar].CURRENTMEMBER.LEVEL.MEMBERS)
MEMBER
Measures.[Sales Trend] AS LinRegPoint(Measures.myX, myMonate, [Measures].[Sales Amount], Measures.myX)
SELECT
NON EMPTY { [Measures].[Sales Amount],Measures.[Sales Trend] } ON COLUMNS,
NON EMPTY {myMonate} ON ROWS
FROM [Adventure Works]
Und wenn wir das ganze als Grafik anzeigen, dann sieht das in etwa so aus:
Wenn das unseren Controller nicht glücklich macht 😉
An dieser Stelle sollte noch angemerkt werden, dass die LinRegPoint-Funktion mit Rekursion arbeitet und damit, vor allem bei vielen Zeit-Werten, eine potentielle Performancebremse ist. Wer also beim Darstellen des Trends mit diesem Verfahren an die Grenzen stößt, der sollte dann doch mal Dundas ausprobieren, oder auf die nächste Version des SQL Servers (2008) hoffen.