Friday, April 27, 2007

How to Create Dynamic Annotations with Dundas Charts

Let me just tell you that I love my job. I work at The Generations Network in the Business Intelligence Team. We manage the data warehouse for the company and I develop reports in ProClarity Analytics and in SQL Server Reporting Services (SSRS). We recently purchased Dundas Charts which adds some nice functionality to SSRS charting. One of the features is the ability to add annotations. There are some days when we experience a huge spike in data because of processing issues. Before, people would ask us "what happened on the 1st? Did we have a great day or what???" Now we can add annotations that explain dips or spikes.

Creating the annotations manually is pretty simple. But I wanted to create them dynamically. We have or will have hundreds of reports and charts that look at this data. Manually creating annotations for each report or chart would be ridiculous. By creating them dynamically, every chart we create can include these annotations. So how did I do it? Well it took a lot of help from Dundas Support who was excellent. I don't know VB.net very well but I knew how it could work. They really stepped up and made it easy. I would highly recommend Dundas Charts! Anyways, here is the solution:

1. I first created a normal SQL table with 2 fields... Annotation_Name and Annotation_Text. In order for this to work you need to name the annotation like a date (01/01/2007). The code will then anchor the annotation to the data point based on the name of the annotation. In the text column, you can add the text of the annotation.

2. After the table is created I needed a way to query this table, loop through the results, and create a new annotation for each record in the results set. This is where I had to do a lot of research, get a lot of help from Dundas Support, and some help from one of my professors. It is really simple if you know VB.net. Here is how you do it:

- You will need System.Data.dll. Find the assembly in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

- Copy the assembly System.Data.dll to the following folder: C:\program files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies. When the report is deployed, you will need to copy this to the ReportServer\bin folder on the server. By default, this is located at C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer\bin (where "x" is some number.)

- In the Chart's code editor > External Assemblies tab, add a new external assembly reference to System.data.dll

- In the source code paste in the code below. Modify the connection string and query string.

' Define Variables
Dim connectionString As String = "Data Source=localhost;Integrated Security=SSPI; Initial Catalog=DB"
Dim queryString As String = "Select Annotation_Name, Annotation_Text From Rpt_Annotations"
Dim connection As New System.Data.SqlClient.SqlConnection(connectionString)
' Open connection
connection.Open()
Dim command As New System.Data.SqlClient.SqlCommand(queryString, connection)
Dim reader As System.Data.SqlClient.SqlDataReader = command.ExecuteReader()
Dim newAnnotation As CalloutAnnotation

' Add annotations from the query results
While(reader.Read())newAnnotation = New CalloutAnnotation

' Assign the annotation name and text
newAnnotation.Name = reader.GetString(0)
newAnnotation.Text = reader.GetString(1)

newAnnotation.SmartLabels.Enabled = True
chartObj.Annotations.Add(newAnnotation)

End While


' Match up each annotation to its data point

For Each annotation As Annotation In chartObj.Annotations

Dim point As DataPoint = ChartObj.Series(0).Points.FindValue((DateTime.Parse(annotation.Name)).ToOADate(), "X")

If Not point Is Nothing Then
annotation.AnchorDataPoint = point

End If
Next



- This can be tricky since it is all in the code editor. If you have problems, go to the Chart Designer > Advanced tab and set the Chart's SuppressExceptions property to false to see what errors might be occurring.


And that's it. It works wonderfully and now I can insert a new record in the annotations table and have it show up on many reports and charts! Beautiful.

No comments: