SQL Report Builder - raporty RDL i wydruki dokumentów w SSRS

Report Builder i SSRS - podział ról

Raporty w ekosystemie SQL Server powstają w dwóch miejscach. Microsoft Report Builder jest aplikacją desktopową dla Windows, w której projektuje się zapytanie do bazy oraz układ strony raportu. SQL Server Reporting Services (SSRS) jest usługą serwerową. Przechowuje opublikowane raporty w bazie ReportServer i renderuje wynik do wybranego formatu na żądanie użytkownika.

Raport z Report Buildera pozostaje zwykłym plikiem RDL, dopóki nie trafi na serwer. Po publikacji dostaje adres w portalu internetowym SSRS i własne uprawnienia. Granice obu narzędzi szerzej opisuje artykuł SQL Report Builder a SSRS.

Instalacja i wersje

Report Builder instaluje się jako osobny program pobrany z Microsoft Download Center albo rozesłany przez Microsoft Endpoint Configuration Manager. Aktualna wersja Microsoft Report Builder współpracuje z serwerami SSRS 2016 i nowszymi oraz z Power BI Report Server. Starsze wdrożenia korzystają z Report Builder 3.0, który obsługiwał serwery od SQL Server 2008 R2 do 2014. Opis narzędzia zawiera dokumentacja Microsoft Report Builder w Microsoft Learn, a przegląd zastosowań artykuł SQL Report Builder i Reporting Services.

Report Builder a Report Designer w Visual Studio

Drugim narzędziem do plików RDL jest Report Designer w Visual Studio, instalowany jako rozszerzenie Microsoft Reporting Services Projects. Programista trzyma w nim raporty w jednym rozwiązaniu z kodem aplikacji i publikuje je razem z wdrożeniem nowej wersji.

Report Builder lepiej sprawdza się u analityka, który zmienia układ zestawienia bez środowiska programistycznego. Oba narzędzia zapisują ten sam format RDL, więc plik przechodzi między nimi, o ile zgadza się wersja schematu.

Plik RDL - definicja raportu w XML

Report Definition Language jest dialektem XML opisanym schematem Microsoftu. Plik .rdl zawiera kompletny opis raportu, więc da się go porównać z poprzednią wersją i trzymać w systemie kontroli wersji. Główne sekcje pliku:

• DataSources - odwołanie do współdzielonego źródła danych albo osadzony ciąg połączenia.
• DataSets - zapytania T-SQL z listą pól, które raport może wyświetlić.
• ReportParameters - parametry z typem danych i listą dostępnych wartości.
• ReportSections - obszar treści raportu i ustawienia strony, w tym wymiary papieru.

Definicja pojedynczego parametru zajmuje kilka linii XML. Poniższy fragment opisuje ukryty parametr z identyfikatorem dokumentu, używany przy wydruku z aplikacji:

<ReportParameter Name="DokumentId">
  <DataType>Integer</DataType>
  <Prompt>Dokument</Prompt>
  <Hidden>true</Hidden>
</ReportParameter>

Reguła: źródłem prawdy jest plik RDL w repozytorium, a nie kopia na serwerze. Zmiana wprowadzona tylko w portalu ginie przy kolejnej publikacji.

Każdy plik RDL deklaruje przestrzeń nazw schematu. Report Builder 3.0 zapisuje schemat 2010/01, a serwery SSRS 2016 i nowsze przyjmują schemat 2016/01. Otwarcie starego pliku w nowym Report Builder podnosi wersję schematu przy zapisie, co blokuje późniejszą publikację na serwerze 2008 R2.

Dla systemu Studio WMS.net SoftwareStudio zaleca trzymanie kopii definicji raportów w zabezpieczonym folderze na dysku NTFS, aby w razie potrzeby można je było ponownie opublikować. Kontekst tej zasady opisuje tekst Magazyn Framework. Pełną specyfikację formatu podaje strona Report Definition Language w Microsoft Learn.

Źródła danych i zestawy danych T-SQL

Źródło danych opisuje połączenie z serwerem oraz sposób uwierzytelnienia. Współdzielone źródło publikuje się raz na serwerze raportów, a korzystają z niego wszystkie raporty w folderze. Migracja bazy na nowy serwer wymaga wtedy edycji jednego obiektu zamiast kilkudziesięciu plików RDL.

W środowisku z grupą dostępności Always On ciąg połączenia źródła raportowego może zawierać ApplicationIntent=ReadOnly. Listener kieruje wtedy zapytania raportów do repliki tylko do odczytu, o ile skonfigurowano routing tylko do odczytu. Ciężkie zestawienia nie konkurują wtedy o zasoby z zapisami terminali.

Raport uruchamiany przez subskrypcję nie ma interaktywnego użytkownika, więc poświadczenia muszą być zapisane w źródle danych. Zapisane konto powinno mieć tylko prawo odczytu do widoków raportowych.

Zapytanie zestawu danych

Zestaw danych zawiera zapytanie T-SQL i listę zwracanych pól. Każdy parametr zapytania poprzedzony znakiem @ Report Builder zamienia automatycznie w parametr raportu. Przykładowy zestaw danych wydruku dokumentu WZ wygląda tak (nazwy tabel są poglądowe):

SELECT d.NrDokumentu,
       d.DataWystawienia,
       k.Nazwa     AS Odbiorca,
       p.Lp,
       t.Indeks,
       t.Nazwa     AS Towar,
       p.Ilosc,
       t.Jm,
       p.Partia
FROM dbo.Dokument AS d
JOIN dbo.Kontrahent AS k      ON k.KontrahentId = d.KontrahentId
JOIN dbo.DokumentPozycja AS p ON p.DokumentId = d.DokumentId
JOIN dbo.Towar AS t           ON t.TowarId = p.TowarId
WHERE d.DokumentId = @DokumentId
ORDER BY p.Lp;

Złączenia i reguły biznesowe lepiej przenieść do widoku albo procedury składowanej w bazie. Raport odwołuje się wtedy do obiektu, który administrator może zoptymalizować i przetestować bez otwierania pliku RDL. Zasady projektowania takiej bazy opisuje artykuł system magazynowy na SQL Server, a przygotowanie zapytań z pomocą modelu językowego tekst asystent do raportów SQL. Budowę zestawów danych szczegółowo omawia dokumentacja zestawy danych raportu w SSRS.

Parametry raportu i listy wartości

Parametr z listą dostępnych wartości pobiera ją z osobnego zestawu danych, na przykład z listy magazynów. Parametr wielowartościowy przekazuje do zapytania kilka wartości naraz. Dla źródła SQL Server serwer raportów rozwija zapis IN (@Magazyn) do listy wybranych identyfikatorów:

SELECT t.Indeks,
       t.Nazwa,
       SUM(s.Ilosc) AS Stan
FROM dbo.StanMagazynowy AS s
JOIN dbo.Towar AS t ON t.TowarId = s.TowarId
WHERE s.MagazynId IN (@Magazyn)
  AND s.DataStanu = @NaDzien
GROUP BY t.Indeks, t.Nazwa
HAVING SUM(s.Ilosc) > 0
ORDER BY t.Indeks;

Rozwinięcie działa tylko w zapytaniu tekstowym. Procedura składowana dostaje wartości jako jeden ciąg, zbudowany w raporcie wyrażeniem =Join(Parameters!Magazyn.Value, ","). Po stronie bazy rozbija się go funkcją STRING_SPLIT, dostępną od SQL Server 2016.

Parametry kaskadowe

Parametr kaskadowy zależy od wartości innego parametru. Po wyborze magazynu lista stref pokazuje tylko strefy tego magazynu, bo zestaw danych listy stref ma w warunku WHERE parametr @Magazyn. Kolejność parametrów w pliku RDL musi odpowiadać tej zależności, inaczej raport zgłosi błąd przy otwieraniu. Zestaw danych listy wartości powinien czytać słownik, na przykład tabelę magazynów, a nie wykonywać SELECT DISTINCT na tabeli ruchów, bo wtedy samo otwarcie okna parametrów skanuje całą historię. Konfigurację opisuje dokumentacja parametry raportu w Report Builder.

Układ strony i wyrażenia w raportach paginowanych

Raport paginowany ma stałe wymiary strony, na przykład A4 w pionie z marginesami 1 cm. Treść układa się w obszarze tablix, który łączy cechy tabeli i macierzy. Grupa po numerze dokumentu z podziałem strony między wystąpieniami drukuje każdy dokument od nowej kartki. Serię dokumentów WZ z jednego dnia można więc wydrukować jednym wywołaniem raportu.

Wartości wyliczane zapisuje się wyrażeniami w składni zbliżonej do Visual Basic. Stopka z numeracją stron, warunkowy kolor ilości i format daty wyglądają tak:

="Strona " & Globals!PageNumber & " z " & Globals!TotalPages
=IIF(Fields!Ilosc.Value < 0, "Red", "Black")
=Format(Fields!DataWystawienia.Value, "dd.MM.yyyy")

Pola Globals!PageNumber i Globals!TotalPages działają tylko w nagłówku i stopce strony. W treści raportu numerację grup liczy się funkcją agregującą na zestawie danych. Składnię opisuje dokumentacja wyrażenia w Report Builder i SSRS.

Formaty renderowania

Ten sam plik RDL serwer renderuje do kilku formatów. Wybór zależy od tego, co odbiorca zrobi z wynikiem. Użytkownik wskazuje format w menu eksportu portalu, a aplikacja przekazuje go parametrem rs:Format w adresie raportu. Raporty na ekrany telefonów omawia osobno artykuł SSRS - raporty mobilne i kwerendy SQL Server.

• PDF - wydruk dokumentu z zachowaniem układu strony oraz archiwizacja.
• Excel (XLSX) - zestawienia do dalszej analizy w arkuszu kalkulacyjnym.
• Word (DOCX) - dokumenty do ręcznej edycji przed wysłaniem.
• CSV - płaski eksport danych do importu w innym systemie.

Wydruki dokumentów magazynowych w systemach SoftwareStudio

Program WMS.net korzysta z SQL Server Reporting Services, a definicje raportów przechowuje w formacie RDL. Własne wydruki i zestawienia tworzy się w Report Builder i publikuje w systemie bez modyfikacji kodu aplikacji. Architekturę programu opisuje tekst program WMS.net.

Typowy zestaw obejmuje dokumenty magazynowe, na przykład WZ, oraz zestawienia stanów na lokalizacjach. Wydruk WZ musi zawierać partie i numery seryjne z tej samej transakcji, w której zatwierdzono wydanie. Obieg tego dokumentu opisuje artykuł ewidencja wydań z magazynu - dokumenty WZ i ZWZ.

Dokument WZ drukuje się często w dwóch egzemplarzach z nadrukiem „Oryginał” albo „Kopia”. Zamiast uruchamiać raport dwa razy, zestaw danych mnoży wiersze przez listę egzemplarzy, a grupa po numerze egzemplarza z podziałem strony drukuje każdy egzemplarz od nowej kartki:

SELECT e.Nr, e.Egzemplarz, w.*
FROM (VALUES (1, N'Oryginał'), (2, N'Kopia')) AS e (Nr, Egzemplarz)
CROSS JOIN dbo.vWydrukWZ AS w
WHERE w.DokumentId = @DokumentId
ORDER BY e.Nr, w.Lp;

Wydruk z aplikacji przez dostęp URL

Aplikacja może zlecić wydruk bez otwierania portalu. Dostęp URL do serwera raportów przyjmuje w adresie ścieżkę raportu oraz wartości parametrów, w tym format wyjściowy (serwer i folder są przykładowe):

https://raporty.firma.local/ReportServer?/Magazyn/DokumentWZ&rs:Command=Render&rs:Format=PDF&DokumentId=48213

Serwer zwraca gotowy plik PDF, który aplikacja przekazuje do drukarki albo zapisuje przy dokumencie. Parametr DokumentId jest w raporcie ukryty, więc magazynier nie widzi okna wyboru. Uprawnienia do folderu /Magazyn nadaje się grupie domenowej, a nie pojedynczym kontom. Składnię adresów podaje dokumentacja dostęp URL w SSRS.

Etykiety a raporty RDL

Etykiety logistyczne z kodem GS1-128 lepiej drukować w języku ZPL bezpośrednio na drukarce Zebra. Raport RDL nie ma wbudowanego elementu kodu kreskowego i pokazuje kod jako obraz albo czcionkę. Na drukarce termicznej 203 dpi przeskalowany obraz kodu łatwo traci czytelność. RDL sprawdza się przy dokumentach A4, a etykieta paletowa należy do osobnej ścieżki opisanej w artykule etykieta GS1.

Subskrypcje i harmonogram dostarczania raportów

Subskrypcja uruchamia raport według harmonogramu i dostarcza wynik bez udziału użytkownika. SSRS dostarcza raporty pocztą e-mail albo zapisuje je w udziale plików. Harmonogramy wykonuje SQL Server Agent, więc usługa agenta musi działać na instancji z bazą ReportServer. Opis mechanizmu zawiera dokumentacja subskrypcje i dostarczanie w SSRS.

W edycji Standard wysyłkę spersonalizowaną dla każdego odbiorcy realizuje się poza SSRS. SoftwareStudio używa do tego modułu ssJob, który harmonogramuje generowanie i wysyłkę raportów e-mailem albo publikację na portalu klienta, bez ręcznej obsługi. Moduły raportowe działają w systemach takich jak Studio WMS.net.

Raporty zbyt ciężkie do wykonania w godzinach pracy obsługuje się migawką albo buforowaniem. Serwer wykonuje zapytanie nocą, a użytkownicy rano otwierają zapisaną kopię. Baza produkcyjna, na której pracują terminale, nie odczuwa wtedy dużych zestawień.

Zasada: raport z zakresem wielu miesięcy nie powinien czytać tabel operacyjnych w godzinach pracy. Migawka albo replika bazy przejmuje takie obciążenie.

Monitorowanie wykonań raportów

Widok ExecutionLog3 w bazie ReportServer zapisuje każde wykonanie raportu, a czas pobrania danych podaje osobno od czasu renderowania. Zapytanie z ostatniego tygodnia pokazuje raporty, które najdłużej czekają na bazę:

SELECT TOP (20) ItemPath, Format, TimeStart,
       TimeDataRetrieval, TimeProcessing, TimeRendering, [RowCount]
FROM ReportServer.dbo.ExecutionLog3
WHERE TimeStart >= DATEADD(DAY, -7, GETDATE())
ORDER BY TimeDataRetrieval DESC;

Wysoki TimeDataRetrieval wskazuje na zapytanie bez odpowiedniego indeksu. Wysoki TimeRendering przy małej liczbie wierszy zwykle oznacza zbyt złożony układ, na przykład zagnieżdżone obszary tablix z podraportami.

Szerszy przegląd usług serwerowych zawiera artykuł raportowanie w SQL Server Reporting Services, a opis portalu i eksportu tekst Microsoft SQL Reporting Services.