Tip: Change Multiple Field Formats in SSRS

See the companion tutorial video, Format Multiple SSRS Table Fields

Edit: I have been proven wrong! Which is a good thing! Mark Bradbourne points out that there IS a way to format multiple fields within the dialogue, using the Properties dialogue (instead of Textbox Properties), and he has provided this handy video.  I love the hivemind. (I still contend that it’s cool to be able to edit the SSRS XML, but his is a better and safer solution.)

One of the things about SSRS that irritates me is that in the graphical editor, you have to set the text box properties (say, to format numbers as currency) for each individual text box…unlike Excel, where you can select multiple fields, hit CTRL-1 and format them all at once.

Here’s a better way (with some restrictions) to update the formatting for multiple text boxes.  Let’s say we’re making a grid 12 columns across, and it contains mostly currency data. Instead of right-clicking every box, let’s do this:

  • Format one cell as you’d like it (right click > TextBox Properties > Number > etc.)
  • Select View > Code. You’ll see the XML that defines the report.
  • Search for “.Value”; keep clicking “Find Next” until you arrive at the value you formatted. It will look something like this:

<Value>=Fields!Year1_Assets.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<Format>’$’#,0;(‘$’#,0)</Format>
</Style>
</TextRun>

  • It’s that <Format> line we’re interested in. Copy that line.
  • Search for the next .Values, and add the <Format> line. Or, in my case, I can search for the next <FonFamily>Tahoma</FontFamily> and replace it with <FonFamily>Tahoma</FontFamily><Format>’$’#,0;(‘$’#,0)</Format>

It’s a workaround, sure. But it beats the hell out of the several-steps right-click process we were doing before. Happy reporting!

-Jen McCown

http://www.MidnightDBA.com/Jen

14 thoughts on “Tip: Change Multiple Field Formats in SSRS”

  1. Jeff Rush

    Good call.

    For those who are hesitant to dive into the XML, you can also ctrl+click all of the text boxes you want changed and, assuming they currently share the same formatting, update the property you want changed and it will change all selected items.

    1. Jen McCown Post author

      You can select multiple fields and alter things like alignment and font size/weight, but you can’t alter numeric formatting in this way. You’re limited to formatting the numbers one at a time. Check out the video (near the end) for an example…I show you how the “textbox properties” option is greyed out if you select multiple fields.

      Thx for reading!

  3. Mark Bradbourne

    You can edit multiple fields formats at once in the graphical editor.

    Edit the first field, then with it selected click View > Properties Window from the menu bar and copy the format code.

    Select the other fields that you want to apply the format to, then click View >Properties Window from the menu bar and paste the format code in the proper line.

    …or am I misinterpreting what you are trying to do here…

    1. Jen McCown Post author

      Cool, thanks!! I still contend that bein able to edit the XML is cool, but yours is a much better solution. If you’ll keep that video up, I’ll link to it in my blog.

      Man, I love the hivemind…

Comments are closed.