e-Pomoc techniczna
InsERT nexo – Budowanie własnego raportu SQL
Program: InsERT nexo
Kategoria: Raporty
Uwaga! Funkcjonalność przeznaczona jest dla zaawansowanych użytkowników posiadających specjalistyczną wiedzę na temat programowania oraz struktury baz danych. W przypadku trudności z osiągnięciem oczekiwanego efektu zalecamy kontakt z dyplomowanym serwisantem, bądź skorzystanie z pomocy zdalnej.
Raporty własne SQL są to raporty tworzone przez użytkowników, którzy potrafią korzystać z zapytań języka SQL (dostępne w wersji InsERT nexo PRO).
Zapytania wprowadzone w sekcji Źródło danych mogą być zupełnie proste, np. zawartość kartoteki asortymentu w postaci symbolu, nazwy i opisu.
SELECT Id, Symbol, Nazwa, Opis FROM [ModelDanychContainer].[Asortymenty];
Na nazwy kolumn w wynikach raportu możemy wpływać, posługując się klauzulą AS tak, aby w naszym przykładzie kolumny nazywały się odpowiednio Symbol towaru, Nazwa towaru oraz Opis towaru. Dodamy też domyślne sortowanie wyników raportu wg symbolu towaru. Oczywiście w tabeli z wynikami raportu można sortować względem dowolnej kolumny, klikając myszą w jej nagłówek.
SELECT Id, Symbol AS [Symbol towaru], Nazwa AS [Nazwa towaru], Opis AS [Opis towaru]
FROM [ModelDanychContainer].[Asortymenty]
ORDER BY Symbol;
Listę wyników raportu można zawęzić przez zastosowanie warunków. Załóżmy, że w naszym dotychczasowym przykładzie chcemy zawęzić listę wyników do tych pozycji, których symbol rozpoczyna się od "POYAR".
SELECT Id, Symbol AS [Symbol towaru], Nazwa AS [Nazwa towaru], Opis AS [Opis towaru]
FROM [ModelDanychContainer].[Asortymenty]
WHERE Symbol LIKE 'POYAR%'
ORDER BY Symbol;
Oczywiście nie wszystkie potrzebne dane znajdują się w jednej tabeli. Konieczne jest wówczas łączenie danych z wielu tabel. Służy do tego klauzula JOIN, którą wykorzystamy do pozyskania danych do wyświetlenia w kolejnej kolumnie. Będą to nazwy grupy dla każdego z elementów kartoteki. Ponieważ nazwy kolumn w obu tabelach się powtarzają, konieczne jest zastosowanie przydomków (aliasów - klauzula AS), które pozwolą dokładnie określić, o którą tabelę nam chodzi w danym miejscu zapytania.
SELECT a.Id, a.Symbol, a.Nazwa, grupa.Nazwa AS [Grupa]
FROM [ModelDanychContainer].[Asortymenty] AS a
LEFT JOIN [ModelDanychContainer].[GrupyAsortymentu] AS grupa ON grupa.Id=a.Grupa_Id
Język SQL udostępnia również szereg funkcji pozwalających przeliczać dane. Najprostsza z nich umożliwia określenie liczby zapisów, np. policzenie towarów każdej z grup.
SELECT grupa.Id, grupa.Nazwa AS [Grupa], COUNT(a.Id) AS [Liczba towarów]
FROM [ModelDanychContainer].[GrupyAsortymentu] AS grupa
LEFT JOIN [ModelDanychContainer].[Asortymenty] AS a ON grupa.Id=a.Grupa_Id
GROUP BY grupa.Id, grupa.Nazwa
Innym przykładem wykorzystania funkcji jest obliczenie maksymalnej, minimalnej oraz średniej ceny ewidencyjnej towarów w każdej z grup.
SELECT grupa.Id, grupa.Nazwa AS [Grupa], AVG(a.CenaEwidencyjna) AS [Cena średnia],
MIN(a.CenaEwidencyjna) AS [Cena minimalna], MAX(a.CenaEwidencyjna) AS [Cena maksymalna]
FROM [ModelDanychContainer].[GrupyAsortymentu] AS grupa
LEFT JOIN [ModelDanychContainer].[Asortymenty] AS a ON grupa.Id=a.Grupa_Id
GROUP BY grupa.Id, grupa.Nazwa
Parametryzowanie zapytań
W ramach zapytania można definiować i wykorzystywać parametry, których wartości określa użytkownik raportu przy jego uruchamianiu. Dzięki temu, jeśli użyjemy parametrów w warunkach filtrujących zapytania (WHERE), użytkownik uruchamiający raport będzie mógł decydować o zakresie analizowanych danych.
Parametry, z których można korzystać w zapytaniach dzielą się na dwie grupy:
-
Parametry kontekstowe, tzn. wynikające z bieżących ustawień programu, w którym uruchamiany jest raport.
-
Parametry użytkownika, czyli te, których wartość podaje użytkownik raportu w czasie jego uruchamiania.
Twórca raportu zarówno definiuje parametry jak i wykorzystuje je bezpośrednio w zapytaniu. Mają one ogólną postać {definicja_parametru}.
Parametry kontekstowe
Parametry wynikające z kontekstu mają postać znacznika: {identyfikator}. Definicja tego typu parametru zawiera jedynie identyfikator wartości z kontekstu, która ma zostać wstawiona do treści zapytania przed jego wykonaniem. Przed wykonaniem znaczniki te (definicje parametrów) są zastępowane zmienną przechowującą aktualną wartość parametru, który reprezentują. Przy braku wartości (wartość nieokreślona) zmienne te mają wartość NULL.
| Parametr | Znaczenie i wartość | Typ wartości | Przykład wartości |
| ODDZIAL_ID | Identyfikator jednostki organizacyjnej. | int | 100000 |
| MAGAZYN_ID | Identyfikator magazynu. | int | 100052 |
| RACHUNEK_BANKOWY_ID | Identyfikator rachunku bankowego. | int | 100120 |
| STANOWISKO_KASOWE_ID | Identyfikator stanowiska kasowego. | int | 100540 |
| OKRES_OD | Data stanowiąca początek okresu księgowego. | datetime | 01-01-2017 |
| OKRES_DO | Koniec okresu księgowego. Data. | datetime | 31-12-2017 |
| OKRES_ID | Identyfikator okresu księgowego. | int | 100002 |
| UZYTKOWNIK_ID | Identyfikator zalogowanego użytkownika. | uniqueidentifier | FE7747E1-2810-47D2-AC7C-0AAEBBFCCA5F |
| PRACOWNIK_ID | Identyfikator pracownika. | int | 100456 |
| DATA_BLOKADY | Data blokady. | datetime | 12-05-2017 |
| DATA_APLIKACYJNA | Data aplikacyjna. | datetime | 08-06-2017 |
| DRUKARKA_FISKALNA_ID | Identyfikator drukarki fiskalnej. | int | 100145 |
| MIESIAC_ROBOCZY | Data miesiąca roboczego. | datetime | 01-04-2024 |
Poniżej kilka przykładów użycia parametrów kontekstowych.
Lista dokumentów ograniczona do bieżącego magazynu:
SELECT Id, Symbol AS Typ, NumerWewnetrzny_PelnaSygnatura AS [Numer dokumentu],
DataWydaniaWystawienia AS Data, KwotaDoZaplaty AS [Wartość]
FROM [ModelDanychContainer].[Dokumenty]
WHERE MagazynId={MAGAZYN_ID}
Lista dokumentów księgowych z bieżącego okresu:
SELECT baza.Id, DataZapisu, DataDowodu, Dowod_PelnaSygnatura,
NazwaKlienta, NumerWlasny, Wartosc
FROM [ModelDanychContainer].[BazoweDokumentyKsiegowe] AS baza
JOIN [ModelDanychContainer].[BazoweDokumentyKsiegowe_DokumentKsiegowy] AS dk ON dk.Id = baza.Id
WHERE DataZapisu BETWEEN {OKRES_OD} AND {OKRES_DO}
Lista dokumentów wprowadzonych przez zalogowanego użytkownika od daty blokady. Jeśli nie ma ustawionej aktywnej blokady wtedy lista wszystkich jego dokumentów, bez ograniczenia czasowego.
SELECT d.Id, Symbol AS Typ, NumerWewnetrzny_PelnaSygnatura AS [Numer dokumentu],
DataWydaniaWystawienia AS Data, KwotaDoZaplaty AS [Wartość]
FROM [ModelDanychContainer].[Dokumenty] AS d
JOIN [ModelDanychContainer].[Uzytkownicy] AS u ON u.Osoba_Id=d.WystawilaOsobaId
WHERE u.Id={UZYTKOWNIK_ID} AND (DataWydaniaWystawienia>{DATA_BLOKADY} OR {DATA_BLOKADY} IS NULL)
Parametry użytkownika
Twórca raportów SQL może definiować własne parametry dynamiczne wg następującego schematu: {typ:etykieta:pozostałe}
-
typ: Oznaczenie typu danych oraz rodzaju kontrolki.
-
etykieta: Etykieta ekranowa, czyli napis poprzedzający kontrolkę w interfejsie użytkownika. Należy podawać ją bez dwukropka, który jest separatorem w definicji parametrów.
-
pozostałe: Inne właściwości kontrolki zależne od jej typu.
Na końcu rozdziału znajduje się szczegółowa lista typów dynamicznych parametrów użytkownika. Poniżej przedstawiamy przykłady użycia tych parametrów w zapytaniach.
Parametr tekstowy. Wybranie pozycji kartoteki Asortyment, których symbol rozpoczyna się od podanego ciągu.
SELECT Id, Symbol, Nazwa, Opis
FROM [ModelDanychContainer].[Asortymenty]
WHERE Symbol LIKE {T:Symbol zaczyna się od:POYAR} + '%';
Parametr typu liczba całkowita. Wybranie kontrahentów, dla których wystawiono co najmniej określoną liczbę dokumentów sprzedaży. Domyślnie co najmniej 10.
SELECT Sygnatura_PelnaSygnatura, NazwaSkrocona, ile
FROM [ModelDanychContainer].[Podmioty] AS p
LEFT JOIN (
SELECT COUNT(d.Id) AS ile, d.PodmiotId
FROM [ModelDanychContainer].[Dokumenty] AS d
WHERE d.__MdmDiscriminator__='DokumentDS'
GROUP BY d.PodmiotId
) AS doki ON doki.PodmiotId=p.Id
WHERE ile IS NOT NULL AND ile >= {I:Liczba dokumentów co najmniej:10}
Parametr walutowy. Lista dokumentów sprzedaży o wartości przekraczającej podaną wartość. Domyślnie 10000.
SELECT d.Id, d.NumerWewnetrzny_PelnaSygnatura AS [Numer], d.DataWydaniaWystawienia AS [Data],
p.NazwaSkrocona AS [Kontrahent], d.KwotaDoZaplaty AS [Wartość]
FROM [ModelDanychContainer].[Dokumenty] AS d
LEFT JOIN [ModelDanychContainer].[Podmioty] AS p ON p.Id=d.PodmiotId
WHERE d.__MdmDiscriminator__='DokumentDS' AND d.KwotaDoZaplaty > {M:Wartość dokumentu większa niż:10000}
Parametr typu data. Lista dokumentów wystawionych w wybranym dniu lub lista wszystkich dokumentów jeśli dzień nie został wybrany (domyślnie). Brak wybrania konkretnego dnia skutkuje wartością NULL zdefiniowanego parametru. Na uwagę zasługuje fakt, że wielokrotne powtarzanie identycznej definicji parametru nie skutkuje dodawaniem kolejnych filtrów po uruchomieniu raportu. Są one traktowane jako jeden i ten sam parametr.
SELECT d.Id, d.NumerWewnetrzny_PelnaSygnatura AS [Numer], d.DataWydaniaWystawienia AS [Data],
p.NazwaSkrocona AS [Kontrahent], d.KwotaDoZaplaty AS [Wartość]
FROM [ModelDanychContainer].[Dokumenty] AS d
LEFT JOIN [ModelDanychContainer].[Podmioty] AS p ON p.Id=d.PodmiotId
WHERE d.DataWydaniaWystawienia={D:Data wystawienia} OR {D:Data wystawienia} IS NULL
Format definicji parametru typu data pozwala na określenie domyślnej wartości po uruchomieniu raportu. Może to być:
-
stała, np. {D:Data wystawienia:20170101}
-
wyrażenie SQL zwracające datę, np. {D:Data wystawienia:SELECT GETDATE()}
-
parametr kontekstowy, np. {D:Data wystawienia:SELECT {DATA_APLIKACYJNA}}
Parametr typu zakres dat. Lista dokumentów wystawionych w wybranym zakresie dat.
SELECT d.Id, d.NumerWewnetrzny_PelnaSygnatura AS [Numer], d.DataWydaniaWystawienia AS [Data],
p.NazwaSkrocona AS [Kontrahent], d.KwotaDoZaplaty AS [Wartość]
FROM [ModelDanychContainer].[Dokumenty] AS d
LEFT JOIN [ModelDanychContainer].[Podmioty] AS p ON p.Id=d.PodmiotId
WHERE d.DataWydaniaWystawienia BETWEEN {DR:Z okresu}
Format definicji parametru typu zakres dat pozwala na określenie domyślnego okresu po uruchomieniu raportu. Mogą to być:
-
stałe, np. {DR:Z okresu:20170101:20171231}
-
wyrażenia SQL zwracające datę, np. {DR:Z okresu:SELECT GETDATE()-3:SELECT GETDATE()}
-
parametry kontekstowe, np. {DR:Z okresu:{OKRES_OD}:{OKRES_DO}}
-
stała określająca zakres, np. {DR:Z okresu:BIEZACY_TYDZIEN}
Parametr typu lista opcji. Lista jednostek miar wybranego typu. Domyślnie pokazane będą wszystkie jednostki. Dla uproszczenia przykładu, żeby nie powtarzać dwukrotnie definicji parametru, wykorzystano możliwość zdefiniowania zmiennej przechowującej identyfikator wybranego typu jednostki miary.
DECLARE @typ int = {LI:Typ:masa#0#objętość#1#powierzchnia#2#długość#3#czas#4#zależy od asortymentu#5}
SELECT Nazwa, Symbol, Precyzja FROM [ModelDanychContainer].[JednostkiMiar]
WHERE Typ=@typ OR @typ IS NULL
Do określenia listy elementów do wyboru można też użyć wyrażenia SQL zwracającego dane zawierające identyfikatory i nazwy opcji. Lista towarów powiązanych z wybraną jednostką miary.
DECLARE @jmId int = {LI:Jednostka:SELECT Id, Nazwa + ' (' + Symbol + ')' FROM [ModelDanychContainer].[JednostkiMiar] ORDER BY Symbol}
SELECT DISTINCT * FROM [ModelDanychContainer].[Asortymenty] AS a
JOIN [ModelDanychContainer].[JednostkiMiarAsortymentow] AS jm ON a.Id=jm.Asortyment_Id
WHERE jm.JednostkaMiary_Id=@jmId OR @jmId IS NULL
Parametr typu lista opcji występuje też w drugiej odmianie (LI2), która różni się sposobem prezentacji oraz wyboru. Lista elementów jest w niej dostępna bezpośrednio w rozwijanej liście wyboru.
Parametr typu lista opcji w możliwością wyboru wielu. Asortyment powiązany z wybranymi jednostkami miary.
SELECT Id, Nazwa, Symbol, Precyzja FROM [ModelDanychContainer].[JednostkiMiar]
WHERE Typ IN ({CHL:Typ:masa#0#objętość#1#powierzchnia#2#długość#3#czas#4#zależy od asortymentu#5})
Do określenia listy elementów do wyboru można też użyć wyrażenia SQL zwracającego dane zawierające identyfikatory i nazwy opcji. Lista towarów powiązanych z wybranymi jednostkami miary.
SELECT DISTINCT a.Id, a.Symbol, a.Nazwa
FROM [ModelDanychContainer].[Asortymenty] AS a
JOIN [ModelDanychContainer].[JednostkiMiarAsortymentow] AS jm ON a.Id=jm.Asortyment_Id
WHERE jm.JednostkaMiary_Id
IN ({CHL:Jednostka:SELECT Id, Nazwa + ' (' + Symbol + ')'
FROM [ModelDanychContainer].[JednostkiMiar] ORDER BY Symbol})
Chcąc uwzględnić sytuację, gdy jednostki w filtrze nie zostaną określone przez użytkownika, należy sprawdzić czy parametr ma wartość NULL. Załóżmy, że wówczas w wynikach raportu ma się pojawić cały asortyment. W tym celu dodajemy dodatkowy warunek odwołujący się do tego samego parametru i porównujący go tekstowo z ciągiem 'NULL'. W nowym warunku powtarzamy dokładnie definicję parametru. Mechanizm porówna obie definicje i stwierdzi, że chodzi o ten sam parametr i nie wygeneruje nowego filtra w raporcie. Mimo, że powtórzona definicja parametru znajduje się w stałej tekstowej, nie należy używać w niej podwójnych cudzysłowów, zgodnie z regułami tworzenia stałych tekstowych w języku SQL, ponieważ definicja ta zostanie najpierw przekształcona przez mechanizm raportów własnych nexo i w to miejsce zostanie wstawiona wartość wybrana przez użytkownika.
SELECT DISTINCT a.Id, a.Symbol, a.Nazwa
FROM [ModelDanychContainer].[Asortymenty] AS a
JOIN [ModelDanychContainer].[JednostkiMiarAsortymentow] AS jm ON a.Id=jm.Asortyment_Id
WHERE jm.JednostkaMiary_Id
IN ({CHL:Jednostka:SELECT Id, Nazwa + ' (' + Symbol + ')'
FROM [ModelDanychContainer].[JednostkiMiar] ORDER BY Symbol})
OR 'NULL'='{CHL:Jednostka:SELECT Id, Nazwa + ' (' + Symbol + ')'
FROM [ModelDanychContainer].[JednostkiMiar] ORDER BY Symbol}'
Parametr typu element kartoteki. Lista dokumentów wybranego kontrahenta.
SELECT d.Id, d.NumerWewnetrzny_PelnaSygnatura AS [Numer],
d.DataWydaniaWystawienia AS [Data], d.KwotaDoZaplaty AS [Wartość]
FROM [ModelDanychContainer].[Dokumenty] AS d
WHERE d.PodmiotId={X:KH:Dla kontrahenta} OR {X:KH:Dla kontrahenta} IS NULL
Parametr typu zbiór elementów kartoteki. Podsumowanie sprzedaży netto wybranego asortymentu w bieżącym miesiącu.
SELECT 0 AS Id, 'Sprzedaż w bieżącym miesiącu' AS Opis, SUM(poz.Wartosc_NettoPoRabacie) AS [Wartość]
FROM [ModelDanychContainer].[Dokumenty] AS d
JOIN [ModelDanychContainer].[PozycjeDokumentu] as poz ON poz.Dokument_Id=d.Id
RIGHT JOIN [ModelDanychContainer].[Asortymenty] as a ON a.Id=poz.AsortymentAktualnyId
WHERE d.__MdmDiscriminator__='DokumentDS' AND a.Id IN ({LM:TW:Wybrany asortyment})
AND d.DataWydaniaWystawienia
BETWEEN (SELECT CAST(EOMONTH (GETDATE(), -1 ) AS datetime) + 1) AND (SELECT EOMONTH(GETDATE()))
Lista dostępnych typów parametrów:
| Typ parametru | Oznaczenie | Format | Wartość zwracana |
| tekstowy | T | {T:etykieta:wartoscDomyslna} wartoscDomyslna - ciąg wpisywany do kontrolki jako wartość domyślna - opcjonalne | wpisany ciąg (bez znaków ograniczających) NULL, gdy nieokreślone |
| liczba całkowita | I | {I:etykieta:wartoscDomyslna} wartoscDomyslna - liczba wpisywana do kontrolki jako wartość domyślna - opcjonalne | wprowadzona liczba typu int
NULL, gdy nieokreślone |
| walutowy | M | {M:etykieta:wartoscDomyslna} wartoscDomyslna - kwota wpisywana do kontrolki jako wartość domyślna (separator "," albo "."), precyzja wpisanej wartości określa późniejszą precyzję kontrolki - opcjonalne | wprowadzona wartość typy money
NULL, gdy nieokreślone |
| data | D | {D:etykieta:wartoscDomyslna} wartoscDomyslna - data w postaci YYYYMMDD lub wyrażenie SQL, np. pierwszy dzień bieżącego miesiąca: SELECT DATEADD(dd,-(DAY(GETDATE())-1), GETDATE()) lub też parametr kontekstowy typu data - opcjonalne | data typu datetime
NULL, gdy nieokreślone |
| zakres dat | DR |
| ciąg dataPoczatkowa AND dataKoncowa gotowy do wstawienia po BETWEEN Wartości 1753-01-01 i 9999-12-31, gdy nieokreślone |
| lista opcji lub lista z uproszczonym wyborem | LI lub LI2 |
| identyfikator wybranej opcji NULL, gdy nieokreślone |
| lista opcji z możliwością wyboru wielu | CHL |
| lista identyfikatorów wybranych opcji id1,id2,id3 gotowa do wstawienia w klauzulę IN() NULL, gdy nieokreślone |
| element kartoteki | X | {X:kartoteka:etykieta:idDomyslnego} kartoteka - typ instynktu: TW dla towaru, KH dla kontrahenta, KK dla konta księgowego, DKK dla definicji konta księgowego, PR dla pracownika, z tym, że kartoteki KK i DKK działają tylko w Rewizorze - uruchomionym samodzielnie lub w multiprogramie idDomyslnego - identyfikator domyślnie wybranego elementu (w widoku kontrolki) - opcjonalne | identyfikator wybranego elementu NULL, gdy nieokreślone (lub element nie został wybrany) (zarówno dla KH jak i PR jest to identyfikator z tabeli Podmioty) |
| zbiór elementów kartoteki | LM | {LM:kartoteka:etykieta:idyDomyslnieWybranych} kartoteka - typ listy: TW dla towaru, KH dla kontrahenta, PR dla pracownika idyDomyslnieWybranych - identyfkatory domyślnie wybranych elementów (w widoku kontrolki) - rozdzielone przecinkami - opcjonalne | lista identyfikatorów wybranych opcji id1,id2,id3 gotowa do wstawienia w klauzulę IN() NULL, gdy nieokreślone (zarówno dla KH jak i PR jest to identyfikator z tabeli Podmioty) |
Na zakładce Pozostałe można określić następujące parametry:
-
OPIS - krótka informacja dla użytkownika o przeznaczeniu raportu. Pole obligatoryjne; maksymalna długość opisu to 255 znaków. Jego zawartość jest widoczna w podglądzie modułu Raporty po zaznaczeniu raportu na liście.
-
GRUPA - określa przyporządkowanie tworzonego raportu do jednej z grup widocznych na liście w module Raporty. Po rozwinięciu dostępna jest lista aktualnie zdefiniowanych grup raportów; możliwe jest jednak utworzenie nowej grupy poprzez wpisanie jej nazwy. Maksymalna długość nazwy grupy to 64 znaki, brak wartości nie jest dopuszczalny. Grupa zostanie dodana wraz z zapisaniem tworzonego raportu.
-
CZY RAPORT WIDOCZNY NA LIŚCIE RAPORTÓW - zakreślenie pola spowoduje, że raport będzie widoczny na liście raportów dostępnej w oknie głównym modułu Raporty.
-
WIDOCZNY W - wskazuje produkty z linii InsERT nexo, w których tworzony raport własny będzie możliwy do uruchomienia. Domyślnie wybrany jest produkt, w którym tworzony jest raport.
-
DODAJ KOLUMNY Z TEGO RAPORTU DO WBUDOWANYCH SERWISÓW ORAZ RAPORTÓW - pole dostępne po aktywowaniu funkcji Własne kolumny. Jego zakreślenie spowoduje, że tworzony raport staną się źródłem własnych kolumn.
-
DODAJ PARAMETRY Z TEGO RAPORTU DO WBUDOWANYCH SERWISÓW ORAZ RAPORTÓW - pole dostępne aktywowaniu funkcji Własne filtry. Jego zakreślenie spowoduje, że parametry (np. filtry) tworzonego raportu staną się źródłem własnych filtrów.
