Load the FusionCharts Library
<script src="/flash/FusionCharts.js" type="text/javascript"></script>
Include the script tag above in the document to load the FusionCharts library.
The FusionChart library is included as part of the core PowerSchool product, so the page just needs to reference the .js file.
Create a JavaScript Array and Load it with the Chart Data
Create a JavaScript array named chartData
var chartData = [ ];
The array will contain multiple JavaScript objects with the following format
{"label":"labelName","value":##}
The JavaScript objects can be added to the array using the array.push() function
chartData.push({"label":"labelName","value":##});
The following is a sample of what it may look like to create an array to hold student enrollment
by grade level for a high school
<script type="text/javascript">
var chartData = [];
chartData.push({"label":"9","value":127});
chartData.push({"label":"10","value":138});
chartData.push({"label":"11","value":110});
chartData.push({"label":"12","value":255});
</script>
Create a chart properties object
A chart properties object must be created to define the look, location, and data for the FusionChart.
var chartObject = {
"type": "column2d",
"renderAt": "chartContainer",
"width": "100%",
"height": "300",
"dataFormat": "json",
"dataSource": {
"chart": {
"caption": "Enrollment by Grade",
"xaxisname": "Grade Level",
"yaxisname": "Enrollment"
},
"data": chartData
}
};
- type - Type of chart (Bar, Pie, Doughnut)
- renderAt - ID of container where chart will be rendered
- width - Width of chart (%)
- height - Height of chart (px)
- dataFormat - Format of data to be rendered (json)
-
dataSource
-
chart
- caption - Chart caption
- xaxisname - Label for X axis
- yaxisname - Label for Y axis
- data - Array of data to be rendered
Create a target <div> element for the chart
An HTML container must be created as a target for rendering the FusionChart.
The sample chart properties object above has a renderAt value of "chartContainer", so an HTML element with this id must exist
to successfully render the chart data.
<div id="chartContainer"></div>
Put it All Together
<head>
~[wc:commonscripts]
<script src="/flash/FusionCharts.js" type="text/javascript"></script>
<script type="text/javascript">
var chartData = [];
chartData.push({"label":"9","value":127});
chartData.push({"label":"10","value":138});
chartData.push({"label":"11","value":110});
chartData.push({"label":"12","value":255});
var chartObject = {
"type": "column2d",
"renderAt": "sampleChart",
"width": "100%",
"height": "300",
"dataFormat": "json",
"dataSource": {
"chart": {
"caption": "Enrollment by Grade",
"xaxisname": "Grade Level",
"yaxisname": "Enrollment"
},
"data": chartData
}
};
$j(document).ready(function(){
var myChart = new FusionCharts(chartObject).render();
});
</script>
</head>
<body>
<div id="sampleChart"></div>
</body>
Notice that the .render() command is placed within a jQuery ready() function.
This is necessary to ensure that the entire page has been rendered and the target container (sampleChart) is ready.
Retrieving the Data
We've created some very attractive charts at this point, but the data has been hard-coded.
The next step will be retrieve the data from the PowerSchool database and use the results to
populate the chartData array. This can be done using PowerSchool's tlist_sql structure.
The SQL
SELECT grade_level, count(*)
FROM students
WHERE enroll_status = 0
GROUP BY grade_level
Grade Level |
Count |
~[tlist_sql;
SELECT grade_level, count(*)
FROM students
WHERE enroll_status = 0
GROUP BY grade_level
ORDER BY grade_level
;]
~(grade) |
~(count) |
[/tlist_sql]
Integrate the Query into tlist_sql
var chartData = [];
~[tlist_sql;
SELECT grade_level, count(*)
FROM students
WHERE enroll_status = 0
AND (students.schoolid = ~(curschoolid) OR ~(curschoolid) = 0)
GROUP BY grade_level
ORDER BY grade_level
;]
chartData.push({"label":"~(grade_level)","value":~(count)});
[/tlist_sql]
With the code above, the chartData.push() function will be repeated for each record returned by the query, replacing
~(grade_level) and ~(count) with the resulting values for each record.
Notice the following line the query:
AND (students.schoolid = ~(curschoolid) OR ~(curschoolid) = 0)
This will allow the query to return data for all students when the user has selected the district office, but only students
at the currently selected school will be included when not at the district office.
See the PS HTML
reference for more information about tlist_sql and PowerSchool codes like ~(curschoolid).
See the SQL
reference for more information about SQL syntax.
Security
If charts contain sensitive information, access to the page containing the chart can be restricted using
PowerSchool's page permissions.
Consider any potential data-security issues when using tlist_sql. Be sure not to query and display sensitive
student information that may otherwise be protected by field level security.
Tlist_sql will NOT take field level security into account.