Add column to SSRS Excel export

A situation arose today where the key fields for an SSRS report just fitted into an A4 landscape page, but additional fields would be useful when exporting to Excel or CSV. Others seem to have had the same requirement and suggest setting the column visibility depending upon the render format. The suggestion works nicely once tweaked to cater for newer versions of Excel!

Right click on the column and change the column visibility to “Show or hide based on an expression”:


And then set the expression to the following:

=NOT(UCASE(LEFT(Globals!RenderFormat.Name,5))="EXCEL" OR (Globals!RenderFormat.Name)="CSV")

The expression above is for the case where you only want the column to only show when exported to Excel or a CSV. The tweak from other solutions is to check that the render format starts with, instead of just equals, “EXCEL”.

Twelfth Day of Christmas

A festive post for the twelfth day of Christmas and one way to avoid taking the decorations down for a while longer!

If you have each gift from the “Twelve days of Christmas” song as an individual record in a SQL Server database table, can you write a query that returns one row for each verse of the song?

Yes you can. This problem is similar to other cases where you need to flatten multiple rows into a single string. In this case for each day we want a comma separated list of the gifts for that day and all preceding days.

Another case you may have come across is flattening multiple access rights per user into a single row per user, where each row has a comma separated list of that user’s rights:

User Access Rights
Anne Read, Write
Bob Read
Chad Admin, Read, Write

Back to the twelve days of Christmas, first create a table to contain the gifts:

CREATE TABLE #christmas_gifts (day_num INT, gift VARCHAR(30))

And insert the data:

INSERT INTO #christmas_gifts VALUES (1, 'a Partridge in a Pear Tree')
INSERT INTO #christmas_gifts VALUES (2, '2 Turtle Doves')
INSERT INTO #christmas_gifts VALUES (3, '3 French Hens')
INSERT INTO #christmas_gifts VALUES (4, '4 Calling Birds')
INSERT INTO #christmas_gifts VALUES (5, '5 Gold Rings')
INSERT INTO #christmas_gifts VALUES (6, '6 Geese-a-Laying')
INSERT INTO #christmas_gifts VALUES (7, '7 Swans-a-Swimming')
INSERT INTO #christmas_gifts VALUES (8, '8 Maids-a-Milking')
INSERT INTO #christmas_gifts VALUES (9, '9 Ladies Dancing')
INSERT INTO #christmas_gifts VALUES (10, '10 Lords-a-Leaping')
INSERT INTO #christmas_gifts VALUES (11, '11 Pipers Piping')
INSERT INTO #christmas_gifts VALUES (12, '12 Drummers Drumming')

And here is the query to return each verse of the song as a single row:

 'On the '
 + CAST(theday.day_num AS VARCHAR)
 + CASE 
   WHEN theday.day_num%10=1 AND theday.day_num%100<>11 THEN 'st'
   WHEN theday.day_num%10=2 AND theday.day_num%100<>12 THEN 'nd'
   WHEN theday.day_num%10=3 AND theday.day_num%100<>13 THEN 'rd'
   ELSE 'th' 
 + ' day of Christmas SQL gave to me '
     FROM #christmas_gifts todate 
     WHERE todate.day_num <= theday.day_num
     ORDER BY todate.day_num DESC
     FOR XML PATH('')),
    '</gift><gift>a ', ' and a '),
    '</gift><gift>', ', '),
    '</gift>', ''),
    '<gift>', '') AS verse
FROM #christmas_gifts theday

The approach used above has been around for a while and a quick Google will turn up plenty of background material. The inner query selects the list of gifts for the day and its previous days as an XML structure (SELECT … WHERE todate.day_num <= theday.day_num … FOR XML). We then replace the XML tags with commas or “and”.

If you want to get a feel for the XML that is returned by the inner select then you can run that part of the query and take a look, e.g. if you run the inner select for day 2:

FROM #christmas_gifts todate 
WHERE todate.day_num <= 2
ORDER BY todate.day_num DESC

The XML you should get back is:

<gift>2 Turtle Doves</gift><gift>a Partridge in a Pear Tree</gift>

You may spot that this isn’t well formed XML, because there is no containing element (such as <gifts>…</gifts>). The absence of a containing element was specified with the empty string in the PATH(”) part of the FOR XML clause. Leaving out the containing element made the subsequent string replacements a bit simpler (arguably).