Business Intelligence Tech Blog

At The Generations Network we 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 some help from Dundas Support who was excellent. 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.

This last week I have been searching the internet for a solution on how to create a Reporting Services Report Execution Snapshot with SQL Server Integration Services. SQL Server Reporting Services mainly runs on XML and SOAP methods. So calling a RS function or method is relatively simple. SSIS even has a web service task that theoretically would work perfect for this situation. I tried referencing RS .wsdl file and SSIS wouldn’t accept the format. After some more research I realized the web service task is pretty much useless when connecting to Reporting Services.

Then I looked at the scripting task in SSIS and that seemed too complicated. To get it to work I would have to create a RS proxy server and write a good amount of code. It seemed too difficult for what seemed to be an easy task.

I then found the rs utility. You can write reporting services script files (.rss) using VB.net to call Reporting Services web service methods and run these files from the command line. By putting the command line command in a batch file I am able to create an Execute Process task in SSIS and call the batch file. It was that easy.

So here is my solution:

reportsnapshot.rss
Public Sub Main()
‘Run this in the command line: rs -i reportsnapshot.rss -s http://server/reportserver -v reportPathParameter=%1

Dim reportPath as string = reportPathParameter
Dim schedule AS NoSchedule
Dim EnableManualSnapshotCreation as Boolean = true
Dim KeepExecutionSnapshots as Boolean = false

‘Sets the credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

‘Sets the Report History options
rs.SetReportHistoryOptions(reportPath, EnableManualSnapshotCreation, KeepExecutionSnapshots, schedule)

‘Updates the Report Snapshot
rs.UpdateReportExecutionSnapshot(reportPath)

End Sub

reportsnapshot.bat
rem change directory
cd to where ever you have the .rss file saved

rem This batch file calls Reporting Services Script ‘reportsnapshot.rss’ and passes the report path which is the %1 variable
rs -i reportsnapshot.rss -s http://server/reportserver -v reportPathParameter=%1

rem This will indicate whether there is an error. Error = 1, Non Error =0. This is used in the Process Task to indicate whether the task fails or succeeds.
EXIT [ERRORLEVEL]

Execute Process Task
Executable: Path to where you have the .bat file saved
Arguments: The path to the report (ex. “/folder/report”) Note: You need the first “/”. This will be passed into the batch file as the %1 parameter.
FailTaskIfReturnCodeIsNotSuccessValue: True
SuccessValue: 0 (The EXIT [ERRORLEVEL] will return a 0 or 1. 1 is fail. This will make it so the task will fail if the batch file encounters an error.)

If you are in need of clarification or if you have questions about this you can e-mail me!