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:
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:
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:
(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:
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.