Archiv der Kategorie: MS Reporting Services

Microsoft Reporting Services 2005, 2008, 2008 R2, 2012, 2014

SSRS: Top 10 und Rest zum Auf- und Zuklappen

Die Aufgabe in diesem Blog-Eintrag ist es, bei einem gegebenen Dataset nur die ersten 10 Einträge anzuzeigen und den Rest über +/- auf- und zuklappen zu können.

Dazu gibt es einen guten Artikel: http://www.bidn.com/blogs/mikedavis/ssis/172/top-n-bottom-n-grouping-in-ssrs-2008

Allerdings verwendet dieser die Filter-Funktion “Top n” der Gruppe. (Und für den Rest Bottom (Anzahl-n)) Diese hat folgende Nachteile:

  • Wenn der 10. und der 11. Eintrag gleich sind, so zeigen die Top 10 in Wirklichkeit 11 Einträge. Das wäre noch nicht so schlimm, aber:
  • Damit sind natürlich auch der 10.-letzte und der 11.-letzte Eintrag gleich, somit kommen beide in dem Rest noch einmal, so dass beide Einträge doppelt erscheinen.

Das sieht dann so aus:

image

Der Autor hat zwar noch einen weiteren Artikel veröffentlich, in dem er das Problem versucht zu beheben – aber dabei verwendet er SQL-Funktionen. Ich möchte das Problem nur mit SSRS-Features lösen, um eine allgemein gültige Lösung zu haben, zumal in meinem Kundenprojekt MDX zum Einsatz kam.

Mit folgender Anleitung kann man dieses Problem beheben:

Stellen wir uns vor, wir haben ein Dataset wie:

image

Dieses Dataset zeigen wir in einer Tabelle an – ohne Gruppierung. Das sieht so aus – mit der “Details”-Gruppe:

image

Für die Details-Gruppe setzen wir folgende Eigenschaften:

  • Sortierung: Wert absteigend, danach Person aufsteigend. Letzteres ist notwendig, um die Reihenfolge deterministisch zu machen (falls der Wert gleich)
  • Sichtbarkeit: über Expression: =RowNumber(Nothing)>5
    Damit werden nur die ersten 5 Zeilen angezeigt.

Diese Details sollen die Top 5-Werte anzeigen.

Für die Gruppe, die die restlichen Werte anzeigen soll, legen wir angrenzend daran eine neue Detailgruppe an:

image

Diese Gruppe stellen wir so ein:

  • Details: image
  • Name: Details_Rest
  • Sortierung: Wert absteigend, danach Person aufsteigend.
  • Sichtbarkeit: über Expression: =RowNumber(Nothing)<=5
    Damit werden die ersten 5 Zeilen nicht angezeigt.
  • Die Spalten werden wieder mit Person und Wert besetzt.

Nun legen wir jeweils eine übergeordnete Gruppe an:

image

Die erste Gruppe stellen wir so ein:

  • Name: Person_Top
  • Gruppieren nach Person, das entspricht also unserer Detailzeile (Allgemein verwenden wir hier den Primärschlüssel)
  • Sortierung: wie die Detailgruppe
  • Sichtbarkeit: Anzeigen
  • Die neu angelegte Spalte Person löschen wir (Wichtig! Nur die Spalte löschen nicht die Gruppe löschen)
    image

Das gleiche machen wir für die zweite Detailzeile und richten auch hier eine übergeordnete Gruppe ein:

  • Name: Person_Rest
  • Gruppieren nach Person, das entspricht also unserer Detailzeile (Allgemein verwenden wir hier den Primärschlüssel)
  • Sortierung: wie die Detailgruppe
  • Sichtbarkeit: Anzeigen

Somit sieht unser Bericht so aus:

image

Ganz links fügen wir eine neue Spalte (außerhalb der Gruppe) hinzu:

image

Dann löschen wir wieder die unnötige Person-Spalte (ohne die Gruppe zu löschen):

image

Die Zellen ganz links setzen wir:

  • Name = txt_Top
  • Inhalt = Top 5

bzw.

  • Name = txt_Rest
  • Inhalt = Rest

Unser Bericht sieht dann so aus:

image

Jetzt wollen wir noch das Auf- und Zuklappen implementieren:

  • Gruppe Person_Top
    • Sichtbarkeit umschalten nach Textbox “txt_Top”
  • Gruppe Person_Rest
    • Sichtbarkeit: Ausblenden
    • Sichtbarkeit umschalten nach Textbox “txt_Rest”
      image

Jetzt muss noch etwas Kosmetik gemacht werden:

  • Die txt_Top-Textbox sollte ein – zu Beginn haben und kein +. Also müssen wir bei dieser Textbox den InitialToggleState auf true setzen
  • Wenn die Gruppe zusammengeklappt ist, sieht man die erste Person. Deswegen schreiben wir in die jeweiligen Spalten
    =iif(count(Fields!Person.Value)>1, Nothing, Fields!Person.Value) bzw.
    =iif(count(Fields!Person.Value)>1, Nothing, Fields!Wert.Value)

Geschafft!

image

und

image

Der Beispielbericht ist hier als Download verfügbar.

Zum Abschluss noch ein paar Worte zur Motivation, warum wir jeweils zwei Gruppen verwenden:

  • Die äußere Gruppe wird verwendet, um den “Toggle” zu ermöglichen (also das Ein- und Ausblenden), die inenre Gruppe, um zu definieren, welche Zeilen in der Gruppe sichtbar sind. Da beides über “Sichtbarkeit” geregelt wird, benötigen wir 2 Gruppen.
  • Die Alternative wäre, mit Filtern in der Gruppe zu arbeiten. Dann bräuchte man nur eine Gruppe. Leider darf aber RowNumber(…) nicht in Filtern verwendet werden, so dass dieser Weg ausscheidet.

Performanceprobleme bei SSAS-Dimensionsabfragen aus SSRS

Nachdem wir in einem Projekt unseren Test-Datenbestand (12 Mio Fakten) auf den Produktiv-Bestand (73 Mio Fakten) erweitert hatten, zeigten die Berichte (SSRS 2008 R2) massiv schlechte Antwortszeiten in bestimmten Berichten auf unseren Cube (SSAS 2008 R2) – jeweils nach der Cubeaufbereitung. Somit war klar, dass Ursache war, dass einige der im Bericht verwendeten Abfragen nicht im Cache waren, da dieser ja durch die inkrementelle Dimensionsaufbereitung und Aufbereitung einiger Cubepartitionen gelöscht wird.

Naheliegende Strategien waren:

  • Aufteilung in mehr Partitionen unter Angabe der Slice-Property
  • Verbesserte Aggregationen über Usage based Aggregation Design
  • Cache Warming – Strategien

All das brachte uns aber nicht wirklich weiter.

Letztendlich stellte sich aber heraus, dass die Ursache gar nicht in den (tlw. komplexen) Abfragen zur Ermittlung der Fakten zu suchen war, sondern in „Dimensions-Abfragen“. Damit meine ich folgendes: Unsere Berichte werden (aus einer eigenen Web-Applikation heraus) mit Parametern aufgerufen, die IDs sind (eigentlich die Member Unique Names, also z.B. Datum.Jahr.&[2010]). Dabei haben die Parameter (aus Performancegründen) keine Datasets, die alle verfügbaren Werte enthalten. Nun wollen wir aber in dem Bericht natürlich auch den übergebenen Wert im Klartext anzeigen, also in obigem Beispiel das Jahr 2010. Deswegen mussten also einfache Abfragen her, die aus dem Member Unique Name den Klartext (Member Caption) ermitteln.

In unserem Projekt hatten wir uns darauf geeinigt, wenn möglich den SSRS-Designer für SSAS-Abfragen zu verwenden, um eine gute Wartbarkeit (ohne tiefere MDX-Kenntnisse) zu erreichen.

Damit gibt es zwei einfache Arten, dieses Problem zu lösen:

Die erste Art, hat eine Spalte – das Jahr – und zusätzlich den (MultiSelect) Report Parameter als Parameter für die Abfrage:

Wizard für die langsame Abfrage
(Bild zum Vergößern anklicken!)

Als MDX ergibt sich:

SELECT { } ON COLUMNS, { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DatumJahr, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE

Man kann auch die Abfrage etwas erweitern:

Man fügt ein berechnets Measure (in diesem Fall namens „UniqueName“) mit dem MDX

[Datum].[Jahr].CurrentMember.UniqueName

hinzu:

Wizard der schnellen Abfrage
(Bild zum Vergößern anklicken!)

Damit ergibt sich folgendes MDX:

WITH MEMBER [Measures].[UniqueName] AS [Datum].[Jahr].CurrentMember.UniqueName SELECT NON EMPTY { [Measures].[UniqueName] } ON COLUMNS, NON EMPTY { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DatumJahr, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Anmerkung: Das dabei enstehende versteckte Dataset zum Befüllen der „verfügbaren Werte“ des Parameters löschen wir, da wir ja keine „verfügbaren Werte“ anzeigen wollen (unsere Dimension hatte zu viele Einträge).

Beide Abfragen scheinen dasselbe zu tun und mit der selben Performance. Dies ist aber ein Trugschluss!

Leeren wir zunächst (und vor Ausführung eines neuen Test-Statements) den SSAS-Cache mit

<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine“>
<Object>
<DatabaseID>SimpleCube</DatabaseID>
<CubeID>Verkaeufe</CubeID>
</Object>
</ClearCache>

(siehe dazu auch: http://www.ssas-info.com/analysis-services-faq/27-mdx/133-mdx-how-do-i-clear-analysis-services-ssas-database-cache)

Dann betrachten wir die Darstellung im SQL Server Profiler (mit den Standard-Einstellungen für den SSAS):

Die erste Abfrage ergibt folgendes Bild:

SQL Profiler Ergebnis der langsamen Abfrage
(Bild zum Vergößern anklicken!)

Schauen wir uns die einzelnen Zeilen an:

  • Session Initialize – spricht für sich
  • Query Begin: Hier fängt die Abfrage an – im unteren Bereich sieht man das MDX und die Parameterwerte für die @Parameter im MDX
  • Progress Report Begin: Daten aus einer Partition werden gelesen (!), d.h. von der Festplatte in den Speicher übernommen.
  • Progress Report End: das dazugehörige Ende
  • Query SubCube: die gelesenen Daten werden verwendet, um die Abfrage zu beantworten (in diesem Fall Non-Cache)
  • Query End: Das Ende der Abfrage

Die zweite Abfrage ergibt ein anderes Bild:

SQL Profiler Ergebnis der schnellen Abfrage
(Bild zum Vergößern anklicken!)

Hier sehen wir, dass kein Zugriff auf eine Partition erfolgt!

Der Zugriff auf die Partition ist natürlich schädlich, da er bei großen Datenmengen lang dauern kann, zumal er gar nicht benötigt wird, da wir ja nur Dimensions-Element-Bezeichnungen abfragen wollen.
Es ist nicht erklärbar, warum SSAS hier dennoch auf die Fakten zugreift.
Deswegen ist die zweite Abfrage auf jeden Fall vorzuziehen!

Noch ein interessante Anmerkung zum Schluss: Wenn man die erste Abfrage im SQL Server Management Studio ausführt, ist sie auch nicht langsam. Da im Management Studio keine parametrisierten Abfragen möglich sind, muss man dazu die Parameter durch die entsprechenden Strings ersetzen, und erhält somit folgendes MDX:

SELECT { } ON COLUMNS, { ([Datum].[Jahr].[Jahr].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(„{ [Datum].[Jahr].&[2001],[Datum].[Jahr].&[2008] }“, CONSTRAINED) ) ON COLUMNS FROM [Verkaeufe]) CELL PROPERTIES VALUE

Führt man dieses MDX aus (nachdem man den Cache geleert hat), so erhält man folgendes Bild:

Ergebnis des Profilers der langsamen Abfrage ausgeführt im Management Studio
(Bild zum Vergößern anklicken!)

Man sieht hier also auch nur ein Query Begin und ein Query End, also auch keinen Zugriff auf die Cube-(bzw. Partitions-)Daten.

Dies hatte uns die Fehlersuche erschwert, da wir natürlich zunächst auf der Suche nach dem verantwortlichen Statement alle Statements im SQL Server Profiler mitgeschnitten und dann einzeln im Management Studio ausgeführt hatten, was – wie eben gesehen – das problematische Statement leider nicht offenbart.

Säulendiagramme mit Datum auf der x-Achse

Bekanntermaßen kann man in Reporting Services alle Standard-Charts darstellen.

Bei den Säulen-Diagrammen hat man sogar – was ich heute zeigen möchte – die Möglichkeit, auf der x-Achse Datumswerte anzuzeigen. Dies ist z.B. in (Pseudo-) Gantt-Charts interessant.

Für unser Beispiel wollen wir die Laufzeit von zwei Phasen eines Projekts darstellen:

Phase 1 läuft vom 1.3.2011 bis zum 20.3.2011, wobei die Zeit ab dem 17.3.2011 rot dargestellt werden soll.
Phase 2 läuft vom 15.3.2011 bis zum 7.4.2011, wobei die Zeit ab dem 1.4.2011 rot dargestellt werden soll.

Das Ziel sieht somit so aus:

Ziel der 2 Phasen mit Datum auf x-Achse
(Zum Vergrößern anklicken)

Um dies zu erreichen, geben wir folgendes im Chart an:

  • als Kategorie: die Phase
  • als Reihe: die Farbe
  • als Wert: das Datum

Allerdings ist das Datum nicht einfach einzugeben. Wir müssen vielmehr folgendes einstellen (examplarisch an der Phase 1 gezeigt):

  • ein transparenter Balken bis zum 1.3.2011
  • ein grüner Balken bis zum 17.3.2011, also 16 Tage breit
  • ein roter Balken bis zum 20.3.2011, also 3 Tage breit

Für den ersten Balken können wir als Wert einfach den 1.3.2011 angeben. Für die darauffolgenden Balken müssen wir aber das Datum als Wert angeben, das die Anzahl der Tage auf den 30.12.1899 addiert, also

  • für einen Balken mit 1 Tag Länge: 31.12.1899
  • für einen Balken mit 2 Tagen Länge: 1.1.1900
  • für einen Balken mit 2,5 Tagen Länge: 1.1.1900 12:00
  • für einen Balken mit 3 Tagen Länge: 2.1.1900
  • für einen Balken mit 16 Tagen Länge: 15.1.1900
  • usw.

Natürlich müssen wir darauf achten, dass die Reihenfolge der Balken stimmt. Deswegen muss die Reihe entsprechend sortiert sein.

Außerdem werden wir die x-Achse so formatieren, dass sie die Datumswerte schön anzeigt.

Nun haben wir alles beisammen, um das Projekt zu schaffen:

Als SQL wählen wir:

SELECT ‚Phase 1‘ as Phase, ‚transparent‘ as Farbe, 1 as sort, convert(Datetime, ‚1.3.2011‘, 104) as wert
UNION ALL
SELECT ‚Phase 1‘ as Phase, ‚green‘ as Farbe, 2 as sort, convert(Datetime, ‚15.1.1900‘, 104) as wert
UNION ALL
SELECT ‚Phase 1‘ as Phase, ‚red‘ as Farbe, 3 as sort, convert(Datetime, ‚2.1.1900‘, 104) as wert
UNION ALL
SELECT ‚Phase 2‘ as Phase, ‚transparent‘ as Farbe, 1 as sort, convert(Datetime, ‚15.3.2011‘, 104) as wert
UNION ALL
SELECT ‚Phase 2‘ as Phase, ‚green‘ as Farbe, 2 as sort, convert(Datetime, ‚16.1.1900‘, 104) as wert
UNION ALL
SELECT ‚Phase 2‘ as Phase, ‚red‘ as Farbe, 3 as sort, convert(Datetime, ‚5.1.1900‘, 104) as wert

Und im Chart muss folgendes eingestellt werden:

Es ist ein Stacked Bar Chart:

Stacked Bar Chart

Die Grundeinstellungen sieht man hier:

Einstellungen des Charts

Die genauen Eigenschaften der Kategorie (Phase) sind:

General Eigenschaften der Category
(Zum Vergrößern anklicken)

und

Sorting Category
(Zum Vergrößern anklicken)

Die genauen Eigenschaften der Reihe (Farbe) sind:

Generelle Eigenschaften der Reihe
(Zum Vergrößern anklicken)

und

Sortier-Eigenschaften der Reihe
(Zum Vergrößern anklicken)

Die genauen Eigenschaften der Werte sind:

Einstellungen der Werte 1
(Hier darauf achten, dass nicht count(Wert) dort steht (wie es SSRS beim Anklicken erstellt), sondern nur wert!

und

Fülleigenschaften der Werte
(Hier darauf achten, dass unter Farbe der Wert „=Fields!Farbe.Value“ steht, zu erreichen über das fx-Symbol)

Dann muss nur noch die x-Achse korrekt eingestellt werden:

Optionen der Achse
(Zum Vergrößern anklicken)

Dabei sind in diesem Fall als Minimum „=DateSerial(2011, 2, 27)“ und als Maximum „=DateSerial(2011,4,10)“ eingestellt. Natürlich sollte man das „in Wirklichkeit“ aus den Daten über min / max und ggf. dateadd von Tagen berechnen.

Als Datumsformat ist das deutsche Format eingestellt:

Datumsformat der x-Achse

Um die kleinen Zacken pro Tag (ohne Datumsanzeige) zu haben (wie man sie oben in dem Ziel-Chart sehen kann), habe ich noch die minor tick marks auf 1 gesetzt:

Minor Tick Marks der x-Achse
(Zum Vergrößern anklicken)

Die vollständige rdl-Datei kann hier heruntergeladen werden.

Dieses Projekt lässt sich zu einem Pseudo-Gantt-Chart ausbauen, in dem dann (nur) die senkrechten Linie und Pfeile fehlen, was für eine erste Orientierung allerdings ausreichend sein dürfte.

Natürlich lassen sich auch andere Visualisierungen damit realisieren, z.B. Darstellung von Up-/Down-Time von Maschinen etc.

Berechnete Elemente in MDX-Abfragen mit Anwendung in Reporting Services 2005

Lange habe ich gesucht, um ein sinnvolles Beispiel für ein berechnetes Dimensions-Element zu finden (berechnete Measures sind ja ständig zu finden).

Ein Beispiel ist eine Matrix in Reporting Services 2005 (in 2008 ist mit der Tablix ja alles (bzw. vieles) besser).

Ich habe über das Problem bereits zwei Artikel auf sqlservercentral.com veröffentlicht (Reporting Services: Adding extra columns / rows to a matrix und Reporting Services: Read Data from SSAS and SQL Server in One Dataset). Heute möchte ich das Problem auf eine andere Weise lösen.

Nehmen wir an, wir wollen in den Spalten alle Monate sehen und die Summe und einen Planwert und die Differenz zwischen Summe und Planwert. Wenn wir es jetzt schaffen, das ganze in einem MDX-Statement zu laden, so kann auch die Matrix von SQL Server Reporting Services 2005 das anzeigen.

Alle Monate in MDX anzuzeigen, ist einfach. Das Beispiel zeigt je Produkt und Monat den Verkaufswert an (ein konstruiertes, vereinfachtes Beispiel – ich habe sogar auf die Monatsnamen verzichtet und zeige die Monate im Format JJJJMM an):

select non empty [Datum].[Monat].[Monat].members on columns,
non empty [Produkt].[Produkt].[Produkt].members on rows
from [Verkaeufe]
where ([Measures].[Verkaufswert], [Datum].[Jahr].&[2010])

liefert:

Monate mal Produkte

Die Spalte Summe geht auch einfach. Dazu müssen wir den Alle-Member der Attribut-Hierarchie Monat anzeigen:

select non empty {[Datum].[Monat].[Monat].members, [Datum].[Monat].[All]} on columns,
non empty [Produkt].[Produkt].[Produkt].members on rows
from [Verkaeufe]
where ([Measures].[Verkaufswert], [Datum].[Jahr].&[2010])

liefert:

Monate inkl. Alle Mal Produkt

Für den Planwert konstruieren wir ein einfache Element der Attribut-Hierarchie Monat, das konstant 10 ist:

with member [Datum].[Monat].[Plan] as 10
select non empty {[Datum].[Monat].[Monat].members, [Datum].[Monat].[All], [Datum].[Monat].[Plan]} on columns,
non empty [Produkt].[Produkt].[Produkt].members on rows
from [Verkaeufe]
where ([Measures].[Verkaufswert], [Datum].[Jahr].&[2010])

liefert:

Monate inkl. Alle und Plan mal Produkte

(Die unschöne unbekannt-Zeile kann man natürlich leicht loswerden, aber das ist hier nicht unser Thema)

Das war noch eine leichte Übung. Aber in diesen Berechnungen können wir auch rechnen, wie in jedem MDX. Somit können wir einfach die Differenz anzeigen:

with member [Datum].[Monat].[Plan] as 10
member [Datum].[Monat].[Differenz] as [Datum].[Monat].[All] – [Datum].[Monat].[Plan]
select non empty {[Datum].[Monat].[Monat].members, [Datum].[Monat].[All], [Datum].[Monat].[Plan], [Datum].[Monat].[Differenz]} on columns,
non empty [Produkt].[Produkt].[Produkt].members on rows
from [Verkaeufe]
where ([Measures].[Verkaufswert], [Datum].[Jahr].&[2010])

liefert:

Monate inkl. Alle, Plan, Differenz Mal Produkte

Das finde ich ein sehr schönes Beispiel, wie man berechnete Dimensions-Elemente einsetzt.

Um das ganze in Reporting Services zu verwenden, muss man alles natürlich auf die Zeilen bringen und nur das Measure in den Spalten haben – aber das ist trivial.

Außerdem muss man in der Matrix noch die Sortierung lösen, aber über iif-Berechnungen in dem Sortierungsfeld ist das auch leicht zu lösen.

SSRS: Darstellung von multi-valued Parametern im Report

In Berichten ist es eine gute Angewohnheit, die durch den Benutzer ausgewählten Parameter im Bericht anzuzeigen.

Bei Parametern, die dem Benutzer nur einen Wert einzugeben, ist das einfach, da dieser Ausdruck im Ausdruck-Editor einfach zusammengeklickt werden kann, zu z.B.

=“Eingegebene Zahl: “ & Parameters!EinfacherParam.Value

Parameter mit einem Wert

(Zum Vergrößern aufs Bild klicken)

Wenn es sich aber um einen Multi-Value Parameter (wie in meinem Beispiel der Parameter „Wochentag“) handelt, dann erzeugt der Ausdruck-Editor … Parameters!Wochentag.Value(0), was den Value (das ist der Schlüssel) ersten ausgewählten Wochentag ausgibt.

Da mir aber etwas wie „Sie haben folgende Wochentage ausgewähtl: Montag, Dienstag, …“ vorschwebt, müssen wir zunächst den Label anzeigen lassen und nicht den Schlüssel. Das geht über Parameters!Wochentag.Label(0).

Nun wollen wir aber alle ausgewählten Parameterwerte sehen und nicht nur den ersten. Da es sich bei Parameters!Wochentag.Label offensichtlich um ein Array handelt (Array startet mit Position 0), kann man über die Join-Funktion die einzelnen Werte konkatenieren.

Dazu kann man folgenden Code im Ausdruck-Fenster eingeben:

=“Alle (via Ausdruck): “ &
Join(Parameters!Wochentag.Label, „, „)

Alle Params via Ausdruck

Damit endet die Möglichkeit im Ausdruck-Fenster.

Will man weitere Funktionalität einbauen, wie z.B. die Liste 1-Montag, 2-Dienstag, …, so kann man das dadurch erreichen, dass man im Code-Fenster auf die Parameter und ihre Werte zugreifen kann:

Code eingeben

(Zum Vergößern Bild anklicken)

Das Code-Fenster erreicht man über Bericht > Berichsteigenschaften > Code.

Dort habe ich folgenden Code eingefügt:

function ParameterwerteVerkettet(ParamName as string)
dim i as integer
dim erg as string

for i = 1 to Report.Parameters(ParamName).Count
erg = erg & Report.Parameters(ParamName).Value(i-1) & „: „
erg = erg & Report.Parameters(ParamName).Label(i-1)
if i<> Report.Parameters(ParamName).Count Then erg = erg & „; „
next i

ParameterwerteVerkettet=erg
end function

Wie man sieht, kann man einfach in dem VB-Code auf die Parameter-Arrays Value und Label zugreifen.

Diese Funktion ruft man dann im Ausdruck wie folgt auf:

Ausdruck via Code

Mein fertiger Bericht sieht dann wie folgt aus:

fertiger Report

Das RDL kann man hier runterladen: www.csopro.de/blogdateien/ParamsAnzeigen.rdl

Noch einige weiterführende Hinweise:

Kalendersteuerelement bei Reports auf Cubes

Reporting Services hat ein Kalendersteuerelement, in dem man Datumswerte einfach eingeben kann, wenn ein Prompt vom Typ Date / Time ist. Heute möchte ich beschreiben, wie man das verwenden kann, wenn man einen Bericht erstellt, der auf einem Analysis Services-Cube basiert. Beim Cube ist (in der Regel) das Problem, dass es zwar eine Zeit-Dimension gibt, diese aber natürlich wie alle Dimensionen einen Member Unique Name (also Key) wie z.B. „[Intervall].[Datum].&[20081012]“ oder „[Intervall].[Datum].&[3207]“ haben. Dies ist offensichtlich ein String und kein DateTime, weswegen beim Standard-Vorgehen der Reporting Services eine Kombobox mit allen Datumswerten erstellt.

Aber hier im Detail das Vorgehen:

Im Wizard zum Erstellen der Abfrage gehen wir wie folgt vor:

Wizard zum Erstellen der Abfrage an den Cube

(Zum Vergrößern auf das Bild klicken)

Ich habe einfach eine Kennzahl und das Datum in die Abfrage gezogen und einen Filter auf das Datum gesetzt, wobei ich (wichtig!) die beiden Checkboxen bei den Parametern gesetzt habe.

Bei der Berichtsausführung werden die beiden Parameter über Komboboxen realisiert:

Parameter From als Kombobox

Dazu hat nämlich der Wizard zwei versteckte Data Sets angelegt, die die entsprechenden Werte liefern:

Hidden Datasets

Als nächstes müssen wir das MDX anpassen. (Im Query Designer auf Design Mode klicken)

 

Der Wizard hat folgendes MDX erstellt:

SELECT NON EMPTY { [Measures].[Annahmen] } ON COLUMNS, NON EMPTY { ([Intervall].[Datum].[Datum].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromIntervallDatum, CONSTRAINED) : STRTOMEMBER(@ToIntervallDatum, CONSTRAINED) ) ON COLUMNS FROM [<CubeName>]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Die beiden mit @-benannten Ausdrücke sind die Prompts.

Wir müssen nun den Teil „STRTOMEMBER(@FromIntervallDatum, CONSTRAINED) “ so abändern, dass dort eine Variable vom Typ DateTime eine Rolle spielt. Der Schlüssel eines Datumselements in diesem Fall sieht so aus „[Intervall].[Datum].&[3190]“, wobei die ID die Anzahl der Tage zwischen dem 1.1.2000 und dem betreffenden Tag ist.

Gehen wir nun davon aus, dass wir einen Prompt @von vom Typ DateTime haben, müsste der STRTOMEMBER so aussehen:

STRTOMEMBER(„[Intervall].[Datum].&[“ +cstr( datediff(„d“, DateSerial(2000, 1, 1), @von )) + „]“)

Die DateSerial-Funktion verwende ich hier aus hygienischen Gründen, um keinen Cast von String auf DateTime (der abhängig von der Locale wäre) zu verwenden)

Auf das CONSTRAINED verwende ich, da sonst der Query Designer meckert.

Wenn man selbst natürlich einen anderen Key benutzt, muss dies angepasst werden. Bei einem Key im Format YYYYMMDD, also [Intervall].[Datum].&[20091205], müsste die Formel so sein:

STRTOMEMBER(„[Intervall].[Datum].&[“ +cstr( year(@von) ) + right(„0“ + cstr(month(@von), 2) + right(„0“ + cstr(day(@von), 2) + „]“)

Nun müssen wir noch den Parameter der Abfrage definieren (über Button Abfrage Parameter):

Parameter der Abfrage

Damit existiert noch nicht der Prompt.

Unter den Eigenschaften des Datasets erscheint der Parameter @von bereits:

Dataset Properties: Parameter

Dort tragen wir in „Parameter Value“ einfach „[@von]“ ein.

Damit wird automatisch ein Report Parameter von angelegt

Report Parameter von, den wir wie folgt bearbeiten: Wir setzen den Datentyp auf Datetime:

Bild

(Außerdem löschen wir den nicht mehr benötigten Prompt „FromIntervallDatum“)

Damit steht jetzt auch hier das Kalendersteuerelement zur Verfügung:

fertiges Kalendersteuerelement

Web-Veröffentlichungen zu Reporting Services

Ich habe zwei Artikel unter www.SqlServerCentral.com veröffentlicht, in denen ich aufzeige, wie man die in meinen Augen größten Mankos des Reporting Services umgehen kann:

  • Reporting Services ist nicht in der Lage, zwei DataSets zu joinen. In meinen Artikeln zeige ich,
    • wie man in einer Kreuztabelle sowohl die Istwerte auf Monatsebene (erstes SQL-Statement) als auch die Planwerte in Summe (zweites SQL-Statement) kombinieren kann.
    • wie man sogar SQL-Abfragen mit Abfragen auf andere Datenbanktypen (in meinem Fall eine Analysis Services-Datenbank) kombinieren kann
  • Die Kreuztabelle (Matrix) von Reporting Services hat wenig Funktionalitäten, so dass selbst das Hinzufügen einer weiteren Spalte nicht möglich ist (lediglich Summen-Spalten werden unterstützt)

Diese Artikel sind unter http://www.sqlservercentral.com/articles/Reporting+Services/63415/ und http://www.sqlservercentral.com/articles/Linked+Server/63867/ nachzulesen.

Dazu benötigt man ein kostenloses Account bei SQLServerCentral.com