Ein typisches Verhalten in Reporting-Services ist es, dass Daten, die mit Parametern herausgefiltert werden, auch im Bericht nicht dargestellt werden. Beispiel: In diesem Chart werden die Umsätze für ein Jahr dargestellt:
Wenn nun nur noch die Monate Januar-April im Month-Parameter ausgewählt werden, erscheint folgendes Chart:
Die Daten werden per MDX aus einem Cube gelesen und im Dataset mit einem Subselect oder in der WHERE-Bedingung gefiltert:
SELECT
{[Measures].[Sales Amount]} ON 0,
NON EMPTY
{([Date].[Calendar].[Date].ALLMEMBERS )} ON 1
FROM
(SELECT ( STRTOSET(@DateCalendarYear, CONSTRAINED) ) ON 0
FROM
(SELECT ( STRTOSET(@DateMonthofYear, CONSTRAINED) ) ON 0
FROM [Adventure Works]))
Der Kunde wünscht nun, dass die übrigen Monate aber dennoch (mit dem Wert 0) angezeigt werden sollen. Hierfür gibt es zum einen die Möglichkeit, dieses bereits im MDX-Query zu lösen. Dies geschieht über einen String-Vergleich in einem IIF-Block. Leider ist diese Methode recht unperformant.
Ein besseres Ergebnis kann man mit dieser Methode erzielen, vorausgesetzt man verwendet SQL Server ab Version 2008 R2:
Im Dataset werden zunächst alle Daten für das Jahr geholt:
SELECT
{[Measures].[Sales Amount]} ON 0,
NON EMPTY
{([Date].[Calendar].[Date].ALLMEMBERS )} ON 1
FROM
(SELECT ( STRTOSET(@DateCalendarYear, CONSTRAINED) ) ON 0
FROM [Adventure Works])
Dann wird ein zweites Dataset „GueltigeMonate“ erstellt, in dem die Monate anhand des Parameters gefiltert werden:
WITH
MEMBER [Measures].[ParameterCaption] AS [Date].[Month of Year].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Date].[Month of Year].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Date].[Month of Year].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
{[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[Date].[Month of Year].ALLMEMBERS ON ROWS
FROM
(SELECT STRTOSET(@Monate,CONSTRAINED) ON 0
FROM [Adventure Works])
Nun müssen beide Datasets miteinander verbunden werden. Hierzu werden die Eigenschaften der Serie geöffnet:
Im Feld für die Daten (Value) wird nun der Lookup gegen das Dataset „GueltigeMonate“ hergestellt. Hierfür wird die Funktion „Lookup“ benötigt. Die Syntax sieht so aus:
=IIF(LEFT(Fields!Month.Value,LEN(Fields!Month.Value)-5)=
LOOKUP(LEFT(Fields!Month.Value,LEN(Fields!Month.Value)-5), Fields!ParameterCaption.Value,Fields!ParameterCaption.Value,”GueltigeMonate”),
Sum(Fields!Sales_Amount.Value),0)
Diese Syntax bewirkt folgendes:
Wenn der Monatsname im Haupt-Dataset gleich einem Monatsnamen ist, der im gefilterten Monats-Dataset vorhanden ist, so wird der Wert angezeigt, ansonsten wird die Zahl 0 angezeigt.
Wenn nun im Bericht die gleiche Selektion wie am Anfang beschreiben durchgeführt wird, ergibt sich das gewünschte Bild:
Die Anzeige-Geschwindigkeit konnte mit der Lookup-Methode gegenüber der Behandlung im Query halbiert werden. In einem der nächsten Blog-einträge werde ich die Lookup-Funktion dann nochmal näher beleuchten.
