Materiały szkoleniowe
Podstawy budowy
raportów w Oracle Reports 2.5
ćwiczenia
 Podstawy
Spis treści
Zawartość tabel wykorzystywanych na kursie __________________________________ 5
Zawartość tabeli DEPT ________________________________________________________6
Zawartość tabeli EMP _________________________________________________________6
Zawartość tabeli SALGRADE __________________________________________________6
Budowa tabel wykorzystywanych na kursie _______________________________________7
Podstawy________________________________________________________________ 9
Zapytanie i model danych _____________________________________________________10
Rozkład domyślny ___________________________________________________________11
Modyfikacje rozkładu domyślnego _____________________________________________12
Uruchomienie raportu ________________________________________________________12
A
amanie i funkcje grupowe ________________________________________________ 15
Model danych _______________________________________________________________16
Dobranie rozkładu domyślnego ________________________________________________17
Funkcje grupowe w raportach _____________________________________________ 19
Podsumowania ______________________________________________________________20
Inne funkcje grupowe ________________________________________________________21
Rozkład ____________________________________________________________________21
A
ączenie zapytań ________________________________________________________ 23
Raport nadrzędny-podrzędny__________________________________________________24
Kolumny formuł i wypełniające ____________________________________________ 27
Kolumny formuł _____________________________________________________________28
Kolumna wypełniająca _______________________________________________________28
Wyjątki ____________________________________________________________________29
Raport Macierzowy ______________________________________________________ 33
Raport macierzowy na trzech zapytaniach _______________________________________34
Problem pustych miejsc _______________________________________________________37
Raport o zredukowanej ilości zapytań ___________________________________________38
Strona 2
Budowa raportów
Programowe sterowanie wydrukiem _________________________________________ 41
Programowe wyłączenie ______________________________________________________ 42
Formatowanie warunkowe ____________________________________________________ 43
Uruchamianie raportu z parametrem ________________________________________ 45
Parametr uruchomienia raportu. ______________________________________________ 46
Zapytanie z parametrem _____________________________________________________ 46
Przekazanie fragmentu zapytania ______________________________________________ 49
Suplement ______________________________________________________________ 51
Data bie\
ąca ________________________________________________________________ 52
Tryb dopasowania ___________________________________________________________ 53
Rozkład listowy _____________________________________________________________ 53
Kierunek tekstu _____________________________________________________________ 54
Wyzwalacze Raportu ________________________________________________________ 54
Strona 3
Zawartość tabel wykorzystywanych na kursie
 Podstawy
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
Strona 6
Budowa raportów
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
Strona 7
 Podstawy
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
Strona 8
Budowa raportów
Podstawy
Strona 9
 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 kliencie1. 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ą:
1
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.
Strona 10
Budowa raportów
" 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.
Strona 11
 Podstawy
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.
Strona 12
Budowa raportów
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.
Strona 13
A
amanie i funkcje grupowe
 Podstawy
Model danych
Operacja łamania mo\
e kojarzyć się z grupowaniem w SQL. Wez
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.2
2
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.
Strona 16
Budowa raportów
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).
Strona 17
 Podstawy
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.
Strona 18
Budowa raportów
Funkcje grupowe w raportach
Strona 19
 Podstawy
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.3 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.
3
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.
Strona 20
Budowa raportów
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):
Strona 21
 Podstawy
Strona 22
Budowa raportów
A
ączenie zapytań
Strona 23
 Podstawy
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ść
Strona 24
Budowa raportów
pozyskiwanych danych w zapytaniu nadrzędnym jest stosunkowo du\
a a w podrzędnym
mniejsza.
Strona 25
 Podstawy
Strona 26
Budowa raportów
Kolumny formuł i wypełniające
Strona 27
 Podstawy
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ć z
ródło sumowania
CS_deptno z sal na CF_brutto.
Strona 28
Budowa raportów
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.
Strona 29
 Podstawy
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;
Strona 30
Budowa raportów
Ostatecznie interesujący nas raport będzie miał formę jak na poni\
szym
rysunku(fragment):
Strona 31
 Podstawy
Strona 32
Budowa raportów
Raport Macierzowy
Strona 33
 Podstawy
Raport macierzowy na trzech zapytaniach
Raport macierzowy to rodzaj raportu w którym dla pewnych dwóch4 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ójki5 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.
4
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.
5
Na następnych stronach będzie opisana konstrukcja raportu macierzowego wyłącznie na jednym zapytaniu.
Strona 34
Budowa raportów
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.
Strona 35
 Podstawy
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 wyraz
nie widać kolumny i wiersze. Usuwam równie\
szablon tekstowy count.
W wyniku tych zmian ostatecznie otrzymuję.
Strona 36
Budowa raportów
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 UNION6.
Zmodyfikowane zapytanie wybiera.
DEPTNO JOB COUNT(*)
---------- --------- ----------
10 ANALYST 0
10 CLERK 1
10 MANAGER 1
6
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.
Strona 37
 Podstawy
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ć.
Strona 38
Budowa raportów
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
Strona 39
 Podstawy
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);
Strona 40
Budowa raportów
Programowe sterowanie wydrukiem
Strona 41
 Podstawy
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 wyraz
nie widać, \
e
Strona 42
Budowa raportów
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).
Strona 43
 Podstawy
Strona 44
Budowa raportów
Uruchamianie raportu z parametrem
Strona 45
 Podstawy
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.
Strona 46
Budowa raportów
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ć.
Strona 47
 Podstawy
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ść.
Strona 48
Budowa raportów
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.
Strona 49
 Podstawy
Strona 50
Budowa raportów
Suplement
Strona 51
 Podstawy
W tym rozdziale przedstawione zostaną zagadnienia które nie zmieściły się poprzednich
rozdziałach.
Data bie\
ąca
y
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 z
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;
Strona 52
Budowa raportów
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ą.
Strona 53
 Podstawy
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)
Strona 54
Budowa raportów
INSERT INTO dziennik values(dziennik_seq.nextval,' PRZED RAPORTEM',SYSDATE);
commit;
return (TRUE);
end;
Strona 55