Temat: [ORACLE] raport "na kliknięcie" z wykresami w sqlplus

Zastanawiające jest to, jak w czasach rozbudowanych narzędzi BI przydają się najprostsze sposoby generowania raportów. Jednym z takich rozwiązań jest niewątpwlie użycie sqlplus'a. Opiszę bardzo prosty sposób generowania raportów "na kliknięcie", ale wzbogacony dodatkowo wykresami. Zacznijmy więc od konfiguracji stacji roboczej tak, aby kliknięcie w skrypt uruchamiało sesję sqlplus. Proponuję przygotować plik wsadowy i przypisać jako domyślny przy wywołaniu skryptów SQL.

sqlplus /nolog @%1

W efekcie, po dwukliku na dowolnym skrypcie SQL, zostanie uruchomiony sqlplus (bez domyślnego logowania do BD) i następnie wywołany do uruchomienia wybrany skrypt. Obiecałem wykresy, a w
tym celu polecam Google Chart API. Wykresy generowane będą dynamicznie, więc nie trzeba dystrybuować niczego, poza plikiem HTML. Wyobraźmy sobie, że chcemy prezentować wartości pewnych wskaźników za pomocą odchylającej się strzałki. Dla każdego wykresu trzeba więc wygenerować odpowiedni URL zgodnie z dokumentacją dostarczoną przez Google. Elegancko będzie zapakować to w funkcje.

CREATE OR REPLACE FUNCTION Google_O_Meter( KPI NUMBER )

RETURN VARCHAR 

IS

BEGIN 

	RETURN  '<img src=http://chart.apis.google.com/chart?cht=gm&chds=0,1&chf=bg,s,F7F7E7&chs=100x50&chdlp=t&chd=t:' || trim(to_char(nvl(KPI,0),'990.999')) || '>';

END;

Proponuję też drugi przykład, tym razem wykres kołowy pobierający kolejne wartości z kolumn. Chciałem w nim też przemycić prosty sposób na elastyczne zarządzanie liczbą kolumn, tutaj maksymalnie dziesięciu.

CREATE OR REPLACE FUNCTION Google_Pie( 

		KPI1  NUMBER 

	, 	KPI2  NUMBER DEFAULT NULL 

	, 	KPI3  NUMBER DEFAULT NULL 

	, 	KPI4  NUMBER DEFAULT NULL 

	, 	KPI5  NUMBER DEFAULT NULL 

	, 	KPI6  NUMBER DEFAULT NULL 

	, 	KPI7  NUMBER DEFAULT NULL 

	, 	KPI8  NUMBER DEFAULT NULL 

	, 	KPI9  NUMBER DEFAULT NULL 

	, 	KPI10 NUMBER DEFAULT NULL 	)

RETURN VARCHAR 

IS

	TYPE t_KPI IS TABLE OF NUMBER; 

	KPI t_KPI := t_KPI(KPI1,KPI2,KPI3,KPI4,KPI5,KPI6,KPI7,KPI8,KPI9,KPI10);

	i INTEGER := 1;

	chds_max INTEGER := 0;

	HTML VARCHAR2(1024) := '<img src=http://chart.apis.google.com/chart?chf=bg,s,F7F7E7&chco=224499&chs=100x100&cht=p3&chdlp=t&chd=t:';

BEGIN 

	i := KPI.first;

  	LOOP

		HTML := HTML || trim(to_char(KPI(i),'9999990.99'));

		IF KPI(i) > chds_max THEN chds_max := KPI(I); END IF;

		i := KPI.next(i);

		IF KPI(i) IS NOT NULL THEN HTML := HTML || ','; ELSE EXIT; END IF;	END LOOP;

	RETURN HTML || '&chds=0,' || trim(to_char(chds_max,'9999990.99')) 	||'>';

END;

Teraz można już spokojnie zapomnieć o Google API i zabrać się za przygotowanie raportu. Kod dzięki nowym funkcjom będzie wyglądać dość elegancko. Zwracam uwagę na polecenie HOST, które po wykonaniu skryptu automatycznie otworzy przeglądarkę WWW z gotowym raportem. Przykładową tabelkę CREATE TABLE SALES_TABLE (PRODUCT VARCHAR(100), TARGET NUMBER,
SALES NUMBER, MARKET NUMBER); wypełniłem czterema wierszami.

CONNECT user/[haslo]@baza;



SET MARKUP HTML ON TABLE "BORDER=1 WIDTH=300" SPOOL ON PREFORMAT OFF ENTMAP OFF;

SET pages 9999;

SET LINESIZE 500;

SPOOL raport.html;



PROMPT <H1>Sales dashboard</H1>



PROMPT <H2>Target realization</H2>

SELECT PRODUCT as "Product type"

	,SALES as "Current Sales"

	,Google_O_Meter(SALES/TARGET) as "Target realization" 

	FROM SALES_TABLE;



PROMPT <H2>Sales structure</H2>

WITH T AS 

(SELECT

	 SUM(CASE WHEN PRODUCT = 'Loans' THEN SALES ELSE 0 END) LOAN

	,SUM(CASE WHEN PRODUCT = 'Cards' THEN SALES ELSE 0 END) CARDS

	,SUM(CASE WHEN PRODUCT = 'Accounts' THEN SALES ELSE 0 END) ACC

	,SUM(CASE WHEN PRODUCT = 'Funds' THEN SALES ELSE 0 END) FUN

	FROM SALES_TABLE) 

SELECT LOAN AS "Loans"

	,CARDS AS "Cards"

	,ACC AS "Accounts"

	,FUN AS "Funds"

	,Google_Pie(LOAN,CARDS,ACC,FUN) AS "Structure" 

	FROM T;



SPOOL OFF;

HOST raport.html;

EXIT;

Wystarczy teraz dwukliknąć w skrypt, ewentualnie podać hasło, i za chwilę otrzymamy w przeglądarce gotowy raport taki, jak poniżej.



Przy następnej okazji napiszę, jak generować podobne wykresy z kolejnych wierszy zapytania, bez komplikowania samego raportu.Bogdan Taranta edytował(a) ten post dnia 31.08.10 o godzinie 21:06