Herr G. Ordnet, Controller der Firma Adventure Works, möchte sich für ausgewählte Produkte seines Unternehmens die Internetverkäufe und die Frachtkosten ansehen. Er beauftragt den Reportbauer Z. Schnell mit dem Erstellen eines Berichts. Selbiger überdenkt die Anforderung und stellt einen Bericht mit einer Parameterliste aller Produkte zur Verfügung.

Nachdem Herr G. Ordnet mehrere Male alle 606 Produkte seiner Firma in der Auswahlkombobox des Berichtes durchforstet hat, platzt ihm der Kragen und er bittet um eine “vernünftige Einschränkung der Produkte nach Kategorien.”

Ein Glück, dass in der Datenbank diese Produktkategorien vorhanden sind, denkt sich Herr Schnell und entwickelt die sog. “cascading parameters” – also voneinander abhängige, hierarchisch angeordnete Parameter, deren Auswahl die Wertanzahl folgender Parameter einschränkt.

Zuerst werden im Report drei statt einem Parameter erzeugt. Alle Parameter sind mehrwertig und haben keinen Standardwert.

Dann werden drei Datasets für die Parameter mit folgenden Statements angelegt:

Produktkategorie:

SELECT      ProductCategoryKey, EnglishProductCategoryName FROM  DimProductCategory

Produktunterkategorie:

SELECT        ProductSubcategoryKey, EnglishProductSubcategoryName FROM         DimProductSubcategory

WHERE        (ProductCategoryKey IN (@ProductCategory))

Produkte selbst:

SELECT        EnglishProductName, ProductKey FROM  DimProduct

WHERE        (ProductSubcategoryKey IN (@ProdSubCategory))

Schlussendlich erzeugt Herr Schnell ein Dataset für die Werteabfrage:

SELECT   DimProduct.EnglishProductName, FactInternetSales.OrderQuantity * FactInternetSales.UnitPrice AS OrderVolume, FactInternetSales.Freight FROM DimProduct INNER JOIN FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey

WHERE  (FactInternetSales.ProductKey IN (@Products))


Weil Herr Schnell auf Standardwerte verzichtet hat, und alle vom vorhergehenden Parameter abhängigen Parameterabfragen ohne eine Auswahl des vorhergehenden Parameters nicht getätigt werden können, sind die Auswahlboxen der nachfolgenden Parameter solange deaktiviert, bis die nötige Vorauswahl von Kategorien oder Unterkategorien abgeschlossen ist.

Nun hat Herr G. Ordnet nur die Produkte zur Auswahl, deren Anzahl er vorher durch die Auswahl an Kategorien eingeschränkt hat – und Herr Schnell noch seinen Job.

Eine der häufigsten Anforderungen in Berichten, neben der grafischen Darstellung von Datenreihen, ist die Auflistung von Daten in Tabellen und Matrizen. Dabei sollen in den meisten Fällen auch die Summen der Gruppen und die Gesamtsumme, wie in der ersten Abbildung, gebildet werden.

Ja und, kann doch jeder: Einfach die Sum-Funktion verwenden:

Stimmt, in den meisten Fällen kommen wir damit zum Ziel.

Was aber, wenn wir noch mehr Informationen anzeigen wollen und dadurch einige Werte doppelt vor kommen, wie z.B. in unserem Beispiel, wenn wir nicht nur die Informationen des Internetverkaufs anzeigen wollen, sondern auch noch die Daten der Wiederverkäufer und zwar landesbezogen. Wenn wir dann die Sum-Funktion verwenden bekommen wir folgende Tabelle:

Wie man erkennen kann sind die Informationen über den Internetverkauf unabhängig vom Land, wodurch in jeder Zeile die Gesamtmenge für diesen einen Reiniger angezeigt wird. Der Wert für die Anzahl, der mit der einfachen Summenfunktion ermittelt wurde, summiert natürlich jede Zeile auf, wodurch wir ein Ergebnis von 6 * 908 = 5448 erhalten. Richtig wäre, wenn auch in der Gruppenzeile 908 stehen würde.

Die Lösung

Wir müssen also in der Summenfunktion prüfen, ob der Wert schon mal aufsummiert wurde. Das können wir erreichen, indem wir im Code eine globale Variable vom Typ String definieren. Jedes neue Produkt wird ans Ende des Strings geschrieben, so dass eine Liste mit den einzelnen Produkten entsteht. Als nächstes wird noch eine Funktion benötigt, die zum Einen den Wert und zum Anderen den Produktnamen übergeben bekommt. Diese überprüft, ob das Produkt schon in der Liste steht, falls dies nicht der Fall ist, wird der Wert zurückgegeben und der Name in der Liste aufgenommen. Falls der Name schon vorhanden ist, wird 0 zurückgegeben, wie der Codeausschnitt zeigt:

private productList as String = String.Empty

function DistinctSum(quantity as Integer, product as String) as Integer
    if productList.IndexOf(product) = -1 then
        productList += product + “, ”
        return quantity
    else
        return 0
    end if
end function

Man muss nur darauf achten, dass die Funktion immer Daten vom gleichen Typ zurück gibt, da es sonst passieren kann, dass die Summe nicht berechnet wird.

Abschließend muss die Gruppenzeile so angepasst werden, dass sie die neu erstellte Funktion verwendet. Leider ist es nicht möglich, einfach das Array mit den Details zu übergeben und die Summe zu berechnen. Daher verwenden wir weiterhin die Summenfunktion, um die Liste der Details einer Gruppe zu durchlaufen. Der Trick ist jetzt  nicht einfach ein Feld eines Datasets einzutragen, sondern inerhalb der Summe die im Code erstellte Funktion zu verwenden, die dann für jedes einzelne Element prüft, ob es schon aufaddiert wurde:

=Sum(code.DistinctSum(Fields!Internet_Order_Quantity.Value,Fields!Product.Value))

Wenn der Bericht, wie beschrieben angepasst wurde sollte jetzt für die Anzahl der Bestellungen, wie in der abschließenden Grafik,  der richtige Wert angezeigt werden.

1. Die Anforderung ist simpel:

“Die Linien innerhalb der Matrix sollen Grau und die untere Zeile soll bitte schwarz sein.”

Die meisten Reportdesigner schreiben sich solch ein Anliegen gar nicht auf, sondern setzen schlicht die Rahmenfarbe der Zeilen auf Grau und die des Subtotals auf Schwarz und reichen den Report zufrieden weiter.

Ergebnis:

2. Die Anforderung erweitert sich “gering”:

“Sehr geehrter Reportdesigner, die senkrechten Linien gefallen uns nicht, könnten Sie stattdessen einen Abstand zwischen die Länderspalten einbauen?”

Noch kommt keine Unruhe auf, flugs wird folgendes Design umgesetzt:
Auf den Zeilen wird eine neue Gruppe mit derselben Gruppierung wie die vorhergehende und im Detailfeld der Matrix einfach eine weitere Spalte eingefügt.
Die Rahmen der eingefügten Abstandsspalten bleiben unsichtbar, die Rahmenfarbe des Subtotals bleibt weiterhin schwarz.

Ergebnis:

Die Linie des Subtotals zieht sich leider über alle Spalten hinweg. Dies mag dem einen oder anderen nicht der Rede wert erscheinen, ärgerlich und unschön bleibt es trotzdem.

Konsequenz des pfiffigen Reportdesigners:

Ausblenden der Rahmen des Subtotals und Formatierung der Rahmenfarbe der Zeilengruppen und des Detailfeldes!

Dies kann durch folgende Ausdrücke erreicht werden (bei BorderStyle, Bottom=Solid)

Für die Zeilengruppen (Textbox Product) gilt:
BorderColor=IIF(RowNumber(“MyDataSetName”)=CountRows(“MyDataSetName”), “Black”,”Silver”)
d.h.:
Wenn die letzte Zeile des Datasets erreicht ist, färbe die Rahmen bitte Schwarz ein, sonst Silber.

Für die Zeile funktioniert das prima, für das Detailfeld muss aber auf die letzte Zeile der Spaltengruppe verwiesen werden, da das Scope (“MyDataSetName”) auf Detailebene nicht gültig ist.

Für das Detailfeld (Textbox mit Wertfeld) muss also gelten:
BorderColor=IIF(RowNumber(“MyColumnGroupName“)=CountRows(“MyColumnGroupName “),”Black”,”Silver”)

Und genau an dieser Stelle fängt der Ärger an.

Nehmen wir an, der Reportdesigner ist ein sparsamer Mensch und möchte sich im Dataset die leeren Zeilen für den OrderCount in Australien und Frankreich sparen und filtert diese Zeilen heraus.

MDX:

SELECT
NON
EMPTY

{

[Measures].[Reseller Order Count]

} ON
COLUMNS,

NON
EMPTY — > !! FILTERT LEERZEILEN

{ (STRTOSET(@countryCountry,CONSTRAINED) ,

STRTOSET(@productProduct,CONSTRAINED))} ON
ROWS
FROM [Adventure Works]

Das Dataset sieht dann wie folgt aus:

Ergebnis im bereitgestellten Report: (Die Vorschau im VisualStudio zählt natürlich NICHT!)

Das Problem wird schnell klar, natürlich ist die letzte Zeile in der Ländergruppe für Australien auch die einzige,
der o.g. Ausdruck sorgt für eine schwarze Färbung und alle
nachfolgenden, vom Report zu rendernden Elemente übernehmen die Formatierung des letzten Elementes, welches noch einen direkten Bezug zur Datasetzeile bzw. den Zeilen der angegebenen Gruppe besitzt. Das Phänomen kann sowohl auf Zeilen wie auch auf Spaltengruppen beobachtet werden.

Ein weiterer Versuch, die Ausdrücke für die Rahmenfarbe anders zu formulieren:
BorderColor=IIF(InScope(“matrix1_RowGroup1″),”Silver”,”Black”)

zeigte gar kein Ergebnis für die Spalten:

Damit bleibt leider kein anderer Schluss, als die Leerzeilen für die Ländergruppierung mit ins Dataset aufzunehmen:

SELECT
NON
EMPTY

{

[Measures].[Reseller Order Count]

} ON
COLUMNS,

–non empty auskommentiert

{ (STRTOSET(@countryCountry,CONSTRAINED) ,

STRTOSET(@productProduct,CONSTRAINED))} ON
ROWS
FROM [Adventure Works]

Dataset:

Gewünschtes Ergebnis im Report:

Dieser Eintrag ist für alle interessant die mit Vista als Betriebssystem arbeiten und sich an die Installation des SP1 gewagt haben.

Nach der Installation dürften sich die Nutzer folgender Ansichten im Internet Explorer erfreuen:

Abb. 1: Fehlermeldung beim Aufruf des Berichts Managers

Abb. 2: Fehlermeldung beim Aufruf des Berichtsservers

Voraussetzung ist natürlich ein Reporting Services Dienst der vor dem SP1 korrekt lief. Sollte das nicht der Fall sein, könnte folgender Artikel weiterhelfen Wie Installieren von SQL Server 2005 Reporting Services auf einem Windows Vista-based Computer.

Nun zur Behebung des Problems

Die Ursache ist die Abschaltung der Funktionalitäten für ‚Skripte’ und ‚Ausführen’ für die Site des Berichtsservers. Mit folgenden Schritten sollten die geliebten Berichte wieder erreichbar sein:

1. Klicken Sie in der Systemsteuerung auf System und Verwaltung.
2. Klicken Sie auf Verwaltung.
3. Doppelklicken Sie auf Internetinformationsdienste-Manager.
4. Klicken Sie in dem Dialogfeld User Account Control auf Fortsetzen.
5. Öffnen Sie im linken Bereich der Verbindungen den Baum soweit bis Sie auf ReportServer treffen. Im Normalfall sollte das folgender Pfad sein <Servername> -> Sites -> Default Web Site -> ReportServer.
6. Klicken Sie auf ReportServer.
7. Im mittleren Bereich auf Handlerzuordnungen doppelklicken.

    


8. Hier liegt die Ursache des Ganzen, das deaktivierte ISAPI Modul. Diese muss nun wieder aktiviert werde. Im Bereich Aktionen auf Featureberechtigungen bearbeiten klicken.

    


9. Im Dialog Featureberechtigungen Skript aktivieren und mit OK bestätigen.
10. Nochmals im Bereich Aktionen auf Featureberechtigungen bearbeiten klicken.
11. Im Dialog Featureberechtigungen Ausführen aktivieren und mit OK bestätigen. Das ISAPI Modul sollte nun wieder aktiviert sein.
12. Anschließend muss jetzt noch der IIS neu gestartet werden. Dazu im linken Bereich Verbindungen den Server auswählen und im rechten Bereich Aktionen auf Neu starten klicken.

Viel Spaß beim weiteren Arbeiten mit Reporting Services 2005, Ihr Jörg Knuth.

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.