Microsoft SQL Server

Tworzenie kluczowych wskaźników wydajności KPI w raportach Microsoft SQL Server Reporting Services 2008 Udostępnij na: Facebook

Opublikowano: 9 października 2009
Autor: Radosław Łebkowski

Zawartość strony
Sposób 1. – kolorowanie komórek  Sposób 1. – kolorowanie komórek
Sposób 2. – wykorzystanie obiektów Gauge  Sposób 2. – wykorzystanie obiektów Gauge
Sposób 3. – wykorzystanie symboli graficznych  Sposób 3. – wykorzystanie symboli graficznych
Dodatkowe informacje:  Dodatkowe informacje:

 

Usługi raportowe w SQL Server 2008 wprowadzają wiele nowych funkcji związanych z tworzeniem i dystrybucją raportów oraz wizualizacją danych.

Obok nowych obiektów do wizualizacji danych (tabliks, wykresy) zostały dodane również obiekty Gauge. Pozwalają one użytkownikowi na szybkie i proste umieszczenie w raportach informacji o wskaźnikach KPI (Key Performance Indicator). Wskaźniki KPI umożliwiają prezentację kluczowych miar biznesowych (np. aktualnej wartości sprzedaży w firmie) w odniesieniu do wartości celu (w rozważanym przykładzie celem będzie plan sprzedaży).

Zaletą stosowania tego typu obiektów jest przede wszystkim to, że użytkownik raportu bardzo szybko jest w stanie zidentyfikować wartości:

  • które są zgodne z oczekiwaniem (zielone),
  • które znacząco odbiegają od oczekiwań (czerwone),
  • które są bliskie realizacji celu (żółte).

Prezentację wskaźników KPI z wykorzystaniem SQL Server Reporting Services 2008 można zrealizować na trzy przykładowe sposoby:

  1. kolorując zawartość komórek tabeli, w zależności od wartości liczbowej prezentowanej w komórce,
  2. wykorzystując obiekt Gauge prezentujący wartość aktualną i wartość celu w postaci wskaźnika graficznego,
  3. zastępując prezentowaną wartość w komórce wybranym symbolem graficznym reprezentującym stan wskaźnika KPI.

Poniżej zostaną przedstawione przykłady implementacji tych trzech rozwiązań. Aby można przetestować je we własnym środowisku, należy spełnić następujące kryteria:

  • mieć zainstalowaną na komputerze aplikację Report Builder 2.0;
  • mieć skonfigurowany dostęp do bazy danych AdventureWorks2008DW zainstalowanej na serwerze Microsoft SQL Server 2008. Bazę tę można pobrać ze strony Codeplex;
  • mieć skonfigurowaną usługę SQL Server Reporting Services 2008 z uprawnieniami do zapisu i odczytu raportów na serwerze;
  • posiadać adres URL do serwera Reporting Services oraz skonfigurowaną aplikację Report Builder 2.0.

Sposób 1. – kolorowanie komórek

W przykładzie wykorzystamy bazę danych AdventureWorks2008DW jako źródło danych do raportu.

Poniższe zapytanie pobiera dane o wielkości sprzedaży kluczowych produktów oraz kosztach ich wytworzenia w poszczególnych regionach. Naszym celem jest stworzenie raportu, który odróżni produkty rentowne od mniej rentownych. Pole Ratio z zapytania SQL (będące stosunkiem wartości sprzedaży do kosztów) będzie wyznacznikiem poziomu rentowności produktu.

  • W przypadku Ratio < 1.6 wymagana jest szybka reakcja w celu poprawy poziomu rentowności.
  • W przypadku 1.6 <= Ratio < 2 produkt powinien być monitorowany.
  • W przypadku Ratio >= 2 produkt jest rentowny i nie wymaga interwencji.

Wykorzystane w raporcie zapytanie SQL ma postać następującą:

SELECT TOP 100 *,

       Sale / Cost Ratio

FROM   (SELECT   p.Modelname Product,

                 t.salesterritoryregion Region,

                 Sum(salesamount)        Sale,

                 Sum(f.totalproductcost) Cost

        FROM     dbo.factinternetsales f

                 INNER JOIN dbo.dimsalesterritory t

                   ON t.salesterritorykey = f.salesterritorykey

                 INNER JOIN dbo.dimproduct p

                   ON p.productkey = f.productkey

        WHERE    f.salesterritorykey IN (4,1)

                 AND f.productkey < 650

        GROUP BY p.Modelname, 

                 t.salesterritoryregion, p.ProductLine) x

ORDER BY Sale DESC
  1. Uruchamiamy aplikację Report Builder 2.0. Następnie definiujemy źródło danych do raportu. W tym celu z panelu Report Data wybieramy opcję New, a następnie Data Source... (zobacz rysunek 1.). W oknie Data Source Properties wybieramy współużytkowane źródło danych lub definiujemy nowe źródło, podając parametry połączeniowe do serwera, na którym zlokalizowana jest baza danych AdventureWorks2008DW. Po skonfigurowaniu źródła danych na liście obiektów pojawia się źródło danych Data Source1.

    Tworzenie nowego źródła danych

    Rysunek 1: Tworzenie nowego źródła danych.

    Definiowanie źródła danych

    Rysunek 2: Definiowanie źródła danych

  2. Kolejnym krokiem jest zdefiniowanie zbioru danych Data Set. W tym celu wybieramy kursorem utworzone źródło danych i z menu kontekstowego wybieramy opcję Add Dataset... W oknie Dataset Properties wklejamy powyższe zapytanie (zobacz rysunek 3.). Po zatwierdzeniu w oknie obiektów pojawia się nowy zbiór danych z listą pól pobranych z zapytania.

    Definiowanie zestawu danych Dataset

    Rysunek 3: Definiowanie zestawu danych Dataset.

  3. Następnie przystępujemy do utworzenia tabeli opartej na zdefiniowanym zbiorze danych. Do tego celu wykorzystamy kreator dostępny w aplikacji. Na zakładce Insert rozwijamy menu Matrix i wybieramy pozycję Matrix Wizard... W pierwszym kroku kreatora wybieramy zbiór danych, który wykorzystamy w naszej tabeli (zobacz rysunek 4.). Zaznaczamy Dataset1 i przechodzimy dalej przyciskiem Next.

    Wybór zestawu danych Dataset w kreatorze tabeli

    Rysunek 4: Wybór zestawu danych Dataset w kreatorze tabeli.

  4. W kroku Arrange fields rozmieszczamy dostępne pola w tabeli. Umieszczamy pole Region w sekcji Column groups, pole Product w sekcji Row groups, natomiast pola Sale, Cost oraz Ratio w sekcji Values (zgodnie z rysunkiem 5.). Przechodzimy dalej przyciskiem Next.

    Rozmieszczenie pól w tabliksie

    Rysunek 5: Rozmieszczenie pól w tabliksie.

  5. W kroku Choose the layout odznaczamy pozycję Show subtotals and grand totals. Przechodzimy dalej przyciskiem Next. W kroku Choose a style wybieramy szablon graficzny, np. Ocean, i zatwierdzamy przyciskiem Finish.

  6. Dla poprawienia efektu wizualnego tabliksu można dostosować szerokość kolumn oraz zmienić formatowanie kolumn Sale oraz Cost na walutowe, natomiast Ratio – na procentowe. Zmianę formatowania można wykonać, zaznaczając kursorem wybraną komórkę, a następnie z menu kontekstowego wybierając opcję Text Box Properties.... W oknie Text Box Properties wybieramy zakładkę Number (zobacz rysunek 6.). Dla kolumn Sale i Cost wybieramy format Currency, natomiast dla Ratio – opcję Percentage.

    Zmiana formatowania komórek tabliksu

    Rysunek 6: Zmiana formatowania komórek tabliksu.

  7. Aby podejrzeć otrzymany raport, wybieramy opcję Run na pasku menu. Przykładowy raport przedstawiony jest na rysunku 7.

    Przykładowy raport jeszcze bez formatowania warunkowego

    Rysunek 7: Przykładowy raport jeszcze bez formatowania warunkowego.

  8. Aby dodać formatowanie warunkowe do raportu, przechodzimy ponownie w tryb edycji (wybierając opcję Design na pasku menu). Zaznaczamy kursorem komórkę kolumny Sale (na rysunku 8.), następnie wybieramy opcję Text Box Properties.

    Właściwości komórki tabliksu

    Rysunek 8: Właściwości komórki tabliksu.

  9. W zakładce Fill wybieramy pozycję Fill color fx (rysunek 9.). W oknie Expression wstawiamy formułę dynamicznie przypisującą kolor tła komórki, w zależności od bieżącej wartości Ratio w wierszu tabliksu. Formuła ma postać następującą:

     =IIF(Fields!Ratio.Value >= 2, "LightGreen", (IIF(Fields!Ratio.Value < 1.6, "Tomato", "Yellow"))) 

    Warunkowe kolorowanie komórki

    Rysunek 9: Warunkowe kolorowanie komórki.

  10. Po zatwierdzeniu zmian przechodzimy ponownie do podglądu raportu (rysunek 10.). Jak można zauważyć, w tym momencie kolor komórek tabeli zależny jest od przedziału, do którego należy bieżąca wartość Ratio. Zielony kolor oznacza najlepszy przedział, a czerwony – najsłabszy (najmniej rentowne produkty).

    Gotowy raport z warunkowym formatowaniem komórek

    Rysunek 10: Gotowy raport z warunkowym formatowaniem komórek.

 Do początku strony Do początku strony

Sposób 2. – wykorzystanie obiektów Gauge

  1. Do prezentacji wskaźników wydajności możemy wykorzystać również obiekty Gauge wprowadzone w wersji SQL Server 2008. Standardowo mamy do dyspozycji zestaw wskaźników graficznych podzielonych na dwie grupy: Linear i Radial (zobacz rysunek 11.). Obiekt Gauge możemy wykorzystać zarówno jako osobny element raportu, jak również jako element wypełniający komórkę tabliksu.

    Typy obiektów Gauge dostępne standardowo w Reporting Services 2008

    Rysunek 11: Typy obiektów Gauge dostępne standardowo w Reporting Services 2008.

  2. Do przygotowania raportu opartego na wskaźnikach Gauge wykorzystamy tablix z poprzedniego punktu (bez formuły formatowania warunkowego z punktu 9.).
    Aby dodać obiekt Gauge będący częścią tabliksu, musimy najpierw przygotować kolumnę, w której umieścimy obiekt Gauge. W tym celu Report Builderze w widoku projektanta klikamy prawym przyciskiem myszy na komórce Sale i z menu podręcznego wybieramy opcję Insert Column -> Inside Group – Left (rysunek 12.).
    Zostanie dodana nowa kolumna po lewej stronie wartości Sale.

    Dodanie nowej kolumny na wskaźnik Gauge

    Rysunek 12: Dodanie nowej kolumny na wskaźnik Gauge.

  3. Aby dodać nowy obiekt Gauge, należy z menu głównego na zakładce Insert wybrać pozycję Gauge, a następnie wskazać miejsce, w którym obiekt będzie wstawiony. W naszym przypadku klikamy na białą komórkę w nowo wstawionej kolumnie. Z listy typów obiektów wybieramy typ Horizontal (pierwszy w grupie Linear).

  4. W kolejnym kroku łączymy obiekt Gauge z polem danych. W tym celu klikamy ponownie na obiekcie Gauge, co spowoduje pojawienie się obszaru LinearPoiner1, tak jak na rysunku 13. Przeciągamy pole Ratio ze zbioru danych do obszaru LinearPointer1 – rysunek 14.

    Powiązanie obiektu Gauge ze źródłem danych

    Rysunek 13: Powiązanie obiektu Gauge ze źródłem danych.

    Pole Ratio powiązane ze wskaźnikiem Gauge

    Rysunek 14: Pole Ratio powiązane ze wskaźnikiem Gauge.

  5. W następnym kroku zmieniamy skalę wskaźnika, ustalając zakres adekwatny do danych w polu Ratio. W tym celu klikamy kursorem na wskaźniku i zaznaczamy jego skalę. Następnie z menu podręcznego wybieramy opcję Scale Properties (rysunek 15.). W oknie Linear Scale Properties w zakładce General w polu Maximum wstawiamy wartość 2.5 i zatwierdzamy przyciskiem OK.

    Ustawienia parametrów skali obiektu Gauge

    Rysunek 15: Ustawienia parametrów skali obiektu Gauge.

  6. Chcąc zmienić domyślne ustawienia wyglądu wskaźnika, klikamy ponownie kursorem na wskaźniku i z menu kontekstowego wybieramy opcję
    Gauge -> Pointer Properties... (rysunek 16.). W oknie Linear Pointer Properties zmieniamy wartość pola Pointer type na Bar. W polu Width wstawiamy wartość 30.
    Następnie w zakładce Pointer Fill zmieniamy ustawienia Fill Style na Solid. Przy atrybucie Color wybieramy przycisk fx.
    W polu edycji formuł wstawiamy poniższą formułę zmieniającą kolor wskaźnika, w zależności od wartości Ratio. Zatwierdzamy zmiany przyciskiem OK.

     =IIF(Fields!Ratio.Value >= 2, "LightGreen", (IIF(Fields!Ratio.Value < 1.6, "Tomato", "Yellow"))) 

    Ustawienia wskaźnika obiektu Gauge

    Rysunek 16: Ustawienia wskaźnika obiektu Gauge.

  7. Aby zwiększyć ilość dostępnego miejsca na raporcie, usuwamy kolumnę Ratio i dostosowujemy szerokość pozostałych kolumn. Końcowy efekt zastosowania wskaźników Gauge w tabliksie przedstawiony jest na rysunku 17.

    Tabliks z obiektami Gauge

    Rysunek 17: Tabliks z obiektami Gauge.

 Do początku strony Do początku strony

Sposób 3. – wykorzystanie symboli graficznych

  1. Do prezentacji wskaźników wydajności możemy wykorzystać również zewnętrzne symbole graficzne w postaci np. plików .png.

    W naszym przykładzie użyjemy symboli graficznych również jako elementów tabliksu. Korzystając z raportu z poprzedniego punktu, usuwamy z niego obiekt Gauge.

  2. Następnie w panelu Report Data wybieramy opcję New -> Image...
    Wczytujemy trzy pliki graficzne zawierające symbole wskaźników dla poszczególnych zakresów wartości. W naszym przykładzie są to trzy pliki graficzne: green.png, yellow.png, red.png (rysunek 18.). W przykładzie po wczytaniu pliki graficzne są osadzane w definicji raportu RDL (Embedded). Możliwe jest również wykorzystanie plików opublikowanych na serwerze Reporting Services (External) lub przechowywanych w bazie danych SQL Server (Database).

    Wczytane pliki graficzne symboli KPI

    Rysunek 18: Wczytane pliki graficzne symboli KPI.

  3. Następnie wstawiamy nowy obiekt Image do raportu w miejsce usuniętego obiektu Gauge (wybieramy opcję Image z zakładki Insert menu głównego). W oknie Image Properties w polu Use this image klikamy przycisk fx i wstawiamy poniższą formułę dynamicznie przypisującą symbol graficzny, w zależności od wartości pola Ratio. Zatwierdzamy zmiany przyciskiem OK.

     =IIF(Fields!Ratio.Value >= 2, "green", (IIF(Fields!Ratio.Value < 1.6, "red", "yellow"))) 
  4. Gotowy raport przedstawiony jest na rysunku 19.

    Raport wykorzystujący symbole wskaźników z zewnętrznych plików graficznych

    Rysunek 19: Raport wykorzystujący symbole wskaźników z zewnętrznych plików graficznych.

 Do początku strony Do początku strony

Dodatkowe informacje:


Radosław Łebkowski Radosław Łebkowski (MCTS, MCITP DBA, MCITP DB Dev, MCITP BI Dev)
Technology Solution Professional – Data Platform
Absolwent Wydziału Elektroniki i Technik Informacyjnych Politechniki Warszawskiej. Od początku w pracy zawodowej zajmował się administracją i optymalizacją systemów baz danych opartych na platformie Microsoft SQL Server, m.in. u jednego z polskich operatorów telekomunikacyjnych. Od kilku lat zajmuje się wdrażaniem hurtowni danych oraz systemów Business Intelligence, a jego pasją jest zgłębianie tajników rozwiązań z obszaru Data Mining oraz Corporate Performance Management.
Doświadczenie zdobywał m.in. w firmie Bonair S.A., gdzie brał udział w wielu wdrożeniach dużych systemów BI i CPM dla firm oraz instytucji administracji publicznej. Obecnie pracuje w firmie Microsoft na stanowisku Technology Solution Professional w dziale Enterprise and Parter Group (EPG), gdzie jest odpowiedzialny za wsparcie klientów sektora Enterprise w obszarze rozwiązań platformy bazodanowej i Business Intelligence.
Posiada certyfikaty MCTS i MCITP z obszaru SQL Server 2005 i 2008 (MCITP Business Intelligence Developer, MCITP Database Administrator, MCITP Database Developer) oraz PerformancePoint Server 2007. Jest również aktywnym członkiem Polish SQL Server User Group (PLSSUG) w Warszawie oraz portalu WSS.pl.
 Do początku strony Do początku strony

Microsoft SQL Server