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.

Das Servicepack 1 für den SQL Server 2008 R2 steht bei Microsoft zum Download bereit.
http://www.microsoft.com/download/en/details.aspx?id=26727

Das Servicepack hat die Versionsnummer 10.50.2500.0.

Zu den Neuerungen gehören:

• Dynamic Management Views for increased supportability
• ForceSeek for improved querying performance
• Data-tier Application Component Framework (DAC Fx) for improved database upgrade
• Disk space control for PowerPivot

Weitere Info befinden sich auf der Download Seite von Microsoft.

GUIDs (Globally Unique Identifier) in Hyperlinks zu verwenden ist im Rahmen von Reporting Services nicht ohne weiteres möglich.

Es wird versucht folgenden URL Aufbau https://crmServer/OrganizationName/activities/task/edit.aspx?id={3c046c31-f921-df11-8947-000c29d3e997}, mit Hilfe von einem Parameter und einem GUID Feld herzustellen.

Die einfachste Art der Verknüpfung von einem Parameter mit URL Informationen und einem GUID Feld

Parameters!URLAuftrag.value & Fields!AuftragID.value

führt zu einem Fehler. Die GUID mit Hilfe von Cstr() in einen String zu konvertieren führt auch nicht zum gewünschten Ergebnis.

Parameters!URLAuftrag.value & Cstr(Fields!AuftragID.value)

Die Ctype () Funktion ist hier die gesuchte Funktion. Mit dieser Funktion ist es möglich das GUID Feld in einen String zu konvertieren und mit dem Parameter, welches die URL Informationen beinhaltet, zu verknüpfen.

Parameters!URLAuftrag.value & Ctype(Fields!AuftragID.value, guid).tostring

Der folgende Javascript Aufruf führt dazu, dass der Hyperlink in einem gesonderten Fenster geöffnet wird.

=”javascript:void(window.open(‘” & (Konvertierungsfunktion) & “}’,'_blank’))”

Es kann Situationen geben da macht es Sinn einen Report aus einer anderen Anwendung heraus zu starten und dem Report die Parameter mit zu übergeben bzw. einen Report direkt über die URL aufzurufen und zu rendern.
Man sollte jedoch beachten das es für einen solchen Aufruf verschieden Parameterarten gibt.

Zum einen gibt es Parameter welche der Bericht benötigt um die richtigen Werte anzuzeigen, zum anderen gibt es Parameter die den Berichtserver “steuern”.

Eine URL könnte Beispielhaft so aussehen:
http://SERVERNAME/Reportserver?/Verzeichnis/Reportname&PARAM1=TEST&PARAM2=HALLO&
rs:Command=Render

Die beiden Parameter PARAM1 und PARAM2 werden für Selektion im Bericht benötigt, der Parameter rs:Command=Render gibt den Befehl zum Rendern des Berichtes an den Berichtserver.

Der Reportname wird ohne Dateiendung angegeben.
Es sollten unbedingt alle Parameter an den Report übergeben werden, auch wenn diese gerade nicht benötigt werden, z.B. &PARAM1=&PARAM2=Hallo…
In diesem Beispiel würde der Parameter PARAM1 ohne Inhalt übergeben.

Mitt &rs: können Parameter zur Steuerung des Berichtservers übergeben werden &rs:Command=Render  löst das Rendern des Berichts aus.

Es gibt noch weitere Parameter die mit übergeben werden können um den Berichtserver zusteuern, einer der Wichtigsten ist Format dieser gibt das Renderingformat an.

Mit &rs:Format= können folgende Renderingformate angeben werden:
HTML3.2 , HTML4.0 , MHTML , IMAGE , EXCEL , WORD , CSV , PDF , XML und NULL

Diese URL z.B. gibt den Bericht im PDF Format aus.
http://SERVERNAME/Reportserver?/Verzeichnis/Reportname&PARAM1=TEST&PARAM2=HALLO&
rs:Format=PDF&rs:Command=Render

Wenn kein Format angegeben wird, so wird der Bericht als HTML erstellt.

Eine Beschreibung alle Parameter für den URL-Zugriff finden man in der TechNet Libary von Microsoft

In einem Projekt mit sehr sensiblen Daten und einem externen Zugriff auf die Reports, kam die Anforderung auf den Session Timeout anzupassen um die Datensicherheit zu erhöhen.

Der Session Timeout im Reporting Server ist nach der Installation 600 Sec = 10 Min.

Diesen kann man jedoch ändern.

Als erstes erstellt man die Datei sessionTimeout.rss mit folgendem Inhalt

Public Sub Main()
    Dim props() as [Property]
    props = new [Property] () { new [Property]()}

    props(0).Name = "SessionTimeout"
    props(0).Value = timeout

    rs.SetSystemProperties(props)
End Sub

Als nächstes führt man folgenden Befehl aus dem Verzeichnis aus, in dem die sessionTimeout.rss gespeichert wurde.

Die Angabe des Timeoutwertes ist in Sekunden.

RS.exe ist das Scripting Tool für den Reporting Service nähere Infos HIER

Wenn man mit dem Firefox auf einen Webserver zugreifen möchte, welcher mit Windows Integrierter Anmeldung arbeitet, wird man zur Eingabe des Benutzernamen und Passwort aufgefordert.

Sobald mit Reporting Service und Datenquellen mit Windows Integrierter Authentifizierung gearbeitet werden soll, sind diese Einstellungen notwendig.

Man kann den Firefox so konfigurieren, das er den Angemeldeten Domain Benutzer an die Webseite übergibt.

1. In der Adresszeile des Firefox muss about:config eingegeben werden.

2. Es erscheint als erstes eine Warnung in dieser wird man auf mögliche Risiken hingewiesen

3. Es müssen zwei Parameter angepasst werden.

• network.negotiate-auth.delegation-uris
• network.negotiate-auth.trustes-uris

Durch einen Doppelklick auf den jeweiligen Parameter öffnet sich das Eingabefenster.
Hier muss die URL des Webservers eingetragen werden.

Durch Komma getrennt können auch mehrere Webserver eingetragen werden.

Der Eintrag sollte bei beiden Parametern identisch sein.

Um eine sogenannte ClickOnce Anwendungen im Firefox zu starten wird ein Addon benötigt.

Eine ClickOnce Anwendungen ist z.B. der Report Builder (2.0 und 3.0), dabei kann man die Anwendung aus der Webseite heraus starten, ohne diese vorher installiert zu haben.

Das nach bisherigen Erkenntnissen beste Addon wurde von Microsoft bereitgestellt.

https://addons.mozilla.org/de/firefox/addon/9449

Addon Installieren

Zum installieren des Addons öffnet man den Firefox und ruft dort die URL des Addon auf.
https://addons.mozilla.org/de/firefox/addon/9449

Als nächstes muss die Schaltfläche Continue to Download betätigt werden.

Nun noch die Lizenzbedingungen bestätigen.

und die Installation starten.

Abschließend muss der Firefox neugestartet werden

Als Systemvoraussetzung setzt das Addon .NET Framework 3.5 SP1 voraus.
Leider lässt es sich aber auch Installieren wenn die Voraussetzung nicht erfüllt ist, es erscheint auch keine Fehlermeldung.

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)

SQL Server 2008 R2

In my last post I mentioned creating a map by yourself as one possible use case for the Visio-To-SQL-Server geo data export. In this post I will give you some details how this can be done.

First, we need some kind of a map. For this post I’m using a map of Europe which I found as a PNG file at Wikipedia: http://upload.wikimedia.org/wikipedia/commons/6/64/Europe_capitals_map_de.png

The image is then inserted into a blank Visio file. You should make it as large as possible (use landscape orientation). Then, we’re ready to begin. We’re using the freeform tool to follow the coast lines. In my case I’m creating a map for UK and Ireland. Note that you can interrupt the process at any time and continue using the freeform tool later. Also, don’t bother too much with the details by now.

Finally, the curve needs to be closed. Visio shows this by actually filling the area using the default fill color:

If you missed the start point, it’s not a problem as you can correct every single point of your curve and also add new control points using the pencil tool. Before doing so, you should add your recently created shape to a new Visio layer and set the transparency of the layer to an appropriate value (e.g. 20%) so you can see both, the sheet and the map image. Now you should work at a higher zoom level to detail out the border lines as shown below. Note that you can CTRL+click any control point to see and modify the curve control.

Your final result may look somewhat like this

Please note that we didn’t care about the country borders in this step (as you can see from Ireland) but just about the coastlines.

Next we’re going to model the border between UK and Ireland. Therefore we need to create some kind of tool shape in order to cut out the corresponding part. To make our work easier, we’re going to hide our GEO layer, so we’re only seeing the map now. We then draw the shape of the border line. Please note that the shape is extended to the sea side as we don’t want to risk that some parts of the underlying shape are not cut out.

The next steps are a little bit confusing if you perform them for the first time. Here is what we are going to do:

1. Make the hidden GEO layer visible again.
2. We can now see all of our drawn shapes including the tool shape. As the tool shape was create at a later time it is in front of our original shapes.
3. Mark the basic shape for Ireland first and then the tool shape while pressing CTRL. It is important to do it this way.
4. Copy both shapes to the clipboard for later use
5. Do a shape substract operation. Depending on your version of Excel it’s a menu entry at shape>operation>subtract or maybe you have to consult the help file
6. This results in Ireland having the topmost part cut out
7. Paste the two shapes from step 4 back into the sheet
8. Do a shape intersect operation
9. Move the resulting shape back into position (use the SHIFT key together with the arrow keys to position the shape properly)

The result should look somewhat like this:

Next we’re going to group all the shapes for UK (don’t forget the islands) together into one shape. We set the text for this shape to “United Kingdom”. Ireland consists of a single shape in my example so we just have to name it accordingly.

These shapes are already what we need for our lines layer in the SSRS map. The lines layer will only be visible as border lines and will be on top of our map. To make this happen, we mark both shapes and create a copy (copy+paste), then move the copy to the same location as the original shapes. While the copied shapes are still marked we add them to a new layer called LINES. We can then hide the LINES layer as it would only be confusing for our next steps.

Now, by using exactly the same tools as above, we’re going to create the sales regions North, Middle and South. If you have problems with the intersection and the subtract tools, you may need to un-group your shapes before using one of the combine-tools. In my example, the sales regions do not match the country borders. I therefore first combine the two shapes on the island of Ireland together to one shape. Next I’m cutting the whole map into three pieces. In the next screenshot I colored the pieces to make it more clear:

All shapes with the same color are grouped together into a single shape. We name the shape according to our sales regions: North, Middle and South. Now we’re almost finished. We now move all the shapes for our sales regions to a new layer called INTERACTIVE and remove all layers except INTERACTIVE and LINES. We can now set both layers to visible again. Maybe some shapes from the INTERACTIVE layer have to be pushed into the background but then the final result should look like this:

Note that the sales regions do not match the border in Ireland.

Using the same macro as described in my last blog post I’m exporting the Visio data into a SQL table called UK_Map. In this case we end up with two layers (no layer 0 as we don’t have any background polygons as the park house in my last post):

Layer 1 (interactive layer)	Layer 2 (line overlay)

In order to have the three different sales region colored by their sales we have to provide some sales data. In this case, I’m going the easy way and provide only the following three lines:

So this is how the final report looks like. Note that the country borders do not match the sales region but are still visible (lines layer). 

Of course we could also add some major cities or more details but for today, I think we did enough.