~[wc:commonscripts]
~[wc:admin_header_css] Start Page > PowerSchool Customization ~[wc:admin_navigation_css] ~[SetPostValue:tab=visualization]

# FusionCharts (Visualizing Data)

~[x:insertfile;tabs.html]

## Prerequisite Skills

- Familiarity with PowerSchool Page Customization
- Working knowledge of HTML
- Understanding of JavaScript and JSON
  - Helpful to have, but this document covers the basics needed for FusionCharts
- Ability to write the necessary SQL queries to extract and group data for charting

## Building FusionCharts (Five Step Process)

1. Load the FusionCharts library
2. Create a JavaScript array and load it with the chart data
3. Create a chart properties object
4. Create a target <div> element for the chart
5. Render the chart

## 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>

`

## Render the chart

The FusionCharts render() function will display the chart.

`

    var myChart = new FusionCharts(chartObject).render();

`

## 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.

## Chart Types

Changing the type of chart can be achieved by simply changing the value of the "type" attribute in the chart properties object.

column2d
column3d
line
area2d
bar2d
bar3d
pie2d
pie3d
doughnut2d
doughnut3d
pareto2d
pareto3d

## 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.

## References and Documentation

- FusionCharts Documentation - www.fusioncharts.com/dev/
- Chart Attributes Reference - www.fusioncharts.com/dev/chart-attributes.html?chart=area2d

~[x:insertfile;footer.html] ~[wc:admin_footer_css]