Dundas Map for SQL Reporting Services 2005 – Dynamic Symbols

As some of you might know, I am a BIG BIG fan of Microsoft’s SQL Reporting Services 2005 and STILL (Arggh!) haven’t had a chance to play with SQL Reporting Services 2008! For awhile now, I have been wanting to take my reports to a new level (mainly targetted at producing an enhanced suits of the Service Desk Express reports) and as such I have been looking into some of the 3rd party add-ons that are available.

Recently I have been doing some experimentation with the demo version of the Dundas Map for SQL Reporting Services 2005 – specifically attempting to dynamically populate symbols (in my case Cities) based on dynamic latitudes and longitudes retrieved from a database. Whilst the Dundas Map Control is very cool, this simple task is NOT at all straightforward and consequently I thought explaining it would make a good post. So here goes.

The Basic Stuff

Once you have installed the Dundas Map Control you can drag it onto any report using the SQL Server Business Intelligence Development Studio. A nice Wizard appears…KILL IT!! Seriously, the Wizard is fine if you want to colour sections of the map based on the sum of values for that area (e.g. sales in Wales vs Scotland). For dynamic symbols it is completely useless as you MUST populate dynamic symbols in code (thankfully either C# or VB.NET)! So you should end up with something that looks like this:

Dundas Map Example 03

Right-click on the Map and select Properties from the popup menu and this is where you can configure what part of the world the map displays. So in my case I have selected the UK and dragged my Dundas Map Control to fill the area of my report as shown below:

Dundas Map Example 04

Now my database structure looks like this:

Dundas Map Example 01

In order to make this post realistic, I want my report to call a stored procedure to return the Cities for a given region (or all regions) and as such I need a stored procedure, a dataset of regions and a parameter that uses these regions as shown below:

dsRegions:

SELECT -1 As [RegionID], 'All Regions' As [RegionName] UNION SELECT RegionID, RegionName FROM dbo.Regions ORDER BY 2

RegionID Parameter:

Dundas Map Example 06

StoredProcedure [dbo].[up_Cities_Select]:

CREATE PROC [dbo].[up_Cities_Select]
@RegionID int
AS
SELECT R.RegionName,
C.CityName,
C.Latitude,
C.Longitude,
C.Population
FROM dbo.Cities C
JOIN dbo.Regions R
ON R.RegionID = C.RegionID
WHERE (R.RegionID = @RegionID OR @RegionID = -1)
ORDER BY C.Population DESC

Code

So far so good. Now here is where things get a little crazy. Right-click on the Map and select View Code from the popup menu. This bring up the Code Editor Window where you choose your choice of language and select the Event you are interested in coding for. For the purpose of this demo I will select VB.NET and the event I am interested in is PostInitialize as shown below:

DundasMapExample_07

There are couple of gotchas that you REALLY need to be aware of before you start using this control:

  • You cannot access the DataSources from the code and as such everything (including the connectionstring to the database) needs to be provided in code!
  • You cannot access the DataSets from the code which means that you need to write all the SQL in the code. For this reason I cannot imagine a realistic example when you wouldn’t use a stored procedure as it makes the code SO much more legible as you will see.

Let me step you through my simple code:

The first thing we need to do is add a SymbolDataBindingRule to tell the MapControl which fields to use for the Latitude, Longitude and Name.

Dim _sbr As New SymbolDataBindingRule
With _sbr
.Name = "sbr"
.BindingField = "CityName"
.XCoordinateField = "Longitude"
.YCoordinateField = "Latitude"
End With
mapObj.DataBindingRules.Add(_sbr)

Now we need to set up a database connection which requires a connectionString. I can’t be doing with hardcoding a connectionString in .NET code as it makes deployment from test to production a nightmare. So, what you can do is create an additional parameter (hidden this time) that holds the connectionString as shown below:

Dundas Map Example 05

Now whilst we are on the subject of Parameters we need to make these Report Parameters available to the Code by clicking on the Code Parameters tab and creating our two new Code Parameters as shown below:

Dundas Map Example 02

OK, so now we can carry on with our code creating our database connection:

Dim _objConn As New System.Data.SqlClient.SqlConnection(codeParams("paramConnectionString"))

We then create a SQLCommand object that uses the above connection, and appropriate stored procedure from the database:

Dim _objCmd As New System.Data.SqlClient.SqlCommand
With _objCmd
.CommandText = "dbo.up_Cities_Select"
.CommandType = System.Data.CommandType.StoredProcedure
.Connection = _objConn
End With

And we pass our parameters to the stored procedure as appropriate:

_objCmd.Parameters.Add("@RegionID", System.Data.SqlDbType.Int)
_objCmd.Parameters("@RegionID").Value = codeParams("paramRegionID").ToString

We then fire up our connection:

_objConn.Open

And create a SQLDataAdapter to fill a datatable with the results of the stored procedure:

Dim _objDA As New System.Data.SqlClient.SqlDataAdapter(_objCmd)
Dim _dt As New System.Data.DataTable
_objDA.Fill(_dt)

We then bind the map object (mapObj) to the datatable.

mapObj.DataSource = _dt
mapObj.DataBind

And then finally we iterate through each of the symbols we have created setting their name:

Dim _i As Integer = 0
For Each _symbol As Symbol In mapObj.Symbols
_symbol.Text = _dt.Rows(_i).Item("CityName")
_i = _i + 1
Next

Before closing the connection:

_objConn.Close

Compile the code and then run it and with a bit of luck it should end up looking like this (obviously subject to some data):

Dundas Map Example 08

Debugging

Another tip (stolen from a post in Dundas Support Forums) is to wrap all your code in a Try Catch such that your errors write out to a file on the local machine. This makes debugging ALOT easier:

Try
...code above...
Catch ex As Exception
System.IO.File.WriteAllText("C:MapRSExceptionText.txt", ex.Message & " - " & ex.StackTrace)
End Try

Summary

You can download a copy of my database and the example report here.

So, as I said at the beginning of the post – not straightforward at all! But with a little work this control adds a valuable component to anyone’s SQL Reporting Services toolbox. As always, any feedback (positive or negative) is always welcome.

Advertisements

How To Default The Start And End Date of Any Month in SQL Reporting Services

It seems, from experience, that most of the Microsoft SQL Reporting Services reports I write these days, have a requirement for a date range set via parameters. Whilst you can just let your users select the dates they want the report to run, you can also provide default values that are then changeable or not. So I thought I would explain in this very short post how to default a StartDate parameter to be the 1st of the current month and an EndDate parameter to be the last day of the current month. In Visual Studio select Report > Report Parameters.

StartDate

Click Add and enter the following:

Name: StartDate
Data type: DateTime
Prompt: Start Date

In the Default values section select the Non-queried radio-button and enter the expression below in the textbox next to it:

=CDate(“01/” & Month(Now) & “/” & Year(Now))

EndDate

Click Add and enter the following:

Name: EndDate
Data type: DateTime
Prompt: End Date

In the Default values section select the Non-queried radio-button and enter the expression below in the textbox next to it:

=DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, Parameters!StartDate.Value))

Click OK.

All we are doing here is taking the previously calculated StartDate parameter (now, for example, 01/10/2008) and adding a month to it, before subtracting one day. This allows us to get the last day of the month irrespective of how long the month is or whether it is a leap year or not.

Final Word Of Caution Re Defaults

Default parameters make life a lot easier for end users but they must be used very carefully in SQL Reporting Services because, a report will automatically execute if all the required parameters are defaulted. As such if the users generally are not accepting your defaults then they should be remove as it is placing an extra query against the database for no reason. Alternatively, add another parameter that is not defaulted so that, whilst the dates pre-populate, the report wont run until that parameter is set.

Hope this was of help. As always, feedback (positive or negative) is always welcome.

Microsoft SQL Reporting Services – Enabling Service Desk Express Print Preview

If you are using Microsoft SQL Reporting Services as your enterprise reporting tool you will probably have already developed a bunch of reports that query the Service Desk Express database. Developing reports for use with Service Desk Express and even completely migrating away from Crystal is easy enough until you come to enabling the print preview type functionality.

Sure you can generate an Incident Report that takes a parameter of IncidentNo and returns all the data about that incident in a perfect format, but how do your support staff launch that report from a given incident ticket?

In a previous post I discussed the use of a System Parameters module, and in this post I am going to use that module in conjunction with a calculated field to enable the print-preview functionality without the use of the Program Launcher application.

Solution

Assumptions:

  • We have already created an appropriate incident print preview report called “HDPrintPreview” that accepts a parameter of IncidentNo.
  • That report is published in a directory called “SDE” on your report server.
  • We have a System Parameters module as discussed in the previous post. If you don’t have this module this technique will still work but it means that if you need to change the name of the report or report server then you need modify the calculated field which can only be done out of hours.

Having made these assumptions we need to create a new system parameter called “Incident Print Preview Report URL” with a parameter value of:

http://<ReportServer>/ReportServer?%2f<Report Directory>%2f<Report>&<Parameters>=

where <ReportServer> is server where Microsoft SQL Reporting Services is installed, <Report Directory> is the directory on the report server where the report is located, <Report> is the name of the report, and <Parameters> are the parameters that need to be passed to the report.

So, let’s say that we have a report server called GANDALF (sorry all mine are named after Lord Of The Rings characters), your parameter value would be:

http://gandalf/ReportServer?%2fSDE%2fHDPrintPreview&IncidentNo=

So far so good…

Now using DB Admin we need to add a calculated field to the Incident module called ReportURL with a formula of:

(SELECT "PARAM_VALUE" + CAST("BASE"."SEQUENCE" AS NVARCHAR(10)) FROM "_SMDBA_"."TBL_PARAMS" WHERE "PARAM_NAME" = 'Incident Report URL')

as shown below:

ReportURL Calculated Field

Report URL Field

Basically, all this calculated field does is append the Incident No to the end of the URL/Parameter Value we specified earlier so that when, for example, you open Incident 1234, the ReportURL field will show:

http://gandalf/ReportServer?%2fSDE%2fHDPrintPreview&IncidentNo=1234

So now for the final bit to make it all work.

Add the ReportURL field to your selected Incident form using form customisation and also add the Display Link button to the form as your new print preview. The Display Link button is available via the buttons menu in form customisation.

Save the form and open an incident using the saved form and click the Display Link button you added. You should find that the SQL Reporting Services report should open automatically, showing the appropriate incident details.

As always feedback welcome – hope it proves useful.