Hi, I'm trying to build a line chart.
I have a database for my school and within this DB I have a table of students language skills such as Grammar, listening etc.
The data in the Skills table looks like this:
Report Date: Student: Skill: Score:
2010/08/5 Hiroshi Grammar 33
2010/08/5 Hiroshi Speaking 40
2010/08/5 Hiroshi Listening 58
2010/08/5 Hiroshi Vocabulary 62
2010/08/12 Hiroshi Grammar 35
2010/08/12 Hiroshi Speaking 41
2010/08/12 Hiroshi Listening 56
2010/08/12 Hiroshi Vocabulary 60
2010/09/09 Hiroshi Grammar 40
2010/09/09 Hiroshi Speaking 45
2010/09/09 Hiroshi Listening 67
2010/09/09 Hiroshi Vocabulary 61
2010/09/23 Hiroshi Grammar 42
2010/09/23 Hiroshi Speaking 46
2010/09/23 Hiroshi Listening 66
2010/09/23 Hiroshi Vocabulary 67
The table is called: Skills
And the mysql looks like this:
SELECT Student,
ReportDate,
TeachersNotes,
ID,
Skill,
Grade
FROM Skills
Then I created a line chart with the sql like this:
select
Skill,
month(ReportDate) AS m,
SUM(Grade),
Student
FROM Skills
GROUP BY Skill, month(ReportDate)
ORDER BY Skill, month(ReportDate)
But only one line can be shown but I need as many lines as there are Skills so If I have 4 skills representing: Grammar, Listening, Speaking, Vocabulary etc then all lines will be shown. How can this be done??
The purpose of doing this is to allow the teacher to simply input student's scores for each of their language skills after each lesson taken by student and have the data automatically populate the chart grouped by Student and grouped by month showing the student how each skill is gradually progressing month after month.
Some times the teacher is adding scores for the same skills type more than ones per month but I just need the totals for each skill for each month per student.
When I select the Data Series I am selecting just one which is 'SUM(Grade)' then when I select Label field I select 'm'
Also I need to have the chart show a different description when user mouse over's the chart line rather than: 'SUM(Grade)'
Thanks and look forward to learning how to do this.
MC
