Recently I needed to do some slightly more advanced formatting of a map legend in SQL Server 2008 R2 Reporting Services (SSRS). I love the map reports you can build with SSRS in 2008 R2 but if your legend needs to show anything other than whole numbers, like a percentage, or perhaps a decimal value, or maybe even display negative values using parentheses instead of dashes, well you might be thinking you’re out of luck.
Thankfully that’s not the case. There isn’t a convenient number format screen for the map legend but it is possible to do all of the fancy formatting you want; it just may not be apparent at first glance.
The first thing that might throw you is that formatting for the various values that you want in the legend is not done in the properties of the legend itself. Legends in SSRS maps are just containers. You can display the values for multiple pieces of data in a single legend, like showing both the color and the size of a bubble. This means that the number format for a particular data set is done on whichever layer contains that piece of data.
In the list of map layers click on the little down arrow to the right of the layer that contains the legend data you want to modify and then select either the color or the size rule that you want to display. In this example I’m changing the color rules for a point layer, but you’ll find the same options the center point of a polygon layer as well.
Once in the properties dialog select the legend tab.
Here you can select which legend container you want this legend to appear in and you can set the format in the “Legend text:” box.
Here’s a quick breakdown of the format string you see in that box:
#FROMVALUE{N0} - #TOVALUE{N0}
- #FROMVALUE (and #TOVALUE) – pretty self explanatory these are indicators for the values on either side of each range in your legend.
- {N0} – This is the format of the value on either side of range.
- If you’re so inclined you can even change the center ‘-’ to some other character(s) to change how the range values are separated.
On to the formatting itself. “N0″ is the same formatting string you see elsewhere in your SSRS reports, meaning a number with 0 decimals. So by using rather typical Excel style formatting strings you can make the values in your legend look however you like. So
Showing 2 decimals
#FROMVALUE{N2} - #TOVALUE{N2}
Percent with a single decimal
#FROMVALUE{P1} - #TOVALUE{P1}
Or this way if you prefer
#FROMVALUE{#.0%} - #TOVALUE{#.0%}
Which means that if you want to get fancy and do something like, say, change negatives to use parentheses instead of a dash you can use a semicolon to delineate how a number looks when it’s positive or when it’s negative, like so: (I changed the separator to a || just for fun)
#FROMVALUE{#.0%;(#.0%)} || #TOVALUE{#.0%;(#.0%)}
I’ve been spending a bit of time with map reports lately so I’ll some more mapping related stuff to post in the near future.
