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  |
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
|
Member |
|
|
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  |
 |
Littlefoot
Messages: 21823
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
I guess that's all.
-
Attachment: apex_met.PNG
(Size: 9.69KB, Downloaded 3641 times)
|
|
|
|
| Re: generation of charts in apex [message #463177 is a reply to message #463141] |
Wed, 30 June 2010 00:58 |
 |
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 |
|
Littlefoot
Messages: 21823
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:
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> select null link,
label,
pct
from (select 'met' label,
...
When the chart is done, the result is as follows:
Much better than previously, wouldn't you say?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jan 22 06:30:28 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
