If you’ve ever used the FOR XML construct to include many child records in an SSRS report in one cell, you may have run into something that looks like this in your results:
James Diamond & Co., Some Other Firm
At that point you may fret about that “&” being tucked in there. Well fret no more, provided you use SSRS 2008. There is a nice little gem in there that allows for things like this to look like plain English, exactly as you intended. It’s called the Placeholder and here’s how you use it.
1. Set up your report with the table and dataset as normal.
2. Instead of adding the Fields!Whatever.Value to the table, right click in the cell. You’ll see the Create Placeholder option in the menu:
3. Set up the Placeholder dialog box as follows:
4. Save and run your report. That same problem cell from before will look like this:
James Diamond & Co., Some Other Firm, Inc.
Notice that nice little ampersand, instead of that HTML.
If you’ve never used FOR XML to bring in many child records into one cell or record, here is a snippet for you as a bonus:
SELECT
DealName
,(SELECT distinct name + ', ' FROM FilteredViewABC fa WHERE fa.accountid=d.cei_companydealid and fa.statecode=0 FOR XML PATH ('')) as 'Deal_Sources',
FROM FilteredViewXYZ d
I’ve run into this a couple of times, and I always forget where I saw this originally (or even where I have the bookmark). My compliments to those in the blogosphere who first shared this.
Post by: James Diamond,
