When using a chart control to plot data from a stored procedure (or any query for that matter), the stored procedure needs to return series’ in columns. Data to be plotted has to be in numeric form. How, then, can we label the series? Perhaps one would think to add a row at the top of the result set to contain the labels. But that won’t work because the column data types are numeric and the labels are most likely strings.
Fix:
Have the stored procedure return the data labels in separate columns. For example, you have a table:
| year | best | average | worst |
|---|---|---|---|
| 2009 | 5 | 4 | 3 |
| 2008 | 7 | 5 | 3 |
| 2007 | 6 | 5 | 4 |
| 2006 | 15 | 3 | 2 |
| 2005 | 6 | 1 | 0 |
… and in the resulting graph, you want series “best” and “worst” to be labeled something different. Simply modify the result to look like:
| year | best | average | worst | best_label | worst_label |
|---|---|---|---|---|---|
| 2009 | 5 | 4 | 3 | Region 1 | Region 4 |
| 2008 | 7 | 5 | 3 | Region 1 | Region 4 |
| 2007 | 6 | 5 | 4 | Region 1 | Region 4 |
| 2006 | 15 | 3 | 2 | Region 1 | Region 4 |
| 2005 | 6 | 1 | 0 | Region 1 | Region 4 |
… and from there, set the label to the value of the new label columns. It admittedly isn’t the most conceptually elegant solution to the problem, but if you’re pressed for time and desperately in need of a quick fix, why not go for it?
