Olaf Helper

Microsoft Reporting Services (SSRS) kann bekanntlich auf diverse unterschiedlichste Arten von Datenquelle zugreifen, so auch auf XML Daten. Dabei muss es nicht zwingend eine fixe XML Datei sein, die auf einem Web Server gehostet ist. Es darf auch das dynamische Ergebnis eines SOAP oder Web API Request’s sein.

Freie Web APIs, die auch brauch- & verwertbare Daten liefern, gibt es einige. Für einen ersten Test habe ich mich im Bereich Wetter & deren Vorhersage umgesehen. Es gibt eine Google Weather API, die allerdings undokumentiert ist und es gibt auch keine Garantie zur Verfügbarkeit bzw. wie lange es diese API noch gibt. Die API liefert ein einfach gehaltenes XML Ergebnis, das man problemlos im Internet Explorer oder Tools wie XML Notepad anzeigen kann. Nur SSRS mag das Ergebnis nicht, es sein ein ungültiges Zeichen in den Daten enthalten; es ist das Zeichen mit Hex-Wert A0 = No-Break Space (geschütztes Leerzeichen) zwischen dem Luftfeuchtewert und dem Prozentzeichen.

Aber es gibt noch andere Wetter APIs und eine frei nutzbare (sofern man sich an die Bedingungen hält) ist die Yahoo Weather API. Die API liefert einen RSS Feed, was aber auch nichts anderes als XML Daten sind, ein Beispiel kann man bei Yahoo sehen. Die Basis URL lautet http://weather.yahooapis.com/forecastrss und es gibt nur zwei Parameter:

   U = Einheit (unit) für die Temperatur Wert
   W = WOEID, die Id der Stadt / Bereich, für den das Wetter abgefragt werden soll.

Die WOEID kann man z.B. über den GeoPlanet-Explorer heraus suchen. Beispiel für Hannover (w=657169) in Grad Celsius (=c):

http://weather.yahooapis.com/forecastrss?u=c&w=657169

Wie kann ich nun das Ergebnis für einen SSRS Report nutzen? Zunächst legt man eine Datenquelle an, da auf die API nur im dem Bericht zugegriffen wird, reicht eine eingebettete Datenquelle, eine freigegeben wird nicht nötig sein; als Verbindungstyp wählt man natürlich XML. In der Verbindungszeichenfolge gibt man nur die URL wie oben an, sonst nichts; die WOEID als dynamischer Parameter gestalte ich später noch. In den „Anmeldeinformationen“ muss „Aktuellen Windows-Benutzer verwenden“ ausgewählt sein; SSRS erwartet immer eine Anmeldeinformation, auch wenn sie nicht benötigt wird.

Als nächstes benötigt man ein Dataset für die anzuzeigenden Daten. Zum Abfragen von XML Daten werden XML Queries verwendet; das war für mich zunächst auch noch etwas neu, auch wenn ich in T-SQL schon ein wenig damit gearbeitet habe; etwas ins Thema eingearbeitet (siehe weiterführende Links unten) und etwas experimentiert, schon habe ich brauchbare Ergebnisse erhalten. Hier mal ein Beispiel, um die Forecast abzufragen, je API Request gibt es für die nächsten zwei Tage ein Ergebnis.

Die allereinfachste Abfrage sieht so aus: 

 <Query>
  <ElementPath IgnoreNamespaces="true">
<ElementNode>
<XMLName>
*
</XMLName>
</ElementNode>
</ElementPath>
</Query>

Durch die Angabe des Sternchens * versucht der Query Designer zu ermitteln, welche Elemente & Attribute selektierbar sind; da die XML Strukture des RSS Feeds etwas komplexer sind und ein paar Namespaces verwendet werden, funktioniert es nicht so gut. Bessere Ergebnisse erzielt man, wenn man explizit ElementPath & ElementNodes angibt, dadurch werden aber auch mehrere Dataset’s benötigt, um alle Daten zu erhalten. Hier mal ein Beispiel für die Forecast Nodes der XML Daten:  

<Query xmlns:yweather="http://xml.weather.yahoo.com/ns/rss/1.0"
       xmlns:geo="http://www.w3.org/2003/01/geo/wgs84_pos#">
  <ElementPath IgnoreNamespaces="true">
    <ElementNode>
      <XMLName>
        rss{}/channel{}/item{}/forecast
      </XMLName>
    </ElementNode>
  </ElementPath>
</Query>

Es werden zunächst die Namesspaces definiert und dann gezielt über ElementPath + ElementNode die gewünschten Elemente adressiert. Man erhält nun ein Dataset mit Feldern je Element, wobei das letzte Feld immer dem ElementNode-Namen entspricht und keine Daten enthält.

Ein Dataset haben wir nun und mit dem kann man nun eine Table auf dem Report anlegen, um die Daten anzuzeigen. Wie erwähnt müssen weitere Dataset’s je Element-Hierarchie angelegt werden, um weitere Daten aus dem XML Ergebnis zu selektieren. Das ist soweit kein Problem, nur etwas Fleißarbeit.

So, bisher war die URL fix für eine WOEID hinterlegt, wünschenswerte wäre aber, die Wettervorhersage auch für andere Orte abrufen zu können. Dazu lege ich einen Parameter namens WOEID mit einer festen Werteliste an; Default setze ich wieder auf Hannover.

Die Verbindungszeichenfolge muss dann von einem festen Werte auf einen „Ausdruck“ geändert werden, der sieht so aus:

= "http://weather.yahooapis.com/forecastrss?u=c&w=" & Parameters!WOEID.Value

also nichts anderes als die Basis URL mit der WOEID als variabler Teil. Nach der Änderung funktionieren aber die Abfragen im Query Designer nicht mehr, deswegen sollte man diese Anpassung erst ganz zu Letzt machen.

Das Ergebnis sieht so aus und kann wie üblich von meinem SkyDrive heruntergeladen werden, Anpassungen sind dieses Mal eigentlich nicht nötig, da die sonst variable Datenquelle hier fix ist; eben die Yahoo URL. 

Ein nettes Gimmick, dieser Wetterbericht. Es ist wirklich kein Must-Have, aber wenn man eh schon einen Reporting Service im Einsatz hat, kann man den Report doch auch mit veröffentlichen; frisst ja kaum Brot. 

Dann noch eine Subsription mit PDF Versand per Email und schon muss man nicht mehr die Nachrichten hören, um die aktuelle Wettervorhersage zu erhalten. J

Und ich für meinen Teil werden nun weiter Ausschau nach freien Web APIs halten, die man für SSRS Berichte nutzen könnte.

Weiterführende Links bei MSDN:

-       XML-Abfragesyntax zum Angeben von XML-Berichtsdaten
-       Syntax für Elementpfade zum Angeben von XML-Berichtsdaten

Wenn man im SSMS = „Microsoft SQL Server Management Studio“ im Query Editor ein T-SQL Statement ausführt, hat man im Standard immer 2 Kartenreiter als Resultat: „Ergebnisse“ und „Meldungen“. Bei einfachen Statements bekommt man unter „Meldungen“ so etwas wie „(5 Zeile(n) betroffen)“ zu sehen. Bei eher administrativen Statements wie DBCC Kommandos werden schon umfangreichere Meldungen ausgegeben und auch die Texte von benutzerdefinierten Meldungen aus PRINT Anweisungen erscheinen dort.
Durchaus informative Meldungen erhält man zum Beispiel bei BACKUP DATABASE Anweisungen; wenn man den Parameter STATS mit angibt, erhält man Fortschrittsmeldungen, wie viel Prozent des Vorganges bereits abgeschlossen sind.

Wenn man solche Kommandos per ADO.NET absetzt, wäre es praktisch, solche Meldungen empfangen zu können, um dem Benutzer ein Feedback zu geben; „Jetzt sind bereits 30% des Backup Vorganges abgeschlossen.“. Nur wie?

Das geht recht einfach über das SqlConnection.InfoMessage Ereignis; hier ein kleines Beispiel als einfache Konsolen Anwendung:

Imports System.Data.SqlClient

Module InfoMessage

    Sub Main()

        Console.WriteLine("Start backup")

        Using conn As New SqlConnection("Data Source=.\SQL105DEV;Initial Catalog=master;Integrated Security=True;")

             Dim sql = "BACKUP DATABASE [AdventureWorks2008R2] " & _
                       "TO DISK = N'AdvWorks2K8R2.bak' " & _
                          "WITH NOFORMAT, INIT,  NAME = N'Backup AdventureWorks2008R2' " & _
                             ", SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;"

            Using cmd As New SqlCommand(sql, conn)

                AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)

                conn.Open()
                cmd.ExecuteNonQuery()

            End Using

        End Using

        Console.WriteLine("Press any key to exit.")
        Console.ReadKey()

    End Sub

    Private Sub OnInfoMessage(sender As Object, args As SqlInfoMessageEventArgs)

        Console.WriteLine(args.Message)

        For Each err As SqlError In args.Errors
            Console.WriteLine(err.Message)
        Next

    End Sub

End Module

Ergebnis:

P.S.: Der im BACKUP Command verwendet Parameter COMPRESS war in den Versionen bis einschließlich MS SQL Server 2008 ausschließlich der Enterprise Edition vorbehalten; seit SQL Server 2008 R2 geht das auch bereits ab der Standard Edition; juhu.

Wie ich schon berichtete, gab es nach der Installation des Service Pack 1 für das Microsoft Visual Studio 2010 einige Probleme mit IntelliSense im SQL Server Management Studio (SSMS).

Seit kurzen gibt es dazu auch einen MS Support Eintrag:

FIX: The IntelliSense feature in SQL Server Management Studio for SQL Server 2008 R2 may stop working after you install Visual Studio 2010 Service Pack 1

und einen Fix dazu; das Problem kann mit dem Cumulative Update package 7 for SQL Server 2008 R2 behoben werden.