Raportowanie HermesSQL - od widoku SQL do Studio Raporty

Skąd raportowanie HermesSQL bierze dane

Baza MS SQL Server jako źródło

Raportowanie HermesSQL opiera się na integracji z Microsoft SQL Server. System ERP firmy Humansoft zapisuje dokumenty oraz kartoteki w relacyjnej bazie, a raporty czytają dane z tej samej bazy. Jeżeli firma nie zbuduje osobnej hurtowni, wynik raportu odzwierciedla stan bazy w chwili uruchomienia. Dla decyzji operacyjnych to zaleta, bo dane są bieżące, a dla analiz historycznych wymaga to uwagi przy obciążeniu serwera.

Ta strona opisuje drogę danych od tabeli do ekranu użytkownika. Konstrukcję samego pliku raportu i jego wydruków omawia osobny tekst o SQL Report Builder, a usługę serwerową opisuje strona o Microsoft SQL Reporting Services.

Widoki SQL jako kontrakt danych

Raport nie powinien odwoływać się bezpośrednio do tabel systemu. Zmiana struktury tabeli po aktualizacji oprogramowania łamie takie raporty jeden po drugim. Widok pełni rolę kontraktu: nazwy kolumn i ich znaczenie pozostają stałe, a wewnętrzne złączenia można zmienić bez ruszania raportu. Składnię opisuje dokumentacja instrukcji CREATE VIEW.

CREATE VIEW rpt.vSprzedazMiesiac
AS
SELECT DATEFROMPARTS(YEAR(d.DataSprzedazy), MONTH(d.DataSprzedazy), 1) AS Miesiac,
       t.Grupa,
       t.TowarId,
       SUM(p.Ilosc)               AS Ilosc,
       SUM(p.Ilosc * p.CenaNetto) AS WartoscNetto
FROM dbo.SprzedazDokument AS d
JOIN dbo.SprzedazPozycja  AS p ON p.DokumentId = d.Id
JOIN dbo.Towar            AS t ON t.TowarId = p.TowarId
GROUP BY DATEFROMPARTS(YEAR(d.DataSprzedazy), MONTH(d.DataSprzedazy), 1),
         t.Grupa, t.TowarId;

Schemat rpt i nazwy tabel są przykładowe, a nie odwzorowaniem struktury HermesSQL. Wydzielony schemat ułatwia nadawanie uprawnień, bo konto raportowe dostaje prawo odczytu tylko do niego, a nie do całej bazy.

Procedury składowane z parametrami

Gdy raport wymaga logiki, której widok nie wyrazi, źródłem staje się procedura składowana. Przyjmuje parametry i zwraca zbiór wierszy, a SSRS mapuje jej parametry na parametry raportu. Instrukcję opisuje strona o CREATE PROCEDURE.

CREATE PROCEDURE rpt.uspSprzedaz
    @DataOd  DATE,
    @DataDo  DATE,
    @TowarId INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Miesiac, Grupa, TowarId, Ilosc, WartoscNetto
    FROM rpt.vSprzedazMiesiac
    WHERE Miesiac >= @DataOd
      AND Miesiac <  @DataDo
      AND (@TowarId IS NULL OR TowarId = @TowarId)
    OPTION (RECOMPILE);
END;

Warunek z parametrem opcjonalnym powoduje, że jeden plan wykonania obsługuje słabo zarówno wywołanie z towarem, jak i bez niego. Podpowiedź RECOMPILE kompiluje plan przy każdym uruchomieniu, co przy raportach wykonywanych rzadko kosztuje mniej niż zły plan. Tło zjawiska wyjaśnia dokumentacja o wrażliwości planu na parametry.

Obciążenie serwera przy raportach na żywo

Raport czytający tabele operacyjne konkuruje o zasoby z pracą magazynu i sprzedaży. Przy domyślnym poziomie izolacji READ COMMITTED zapytanie raportowe nakłada blokady współdzielone, a długi odczyt może opóźniać zapisy dokumentów. Włączenie opcji READ_COMMITTED_SNAPSHOT przenosi odczyty na wersjonowanie wierszy, dzięki czemu raport nie blokuje zapisu, kosztem większego użycia bazy tempdb.

• Raport na żywo - czyta bazę operacyjną i pokazuje stan bieżący, więc nadaje się do decyzji dnia.
• Raport z kopii - czyta replikę albo odtworzoną kopię bazy, co odciąża produkcję, ale opóźnia dane.

SSRS i Report Builder w tworzeniu raportów

Podział ról narzędzi

Opis raportowania HermesSQL rozdziela zadania między dwa narzędzia Microsoftu: SQL Reporting Services generuje i dystrybuuje raporty, a Report Builder służy do ich projektowania. Trzecim elementem jest platforma Studio Raporty. Report Builder jest bezpłatny, więc firma nie kupuje osobnego oprogramowania do projektowania. Więcej o jego obsłudze mówi dokumentacja Report Builder w SQL Server.

Zestaw danych i publikacja

Autor raportu tworzy źródło danych wskazujące serwer i bazę, a następnie zestaw danych z zapytaniem albo wywołaniem procedury. Kolumny zestawu trafiają do tabel i wykresów, a parametry zapytania do okna parametrów raportu. Po zapisie plik RDL publikuje się na serwerze raportów, gdzie wykonuje się go na żądanie albo według harmonogramu.

Reguła: raport czyta widok albo procedurę z osobnego schematu, nigdy tabele systemu. Po aktualizacji oprogramowania wystarczy poprawić widok, a nie kilkadziesiąt plików raportów.

Testowanie raportu przed publikacją

Raport przed publikacją uruchamia się na kopii bazy o realistycznej wielkości. Wynik pusty sprawdza, czy raport wyświetla komunikat zamiast pustej strony. Wynik z jednym wierszem weryfikuje sumy i wyrównanie kolumn, a zestaw o kilku tysiącach wierszy pokazuje czas wykonania i podział na strony.

Gdy nowy raport zastępuje stary, porównuje się sumy kontrolne z tego samego okresu. Rozbieżność zwykle wynika z innego traktowania dokumentów anulowanych albo korekt, a nie z błędu obliczeń. Ustalenie tej zasady na początku oszczędza późniejsze spory o to, który raport ma rację.

Raportowanie w systemach magazynowych

Ten sam model spotyka się poza ERP. Raportowanie w systemie magazynowym opartym na SSRS opisuje strona o raportach WMS z KPI, a przykład z produkcji mięsnej daje tekst o Studio Raporty dla Foss Meatmaster.

Studio Raporty i udostępnianie raportów

Platforma webowa i dostęp z przeglądarki

Według opisu raporty są udostępniane użytkownikom przez platformę webową Studio Raporty. Użytkownik otwiera raport w przeglądarce, bez instalacji narzędzia projektowego na swoim komputerze. Stacja robocza nie wymaga specjalnego sprzętu, bo obliczenia wykonuje serwer bazy i serwer raportów, a przeglądarka wyświetla gotowy wynik.

Adres raportu można także wywołać bezpośrednio z aplikacji, co jest przydatne do wydruków dokumentów. Składnię takich odwołań opisuje dokumentacja dostępu URL w SSRS.

Menu raportów zależnie od roli

W dokumentacji produktów SoftwareStudio moduł Studio Raporty służy do udostępniania raportów zainstalowanych na serwerze SQL przez nadanie uprawnień i przyjaznych nazw. Konfigurację opisuje strona o konfiguracji programu magazynowego, a zarządzanie menu raportów omawia tekst o raportowaniu w SQL Server Reporting Services. Efekt jest prosty: użytkownik widzi tylko zestawienia istotne dla swojego stanowiska.

Licencje i liczba użytkowników

Opis HermesSQL stwierdza, że system nie wymaga zakupu licencji zależnie od liczby użytkowników raportów, a z zestawień może korzystać dowolna liczba pracowników. Warunki licencyjne samego serwera bazy i usługi raportowej wynikają jednak z umowy z Microsoftem i przed wdrożeniem trzeba je zweryfikować u dostawcy oprogramowania.

Harmonogram i eksport

Raport wykonywany na żądanie nie zawsze jest właściwym rozwiązaniem. Zestawienia dobowe zamawia się w subskrypcji, która uruchamia je o stałej godzinie i wysyła jako plik albo zapisuje w udziale sieciowym. SSRS eksportuje wynik do PDF oraz do Excela, co ułatwia dalszą obróbkę. Więcej o mobilnych raportach i kwerendach opisuje strona o usługach SSRS.

Parametry raportów i raporty interaktywne

Parametry jako filtry

Raport interaktywny pozwala wprowadzić parametry jeszcze przed uruchomieniem. Użytkownik wybiera okres albo produkt i od razu otrzymuje dane, których potrzebuje, zamiast przeglądać cały zbiór. Parametr działa więc jak filtr wykonywany po stronie serwera, co ogranicza ilość przesyłanych wierszy.

• Parametr daty - zakres od i do, przekazywany do zapytania jako wartość typu DATE.
• Parametr wyboru - lista wartości pobierana z osobnego zapytania, na przykład grupy towarów.
• Parametr opcjonalny - wartość NULL oznacza brak filtra, jak przy towarze w procedurze wyżej.
• Parametr ukryty - wartość ustawiana przez aplikację, na przykład identyfikator dokumentu do wydruku.

Parametry kaskadowe

Lista towarów zależna od wybranej grupy wymaga parametrów kaskadowych. Zapytanie listy drugiego parametru przyjmuje wartość pierwszego, więc SSRS odświeża listę po jego zmianie. Kolejność parametrów w raporcie musi odpowiadać kolejności zależności, inaczej okno parametrów zgłosi błąd.

Czas wykonania raportu

Raport z parametrem daty przyspiesza indeks na kolumnie daty dokumentu. Indeks zawierający dodatkowo kolumny czytane w zapytaniu, zwany pokrywającym, pozwala silnikowi obsłużyć zapytanie bez sięgania do tabeli. Raporty ciężkie, uruchamiane wielokrotnie w ciągu dnia, opłaca się buforować w SSRS na kilka minut. Wtedy kolejni użytkownicy dostają wynik z pamięci, a serwer bazy odpoczywa.

Uprawnienia do raportów i danych

Poziomy uprawnień

Opis raportowania HermesSQL wspomina o definiowaniu poziomów uprawnień, które określają, kto ma dostęp do których danych i raportów. Uprawnienia działają na dwóch warstwach. Pierwsza decyduje, czy raport pojawia się w menu użytkownika, a druga, jakie wiersze wynik zawiera. Ukrycie raportu w menu nie wystarcza, jeśli konto ma prawo odczytu tabeli, do której raport się odwołuje.

Ograniczanie wierszy według użytkownika

Handlowiec ma widzieć wyłącznie własnych klientów, a kierownik cały region. Można to rozwiązać parametrem ukrytym z identyfikatorem użytkownika i warunkiem w zapytaniu. Bezpieczniejszy wariant przenosi filtr do bazy w postaci polityki zabezpieczeń na poziomie wiersza, opisanej w dokumentacji Row-Level Security. Wtedy filtr działa niezależnie od tego, jakim narzędziem ktoś odczytuje dane.

Konto źródła danych

Źródło danych raportu łączy się z bazą na koncie usługi albo na koncie użytkownika. Konto usługi upraszcza zarządzanie, lecz wszyscy użytkownicy widzą to, co widzi konto usługi. Konto użytkownika, przekazywane przez uwierzytelnianie zintegrowane, wymaga skonfigurowania delegowania, ale pozwala stosować politykę wierszową. Wybór zależy od tego, czy zróżnicowanie dostępu realizuje aplikacja, czy baza.

Dziennik wykonań raportów

Serwer raportów zapisuje każde wykonanie w bazie katalogowej, w widoku ExecutionLog3. Zapytanie do niego pokazuje, kto uruchamia które raporty i gdzie schodzi czas: na pobieraniu danych czy na renderowaniu.

SELECT TOP (20)
       ItemPath,
       UserName,
       TimeStart,
       TimeDataRetrieval,
       TimeProcessing,
       TimeRendering,
       [RowCount]
FROM ReportServer.dbo.ExecutionLog3
ORDER BY TimeStart DESC;

Wysoki czas pobrania danych wskazuje na zapytanie lub brak indeksu, a wysoki czas renderowania na raport z tysiącami wierszy w jednej tabeli. Ten sam dziennik służy do audytu, bo zawiera nazwę użytkownika i wartości parametrów użytych przy uruchomieniu.

Dane płacowe i partie w raportach

Parametry płacowe z okresami obowiązywania

Opis HermesSQL jako programu płacowego zawiera listę parametrów obowiązujących w konkretnych okresach: stóp procentowych składek na ubezpieczenia społeczne oraz stóp podatkowych, do których dochodzą kwoty średnich zarobków. Zwykle obowiązują przez rok, a system pozwala je zmienić w dowolnym momencie, uwzględniając datę początkową i końcową. Dla raportowania oznacza to jedno: raport z przeszłości musi użyć parametru, który obowiązywał wtedy, a nie obecnego.

SELECT w.PracownikId,
       w.DataNaliczenia,
       w.Brutto,
       p.Wartosc AS ParametrObowiazujacy
FROM dbo.Wynagrodzenie AS w
JOIN dbo.ParametrPlacowy AS p
  ON p.Nazwa = N'ParametrPrzykladowy'
 AND w.DataNaliczenia BETWEEN p.ObowiazujeOd AND p.ObowiazujeDo;

Złączenie po przedziale dat zastępuje odczyt jednej wartości bieżącej. Aby jeden dzień nie należał do dwóch parametrów, zakresy muszą się nie nakładać, co wymusza ograniczenie po stronie bazy albo kontrola przy zapisie.

Dane płacowe należą do kategorii wrażliwych, więc raporty na ich podstawie wymagają najściślejszego zakresu uprawnień. Zestawienie wynagrodzeń nie powinno pojawiać się w menu osób spoza działu kadr i płac, a konto raportowe nie powinno mieć prawa odczytu tabel płacowych poza widokami przeznaczonymi dla tego działu.

Partie w zestawieniach

Opis wskazuje też na śledzenie partii, czyli ustalanie pochodzenia surowców i drogi wyrobów gotowych do końcowego odbiorcy, co ma znaczenie w branżach o zaostrzonym nadzorze, jak spożywcza czy farmaceutyczna. Raport partii pozwala szybko wskazać, gdzie trafiła konkretna partia, przy reklamacji albo wycofaniu produktu. Mechanizm opisuje tekst o śledzeniu partii produktów.

Zestawienia sprzedaży i produkcji

Do zestawień biznesowych należą kontrola produkcji i analiza sprzedaży, które omawiają teksty o raportach kontroli produkcji oraz o analizie sprzedaży w HermesSQL. Dokumenty zakupu jako źródło zestawień opisuje strona o rejestracji zakupów. Wdrożenie raportowania SQL na zamówienie oferuje firma SoftwareStudio.