Skocz do zawartości

Raporty SQL

Polecane posty

Na wstępie, dziękuje za dodanie funkcjonalności - jak dla mnie, jest ona wygodniejsza od raportów LINQ. Mam kilka pytań, w tej kwestii:

- obsługiwane funkcje - czy są jakieś ograniczenia co do funkcji, jakie można używać? Np. przy korzystaniu z funkcji CAST mam problem z formatowanie daty, bez wartości czasu. A może powinienem stosować tutaj inne funkcje formatujące?

- filtrowanie kolumn - czy planujecie dodać taką funkcjonalność? Domyślam się, że parametry kontekstowe i użytkownika, mogą mieć coś z tym do czynienia, ale mimo kilku prób, nie udało mi się ich prawidłowo użyć, bo nie wiem, czy one powinny trafić do WHERE, czy być w SELECT? Jakieś przykładowe zapytanie może macie na wzór? Jest informacja w Pomocy, ale nie ma dokumentacji w SDK, jak z LINQ z przykładami, a ona była świetną bazą do wyjścia dla nas.

 

Link to postu

Ad.1. Nie ma ograniczeń.

Ad.2. Jeśli filtrowanie kolumn oznacza zawężanie listy wyników do tych elementów, których wartość we wskazanej kolumnie spełnia określony warunek to tak, służą do tego parametry zapytania, których wartości można podawać po uruchomieniu raportu, w pasku filtra właśnie. Jeśli mają zawężać wyniki to należy je użyć w WHERE. Przykład listy asortymentu z wybranej grupy:

DECLARE @grupaId int = {LI:Grupa:SELECT Id, Nazwa FROM [ModelDanychContainer].[GrupyAsortymentu] ORDER BY Nazwa}
SELECT
   a.[Id]
  ,a.[Symbol]
  ,a.[Nazwa]
  ,g.[Nazwa] AS Grupa
FROM [ModelDanychContainer].[Asortymenty] AS a
INNER JOIN [ModelDanychContainer].[GrupyAsortymentu] AS g ON a.Grupa_Id=g.Id
WHERE @grupaId IS NULL OR @grupaId=a.Grupa_Id;

Dla porównania, analogiczne zapytanie dla raportu LINQ:

source
    .Where(a => a.Grupa.Id == grupa.Id)
    .Select(aso => new Wynik
    {
        Id = aso.Id,
        Symbol = aso.Symbol,
        Nazwa = aso.Nazwa,
        Grupa = aso.Grupa.Nazwa,
    });

gdzie grupa to parametr typu lista bazodanowa z głównym elementem GrupaAsortymentu.

Edytowane przez Jarek K.
Link to postu

Chuchałem, dmuchałem i nie dałem rady ;) W sensie mam problem, jak wpiąć w to zakres dat. Jakiś przykładzik by się znalazł pod ręką? Zależy mi na użyciu predefiniowanych stałych w tym przypadku no i kwestia przypisania dwóch zmiennych - DATEMIN oraz DATEMAX. Generalnie jakbyście robili dokumentację, to może byście zrobili takie przykładowe kody dla każdego z typu parametrów, co oszczędziło by wam na pewno pytań, a nam ułatwiło zdecydowanie wdrażanie się w tą funkcjonalność.

Link to postu

Dokumentacja jest i to dość szczegółowa i będzie dalej rozwijana w kierunku przykładów.

Co Pan chce uzyskać? DATEMIN i DATEMAX to akurat stałe, więc o jakie zmienne chodzi?

Przykład wykorzystania zakresu dat:

SELECT
   d.[Id]
  ,d.[Symbol]
  ,d.[NumerWewnetrzny_PelnaSygnatura]
  ,d.DataWprowadzenia
FROM [ModelDanychContainer].[Dokumenty] AS d
WHERE d.DataWprowadzenia BETWEEN {DR:Data wystawienia}

 

Link to postu

Chciałem ustawić zakres dat dla zapytania, sumującego ilość wystawionych ofert. Myślałem, że akurat parametry trzeba zawsze definiować, ale podał pan akurat użycie go bezpośrednio w WHERE. Zastosowałem kod z Pana przykładu i niestety mam problem z uzyskaniem pożądanego efektu. Więc, tak mój kod:

SELECT
  MIN(Dokumenty.Id) AS 'Id',
  MIN(Uzytkownicy.Login) AS 'Opiekun',
  COUNT(*) AS 'Ilość ofert',
  CAST(SUM(Dokumenty.WartoscTowarowNetto+Dokumenty.WartoscUslugNetto) AS money) AS 'Wartość'  
FROM ModelDanychContainer.Dokumenty
INNER JOIN ModelDanychContainer.Uzytkownicy ON Uzytkownicy.Id=Dokumenty.DokumentOE_Opiekun_Id
WHERE [__MdmDiscriminator__] = 'DokumentOE'
AND Dokumenty.DataWprowadzenia BETWEEN {DR:Data wystawienia:BIEZACY_DZIEN:BIEZACY_TYDZIEN:BIEZACY_MIESIAC :BIEZACY_KWARTAL}
GROUP BY Dokumenty.DokumentOE_Opiekun_Id

... a po uruchomieniu, filtr wygląda tak:

 

Przechwytywanie.PNG

 

Pewnie coś schrzaniłem, tylko co ;) Chodzi o to, aby użyć waszych predefiniowanych stałych. Walidator kod przepuszcza, ale może muszę użyć jeszcze jakiegoś nawiasu, klamry, nie wiem.

 

A co do dokumentacji, to póki co opieram się o zawartość pomocy do okna edycji, nic więcej nie znalazłem w helpie ani nowym SDK. Jeśli jest coś więcej, najlepiej z przykładami, to przyjmę w każdej ilości xD

Link to postu

Obecnie nie ma możliwości wpływania na opcje samej kontrolki. Chodzi o "(nieokreślone)" oraz "podana wartość" - one muszą być takie. Natomiast można zrobić dowolną listę wyboru za pomocą SQL. Przykład:

DECLARE @wynik int = {LI:Z okresu:
SELECT 0, '(bieżący dzień)' UNION
SELECT 1, '(bieżący tydzień)' UNION
SELECT 2, '(bieżący miesiąc)' UNION
SELECT 3, '(bieżący kwartał)'
};
DECLARE @data datetime = {DATA_APLIKACYJNA}
DECLARE @dateStart datetime = @data, @dateEnd datetime = @data

IF @wynik = 1 BEGIN SET @dateStart = DATEADD(WEEK, DATEDIFF(DAY, 0, @data)/7, 0); SET @dateEnd = DATEADD(WEEK, DATEDIFF(DAY, 0, @data)/7, 5); END
IF @wynik = 2 BEGIN SET @dateStart = DATEFROMPARTS(YEAR(@data), MONTH(@data), 1); SET @dateEnd = EOMONTH(@data); END
IF @wynik = 3 BEGIN SET @dateStart = DATEADD(qq, DATEDIFF(qq, 0, @data), 0); SET @dateEnd = DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, @data) +1, 0)); END

SELECT
   d.[Id]
  ,d.[Symbol]
  ,d.[NumerWewnetrzny_PelnaSygnatura]
  ,d.DataWprowadzenia
  ,@data
FROM [ModelDanychContainer].[Dokumenty] AS d
WHERE d.DataWprowadzenia BETWEEN @dateStart AND @dateEnd

 

Edytowane przez Jarek K.
uproszczenie kodu SQL
Link to postu

To się wydaje drogą okrężną dla przypadku standardowego (bieżący miesiąc, dzień itp.), a dla innych przypadków to jedyny sposób. W dodatku na tyle uniwersalny, że można "zrobić wszystko" :).

Powiązania dokumentów znajdują się w tabeli DokumentDokument. Przykład listy ofert z dokumentami realizującymi:

SELECT
	oe.Id,
	oe.NumerWewnetrzny_PelnaSygnatura AS Oferta,
	dr.NumerWewnetrzny_PelnaSygnatura AS [Dokument realizujący]
FROM ModelDanychContainer.Dokumenty AS oe
LEFT JOIN ModelDanychContainer.DokumentDokument dd ON dd.DokumentyRealizowane_Id=oe.Id
LEFT JOIN ModelDanychContainer.Dokumenty AS dr ON dr.Id=dd.DokumentyRealizujace_Id
WHERE oe.__MdmDiscriminator__='DokumentOE';

Dla pełni obrazu, ten sam przykład w LINQ w składni użyciem metod:

source.SelectMany(o => o.DokumentyRealizujace.DefaultIfEmpty(), 
    (oe, dr) => new Wynik
    {
        Id = oe.Id,
        Oferta = oe.NumerWewnetrzny.PelnaSygnatura,
        Realizowana_przez = dr.NumerWewnetrzny.PelnaSygnatura,
    })

Oraz w LINQ w składni 'sql':

from oe in source
from dr in oe.DokumentyRealizujace.DefaultIfEmpty()
select new Wynik
{ 
    Id = oe.Id,
    Oferta = oe.NumerWewnetrzny.PelnaSygnatura,
    Realizowana_przez = dr.NumerWewnetrzny.PelnaSygnatura,
}

 

Edytowane przez Jarek K.
dodatkowe przykłady
Link to postu

W miarę dobudowania kolejnych opcji raportów, pojawiają mi się niestety kolejne drobne problemy, więc kolejny temat - Waluty:

 

Zarówno...

DECLARE @grupaId uniqueidentifier = {LI:Waluta:SELECT Id, Nazwa FROM [ModelDanychContainer].[Waluty] ORDER BY Nazwa}

... jak i ...

WHERE OE.Dokument_Waluta_Id={LI:Waluta:SELECT Id,Symbol FROM ModelDanychContainer.Waluty} 

... nie wyświetla listy walut w filtrze. Próbowałem także różnych innych kombinacji, nawet ...

DECLARE @grupaId nchar(3) = {LI:Waluta:SELECT Symbol, Nazwa FROM [ModelDanychContainer].[Waluty] ORDER BY Nazwa}

... też bez efektu. Domyślam się, że wynika to z tego, że zgodnie z dokumentacją LI pod id, musi zwrócić int. Pewnie musiałbym użyć elementu kartoteki X lub LM? W każdym razie w helpie nie znalazłem kodu na walutę, plus jest to tylko mój domysł.

Link to postu

Tak jak Pan zauważył niestety jest to ograniczenie z intem. Na szczęście możemy sobie stworzyć sztuczny identyfikator tego typu i go wykorzystać. I to jest prawdziwe obejście, czy tam droga naokoło :):

DECLARE @walId uniqueidentifier, @walNr int = {LI:Waluta:SELECT CONVERT(int, ROW_NUMBER() OVER(ORDER BY Nazwa)), Nazwa FROM [ModelDanychContainer].[Waluty] ORDER BY Nazwa}
DECLARE @select nvarchar(500) = N'SELECT TOP ' + CONVERT(nvarchar, @walNr) + N' @walId_out=Id FROM [ModelDanychContainer].[Waluty] ORDER BY Nazwa;';
EXEC sp_executesql @select, N'@walId_out uniqueidentifier OUTPUT', @walId_out=@walId OUTPUT;

SELECT
   [Id]
  ,[Symbol]
  ,[NumerWewnetrzny_PelnaSygnatura]
FROM [ModelDanychContainer].[Dokumenty]
WHERE Dokument_Waluta_Id=@walId OR @walId IS NULL 

 

Link to postu

No rzeczywiście, tak skomplikowanego zapytania bym się nie spodziewał O.o

W każdy razie działa i to sprawnie, a ja poniżej pozwolę Sobie pozostawić pełny kod raportu, dla czytelników forum, może się komuś przyda 9_9

 

DECLARE @walId uniqueidentifier, @walNr int = {LI:Waluta:SELECT CONVERT(int, ROW_NUMBER() OVER(ORDER BY Nazwa)), Nazwa FROM [ModelDanychContainer].[Waluty] ORDER BY Nazwa}
DECLARE @select nvarchar(500) = N'SELECT TOP ' + CONVERT(nvarchar, @walNr) + N' @walId_out=Id FROM [ModelDanychContainer].[Waluty] ORDER BY Nazwa;';
EXEC sp_executesql @select, N'@walId_out uniqueidentifier OUTPUT', @walId_out=@walId OUTPUT;

SELECT
  MIN(OE.Id) AS 'Id',
  MIN(Uzytkownicy.Login) AS 'Opiekun',
  COUNT(*) AS 'Ilość ofert',
  CONCAT(FORMAT(SUM(OE.WartoscTowarowNetto+OE.WartoscUslugNetto)/1,'N'),' ',MIN(Waluty.Symbol)) AS 'Wartość ofert',
  COUNT(DD.DokumentyRealizowane_Id) AS 'Zrealizowane ZK',
  CONCAT(FORMAT(SUM(ZK.WartoscTowarowNetto+ZK.WartoscUslugNetto)/1,'N'),' ',MIN(Waluty.Symbol)) AS 'Wartość zamówień',
  CONCAT(FORMAT(((CAST(COUNT(DD.DokumentyRealizowane_Id) AS float)/CAST(COUNT(*) AS float))*100),'N'),'%') AS 'Skuteczność'
  
FROM ModelDanychContainer.Dokumenty AS OE
INNER JOIN ModelDanychContainer.Waluty ON Waluty.Id=OE.Dokument_Waluta_Id
INNER JOIN ModelDanychContainer.Uzytkownicy ON Uzytkownicy.Id=OE.DokumentOE_Opiekun_Id
LEFT JOIN ModelDanychContainer.DokumentDokument DD ON DD.DokumentyRealizowane_Id=OE.Id
LEFT JOIN ModelDanychContainer.Dokumenty AS ZK ON ZK.Id=DD.DokumentyRealizujace_Id
WHERE oe.__MdmDiscriminator__='DokumentOE'
AND OE.DataWprowadzenia BETWEEN {DR:Data wystawienia:BIEZACY_MIESIAC} 
AND (OE.Dokument_Waluta_Id=@walId OR @walId IS NULL)
GROUP BY OE.Dokument_Waluta_Id, OE.DokumentOE_Opiekun_Id

 

Link to postu

No właśnie z precyzją zauważyłem teraz mankament, bo jedyna funkcja MSSQL jaką udało mi się sformatować kolumny do dwóch miejsc po przecinku, była FORMAT, ale ona chyba zwraca wartości jako VARCHAR, bo automatycznie wysypuje się sortowanie, czyli sortuje 1, 10, 11, 2, 20, 3 itp. Nie wiem jak w MSSQL, bo bardziej specjalizuje się w MySQL, a tam ABS w sortowaniu by sprawę załatwił. Funkcja ROUND nie pomaga, podobnie jak CONVERT. Jakieś sugestie czy czekać na zmiany?

Link to postu

Na wizualizację wartości oraz sortowanie nie ma wpływu, ponieważ są to wbudowane elementy listy, zależne od typu danych. Dlatego po zmianie na typ tekstowy, żeby uzyskać dwa miejsca po przecinku, źle sortuje - bo sortuje tekstowo. ABS nic tu nie pomoże. Jeśli więc ważniejsza jest wizualizacja z dwoma miejscami po przecinku to trzymałbym się formatowania do tekstu i odpuścił sortowanie. Jeśli ważniejsze jest poprawne sortowanie, to zostawiłbym sformatowane tak jak teraz wartości typu kwotowego.

Link to postu
  • 4 tygodnie później...

Trochę popracowałem z tymi raportami, więc pozwolę Sobie dodać kilka sugestii, które miło by było jakby udało się wdrożyć:

- formatowanie kolumn, o tym już rozmawialiśmy, ale przypominam ;)

- domyślne sortowanie, czyli takie, jakie określamy przy wywołaniu raportu - ORDER BY nie działa, oczywiście można kliknąć w kolumne, ale to nie to

- grupowanie, takie jak to, na wszystkich waszych listach - można je ustawić po wywołaniu raportu, ale fajnie jakby można było zapisać domyślnie

- kolumny domyślnie ukryte, czyli wprowadzamy ją do raportu, ale jeśli użytkownik ich nie włączy, to się nie pokazują domyślnie

Link to postu
  • 2 miesiące temu...

Pozwolę sobie się podpiąć.

 

Czy istnieje sposób przechwycenia/zapisania całego zapytania SQL, łącznie z wyświetlaniem opcji, nazwami kolumn itd? Aby wywołać go następnym razem w danej postaci? Udało mi się przechwycić samo polecenie SQL w MSSQL, ale wiadomo - jest ono dla konkretnych parametrów, nazwy kolumn są jak w bazie itd.

 

Co jakiś czas muszę przygotowywać zestawienie, w którym zmienia się w zasadzie tylko data, ale reszta ustawień jest trochę pracochłonna (wybieranie grup produktów, których trochę mamy), więc przydałaby się możliwość zapisywania w jakiś sposób wybranych ustawień danego zestawienia. Nie znam SQL

 

Z koeli w raporcie "Wydania magazynowe wg asortymentu" przydałoby się, aby była możliwość wybrania w kontrolce "Magazyn" pozycji "dowolny", a nie konkretny magazyn lub z kontekstu.

Link to postu

 

45 minut temu, Marcin Gryska napisał:

Pozwolę sobie się podpiąć.

 

Czy istnieje sposób przechwycenia/zapisania całego zapytania SQL, łącznie z wyświetlaniem opcji, nazwami kolumn itd? Aby wywołać go następnym razem w danej postaci? Udało mi się przechwycić samo polecenie SQL w MSSQL, ale wiadomo - jest ono dla konkretnych parametrów, nazwy kolumn są jak w bazie itd.

 

Co jakiś czas muszę przygotowywać zestawienie, w którym zmienia się w zasadzie tylko data, ale reszta ustawień jest trochę pracochłonna (wybieranie grup produktów, których trochę mamy), więc przydałaby się możliwość zapisywania w jakiś sposób wybranych ustawień danego zestawienia. Nie znam SQL

 

Nie wiem, czy dobrze rozumiem Pańskie pytanie. Jeśli chodzi o zapisanie ostatnich ustawień, które wykonane zostały po uruchomieniu raportu własnego SQL i dotyczą wartości parametrów (opcji) oraz ustawień widoku (kolumny itp) to tak. Po wykonaniu zmian w uruchomionym raporcie trzeba zamknąć zakładkę z raportem. Wtedy następuje zapis. Następne uruchomienie raportu uwzględnia te zmiany. Nie ma możliwości zapisywania wielu takich zestawów. Obecnie, żeby uzyskać taki efekt, należałoby utworzyć osobne raporty dla każdego zestawu parametrów (np. grup) jeśli są one powtarzalne i jako parametr pozostawić tylko datę lub skopiować ten raport tyle razy ile jest zestawów parametrów i dla każdego ustawić filtry i parametry widoku wg uznania.

Link to postu
×
×
  • Dodaj nową pozycję...