Podstawy Budowy Raportów W Oracle Reports 2.5 Cwiczenia

Podstawy

Strona 2

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

Ł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

Łą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

Budowa raportów

Strona 3

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

Podstawy

Strona 6

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 raportów

Strona 7

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

Podstawy

Strona 8

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

Strona 10

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ą:

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.

Budowa raportów

Strona 11

• 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.

Podstawy

Strona 12

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.

Budowa raportów

Strona 13

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.

Podstawy

Strona 16

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.

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.

Budowa raportów

Strona 17

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).

Podstawy

Strona 18

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.

Podstawy

Strona 20

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.

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.

Budowa raportów

Strona 21

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):

Podstawy

Strona 24

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ść

Podstawy

Strona 28

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.

Budowa raportów

Strona 29

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.

Podstawy

Strona 30

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:

Podstawy

Strona 34

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.

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.

Budowa raportów

Strona 35

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.

Podstawy

Strona 36

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ę.

Budowa raportów

Strona 37

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

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

Strona 38

        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ć.

Budowa raportów

Strona 39

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

Podstawy

Strona 40

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);

Podstawy

Strona 42

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

Budowa raportów

Strona 43

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).

Podstawy

Strona 46

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.

Budowa raportów

Strona 47

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ć.

Podstawy

Strona 48

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ść.

Budowa raportów

Strona 49

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.

Podstawy

Strona 52

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;

Budowa raportów

Strona 53

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ą.

Podstawy

Strona 54

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)