Tvorba reportů v MS Reporting Services 2008 – 1. díl

Technologie SQL Server Reporting Services (SSRS) umožňuje vytváření přehledných datových sestav obohacených o nejrůznější grafické prvky, které je možné zakomponovat do stávajících aplikací či exportovat do externích souborů. V tomto seriálu se postupně seznámíme s tvorbou reportů v tomto nástroji a různými specifiky jejich vývoje.

Co jsou to reporty?

Při tvorbě informačního systému je samozřejmostí umožnit uživatelům jistý, byť omezený, přístup k datům ze systémem používané databáze. Klasická výstupní sestava v podobě prostého výpisu výsledku databázového dotazu v textové podobě nemusí být ale vždy dostačující (zejména s ohledem na přehlednost výstupu atd.).

Zde se dostávají ke slovu různé nástroje pro tvorbu databázových reportů, které umožňují vrácené výsledky přehledně formátovat, obohacovat je o prvky jako např. grafy, ukazatele, mapy apod. Jde o produkty řazené mezi tzv. business intelligence nástroje – tedy nástroje pro získávání znalostí z dat (resp. z informací), které dále slouží jako podpora pro rozhodování.

Postupně budeme ukazovat proces tvorby reportu pomocí nástroje SQL Server Reporting Services (dále jen SSRS), který je doplňkem MS SQL Serveru (zde se budeme bavit o verzi SQL Server 2008 R2). V tomto díle si ukážeme tvorbu jednoduchého reportu s filtrem.

Další díly seriálu se budou zaměřovat na rozšiřování reportu o grafické prvky a složitější uspořádané struktury, možnosti exportu do různých formátů, nasazení reportu atp.

Pro demonstraci možností nástroje SSRS budete v tomto článku a jeho dalších pokračováních potřebovat databázi AdventureWorks2008 a volně použitelnou verzi SQL Serveru a SSRS. Doporučuji také se důkladně seznámit se schématem této databáze. Nutná je i alespoň základní znalost jazyka SQL (resp. T-SQL) a výrokové logiky.

Reporting Services v SQL Serveru 2008

Služba Reporting Services byla pro SQL Server (tehdejší verzi 2000) k dispozici jako addon od roku 2004. Od verze SQL Server 2005 je spolu s Analysis Services a Integration Services součástí platformy Business Intelligence Development Studio (dále jen BIDS).

Pro tvorbu reportů máme k dispozici dva nástroje: Report Builder a Report Designer. Zatímco Report Designer je klasickým vývojářským nástrojem, Report Builder připomíná „officovský“ nástroj a je určen spíše pro běžné uživatele. My se budeme zabývat tvorbou reportů v Report Designeru.

Hlavními částmi rozhraní Report Designeru, které nás nyní budou zajímat, jsou panely Report Data (slouží k přípravě datových množin pro report) a Design (již z názvu je zřejmé, že slouží k návrhu vlastního designu reportu).

Panel Report Data obsahuje následující složky:

• Parametry (Parameters) – parametry použité k filtraci kmenových dat reportu
• Datové zdroje (Data Sources) – obsahuje připojení (connection string) k jednotlivým databázím, která jsou potřebná pro tvorbu a použití reportu
• Datové sady (Datasets) – seznam datových sad (ty mohou fungovat jako „náplně“ pro parametry, samotná zdrojová data reportu atd.)

Panel Design sestává ze dvou záložek. Záložka Design obsahuje editor designu reportu. Záložka Preview pak umožňuje zobrazit náhled reportu se všemi jeho funkcemi (tzn. funkčními parametry výběru).

Konstrukce reportu pomocí nástroje Report Server Project Wizard

Nyní si ukážeme jednodušší postup tvorby reportu, a to pomocí průvodce Project Wizard. Tento nástroj automaticky vytvoří základní tabulku reportu podle vstupního SQL dotazu a poté nám pomocí prostředí Report Designer umožní report dále upravovat. V příštích dílech budeme report tvořit od úplných základů.

Prvním krokem při tvorbě našeho reportu bude předpříprava databázových dotazů, které budou tvořit „tělo“ reportu a plnit výběrový filtr konkrétními hodnotami. Nejprve spustíme MS SQL Server Management Studio. Připojíme se k databázi AdventureWorks2008 a otevřeme nový panel pro psaní dotazů (New Query).

Cílem dotazu bude vybrat z databáze jména zákazníků, jejich tituly, kontaktní e-maily a oblast pobytu. Příslušný dotaz bude vypadat takto:

SELECT
per.FirstName AS 'Křestní jméno'
,per.LastName AS 'Příjmení'
,ea.EmailAddress AS 'E-mail'
,adr.AddressLine1 AS 'Adresa'
,adr.City AS 'Město'
,sp.Name AS 'Oblast'
,cr.Name AS 'Země'
,st.Name AS 'Region'
,st.[Group] AS 'Uskupení'
FROM
Person.Person AS per
JOIN Person.BusinessEntity AS be ON per.BusinessEntityID = be.BusinessEntityID
JOIN Person.BusinessEntityAddress AS bea ON be.BusinessEntityID = bea.BusinessEntityID
JOIN Person.Address AS adr on bea.AddressID = adr.AddressID
JOIN Person.StateProvince AS sp ON adr.StateProvinceID = sp.StateProvinceID
JOIN Person.CountryRegion AS cr ON sp.CountryRegionCode = cr.CountryRegionCode
JOIN Person.EmailAddress AS ea ON per.BusinessEntityID = ea.BusinessEntityID
JOIN Sales.Customer AS cus ON cus.PersonID = per.BusinessEntityID
JOIN Sales.SalesTerritory AS st ON cus.TerritoryID = st.TerritoryID

Tyto zákazníky bude možno filtrovat na základě jejich příslušnosti ke geografické oblasti.

Dotaz pro výběr oblastí uložených v databázi:

SELECT
sat.TerritoryID
,sat.Name
FROM
Sales.SalesTerritory AS sat

Nyní přistoupíme k implementaci reportu:

1. Spustíme BIDS.
2. V záložce File vybereme položku New a následně Project.
3. V dialogovém okně vybereme projekt Report Server Project Wizard a podle libosti změníme název projektu.
4. Nástroj Wizard nás vyzve k uvedení datového zdroje, kliknutím na tlačítko Edit vyvoláme dialog pro zadání připojovacího řetězce – vybereme server, na kterém se nachází zdrojová databáze a následně i její konkrétní název (v tomto případě AdventureWorks2008), poté ověříme spojení s databází (tlačítko Test Connection).
5. Po přechodu k dalšímu bodu tvorby reportu jsme vyzváni k zadání SQL dotazu, který bude tvořit základ reportu (Query string) – do příslušného textového pole překopírujeme první předpřipravený dotaz.
6. V další fázi je potřeba zvolit základní formu reportu čili zdali bude ve formátu klasické tabulky (tabular), nebo matice (matrix) – v tomto případě zvolíme formu tabulky (formát matice je používán u reportů, které se zobrazují jako kontingenční tabulky).
7. Následující dialog slouží k určení struktury repotu – jednotlivé sloupce určené základním SQL dotazem můžeme přiřadit do tří různých skupin: Page, Group a Details, tyto určují hierarchickou strukturu reportu; náš první report bude mít strukturu velice jednoduchou, a proto všechny sloupce přesuneme do skupiny Details (tím zajistíme, že se všechny záznamy vypíší nestrukturovaně).
8. Vybereme pro report grafický vzor (ve screenshotech použitých v tomto článku je použit vzor Corporate).
9. Zadáme umístění Report Serveru (výchozí nastavení je nasměrováno na localhost) a jeho verzi (zde je zvolen SQL Server 2008).
10. Zvolíme si název reportu.

Tímto jsme ukončili tvorbu základu reportu a můžeme přistoupit k tvorbě jeho parametrů a designovým úpravám.

Pro vytvoření parametru nejprve potřebujeme vytvořit příslušnou datovou sadu, jejíž prvky budou odpovídat jednotlivým položkám parametru.

1. Pravým tlačítkem myši klikneme na složku Datasets na panelu Report Data a zvolíme „Add Dataset…“ (mimochodem tímto okamžikem už by se v této složce měla nacházet množina příslušející základní tabulce reportu vytvořené pomocí nástroje Wizard – výchozí jméno je DataSet1).
2. V dialogu, který se nám otevře, přepíšeme výchozí hodnotu jména parametru (položka Name) na „Parametr“. Dále musíme určit, zda chceme použít sdílenou množinu, nebo zda půjde o množinu příslušející právě tomuto jednomu reportu – zvolíme druhou možnost.
3. Jako datový zdroj zvolíme DataSource1 – nastavení tohoto datového zdroje již bylo provedeno v průběhu činnosti nástroje Report Server Project Wizard.
4. Do pole Query zkopírujeme předem připravený dotaz (viz výše) a nastavení potvrdíme tlačítkem OK.

Nyní můžeme přikročit k přidání samotného parametru:

1. Pravým tlačítkem myši klikneme na složku Parameters a zvolíme Add Parameter.
2. V dialogovém oknu pro tvorbu parametrů máme možnost určit jméno parametru (položka Name), pod kterým bude vystupovat v rámci tohoto reportu, dále popisek (Prompt), jenž bude parametru příslušet v grafickém rozhraní, a datový typ. Hodnotu položky Name nastavte na „Territory“, popisek zvolte podle své vůle a datový typ ponechejte na hodnotě „Text“. Ze tří následujících checkboxů zaškrtněte pouze poslední – Allow multiple values. Tím dosáhneme toho, že uživatel bude moci zvolit několik hodnot parametru najednou (např. výběr zákazníku pouze z Německa a Francie). Nastavení viditelnosti parametru ponecháme na výchozím Visible.

3. Nyní nastavíme hodnoty, které bude tento parametr dávat k dispozici (záložka Available Values). Určíme, že hodnoty budou definovány databázovým dotazem (Get values from query). Jako zdrojovou množinu určíme Dataset „Parametr“. Jako pole hodnot (Value Field) zvolíme sloupec TerritoryID, jako pole popisků (Label field) zvolíme sloupec Name – účely těchto hodnot a důvod právě takovéhoto nastavení si vysvětlíme příště.

4. Nastavení potvrdíme tlačítkem OK.
5. Nyní je potřeba propojit parametr se základním dotazem reportu. Ve složce Datasets rozklikněte dataset s tělem reportu. Na konec databázového dotazu (položka Query) přidejte následující řádek, jenže určuje, že ve výsledku dotazu budou vybírány pouze takové řádky, jejichž hodnota TerritoryID se nachází v parametru s názvem Territory.:

WHERE sat.TerritoryID IN (@Territory)

6. Dále přidáme řádek, který způsobí vzestupné seřazení výsledků dotazu podle názvu Regionu:

ORDER BY st.Name ASC

V této chvíli je již report prakticky hotov. Podle libosti ještě můžete upravit velikosti písma, šířky sloupců, barvy pozadí…

Pro náhled reportu klikněte na panelu Design na záložku Preview – měl by se objevit rolovací list s výběrem jednotlivých regionů. Po zvolení libovolného počtu regionů klikněte na tlačítko View Report. Výsledkem by měl být seznam zákazníků žijících ve zvolených regionech.

Tvorbu reportu jsme tak zdárně ukončili. V dalším díle jej rozšíříme o některé nové sloupce a přidáme složitější systém parametrů.