Olaf Helper

Umrechnungskurse für Währungen werden in vielen Fachbereichen und Anwendungsfällen benötigt. Der Börsenbroker benötigt sie im Sekundentakt für die Aktienbewertung, beim Verkauf von Import-Artikel mit Tagespreisen wird der aktuelle Tageskurs benötigt und die Buchhaltung verwendet für die Fremdwährung OP Bewertung einen Monats-Durchschnittskurs.

Die EZB Europäische Zentralbank veröffentlich die Tages-Umrechnungskurse seit Anfang 1999 als XML Datei im Internet. Man kann eine Datei mit den Kursen des letzten Banktages (TARGET Tag), der letzten 90 Tagen und die gesamte Aufstellung erhalten.

Diese Xml Daten bieten sich natürlich dazu an, sie einzulesen und per T-SQL weiter zu verarbeiten, um so eine Referenztabelle mit Umrechnungskursen zu pflegen. Etwas gekürzt sieht der Inhalt der Xml Datei so aus:

<?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" 
                 xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
     <gesmes:subject>Reference rates</gesmes:subject>
     <gesmes:Sender>
          <gesmes:name>European Central Bank</gesmes:name>
     </gesmes:Sender>
     <Cube>
          <Cube time='2011-04-18'>
                <Cube currency='USD' rate='1.4275'/>
                <Cube currency='JPY' rate='118.25'/>
                <Cube currency='BGN' rate='1.9558'/>
      <Cube currency='CZK' rate='24.188'/>
          </Cube>
     </Cube>
</gesmes:Envelope>

Grundsätzlich ist das Verarbeiten kein Problem, allerdings muss man, um die Xml Daten überhaupt lesen zu können, zunächst die verwendete Namespace mittels WITH XmlNameSpaces deklarieren, hier gesmes:Envelope. Will man zugleich auch noch CTE (Common Table Expressions) verwenden, muss man nur berücksichtigen, das XmlNameSpaces an erste Stelle der WITH Anweisung gesetzt wird, die CTE’s dürfen erst darauf folgen.

Hat man das Namespace erst einmal deklariert, erfolgt das weitere Auslesen der Daten wie bei allen anderen Xml Daten auch. Das ganz Statement für eine Tabelle, das Einlesen und Umwandeln sieht dann so aus:

  -- ECB Währungskurse

-- Tabelle für die Tageskurse anlegen, sofern noch nicht vorhanden.

IF OBJECT_ID('[dbo].[EcbDailyExchangeRates]', 'U ') IS NULL
    CREATE TABLE [dbo].[EcbDailyExchangeRates]
        ( [ExcDate] date NOT NULL
         ,[Currency] char(3) NOT NULL
         ,[Rate] decimal(19, 6) NOT NULL
         ,CONSTRAINT [PK_EcbDailyExchangeRates] PRIMARY KEY CLUSTERED
              ( [ExcDate] ASC
               ,[Currency] ASC
              ) WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF

                      ,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
        );
GO

-- Sourcen der Ecb Xml Dateien:
-- Letzter Tag:     http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml
-- Letzten 90 Tage: http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml
-- Komplett:        http://www.ecb.int/stats/eurofxref/eurofxref-hist.xml

-- Laden der Xml Datei in eine Variable

DECLARE @ecb XML;
SET @ecb = (SELECT CONVERT(xml, EcbSrc.BulkColumn) AS XmlRates
            FROM OPENRowsET( BULK N'D:\eurofxref-hist.xml'
                            ,SINGLE_BLOB) AS EcbSrc);
 

-- Selektion der Kurse und Einfügen von fehlenden Werten                           
;WITH XMLNAMESPACES
     ( 'http://www.gesmes.org/xml/2002-08-01' as gesmes
      ,DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref')

INSERT INTO [dbo].[EcbDailyExchangeRates]
    ( [ExcDate], [Currency], [Rate])
SELECT Cubes.Rows.value('@time', 'date') AS [ExcDate]
      ,Nodes.Rows.value('@Currency', 'char(3)') AS [Currency]
      ,Nodes.Rows.value('@Rate', 'decimal(19, 6)') AS [Rate]
FROM  @ecb.nodes('/gesmes:Envelope/Cube/*') AS Cubes(Rows)
      CROSS APPLY
      Cubes.Rows.nodes('Cube') as Nodes(Rows)
      LEFT JOIN
      EcbDailyExchangeRates AS Dst
          ON Dst.ExcDate = Cubes.Rows.value('@time', 'date')
             AND Dst.Currency = Nodes.Rows.value('@Currency', 'char(3)')
WHERE Dst.ExcDate IS NULL
      AND NOT Nodes.Rows.value('@Currency', 'char(3)') IS NULL;

Man muss ja nicht immer alles Nachmachen oder –programmieren, besonders nicht Features aus der Rubrik „Noch Bunter – Noch Greller“.

Die Funktion zur „Bedingten Formatierung“, die in MS Excel 2007 hinzugekommen ist, ist aber durchaus gut geeignet, um Werte und deren Größenordnung optisch hervor zu heben. Im MS Reporting Services kann man dieses ebenfalls abbilden, das setzt aber etwas Customer Code voraus.

Zum Testen nehme ich zunächst einmal eine einfache CTE mit fixen Werten für Umsatzzahlen her, die prozentualen Anteile am Jahresumsatz werden dann in einer weiteren CTE errechnet. Diese Prozentwerte will ich dann im Weiteren eben durch eine Hintergrundfarbe hervorheben.

;WITH 
  Umsatz AS 
(    SELECT 'Kunde 1' As Customer, 15000 AS Ums2009, 17000 AS Ums2010
                                    UNION ALL
     SELECT 'Kunde 2', 23000, 22000  UNION ALL
     SELECT 'Kunde 3', 10000, 12000  UNION ALL
     SELECT 'Kunde 4', 39000, 32000  UNION ALL
     SELECT 'Kunde 5',  1000,  3000)

, Summe AS
(   SELECT SUM(Ums2009) AS Total2009
          ,SUM(Ums2010) AS Total2010
    FROM Umsatz)

, Statistik AS
(   SELECT Customer
          ,Ums2009
          ,Ums2010
          ,100 * Ums2009 / Total2009 AS Percentage2009
          ,100 * Ums2010 / Total2010 AS Percentage2010
          ,100 * (Ums2010 - Ums2009) / Ums2009 AS Raise 
    FROM Umsatz, Summe)

SELECT *
FROM Statistik;

Nun brauch ich noch etwas Customer Code. Das Property „BackgroundColor“ erwartet entweder einen Farbnamen wie „Black“ für Schwarz oder einen RGB Werte als Hexadezimal Wert als String wie #FF0000 für einen Rotwert. Letzteres nutze ich, um einen Farbwert passend zum Prozentwert zu errechnen. Der Code, um einen Rotwert analog in der Helligkeit zum Prozentwert zu errechnen, sieht so aus:

' Liefert einen Rot-Farbwert als Hexadezimal-Wert (String) zu den übergebenen Prozentwerten

Public Function PercentageRed(ByVal percentage As Decimal) As String

    ' Wert muss zwischen 0 und 100 sein; sonst zur Sicherheit auf Default Werte setzen

    If percentage < 0 Then percentage = 0
    If percentage > 100 Then percentage = 100

    Dim value As Integer

    value = CInt(255.0 * (100.0 - percentage) / 100.0)

    ' #FF = Rot als Basis
    Return "#FF" & value.ToString("X2") & value.ToString("X2")

End Function

Also wirklich Karo-Einfach, den maximalen Byte-Wert von 255 ins Verhältnis gesetzt zum Prozentwert. Damit hohe Prozentwerte dunkel dargestellt werden, wird vom Prozentwert das Inverse gebildet.

Im Report setzte ich dann als Beispiel für die Textbox „Percentage2010“ das Property „BackgroundColor“ auf „Expression“ und füge nur noch das folgende Expression ein:

=Code.PercentageRed(Fields!Percentage2010.Value)

Das Ergebnis sieht dann so aus; für das bisschen Customer Code doch ganz gut, oder?

Kürzlich ist das Microsoft Visual Studio 2010 Service Pack 1 erschienen. Grundsätzlich neige ich dazu, Service Packs auch zu installieren, neben Benefits wie Stabilität, Performance und Sicherheit bieten sie manchmal auch neue Features.

In diesem Fall ist aber etwas Vorsicht geboten, denn den ersten Rückmeldungen in MSDN Foren wie

SSMS 2008 R2 IntelliSense Stopped Working after VS 2010 SP1

zufolge hat das Service Pack eher negative Auswirkungen auf das SSMS = Sql Server Management Studio; IntelliSense funktioniert anschließend nicht mehr.

Das CU6 für Sql Server 2008 R2 soll das Problem beheben. Aber das kann es ja nun eigentlich nicht sein, das man sich mit einem Service Pack sich Probleme einholt, die man mit einem weiteren CU erst wieder beheben muss.