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.

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.

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

Der erste Aufruf eines Reports vom Reporting Service dauert immer recht lange.
Um dieses Verhalten dem Benutzer gegenüber auszublenden, kann man einen sogenannte Warmup durchführen.
Es gibt verschiedenste Wege ein solches Warmup durchzuführen, ich werde hier einen Weg mittels des Tools WGET beschreiben. 
GNU WGET ist ein kostenloses Komandozeilenprogramm zum Herunterladen von Dateien aus dem Internet. >Download<

Wir nutzen Wget zum beleben des Reporting Service, in dem wir die Startseite das Reportmanagers aufrufen, besser gesagt wir laden die Startseite mittels WEGT herunter.
Folgender Befehl muss ausgeführt werden.

wget  –http-user=BEUTZERNAME –http-password=PASSWORT http://SERVERNAME/Reports/ –delete-after

“http://SERVERNAME/Reports“
Dieser Abschnitt gibt die URL das Report Managers an und muss jeweils noch angepasst werden.

“ –http-user=BENUTZERNAME“
Dieser Abschnitt gibt den Benutzer an mit dem sich WEGT am Report Manager anmeldet.
Bei Domain Benutzer muss DOMAIN\BENUTZERNAME angeben werden.

“ –http-password=PASSWORT“
Dieser Abschnitt gibt das Passwort des Benutzers  an.
Da der Benutzername und das Passwort hier im Klartexte gespeichert werden, sollte hier ein Benutzerverwendet werden, der nur die Startseite des Reporting Service aufrufen kann und sonst keine Reports sieht.
Des Weiteren sollte man darauf achten das Passwort des Benutzers nicht abläuft, weil dann wget keinen Zugriff auf den Reportmanager mehr hat und das Script angepasst werden muss.

“ –delete-after“
Dieser Abschnitt gibt an das die heruntergeladenen Dateien abschließend wieder gelöscht werden, damit das lokale Verzeichnis nicht überläuft.

Wenn der Reporting Service über HTTPS erreichbar ist und es sich um ein privates SSL Zertifikat handelt, kann es sein das Wget das Zertifikat nicht überprüfen kann und abbricht.
In diesem Fall muss dann noch der Parameter “ –no-check-certificate” hinzugefügt werden.

Der gesamte Wget Befehl sollte in einer Batchdatei gespeichert und einem geplanten Task eingebunden werden.
Dieser Task sollte dann so geplant werden, dass er z.B. jeden Morgen um 7Uhr läuft.

Achtung vor den Befehlen kommen immer 2 Minuszeichen!! Leider wird das nicht immer korrekt dargestellt.

Recycle Time

In der rsreportserver.config wird die Recycle Time definiert. Diese gibt an nach welcher Laufzeit der Webdienst des RS zurückgesetzt wird.
Standard ist hier 720, die Angabe ist in Minuten.

<RecycleTime>720</RecycleTime>

Dieses Zeitangabe sollten man bei der Planung des Task beachten und gegebenen Falls anpassen.

SQL Server 2008 R2

As a response on my last post I was asked if this is also possible with postal code regions (PLZ). It’s more difficult to find free, usable map data for this purpose. But with the help of a popular search engine I came up with the following link: http://arnulf.us/PLZ

Here you can get the ESRI shape file for the German postal code regions. You can load this data in exactly the same way as shown in my last post.

This is how the map looks after importing it into a SQL Server table (displayed in SQL Spatial Query Visualizer):

As you can see, the map is very detailed. It would be perfect, if we could add some layer of aggregation. For the German postal code, it would be a good idea to aggregate them using the first two digits, so that for example, the aggregated level of 40 includes all the postal codes like 40221 (the postal code of my company’s headquarters).

In order to join the shapes of the postal code areas we can use the SQL Server 2008 spatial functions. Therefore I loaded the data provided by the link above into a table post_pl. The following T-SQL script then creates a new table with the name post_pl2 containing the shape data for the aggregated areas:

IF EXISTS(SELECT name FROM sys.tables WHERE name = ‘post_pl2′) begin drop table post_pl2 end

create table post_pl2 (
PLZ2 nvarchar(2),
geom geometry
)

DECLARE @pl_group nvarchar(2)
DECLARE @t geometry = ‘GEOMETRYCOLLECTION EMPTY’;

DECLARE my_cursor CURSOR FOR
SELECT distinct left(PLZ99,2) FROM post_pl

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @PL_Group

WHILE @@FETCH_STATUS = 0
BEGIN
    set @t=’GEOMETRYCOLLECTION EMPTY’
    select @t= @t.STUnion(geom.MakeValid()) from dbo.post_pl 
       where PLZ99 like @pl_group+’%’

    insert into post_pl2(PLZ2,geom) values(@pl_group, @t.BufferWithTolerance(0.001,0.1,0))

    FETCH NEXT FROM my_cursor
    INTO @PL_Group
END

CLOSE my_cursor
DEALLOCATE my_cursor

The query takes some time to execute (about a minute on my virtual development environment), so be patient. I’m using BufferWithTolerance here to smooth the results, because the postal code areas do not fit perfectly. The resulting spatial data is now joined together at postal code region level (2 digits):

From here you can get a more detailed view by filtering on a single postal code region (in my example, I’m using the region 40xxx):

Instead of the T-SQL code from above you could also use the .Net stored-procedure from Craig Dunn’s post. This makes it very easy to build the aggregate because it provides a “normal” aggregation function UnionAggregate that is capable of aggregating the data type geometry. If your spatial data is stored in a field of the type geography instead, you will definitely want to take a look at the SQL Server Spatial Tools at Codeplex. Here you can find the corresponding GeographyUnionAggregate for the geography data type.

You could also combine the spatial information from this post with the shapes from my last post to allow a drilldown like Country, German state (Bundesland) and then postal code region and postal code.

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:

  

SQL Server 2005 | SQL Server 2008

While units are a built-in functionality in some OLAP databases, we need to take care of them in SSAS on our own. Usually I model units as a dimension so that the facts are linked to the unit they belong to. However, units usually must not be aggregated (like adding up meters with liters), and therefore we would set the IsAggregatable property to false. The user first has to choose a unit before the result is displayed. In some cases this may not be that clear. For (local) currency for example the values are aggregatable as long as the currency is consistent which may or may not depend of the currency unit. As long as the selection (no matter of how the filtering was done) leads to a single currency it would be possible to display the result in local currency. Whenever there is more than one currency involved, the result cannot be shown.

Taking the usual approach with the non-aggregatable unit dimension you may find a situation like below:

For this example we have stores aggregated to some kind of store group with sales. As the currency is a non-aggregatable dimension you don’t see totals for the rows (just for the columns). Assuming we set our default member for the currency unit dimension to some currency not included (or to unknown like I did), you don’t see any value at all, if the currency unit is not included in the query:

So everything’s fine with this approach. But usually we don’t analyze by currency and if we simply put it on a filter, we might miss values of the other currencies like in the following screenshot of the same sample cube (the value for ‘Other’ gives no hint that there might be other sales here too):

So the idea could be to tell the cube to display the currency values as long as the displayed cell contains only one currency unit. For other cells we can only display a warning. The following screenshot shows the final result:

Please note, that although we don’t have the currency unit included in this pivot table on either the axis or the filter, the cube knows that certain cells only result in EUR values and therefore can be aggregated while other cells consist of more than one local currency and therefore cannot be aggregated.

To explain how this can be done, let’s first look at the model of our sample cube:

The currency unit is linked to the fact table using its primary key UnitID. For the cube we include this key is a measure to the cube… hey, this sounds weird, why should we use the surrogate key as a measure?? Well, we even use it twice, once aggregated by the Min function and one aggregated by the Max function.

Now, for any cell, where “Minimum CurrencyID” equals “Maximum CurrencyID” we are sure that we only have one currency unit in this cell. This can be used in a calculated member. I set the visibility of the original measure Amount to hidden and add a calculated member like this:

CREATE MEMBER CURRENTCUBE.[Measures].Amount
AS iif([Measures].[Minimum CurrencyID]=[Measures].[Maximum CurrencyID],[Measures].[AmountLocal],NULL),
FORMAT_STRING = iif([Measures].[Minimum CurrencyID]=[Measures].[Maximum CurrencyID],
strtomember("[Currency].[Unit].&["+CStr([Measures].[Minimum CurrencyID])+".]").properties("Format String"),
""),
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Fact Sale’  ;

As you can see we also take the format string (e.g. “$”#,##0.00) from the currency table to display each currency properly.

For the above screenshot I modified this code a little bit and returned the value 0 instead of NULL. I also used the text “multiple units” as the default format string for this value 0. I prefer the NULL value though as it is “more” accurate.

As long as you’re using Excel 2007 compliant format strings (as describe in one of my previous posts), everything should display properly in Excel too as shown in the following screenshot:

Other clients can also leverage the formatting defined in our cube. For example in Reporting Services you could use the above formats if you refer to the FORMATTED_VALUE property instead of the VALUE property. So instead of

=Fields!Amount.Value

we would use:

=Fields!Amount.FormattedValue

Here’s a simple report based on the sample cube: