Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Mapping data with Report Builder 3.0 (SSRS 2008 R2) Case Sensitive

Posted by denglishbi on December 9, 2009

This week I received an email from a BI Developer in New York in regards to a posting I did earlier this year, Mapping data with SSRS 2008 R2, wondering why the map was not working with their dataset.  I took a look at their screenshots and based on what I saw I quickly realized that the Map Gallery option with the new Map report item is case sensitive.  Let’s take a quick look at what they were seeing with the latest November CTP release of Report Builder 3.0:

image image image
New startup screen in Report Builder 3.0 We will be using the Map gallery option I zoomed in on the map a bit and dragged it around a bit to reposition it
image image image
Select the visualization type Going to choose option to add a new dataset since I don’t have one setup already that can be shared (new option in SSRS 2008 R2) I will be using the AdventureWorks2008 database for this demo once again
image image image
I created a new version of the view vSalesPerson and used the UCASE function for the StateProvinceName data (you could use UPPER also) I map the fields STATENAME to StateProvinceName and you can see the difference now how the spatial data is Proper Case compared to our dataset which is all Upper Case Select the theme, field to visualize (value in the dataset), the color rule, and then click Finish
image image image
Run the report to preview it and ….  nothing has been mapped …  the relationship did not work because the mapping is case sensitive Go back to the dataset selection and use the original view Sales.vSalesPerson. Notice that the data is all Proper Case Run the report and guess what … we have a winner!

 

You can download the latest version of Report Builder 3.0 and the rest of the SQL Server 2008 R2 add-ons from the Microsoft® SQL Server® 2008 R2 November Community Technology Preview Feature Pack.

image

I have posted this in the SQL Server Connect as a bug, so we will see what Microsoft has to say – SSRS 2008 R2 Map Gallery Case Sensitive.  So for the time being make sure that your data matches what is expected in the spatial dataset…an exact match.  This would mean that your abbreviated formats should all be upper case.

UPDATE (12/11/2009): got an update on the Connect posting I did and the response from Microsoft is….are you ready….

"Thank you for reporting this issue. The matching on the binding fields is case sensitive by design. We do not have plans to change this."

so you have been warned.  Make sure that if you are using this mapping option that you have your data in alignment with what is expected.

Advertisements

One Response to “Mapping data with Report Builder 3.0 (SSRS 2008 R2) Case Sensitive”

  1. […] Mapping data with Report Builder 3.0 (SSRS 2008 R2) Case Sensitive […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: