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:


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
SELECT R.RegionName,
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


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:


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

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:


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

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

mapObj.DataSource = _dt

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

Before closing the connection:


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


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:

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


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.

Financial Year To Date Figures in SQL Reporting Services Grouped By Week

I guess I should lay my cards on the table and say up front, that I love Microsoft SQL Reporting Services 2005 and can’t wait for a business opportunity to use SQL Reporting Services 2008! That said, there are some things that are just plain difficult to do (or at least I’ve found them difficult) and as such thought I’d pen a quick post about producing reports showing financial years grouped by week with/without running values – one of the things that I have found challenging in the past.

As usual I like to explain by example and for the purposes of this post I am going to use the AdventureWorks database that ships with SQL Server 2005 – in particular the Sales.SalesOrderHeader table.

The SQL bit

It is not difficult to get a simple grouping by week of year:

SELECT DatePart(wk, OrderDate) As 'Week', Sum(TotalDue) FROM Sales.SalesOrderHeader WHERE OrderDate BETWEEN '2003-04-01' AND '2004-04-01' GROUP BY DatePart(wk, OrderDate) ORDER BY 1

There are immediately three issues with this query:

  1. Week number 1 is the first week in January 2004 NOT the first week in April 2003 as it should be.
  2. The display is a week number not a start date/end date i.e. Week Beginning.
  3. If there were (which in this case there are not) gaps in sales such that there was a Null week then that week wouldn’t appear at all in the list of results.

I don’t claim to be a SQL guru and as such there may be a really simple solution to the above three issues – I just don’t know what it is. I do however, have a workaround that I find is not only effective but flexible and has a couple of simple advantages:

YearWeeks Table

What I do is create a simple table with the following structure:

CREATE TABLE dbo.YearWeeks ([Year] int NOT NULL, [WeekNo] int NOT NULL, [DateFrom] datetime NOT NULL, [DateTo] datetime NOT NULL)

Into that table I import (using an Excel spreadsheet like this) all the weeks of my financial year such that even though the date might be 2004-02-02 (in the example above) the Year column would be set as 2003 and the WeekNo column would be 45. As I want the DateTo column to be the last second of the day in question I run a little update script:

UPDATE dbo.YearWeeks SET DateTo = DATEADD(ss, 86399, DateTo) … as there are 86400 seconds in a day.

Financial Weeks Query

Now I can write a slightly different query that, whilst I know is less efficient, produces a very elegant and easy result:

SELECT YW.[DateFrom], YW.[DateTo], YW.[WeekNo], (SELECT SUM(SOH.[TotalDue]) FROM Sales.SalesOrderHeader SOH WHERE SOH.[OrderDate] BETWEEN YW.[DateFrom] AND YW.[DateTo]) As 'SalesForWeek' FROM dbo.YearWeeks YW WHERE YW.[Year] = 2003 ORDER BY 1

And that resolves all three of the above issues. Before the next financial year comes round you simply upload another set of dates and all the preparation work is already done for you.

Better Solution?

If anyone reading this has a better solution to this please post a comment and let me know as I would love to know what it is and judging by some of the posts on forums around the Internet, others may welcome it too.

The Reporting Services bit

So I promised that I would show the reporting part of this – well hopefully it is pretty simple to understand now that above workaround is in place:

ExampleFinancialReport (available for download here)

RunningValue Layout - No Chart

As you can see from the image above it is a very simple report with a table and five columns:

  • Week No: =Fields!WeekNo.Value
  • Date From: =Fields!DateFrom.Value
  • Date To: =Fields!DateTo.Value
  • Sales For Week: =Fields!SalesForWeek.Value
  • Cumulative Sales: =RunningValue(Fields!SalesForWeek.Value, Sum, Nothing)

Charts and RunningValues

As a final thought, let’s say you want a graph that shows the cumulative sales over the weeks of the year like the one below:

RunningValue Example Report

The trick here is that you need a “Dummy” category group before the actual (in our case WeekNo) category group to provide scope for the running value as shown here:

Running Value Layout RunningValue Chart Properties

Then the expression for the Cumulative Sales values becomes:

=RunningValue(Fields!SalesForWeek.Value, Sum, “DummyGroup”)


Hope this helps someone – any better ideas would be very welcome and as always, feedback (positive or negative) is always welcome.

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.


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))


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.

Reporting Magic

So, it was about time that I released another project. Reporting Magic is for those of us who rather like Microsoft SQL Reporting Services and would like to use it as their reporting tool of choice for BMC Magic/SDE. Reporting Magic is a complete rewrite of all the reports available for Magic 8.0 in SQL Reporting Services. The Service Desk Express reports are currently being ported but will take a little longer. Two versions are available:

> Microsoft SQL Reporting Services 2000 with Visual Studio 2003 and

> Microsoft SQL Reporting Services 2005 with Visual Studio 2005

As with all my projects they are completely free. Any feedback would, as always, be greatly appreciated.

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.



  • 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:


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:


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:


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.