Materiały szkoleniowe

Podstawy budowy raportów w Oracle Reports 2.5 ćwiczenia

wersja poprawiona 17.04.2000

Autor

Paweł Żołnierczyk

Spis treści

Zawartość tabel wykorzystywanych na kursie

Zawartość tabel wykorzystywanych na kursie

Zawartość tabeli DEPT

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Zawartość tabeli EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--------- ---------- --------- --------- -------- --------- --------- ---------

7839 KING PRESIDENT 81/11/17 5000 10

7698 BLAKE MANAGER 7839 81/05/01 2850 30

7782 CLARK MANAGER 7839 81/06/09 2450 10

7566 JONES MANAGER 7839 81/04/02 2975 20

7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30

7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30

7844 TURNER SALESMAN 7698 81/09/08 1500 0 30

7900 JAMES CLERK 7698 81/12/03 950 30

7521 WARD SALESMAN 7698 81/02/22 1250 500 30

7902 FORD ANALYST 7566 81/12/03 3000 20

7369 SMITH CLERK 7902 80/12/17 800 20

7788 SCOTT ANALYST 7566 82/12/09 3000 20

7876 ADAMS CLERK 7788 83/01/12 1100 20

7934 MILLER CLERK 7782 82/01/23 1300 10

Zawartość tabeli SALGRADE

GRADE LOSAL HISAL

--------- --------- ---------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

Budowa tabel wykorzystywanych na kursie

Podczas kursu będziemy korzystać z uprzednio utworzonych tabel opisujących zatrudnionych w pewnej firmie, ich wynagrodzenia i miejsca pracy. Firma dzieli się na departamenty. Każdy pracownik należy do pewnej grupy zaszeregowania, w zależności od wysokości pensji, którą otrzymuje.

Tabela DEPT — tabela zawierająca wszystkie departamenty

Kolumna	Opis
DEPTNO	Departament number — unikalny numer departamentu
DNAME	Nazwa departamentu — przechowywana w zapisie dużymi literami
LOC	Lokalizacja departamentu (miasto w którym znajduje się departament)

Tabela EMP — wykaz wszystkich pracowników

Kolumna	Opis
EMPNO	Employee number — unikalny numer pracownika
ENAME	Nazwisko pracownika — przechowywane w zapisie dużymi literami
JOB	Etat, stanowisko pracy
MGR	Identyfikator szefa (czyli EMPNO we wierszu szefa)
HIREDATE	Data zatrudnienia
SAL	Pensja
COMM	Prowizja naliczona od początku roku, dotyczy pracowników zatrudnionych na stanowisku SALESMAN
DEPTNO	Numer departamentu w którym zatrudniony jest pracownik. Wartość w tym polu musi odpowiadać jednemu i tylko jednemu wierszowi w tabeli DEPT

Tabela SALGRADE — tabela „widełek” zaszeregowania

Kolumna	Opis
GRADE	Numer grupy zaszeregowania
LOSAL	LOW SALARY — dolna granica widełek płacowych dla stawki zaszeregowania GRADE
HISAL	HIGH SALARY — górna granica widełek

Podstawy

Wykonanie Raportu w zaprojektowanego Reports Designerze 2.5 polega w na wykonaniu instrukcji SELECT na serwerze. Uzyskane w wyniku tej lub tych instrukcji dane otrzymuje klient, można zauważyć, że otrzymywane przez niego dane mają formę „tabelki” która podlega dalszej obróbce na kliencie. Oprócz szaty graficznej na komputerze-kliencie wykonywane są niektóre operacje sumaryczne, obliczenia korzystające z wbudowanych komponentów lub samodzielnie napisanych procedur i funkcji.

Zapytanie i model danych

Reports Designer składa się z wielu obiektów. Przyjrzymy się ich roli i własnościom tworząc elementarny raport.

Model danych - model danych to graficzny obraz danych jako zapytania, lub zapytań które zostaną wykonane oraz obliczeń którym te dane zostaną poddane.

Wchodzimy do edytora modelu danych klikając na ikonę Data Model. Następnie sięgamy po narzędzie SQL, przy jego pomocy rysujemy zaokrąglony prostokąt.

Wchodzimy do właściwości tego obiektu (prawy przycisk myszy). Przed nami ukazuje się następujące okienko:

Wypełniamy je w sposób pokazany na rysunku. Poszczegolne właściwości oznaczają:

• Name - nazwa zapytania wykorzystywana jest dalej do nazwania grup danych.

• Maximum Rows - własność użyteczna tylko w przypadku testowania raportu na małej ilości wierszy, w sytuacji kiedy zapytanie wykonuje się bardzo długo - należy pamietać aby usunąć te wartość przed wysłaniem raportu do klienta.

• External Query - możliwe jest zapisanie tekstu polecenia w oddzielnym pliku.

Klikamy na Applay(Zastosuj). W modelu danych poniżej czarnego zaokrąglonego prostokąta zapytania pojawił się prostokąt grupy. Jeżeli nazwaliśmy zapytanie Q_emp to uzyskaliśmy grupę G_emp. Dalej konsekwetnie ta nazwa zostanie odziedziczona przez graficzny obiekt - ramkę, będzie to ramka R_emp.

Rozkład domyślny

Rozkład raportu - jest to graficzny projekt przyszłego raportu, zawierający obiekty określające sposób (kolor, kierunek itp) drukowania obiektów wystepujących w modelu danych.

Tworzenie rozkładu raportu rozpoczynamy od kliknięcia na ikonę 
 Default Layout (Rozkład Domyslny). Ukazuje nam się okno w którym wybieramy Tabular (Tabelaryczny) i klikamy na przycisk OK.

Teraz pownien nam się ukazać rozkład gdzie poniżej etykiet leżą pola raportu. W przypadku gdy danych w tabeli jest bardzo dużo lub szerokość strony jest waska może do zawinięcia danych na rozkładzie - tzn. etykiety i pola które nie zmieściły się w pierwszym wierszu umieszczane są w odpowiadających im ramkach rozpoczynając od lewej strony.

Modyfikacje rozkładu domyślnego

Jeżeli generator przygotował nam rozkład domyślny możemy skorzystać z narzędzi do kolorowania obiektów. Są to po kolei wypełnianie obiektów kolorem, obrysowywanie obiektów kolorem, kolorowanie trzcionek.

Zmiany formatów czcionki dokonujemy z menu Format/Font. Przykład zmodyfikowanego rozkładu domyślnego znajduje się na poniższej ilustracji.

Uruchomienie raportu

Jeżeli rozkład odpowiada naszym oczekiwaniom możemy go uruchomić. Ikoną
uruchamiamy raport, pojawia się tak zwana formatka uruchomienia raportu. Na formatce która się pojawiła klikamy na „Run Report”.

Teraz przed nami powinien pojawić się wykonany raport.

Widzimy że zapytanie które w SQL+ było w prostej formie tekstowej, teraz jest “ubrane” w pewną formę graficzną. Wygenerowany “wydruk” możemy zamknąć(CLOSE) aby przyjrzeć się strukturze ramek. Aby lepiej było ją widać rozsunąłem ramki i pokolorowałem je nieco intensywniej:

Ramka czerwona to ramka obejmująca jej rola to pilnowanie położenia obiektów. Znaki “dzwonek” na jej bokach to oznaczenie iż posiadaja zdolność do rozszerzania się w pionie.

Ramka koloru zielonego to ramka która drukuje się dla każdego wiersza wybranego w zapytaniu. Ciemna strzałka na jej krawędzi ramki określa kierunek drukowania.

Łamanie i funkcje grupowe

Model danych

Operacja łamania może kojarzyć się z grupowaniem w SQL. Weźmy raport zbudowany na:

SELECT *

FROM emp;

Dane pochodzące z tego raportu „połammy” ze względu na numer departamentu i zawód.

Powyższy wykres uzyskamy wyciągając z G_emp pola deptno i job.

Jednak różnica polega na tym że w odróżnieniu od grupowania łamanie nie powoduje utraty dostępu do danych szczegółowych.

Teraz należy jeszcze „nazwać” grupy i uporządkować rozkład np. w następujący sposób:

Dobranie rozkładu domyślnego

Do sporządzonego modelu danych możemy przygotować dwa rozkłady. Jeżeli wybierzemy rozkład tabelaryczny to uzyskamy poniższy efekt: (Wartość rozpoczynająca każdą nową grupę jest drukowana tylko raz).

Ze względu na wielkość prezentowany jest poniżej jedynie fragment rozkładu

nadrzędny-podrzędny (master-detail), rozkład wyglądający następująco:

Przygotowany w tym raporcie rozkład warto zachować do wykorzystania w następnym rozdziale.

Funkcje grupowe w raportach

W tytule tego rozdziału pojawiło się hasło funkcje grupowe. Z tego typu funkcjami mogliśmy mieć wcześniej do czynienia w SQL'u. Czy jest to więc drugi raz to samo? Okazuje się że nie. Funkcje grupowe wykorzystywane w SQL redukowały każdą grupę do jednego wiersza. Dane z serwera miały być zwrócone w postaci tabelki, nie mieliśmy możliwości odniesienia się do poszczególnych elementów z grup. W funkcjach grupowych w raportach działamy na kliencie, nie tracimy informacji o elementach grup.

Podsumowania

Aby zrozumieć istotę działania funkcji grupowych przeanalizujmy następujący problem. Dla modelu danych z poprzedniego rozdziału przygotujmy raport z podsumowaniami:

CS_total - sumaryczna kwota pensji w firmie

CS_deptno - sumaryczna kwota pensji w każdym departamencie

CS_job - sumaryczna kwota pensji w każdym departamencie przypadająca na dane stanowisko.

Żądane sumy musimy przygotować w Modelu Danych. W tym celu rozciągam nieco w dół prostokąty G_deptno i G_job tak abym mógł korzystając z narzędzia
umieścić na każdym z poziomów sumy.

Pierwsza suma CS_job niech będzie sumą SAL zerowaną co G_job.

Kolejna suma CS_deptno niech będzie sumą w obrębie departamentu. Mogła by to być również suma po kolumnie sal ale zredukujemy ilość obliczeń czyniąc ja sumą po CS_job zerowaną co G_DEPTNO. Sumaryczna kwota wypłacanych pensji w firmie to oczywiście CS_total zerowana dla raportu i sumująca sumy wyliczone w poszczególnych departamentach.(Cechą intuicyjnie nie do końca oczywistą jest fakt iż CS_total leży niejako poza bloczkiem zapytania.)

Inne funkcje grupowe

Inne funkcje dostępne oprócz sumy to min.:

• średnia

• ilość elementów

• procent całości

• min

• max

• pierwszy z grupy

• ostatni z grupy

• wariancja

Rozkład

Do tak przygotowanego modelu proponuje wybrać rozkład nadrzędny-podrzędny. Wydruk raportu możemy mieć następującą formę (fragment):

Łączenie zapytań

Raport nadrzędny-podrzędny

Inna metoda uzyskania raportu master-detail dla tablic Dept i Emp to połączenie dwóch zapytań za pomocą narzędzia Link.

- narzędzie za pomocą którego łączymy zapytania

Za pomocą wspomnianego narzędzia ciągniemy linie od deptno w Q_dept do deptno w Q_emp.

Teraz dla każdego znalezionego wiersza w pierwszym zapytaniu uruchamiane będzie przeszukiwanie tablicy emp.

Struktura raportu jest może nieco przejrzystsza niż przy zredukowanej ilości zapytań. Niejednokrotnie również raport działa szybciej szczególnie wtedy gdy wielkość i ilość pozyskiwanych danych w zapytaniu nadrzędnym jest stosunkowo duża a w podrzędnym mniejsza.

Kolumny formuł i wypełniające

W tym rozdziale zapoznamy się z dwoma obiektami modelu danych:

- kolumna formuły

- kolumna wypełniająca

Kolumny formuł

Kolumna formuły to funkcja liczona każdorazowo przy nowej instancji elementu tabeli lub grupy łamania. Raport którego budowę prześledzimy w tym rozdziale powstał poprzez usuniecie grupy G_job i kolumny sumującej w tej grupie.

Korzystając z ikony umieszczamy w grupie G_emp funkcje. Przyjmijmy, że jest to funkcja obliczająca pensje pracownika brutto w ciągu całego roku. Funkcja zwraca wartość v_brutto dla każdego nowego wiersza wybranego instrukcją SELECT.

function CF_bruttoFormula return Number is

v_annual NUMBER(9,2);

begin

v_annual := 12*:sal+nvl(:comm,0);

return v_annual;

end;

Zwróćmy uwagę że jeżeli w kodzie pojawia się odniesienie do zmiennej zewnętrznej stosujemy notację z dwukropkiem.

Kolumna wypełniająca

Funkcja zwraca jedną wartość, przypuśćmy jednak że zależy nam aby w tej funkcji wyliczyć dwie wartości. Można by taki efekt uzyskać gdyby na poziomie każdego wiersza istniała zmienna której wartość mogłaby być ustawiana na poziomie każdego wiersza pobranego w zapytaniu. Rolę takiej zmiennej może pełnić pełni kolumna wypełniająca. Umieśmy kolumnę CP_ofe na poziomie grup G_emp. Teraz zmodyfikujmy ciało funkcji tak aby CP_ofe stanowiło 40 % kwoty zarobków pracownika.

function CF_bruttoFormula return Number is

v_annual NUMBER(9,2);

begin

v_annual := 12*:sal+nvl(:comm,0);

:CP_ofe := 0.4*v_annual;

return v_annual;

end;

Skoro raport dotyczy zarobków rocznych logicznym jest aby zmienić źródło sumowania CS_deptno z sal na CF_brutto.

Ponieważ nastąpiło wiele zmian w modelu danych najłatwiej będzie przygotować nowy rozkład korzystając z rozkładu domyślnego.

Wyjątki

Przyjrzyjmy się następującej sytuacji. Niech będzie dany raport wybierający wszystkie dane wiersze z tablicy emp. W procedurze CF_mgr_name chcielibyśmy obliczać nazwiska wybranych pracowników.

Raport składa się z jednej grupy w której umieszczamy procedurę następującej treści:

function CF_mgr_nameFormula return Char is

v_mgr_ename emp.ename%TYPE;

begin

SELECT ename

INTO v_mgr_ename

FROM emp

WHERE empno = :mgr;

return v_mgr_ename;

end;

Czytelnika nie znającego PL/SQL zastanowiać może deklaracje v_mgr_name. Ten rodzaj deklaracji to chrakterystyczny właśnie dla PL/SQL, zmienna będzie takiego typu jak kolumna ename w tablicy emp.

Procedura kompiluje się tak jak i cały raport. Przygotowujemy rozkład domyślny i uruchamiamy raport i ... dostajemy informację o wystąpieniu sytuacji wyjątkowej.

Jaka to sytuacja wyjątkowa? Oczywiście KING pracownik który nie posiada szefa. Tę sytuację należy w jakiś sposób obsłużyć, dokonujemy tego w bloku obsługi wyjątków. Sytuacja wyjątkowa pojawia się w instrukcji SELECT ...INTO..., następnie sterowanie przechodzi do bloku obsługi wyjatków.

function CF_mgr_nameFormula return Char is

v_mgr_ename emp.ename%TYPE;

begin

SELECT ename

INTO v_mgr_ename

FROM emp

WHERE empno = :mgr;

return v_mgr_ename;

exception

when NO_DATA_FOUND then

v_mgr_ename := 'THE BOSS';

return v_mgr_ename;

end;

Zapobiegliwy programisty mógłby jeszcze dodać obsługę wszystkich innych wyjątków:

function CF_mgr_nameFormula return Char is

v_mgr_ename emp.ename%TYPE;

begin

SELECT ename

INTO v_mgr_ename

FROM emp

WHERE empno = :mgr;

return v_mgr_ename;

exception

when NO_DATA_FOUND then

v_mgr_ename := 'THE BOSS';

return v_mgr_ename;

when OTHERS then

v_mgr_ename := 'STRANGE !!!';

return v_mgr_ename;

end;

Ostatecznie interesujący nas raport będzie miał formę jak na poniższym rysunku(fragment):

Raport Macierzowy

Raport macierzowy na trzech zapytaniach

Raport macierzowy to rodzaj raportu w którym dla pewnych dwóch wymiarów drukowanych jeden w poziomie, drugi w pionie na ich przecięciu umieszczane są wartości przecięcia.

Przygotowanie raportu rozpocznijmy od skonstruowania zapytań wybierających wymiary przyszłego raportu. Niech Q_jobs wybiera wszystkie zawody w firmie, a Q_depts wszystkie numery i nazwy departamentów.

Zapytania będą miały postać:

SELECT DISTINCT job

FROM emp;

SELECT deptno, dname

FROM dept;

Trzecie zapytanie Q_values musi przygotowywać trójki x,y, f(x,y). Wartość f(x,y) będzie to w naszym przypadku ilość osób w danym departamencie na danym stanowisku, równie dobrze mogła by to być sumaryczna czy średnia wartość zarobków itp.

SELECT deptno, job, count(*)

FROM emp

GROUP BY deptno, job;

Teraz dwa pierwsze zapytania należy przykryć obiektem zwanym produktem krzyżowym.

Teraz rozciągamy prostokąt produktu krzyżowego tak aby przykryć grupy G_jobs i G_depts. Tak utworzoną nową grupę nazywam G_matrix.

Teraz przy pomocy
łączę odpowiednie pola z G_matrix do odpowiednich pól G_values.

Teraz możemy przystąpić do generacji domyślnego rozkładu - będzie to oczywiście rozkład macierzowy(matrix). Warto zwrócić uwagę, że pozostałe domyślne ustawienia wymagają przestawienia.

Na drugiej zakładce musimy wybrać która grupa będzie drukowana wszerz. W rozkładzie domyślnym na przecięciu deptno i job wpisywane są zarówno deptno, job, count(*) z dwóch pierwszych wartości warto zrezygnować - raport stanie się czytelniejszy. Również przestrzeń rezerwowana na wyniki obliczeń count(*), 40 znaków jest stanowczo za duża, nawet gdyby w emp było do 1000 rekordów wystarczyły by 3 znaki.

Bo dokonaniu tych zmian wygenerujmy rozkład.

W rozkładzie tym również dokonuję kilku zmian, o znaczeniu raczej kosmetycznym dzięki czemu raport stanie się czytelniejszy. Przede wszystkim należy wprowadzić ramki dzięki czemu wyraźnie widać kolumny i wiersze. Usuwam również szablon tekstowy count.

W wyniku tych zmian ostatecznie otrzymuję.

Problem pustych miejsc

Jednym z problemów w raportach Developera 2000 który często pojawia się na listach dyskusyjnych są puste miejsca. Wynikają one z tego, iż wartości raportów ustalone są tylko tam gdzie dla x i y istnieje pewna wartość f(x,y). Jak jeżeli przyjrzymy się poniższemu zapytaniu widzimy, że wyznacza ono tylko 9 wartości. Oznacza to że 11 komórek raportu pozostanie pustych.

SQLWKS> SELECT deptno, job, count(*)

2> FROM emp

3> GROUP BY deptno, job;

DEPTNO JOB COUNT(*)

---------- --------- ----------

10 CLERK 1

10 MANAGER 1

10 PRESIDENT 1

20 ANALYST 2

20 CLERK 2

20 MANAGER 1

30 CLERK 1

30 MANAGER 1

30 SALESMAN 4

9 wierszy wybranych.

Istnieją sztuczki graficzne typu graficzne zero które pokazuje się we wszystkich nie zadrukowanych komórkach. Sztuczka ta czasem się udaje ale jest to uzależnione np. od sterowników drukarki. Formalne rozwiązanie tego problemu polega jednak na takim zmodyfikowaniu zapytania aby wybierało te dodatkowe zera dla tych przecięć deptno i job dla których nie ma żadnej danej.

SELECT deptno, job, count(*)

FROM emp

GROUP BY deptno, job

UNION

SELECT D.deptno, E.job, 0

FROM dept D, emp E

WHERE (D.deptno,E.job) NOT IN (SELECT deptno, job

FROM emp

GROUP BY deptno, job);

Powyższe zapytanie sumuje poprzez UNION [DISTINCT] te przecięcia dla których są dane z zapytaniem, w którym uzyskano iloczyn kartezjański kolumny job i wszystkich numerów departamentów eliminując zbędne zestawienia poprzez umieszczenie nieco zmodyfikowanego pierwszego zapytania w klazuli WHERE zapytania drugiego. Duplikaty zwracane przez drugie zapytanie zostały wyeliminowane przez UNION.

Zmodyfikowane zapytanie wybiera.

DEPTNO JOB COUNT(*)

---------- --------- ----------

10 ANALYST 0

10 CLERK 1

10 MANAGER 1

10 PRESIDENT 1

10 SALESMAN 0

20 ANALYST 2

20 CLERK 2

20 MANAGER 1

20 PRESIDENT 0

20 SALESMAN 0

30 ANALYST 0

30 CLERK 1

30 MANAGER 1

30 PRESIDENT 0

30 SALESMAN 4

40 ANALYST 0

40 CLERK 0

40 MANAGER 0

40 PRESIDENT 0

40 SALESMAN 0

Modyfikacja zapytania nie spowoduje zmiany działania raportu i konieczności modyfikowania raportu. Po rozbudowaniu zapytania możemy od razu obejrzeć oczekiwane zera na ekranie.

Raport o zredukowanej ilości zapytań

Spróbujmy stworzyć ten sam raport na mniejszej ilości zapytań. Zatem jedno zapytanie musi zwracać trójki.

Z bloczek zwracający trójki musimy połamać.

Teraz na dwie pierwsze grupy nakładamy produkt krzyżowy. Po uporządkowaniu uzyskuję:

Funkcjonalnie raport nie różni się od poprzedniego. Problemem może być ujedynie uzyskanie deptno za pomocą kolumny formuły.

Gdzie ciało funkcji:

function CF_dnameFormula return Char is

v_dname varchar2(20);

begin

SELECT dname

INTO v_dname

FROM dept

WHERE deptno = :deptno1;

return v_dname;

end;

Druga możliwość to modyfikacja zapytania na przykład do postaci:

SELECT dname, job, count(*)

FROM emp E, dept D

WHERE E.deptno=D.deptno

GROUP BY dname, job

UNION

SELECT D.dname, E.job, 0

FROM dept D, emp E

WHERE (D.deptno,E.job) NOT IN (SELECT deptno, job

FROM emp

GROUP BY deptno, job);

Programowe sterowanie wydrukiem

Programowe wyłączenie

Zdarza się że dopiero w czasie drukowania raportu pewne ramki dla pewnych danych nie muszą być w ogóle drukowane. Może to być np. raport opisujący korzystanie z pewnych usług. W sumie ukazuje się, że osoby korzystające z tych usług mogły to robić, za darmo i nie są nam na wydruku potrzebne.

Dla przykładu spróbujmy wyeliminować KINGA z wydruku. W tym celu w możemy przerobić raport w którym w funkcji znajdowaliśmy nazwisko szefa. Stajemy na ramce R_emp i wyświetlamy jej właściwości. Przechodzimy na drugą zakładkę.

Następnie klikamy na w bloku Foramat Trigger na przycisk Edit. Pokazuje się nam okienko do wpisywania kodu PL/SQL. Wpisujemy:

function R_empFormatTrigger return boolean is

begin

if :mgr is null then

return (FALSE);

else

return (TRUE);

end if;

end;

Aby przekonać się że wyzwalacz faktycznie jedynie ukrywa (usuwa z drukowani) ramki R_emp warto umieścić sumę pensji wszystkich pracowników. Wtedy wyraźnie widać, że pensja KINGA, 5000 jest brana pod uwagę w obliczeniach, ale sam wiersz KINGA nie jest drukowany.

Formatowanie warunkowe

Jeżeli chcielibyśmy pewne rekordy wyróżnić na wydruku np. pracownicy o dochodach poniżej tysiąca byli drukowani innym atrybutem wizualnym korzystamy z funkcji z wbudowanego pakietu SRW.

W wymienionym na poprzedniej stronie wyzwalaczu umieszczamy kod który zmieni kolor drukowania wszystkich tych ramek wewnątrz których pensja jest mniejsza od 1000.

function R_empFormatTrigger return boolean is

begin

if :sal < 1000 then

srw.attr.bfcolor := 'cyan';

srw.set_attr(0, srw.attr);

end if;

return (TRUE);

end;

Warto zaglądnąć do pakiety SRW nie tylko wtedy kiedy programowo zmieniamy kolory czy czcionkę ale również wtedy kiedy chcemy wyświetlić w czasie wykonywania raportu wiadomość czy wykonać w bazie instrukcje DML (np. po to aby odnotować w jakiejś tablicy fakt uruchomienia raportu).

Uruchamianie raportu z parametrem

Parametr uruchomienia raportu.

Raport może sam pobierać swoje parametry z formatki wywołania raportu lub może je otrzymać z .zewnętrznego uruchamiającego go programu. Wtedy formatka parametrów nie jest pokazywana.

Zapytanie z parametrem

Nowy parametr użytkownika możemy utworzyć w nawigatorze obiektów lub podać w zapytaniu.

Warto zwrócić uwagę, że przed PARAMETR_DEPTNO występuje dwukropek.

SELECT *

FROM emp

WHERE deptno = :PARAMETR_DEPTNO;

Jeżeli podaliśmy parametr bezpośrednio w zapytaniu Raports_Designer stworzy go automatycznie. Trzeba jedynie zwrócić uwagę czy jest on oczekiwanego przez nas typu.

Tak utworzony parametr pojawia się na formatce uruchomienia raportu.

To rozwiązanie w zupełności wystarcza jeżeli raport jest gdzieś indziej podpinany. Jeżeli jednak raport funkcjonuje jako samodzielny program. Warto byłoby dołożyć do niego rozwijaną listę z której użytkownik wybierałby departament dla którego chce uruchomić raport. W tym celu musimy wrócić do parametrów uruchomienia raportów i wejść w jego właściwości.

Jeżeli zaznaczymy opcję SELECT okienko zmieni swoja postać.

Teraz mamy możliwość wpisania zapytania. Oraz dwie opcje do wyboru. Możliwości użytkownika są ograniczone jedynie do tego co wybiera lista (Restrict List to Predetermined Values) i Hide First Column jeżeli nie chcemy pokazywać użytkownikowi kluczy. Jeżeli chcemy aby użytkownik miał możliwość pominąć ten parametr i uruchamiać raport dla wszystkich departametów musimy odznaczyć Restrict List to Predetermined Values i zmodyfikować główne zapytanie raportu:

SELECT *

FROM emp

WHERE

(deptno = :PARAMETR_DEPTNO OR :PARAMETR_DEPTNO is null);

Teraz z okienka parametrów można skasować wartość w polu DEPTNO i raport uruchomi się dla wszystkich.

Druga możliwość to zmodyfikowanie listy przy użyciu UNION tak aby pojawiała się dodatkowa pusta wartość.

Przekazanie fragmentu zapytania

Druga możliwość przekazania sparametryzowania raportu to przekazywanie fragmentu zapytania. Tym razem trzeba obowiązkowo rozpocząć od nawigatora obiektów. Dodajemy nowy parametr np. o nazwie PARMETR_DOKLEJENIE typu znakowego od razu ustawiając jego wartość np. na 1=1.

Teraz możemy rozbudować raport z poprzednich stron. W zapytaniu dodajemy:

SELECT *

FROM emp

WHERE

(deptno = :PARAMETR_DEPTNO OR :PARAMETR_DEPTNO is null) and

(&PARAMETR_DOKLEJENIE);

Wartość domyślna(1=1) używana jest na etapie parsowania i sprawdzania poprawności instrukcji SELECT.

W czasie uruchamiania raportu, zawsze poprawną wartość domyślną zamieniamy na interesujący nas warunek.

Ten sposób jest czasem wykorzystywane przez programistów przy czym użytkownik widzi np. pewne grup klasyfikacji klientów, wybiera je z listy, a jest to równoznaczne z wykonaniem pewnego zapytania do tablicy w której trzymane są fragmenty zapytań. Oczywiście należy jeszcze zadbać o używanie tych samych aliasów i o to aby grupy klasyfikacji itp. pokazywały się odpowiednio do treści zapytania zawartego w raporcie.

Suplement

W tym rozdziale przedstawione zostaną zagadnienia które nie zmieściły się poprzednich rozdziałach.

Data bieżąca

Źródłem częstego zaskoczenia programisty jest wbudowana data bieżąca. Jeżeli sięgniemy po pole typu
w edytorze rozkładu, to w polu tym jako źródło wypełniania możemy podać &Current Date.

Jeżeli zarówno nasz serwer jak i komputer mają zgodnie i prawidłowo ustawiony czas nigdy nie zauważymy, że nie jest to niestety data z serwera ale data z systemu operacyjnego. Jeżeli użytkownik z jakiś względów utrzymuje na komputerze niewłaściwą datę (np. ze względu na pirackie oprogramowanie) to drukuje raporty ze złą datą. Zdarzają się też przypadki, że użytkownicy którzy posługiwali się takimi raportami celowo cofali datę aby przygotować raporty które powinny być wydrukowane wcześniej.

Aby zmusić raport do pisania właściwej daty z serwera należy napisać samodzielnie funkcję (kolumnę formuły), która wykorzysta:

select sysdate

into ....

from dual;

Tryb dopasowania

W czasie dostosowywania rozkładu domyślnego do naszych potrzeb tracimy wiele czasu na rozciąganie ramek. Pracę z ramkami możemy jednak znacznie uprościć, jeżeli skorzystamy z Trybu Dopasowania. Ikonka odpowiedzialna za ten tryb ma dwie postaci:

- włączony tryb dopasowania

- wyłączony tryb dopasowania.

Zanim projektant raportu zapozna się z tymi dwoma ikonkami (czasami nie następuje to nigdy) rozciąga ramki aby zrobić miejsce na nowy obiekt. Okazuje się, że jeżeli tryb dopasowania jest włączony, przesuwany obiekt sam toruje sobie drogę spychając lub powiększając obiekty które stoją na jego drodze. Obiekt cofając się działa w sposób odwrotny.

W domyślnym, a więc wyłączonym trybie dopasowania przesuwane pole tekstowe dosuwa się do ograniczającej jego grupę ramki i dalej nie może się przesunąć.

Rozkład listowy

Rozkład listowy do taki w którym występuje pewien tekst w którym pojawiają się nazwy pól poprzedzone znakiem &. W czasie wykonywania raportu pola te wypełniają się za pomocą pól, które są poziomo zmienne i ukryte. Najczęściej element ten stosowany jest do drukowania nagłówków i stopek raportów.

Na poniższym rysunku znajduje się pewien tekst a obok niego trzy ukryte, poziomo zmienne pola.

Warto zwrócić uwagę że fragmenty tekstu wklejanego „dziedziczą” własności z obiektów ukrytych, nazwisko pojawia się pogrubione, a job i hiredate - kursywą.

Kierunek tekstu

Ponieważ pola
nie chcą się obracać (rotate), a czasem chcielibyśmy wydrukować je w pionie , można wykorzystać w tym celu pola tekstowe(T) które pozwalają się obracać.

Wyzwalacze Raportu

Istnieje pięć sytuacji które możemy obsłużyć w czasie wykonywania raportu. Są to po kolei:

Before Parameter Form

After Parameter Form

Before Report

Between Pages

After Report

Dzięki tym wyzwalaczom możemy kontrolować kiedy z jakiego komputera były uruchamiane raporty. W wyzwalaczach tych możemy stosować instrukcje DML, wykonanie instrukcji DDL wymaga użycia pakietu.

function BeforeReport return boolean is

V_instrukcja varchar2(150);

V_numer varchar2(5);

begin

SELECT to_char(dziennik_seq.nextval)

INTO V_numer

FROM dual;

V_instrukcja := 'CREATE table dziennik'||V_numer||'(dzi_id NUMBER,kto VARCHAR2(40),kiedy DATE)';

--Ponizej wykonuje instrukcje na serwerze tworzac tymczasowa tabele ...

SRW.do_sql(V_instrukcja);

--Ponizej bezposrednio wykonuje instrukcje DDL(insert,commit)

INSERT INTO dziennik values(dziennik_seq.nextval,' PRZED RAPORTEM',SYSDATE);

commit;

return (TRUE);

end;

Wyjątkiem od tej sytuacji może być struktura klient-serwer raportów- serwer. Tego typu strukturę stosuje się w przypadku gdy klient jest mało wydajny.

Efekt łamania i obliczania podsumowań jest dostępny w skryptach SQL+ - łamanie instrukcją BRAKE ON, a obliczanie instrukcją COMPUTE jednak ze względu na istnienie programów graficznych są one prawie zupełnie niewykorzystywane.

NP. SELECT job, max(sal) FROM emp GROUP BY job; - nie możemy w tej instrukcji dopisać kolumny ename po słowie SELECT .... - w koneksie grupy ta informacja jest niedostępna.

Można wyobrazić sobie raport macierzowy dla trzech i więcej wymiarów jednak ze względu na dwuwymiarowość papieru może być to tylko np. raport gdzie każdy nowa wartość zmiennej Z spowoduje np. otwarcie nowej strony raportu.

Na następnych stronach będzie opisana konstrukcja raportu macierzowego wyłącznie na jednym zapytaniu.

UNION - domyślnie UNION jest to UNION DISTINCT czyli suma zbiorów w sensie znanym na z matematyki, nie domyślne zachowanie UNION można by uzyskać poprzez zastosowanie UNION ALL wtedy w sumie elementy powtarzające się pozostałyby.

Podstawy

Budowa raportów

Strona 2

Strona 3

Podstawy

Podstawy

Strona 47

---