How to Format Stored Procedures to Provide Data to a Chart

  Who is this article for?  

  Data analysts using Ideagen Smartforms Analytics.

  Understanding of data structure is recommended.

In order to create a new chart, you will need to add a stored procedure to the database that returns data in a format compatible with the charting interface. The required format is as follows:

Row 1 - A JSON array of column series labels
Rows 2 ... N - A JSON object representing a data series where the property "name" is populated by the series name and the property "data" is an array of series values.

For example, if we run the stored procedure "micoInspectionsByInspector" that is built in as a part of the default database as follows:

USE [micofis15]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[micoInspectionsByInspector]
@Inspectionbyinspectorovertime = 5,
@InspectorName = NULL,
@UserName = NULL
SELECT 'Return Value' = @return_value
GO

We might see a result set that looks as follows:

['February','March','April', 'May','June','July' ]
{"name":"BOB SMITH",data:[0,1,0,0,0,0,]}
{"name":"JANE DOE",data:[0,1,0,0,0,0,]}
{"name":"BILL WILLIAMSON",data:[0,0,2,0,0,0,]}
{"name":"SARA LOWE",data:[0,0,0,1,0,0,]}

This indicates that the series labels will be the months from February through July, the series names will correspond to inspectors' first and last names and the data elements will indicate the number of inspections per month in order. Note that the number of data elements per row must equal the number of series labels.