APEX

APEX 4
Anychart 5


Ok, I seem to not understand how the setup of stacked column charts works. I am hoping somebody can point me in the right direction. Hilary has helped me in the past with linking issues, many thanks Hilary, perhaps you have additional insights here as well...

Lets say that I am building a stacked column chart for guitar schools. School A has grades 1, 2, and 3 and school B has grades 3, 4, 5, and 6. Each school has students in every grade level. Every student has a dominant hand (left handed, right handed, or ambidextrous). Now, I want to build a stacked column chart that shows the distribution of dominant hand for each grade level in a school.


This is my primary objective: Show what percentage of students at each grade level in the selected school are either a Leftie, Ambie, or Rightie.

So first, I have an LOV that has the School Names. :P1_SCHOOL_NAME

I build a stacked column chart with the following series:

Series Name: Left
SELECT 'Page_2_URL' link, s.student_grade, count(s.student_id) FROM students s WHERE s.school_name = :P1_SCHOOL_NAME and s.dominant_hand = 'Left' GROUP BY s.student_grade

Series Name: Ambidextrous
SELECT 'Page_2_URL' link, s.student_grade, count(s.student_id) FROM students s WHERE s.school_name = :P1_SCHOOL_NAME and s.dominant_hand = 'Ambidextrous' GROUP BY s.student_grade

Series Name: Right
SELECT 'Page_2_URL' link, s.student_grade, count(s.student_id) FROM students s WHERE s.school_name = :P1_SCHOOL_NAME and s.dominant_hand = 'Right' GROUP BY s.student_grade


Ok so, if I choose a 2d Stacked Column then you get data that looks close to what I want. You can see that in grade 2 at School A 30 students are left handed and 2 are Amb and 60 are right handed. You would see similar distributions for all grade levels, but because each grade level has a different number of students in it, each column is not the same height. Both School A and B teach grade 3, so, naturally grade 3 is the shortest column on each Schools chart. (It is noteworthy to point out here that the student_grade is the label in the SQL because each school has a different number and set of grades) Also, this display isn't perfect because at this point, I'm not really interested in how many students at each grade level are in each group, I want to see what percentage of the students in a grade level are in each group.

So lets change to a 2D Stacked Column (Percent) chart type. Ok, now each column represents 100% of the population at a grade level, perfect! But wait, my distributions look different. Grade 2 at School A now has 33% Lefties, 29% Ambies, and 33% Righties!?!? Thats right! The data is now showing that 29% of my Ambies are in Grade 2, 33% of my Lefties are in grade 2, etc... This is exactly what i DIDN'T want. I want to know what percentage of students in grade 2 are Left handed (or ambie, or Right handed).

Finally, the Series sql statements have a link value that links to page 2 which has an IR. Each region in the stacked bar becomes clickable so that when you click the grade 2 lefties region, you are linked to the page 2 IR with filters set so that you can see the individual students in grade 2 that are left handed.

So, after much ado, i get to my real question: Neither of these scenarios work the way I wish. Did I setup the series wrong? How can this task be accomplished? Oh, and remember that the links play a significant role in the equation. Is there a resource out there to help me understand how to setup these charts in APEX 4?

Many Thanks
Austin

Edited by: AustinJ on Feb 3, 2011 3:17 PM - Made minor grammatical changes.