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.

In Reporting Service lässt sich die Gruppierung einer Tabelle, einer Matrix oder eines Charts extrem dynamisch gestalten. Als Grundlage für dieses Beispiel dient die Datenbank „AdventureWorksDW2008R2“.

Das Ergebnis sollte in etwa so aussehen:

1. DataSet mit folgendem Query anlegen:

SELECT

PC.EnglishProductCategoryName AS Category

,PSC.EnglishProductSubcategoryName AS Subcategory

,P.EnglishProductName AS ProductName

,G.EnglishCountryRegionName AS Country

,G.StateProvinceName AS State

,FIS.SalesAmount

FROM dbo.FactInternetSales FIS

INNER JOIN dbo.DimProduct P

ON FIS.ProductKey = P.ProductKey

INNER JOIN dbo.DimProductSubcategory PSC

ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey

INNER JOIN dbo.DimProductCategory PC

ON PSC.ProductCategoryKey = PC.ProductCategoryKey

INNER JOIN dbo.DimCustomer C

ON FIS.CustomerKey = C.CustomerKey

INNER JOIN dbo.DimGeography G

ON C.GeographyKey = G.GeographyKey

2. Matrix aus Toolbar hinzufügen

3. Feld „Category“ auf RowGroups ziehen

4. Feld „Country“ auf ColumnGroups ziehen

5. Feld „SalesAmount“ in Data-Feld der Matrix ziehen

6. Feld „Subcategory“ auf RowGroups unter Gruppe „Category“ ziehen

7. RowGroup „Category“ umbenennen in „ParentRowGroup“

8. RowGroup „Subcategory“ umbenennen in „ChildRowGroup“

9. ColumnGroup „Country“ umbenennen in ColumnGroup

10. Die Textboxen der Matrix entsprechend benennen

11. Parameter „ParentGroupParam“ anlegen:

12. Zwei weitere Parameter anlegen:

13. Eigenschaften von „ParentGroupParam“ öffnen und unter „Available Values“ folende Einträge vornehmen:

14. Auf gleiche Weise werden die Einträge in „ChildGroupParam“ vorgenommen:

15. Im Parameter „ChildRowGroupParam“ wird eine Anzeige-Logik hinterlegt. Dabei wird dieser Parameter in Abhängigkeit zum Parameter „ParentRowGroupParam“ gebracht:

16. Eigenschaften von „ParentRowGroup“ öffnen und den Expression-Button neben „Group on:“ drücken und folgende Expression eingeben:

=Fields(Parameters!ParentRowGroupParam.Value).Value

17. In gleicher Weise werden auch die anderen Gruppen behandelt:

18. In den Textboxen werden die gleichen Expressions wie in der „Group on“ – Expression verwendet:

Der Bericht kann nun nach verschiedenen Kriterien gruppiert werden.

Alternierende Hintergrundfarben für Detailzeilen lassen sich mit folgendem Ausdruck in die Hintergrund-Eigenschaft der Zeile darstellen:

=IIF(RowNumber(NOTHING) Mod 2,"WhiteSmoke","Transparent")

Auch Gruppen lassen sich mit wechselnden Hintergrundfarben darstellen. Hierfür wird der Ausdruck

=IIF(RunningValue(<Gruppenfeld>,CountDistinct,Nothing) MOD 2,"Gainsboro","Silver")

in der Hintergrundeigenschaft des Gruppenfeldes gesetzt. <Gruppenfeld> wird dann durch die entsprechende Feldbezeichnung der Gruppe ersetzt.

Beispiel: Eigenschaft Hintergrund in Subcategory:

=IIF(RunningValue(Fields!Subcategory.Value,CountDistinct,Nothing) MOD 2,"Gainsboro","Silver")

Eigenschaft Hintergrund in Product: sh. oben

Ergebnis:

Matrix mit alternierendem Hintergrund für Subcategory (Gruppe) und Product (Details)

Bei der Anzeige im Sharepoint sind Kopf- und Fußzeile in einem Report oft unerwünscht, da z.B. das Logo bereits auf der Sharepointseite angezegit wird,. Beim Ausdruck sollen die Zeilen dann wieder angezeigt werden. Hier ein Workaround:

 1. Ein Report „Sub1“ wird ohne Header und Footer, incl. Parameter für die Ansicht im Sharepoint erstellt.

 2. Dem Report wird ein Textfeld „Druckansicht“ hinzugefügt:

 3.  Ein Parameter“@Print“ mit Datentyp Integer wird erstellt.

 4. In den Properties der Textbox wird unter „Visibility“ der Punkt „Show or hide based on an expression“ ausgewählt und folgende Expression eingegeben:

   =Parameters!Print.Value <> 0

 5. Nun wird ein neuer Report mit Header und Footer erstellt. Dieser „Master“-Report muss die gleichen Parameter wie der o.g. Report enthalten.

 6. Dem Master-Report wird eine Matrix hinzugefügt. Die Zeile für die Spaltenüberschriften sowie die Spalte für die Zeilenbeschriftung wird gelöscht. In das verbleibende

     Feld „Data“ wird nun ein Subreport eingefügt.

 7. Als Subreport wird „Sub1“ ausgewählt. Die Parameter werden an den Report übergeben. Der Parameter „@Print“ erhält den Wert 1.

 8. Der Master-Report wird gespeichert.

 9. In der Designansicht von „Sub1“ werden die Properties der Textbox „Druckansicht“ ausgewählt.

10. Unter „Action“ wird der Punkt „Go to Report“ aktiviert. Als Report wird der „Master“-Report  ausgewählt. Die Parameter werden hinzugefügt.

Der Report „Sub1“ kann nun gestartet werden. Bei Klick auf „Druckansicht“ wird der Report mit Header und Footer angezeigt.

Sharepoint-Ansicht

Druckansicht

SSRS 2008; Data basis: AdventureWorks 2008  

The results are to be represented in chart as bars. The data are to be summarized dynamically in groups. The dataset is used in other tables and diagrams and should not be changed.  

Solution: In the characteristics of the Category Group Group Expressions can be inserted. The Expression accesses report parameters, which can be filled by other datasets. Example:  

=Fields!Reseller_Order_Count.Value<= Parameters!StartBucket1.Value  

In this example all data are summarized, whose value is smaller or equal to the parameter. Also the Expression of the announcement is accordingly dynamically filled:  

=IIF(Fields!Reseller_Order_Count.Value <= Parameters!StartBucket1.Value,”1-” & Parameters!StartBucket1.Value,  

IIF(Fields!Reseller_Order_Count.Value>Parameters!StartBucket1.Value AND Fields!Reseller_Order_Count.Value<=Parameters!StartBucket2.Value, 

 IIF(Fields!Reseller_Order_Count.Value>Parameters!StartBucket2.Value AND Fields!Reseller_Order_Count.Value<=Parameters!

StartBucket3.Value,Parameters!StartBucket2.Value+1 & “-” & Parameters!StartBucket3.Value,”>” & Parameters!StartBucket3.Value)))  

For each group a parameter is needed. As many as desired groups can be provided. If the Expression should not supply value, the group is not indicated, charts behaves also here dynamically. There should be values, which are larger than the highest value of a parameter, these are indicated automatically in their own group. In the following example data with 3 groups/parameters are indicated, the fourth group contain the values, which are larger than the indicated highest parameter 10: