Mapping with Virtual Earth in SQL Server Reporting Services

After seeing an excellent article by Wayne Berry about Mapping in SQL Server Reporting Services using the Google Maps API, I was inspired to see what I could do with Virtual Earth.

The answer: not much.

Virtual Earth does has a stellar SDK. The SDK is all JavaScript based, so it may not be used by SQL Server Reporting Services. Virtual Earth does not have a static mapping API. However, there are unsupported ways to get a single image tile–I used this virtual earth example application (veTile) as a basis for retrieving the tile URL.

I translated the code from C# to VB.NET so I could use it directly in reporting services. The code allows you to provide a latitude, longitude, and a zoom level and get back the closest virtual earth tile. The problem is that the resolution is only as good as a single map tile. The actual location of the latitude and longitude could be anywhere on the tile.

Virtual Earth mapping example

The good news, like the Google maps example, is that you can use the tiles in SSRS because no JavaScript is required. If you find a way to improve the resolution, let me know.

The SSRS source code of the report: Virtual Earth Mapping Example source code (vemappingexample.rdl)

In case you just want to get the map tile URL in Visual Basic, I am providing the Virtual Earth Helper source code (VB.NET code). The class (VEHelper) has a single public static method (GetVETileUrl).

Testing custom SpamAssassin rules

As I was writing and testing my custom SpamAssassin rules, I often tried to figure out how to make sure my rule was running in production. I could easily test it locally by using the SpamAssassin command-line. But for production, I couldn’t use the command line.

Perhaps there is a better way, but I decided to send a test spam message into the system and see how SpamAssassin handled it. The easiest way to trigger SpamAssassin to mark a message as spam is to include a URL that will trigger one of the SURBL rules.

If you look at your notification e-mails the rules will be listed near the bottom:

Note: Slightly edited to remove identifying information, including the actual URL.

Unsolicited bulk email from:

Subject: Spam is great

…

Content analysis details: (26.2 points, 1.0 required)

pts rule name description

—- ———————- ————————————————–

0.0 HTML_MESSAGE BODY: HTML included in message

4.5 URIBL_SC_SURBL Contains an URL listed in the SC SURBL blocklist

[URIs: spamurl.com]

3.8 URIBL_AB_SURBL Contains an URL listed in the AB SURBL blocklist

[URIs: spamurl.com]

4.1 URIBL_JP_SURBL Contains an URL listed in the JP SURBL blocklist

[URIs: spamurl.com]

3.0 URIBL_OB_SURBL Contains an URL listed in the OB SURBL blocklist

[URIs: spamurl.com]

Simply grab the URL (spamurl.com in this case) and paste it into a message. Send the message from the outside to a recipient behind SpamAssassin. It should trigger the rules engine, and you should hopefully see your rule fire in the new notification message.

Unsolicited bulk email from:

Subject: Spam is great

…

Content analysis details: (26.3 points, 1.0 required)

pts rule name description

—- ———————- ————————————————–

0.0 HTML_MESSAGE BODY: HTML included in message

4.5 URIBL_SC_SURBL Contains an URL listed in the SC SURBL blocklist

[URIs: spamurl.com]

3.8 URIBL_AB_SURBL Contains an URL listed in the AB SURBL blocklist

[URIs: spamurl.com]

4.1 URIBL_JP_SURBL Contains an URL listed in the JP SURBL blocklist

[URIs: spamurl.com]

3.0 URIBL_OB_SURBL Contains an URL listed in the OB SURBL blocklist

[URIs: spamurl.com]

0.1 AD_CUSTOM_RULE Custom rule fired!

SharePoint calculated fields reference

If you ever use calculated fields (a.k.a. calculated columns) in SharePoint 2007 (WSS 3.0), there is an excellent set of examples from Microsoft WSS 2.0. There is also a newer set of examples for WSS 3.0, but they haven’t deprecated any of the formulas, so everything still applies.

Update: I also found a basic SharePoint calculated field reference, if examples aren’t your thing.

Easy multi-value parameters in SQL Server Reporting Services

I recently came across an application where I needed to use a multi-value parameter in SQL Server Reporting Services (SSRS).  I always seemed to have trouble with these in the past.  This time I found them very easy to use because I found (re-discovered?) a simple way.

The basic idea is to set up a parameter as if it were going to be a single value parameter with the topmost row selected by default.  I will walk through the steps of this scenario.

I chose the AdventureWorks database to do this sample and focused on June 2004 Sales to get an easy dataset to work with.

1. Create the basic dataset:

2. Set up the layout for the report:

3. Preview the report with no parameters:

4. Create a new dataset for the territories list:

5. Set up the parameter, TerritoryID, like a normal single-value parameter.  Be sure to set the default value to make it default to the topmost row in the list.  In this case, the topmost is “Australia”, TerritoryID of 9.

6a. Apply the filter to the table.  Notice that I am not editing the dataset, but simply adding a filter to the table properties.  If you are usually the type to edit the dataset (like myself), just hang with me for a second.  Right-click the edge of the table and then choose Properties:

6b.  Modify the table properties to add the filter.  Since you can’t read it all, it says

=Fields!TerritoryID.Value = Parameters!TerritoryID.Value

7. Check the finished result.  We now have a report with a single parameter.  The topmost selection, Australia, is automatically selected and our filter is applied by default.

8. So far, so good–there is nothing extraordinary about what we’ve done so far.  Now, we simple make the parameter multi-value by changing the report parameters and checking the Multi-value box:

9. Don’t rush off just yet, or you’ll see this error message when you choose Preview:

10.  You need to make one small change to the filter.  Open the table properties again, but this time change the operator from equals (=) to IN.  Since the Parameters!TerritoryID.Value now contains a comma separated list, we need to search IN instead of comparing.

11. Now, preview your report.  The left image shows the initial preview, the right image shows the drop-down list blown out, you can see that Select All is selected by default.

One caveat is that performance is not going to be top notch because Reporting Services is filtering on the entire dataset every time.  It would (most likely, depending on your implementation) be faster to filter in the dataset.  However, this approach often requires a lot of development work on the T-SQL side to get it working.  This approach is easy and quick enough for many applications.  Enjoy.

Update: I found a good example of the hard way to do multi-value parameters (by using SQL).  If you scroll down to the second post, there is source code there for converting a delimited string into a table variable.  I have re-formatted the charlist_to_table source code to make it more legible.