Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » generation of charts in apex
generation of charts in apex [message #462857] |
Mon, 28 June 2010 04:30 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shobhakashyapgmailcom
Messages: 58 Registered: June 2010 Location: India
|
Member |
![shobhakashyap@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
CREATE TABLE "NEW"
( "COL1" VARCHAR2(4000),
"COL2" VARCHAR2(4000),
"COL3" VARCHAR2(4000),
);
insert into NEW values('a','1','met');
insert into NEW values('x','2','not met');
insert into NEW values('z','3','not met');
insert into NEW values('e','4','met');
insert into NEW values('r','5','not met');
insert into NEW values('t',6','met');
insert into NEW values('u',7','not met');
insert into NEW values('i','8','met');
hi
for the above table data i need to populate the graph by finding the percentage of the met and not met conditions.
please any body help me out how to write the query and where i have to implement it.
i need cluster bar verticle graph for mension met condition and not met saperatly in two bars..
thanks in advance
|
|
|
|
|
|
|
|
Re: generation of charts in apex [message #463141 is a reply to message #462979] |
Tue, 29 June 2010 15:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
As your sample data is both invalid (please, next time provide something that executes successfully, not rubbish we have to fix) and not descriptive, I modified them; in my example, there are 5 "met" and 3 "not met" records. It means that percentage is as follows:met : 5 / 8 * 100 = 62.5%
not met : 3 / 8 * 100 = 37.5%
A query that calculates those values is:SQL> select met / cnt * 100 pct_met,
2 not_met / cnt * 100 pct_not_met
3 from (select sum(decode(col_met, 'met', 1, 0)) met,
4 sum(decode(col_met, 'not met', 1, 0)) not_met,
5 count(*) cnt
6 from new
7 );
PCT_MET PCT_NOT_MET
---------- -----------
62.5 37.5
SQL>
Now, Apex: create a "Chart" page. As every graph contains three (and only three) columns (link, label, value), we need to create two series - one for "met" and one for "not met" percentage. In order to do that, we only have to slightly modify the query presented above.
A query for "met" values is:select null link,
'met' label,
met / cnt * 100 value
from (select sum(decode(col_met, 'met', 1, 0)) met,
sum(decode(col_met, 'not met', 1, 0)) not_met,
count(*) cnt
from new
)
Add another series, this time for "not met" percentage. I hope you'll manage to do that by yourself.
Finally, the result is
![/forum/fa/7943/0/](/forum/fa/7943/0/)
I guess that's all.
-
Attachment: apex_met.PNG
(Size: 9.69KB, Downloaded 3114 times)
|
|
|
Re: generation of charts in apex [message #463177 is a reply to message #463141] |
Wed, 30 June 2010 00:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/132904.jpg) |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
Hi Littlefoot,
As shown in the graph, I think there are four series with values 62.5, 0, 0, 37.5.
You have added only two series, MET and NOT MET, then why the graph is showing 4?
regards,
Delna
|
|
|
Re: generation of charts in apex [message #463310 is a reply to message #463177] |
Wed, 30 June 2010 11:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
It seems that Chart works that way; it found two series, and - although the "other" value (when displaying "met", its "other" value is "not met" and vice versa) is zero, it is displayed anyway.
To test it, I created yet another series - a dummy one, simply selecting hardcoded value of 50. Changed chart type to 3D column and here's how it looks like:
![/forum/fa/7953/0/](/forum/fa/7953/0/)
See? As many 0 (zeros) as there are series that, actually, don't exist.
OK, next step. Obviously, my query was poorly written. It *did* the job, but not the way one would want it to. Therefore, I have modified it so that it returns all we need in only one series. This is the query:SQL> select label,
2 pct
3 from (select 'met' label,
4 met / cnt * 100 pct
5 from (select sum(decode(col_met, 'met', 1, 0)) met,
6 count(*) cnt
7 from new
8 )
9 union all
10 select 'not met' label,
11 not_met / cnt * 100 pct
12 from (select sum(decode(col_met, 'not met', 1, 0)) not_met,
13 count(*) cnt
14 from new
15 )
16 );
LABEL PCT
------- ----------
met 62.5
not met 37.5
SQL> I fetched percentages from the UNION, and also created labels (which we will need in Apex). The same query (as above) is used in Apex, with a minor difference - selecting a "link" value:select null link,
label,
pct
from (select 'met' label,
...
When the chart is done, the result is as follows:
![/forum/fa/7954/0/](/forum/fa/7954/0/)
Much better than previously, wouldn't you say?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Jun 03 12:32:58 CDT 2024
|