Formatting a Map Legend to Currency (Or Whatever) in SSRS

In this quick example we’re going to take a look at how you can format the numbers in a map legend to be currency, or whatever you want. By default the numbers appearing in a map legend are formatted to numeric with zero decimal places. Often times you might want this to be currency, or perhaps even percent instead. For whatever reason, several of the options with the map are fairly well hidden and a little difficult to find, particularly for people new to SSRS. Follow the steps below to achieve legend formatting nirvana:

  1. Open your report in Report Builder or BIDS/SSDT
  2. Click twice on the map object until you get the “Map Layers” window
  3. In the “Map Layers” window click the dropdown triangle to the right of the “creepy eye”
    CreepyEye
  4. From the available options select “Polygon Color Rule…”
  5. Select “Legend” from the menu options on the left
  6. In the textbox labeled “Legend Text” you should see something similar to the following “#FROMVALUE{N0} - #TOVALUE{N0}”. This is what SSRS uses to format the legend. The “N0” portion of this is telling Reporting Services to format the legend as Numerical with zero decimal places. To have it format as currency, simply change the N0 to C0 like so “#FROMVALUE{C0} - #TOVALUE{C0}”. Note that you need to change the N to a C in both the FROMVALUE and the TOVALUE portions.

    clip_image002

  7. Click OK

That should do the trick! Now if you run the report you should see the dollar sign in your legend.

clip_image004

If you want to format the legend as something other than dollars, there’s a pretty easy way to figure out what you should use to replace the N0 value. The easiest way is probably to place a textbox on the report, manually type some numbers in it and format it however you desire using the textbox properties window (available via right clicking on the textbox). Once the textbox is formatted to your liking, you can open the properties window (the full report properties window, available under the view tab in Report Builder) and then looking at the Number Format script that was generated. You’ll want to make sure you have the textbox you just created selected when you’re looking through the properties, as the properties window is context sensitive. In the example below you can see I’ve formatted a textbox to show pounds instead of dollars.

clip_image005

In this example I would replace N0 with '£'0 to achieve my desired formatting. Now go forth and format map legends to thine hearts content! Happy reporting!

Leave a Reply

Your email address will not be published. Required fields are marked *


4 × = eight

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>