Arno Cebulla

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.