Steve Gavin wrote with an interesting question that, at first, I got backwards.
He has a requirement for CSV export output from a report that conditionally hides columns, and he couldn't get the output the way he wanted it to be. I immediately thought, "That makes sense, somehow the CSV renderer [and as it turns out, the XML renderer] don't know anything about "visibility" logic, just as they couldn't overlay two items, so all the column data is always showing up."
Well, no. The "backwards" part is that none of the conditionally hidden data is showing up. After testing, here is my assessment:
If a column has its Hidden property set to any expression, even as innocent an expression as =False (I kid you not, that explicit expression meaning "don't hide" will still do it), that column and its values will not display in CSV or XML output.
I guess somebody at Microsoft will say this is a security feature. If it's possible at all that something should be hidden, then never-ever-ever display it in a data export, no matter what?
Come on.
So what's the workaround?
Usually I remind you that you're not stuck with the output Reporting Services hands you, that the XML data export plus XSLT provides a wonderful way to format the report data any way you want. In this case, the XML output has some significant infelicities so we need to do a little more work, but not much.
Create a second report exactly like the first, with no Hidden expressions. It's easy to parse the RDL to remove them, whether you do it with the DOM or XSLT. In XSLT, you might take any identity transform and make a simple change to strip Visibility elements, like this:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output encoding="utf-8"/>
<!– strip Visibility elements from an RDL —>
<xsl:template match="*">
<xsl:copy>
<xsl:apply-templates select="@*"/>
<xsl:value-of select="text()"/>
<xsl:apply-templates select="./*[local-name() != 'Visibility']"/>
</xsl:copy>
</xsl:template>
<xsl:template match="@*">
<xsl:attribute name="{name()}">
<xsl:value-of select="."/>
</xsl:attribute>
</xsl:template>
</xsl:stylesheet>
… so now, at runtime, you can request the XML from this second report rather than the "real" report. Give the XSLT the parameters that you usually give Reporting Services and let the XSLT decide what columns should be rendered to CSV, or a "stripped" version of the XML if you need that, on this run.
"Oh, that's going to be hard!", you say? No it isn't.
First, I'm going to give you a full example here. I'll use Steve's method of parameterizing his report to hide and show columns in my XSLT example, but the thing is trivial enough for you to figure out how your own parameter strategy could be used in something similar.
Second, although you won't be automagically applying the XSLT by attaching it to the RDL, you are really better off learning to request the XML and apply the XSLT yourself. This gives you a chance to apply different XSLT files, at will, for different formatting requirements, and also to apply parameters for dynamic behavior, as we're going to do here. I won't belabor this point, because I've discussed it elsewhere, so please read https://spacefold.com/lisa/2007/10/28/After-you-learn-to-walk-you-can-run-a-postscript-to-the-QnD-XSLT-walkthroughand let's move on.
Important caveat
I should say that this is all possible assuming you, like Steve, have some programmatic way of accessing ReportServer, such as I've already shown in other posts (url access and web service styles). You need this, because we're intervening before the user gets the report results. We're swapping to a different report for the request, we're asking for XML, we're loading the parameters into the XSLT, applying the XSLT to the XML, and providing the result to the user.
If you currently rely on Report Manager for your end-user interface, you need to add a custom interface at least for this particular part.
Once you've made this leap, there is so much more possible!
Workaround implementation
Steve's report offers sufficient complexity and yet a sufficiently general scenario to be a worthwhile example. Basically, his report contains some fixed columns, which always appear, and some other columns grouped into attribute sets. I think there is a parameter associated with each attribute set, with a string of T's and F's representing the user's choices (or perhaps security settings) for column visibility within that set.
I could be wrong; Steve could have only one parameter and the attribute sets are only choose-able by group, not by individual column. In that case the whole problem is a bit simpler, but very similar.
I chose to solve the more complex version, because I have no idea what your visibility strategy is, and I want you to see that you can stretch this method to do… whatever. OK?
Scenario
So, let's assume that Steve has a report layout that looks something like this (last two columns are Requested_Ship and Requested_Install, respectively):
… and let's assume that he has one parameter for Group1 and another for Group2.
If these two parameters have the values "FT" and "TF", respectively, for a given report run, you might see a display result that looks like this:
… meanwhile, the XML or CSV result from this run would only have the fixed columns, like this:
<?xml version="1.0" encoding="utf-8"?>
<Report
p1:schemaLocation=
"ConditionalExports http://reportserver?/ConditionalExports&rs:Format=XML&rc:Schema=True"
Name="ConditionalExports"
xmlns:p1="http://www.w3.org/2001/XMLSchema-instance" xmlns="ConditionalExports">
<table1>
<Detail_Collection>
<Detail Sales_No="1-67901846" Order_Quantity="8" Terms="NET 30"/>
<Detail Sales_No="1-65162786" Order_Quantity="32" Terms="Special"/>
<Detail Sales_No="1-67980171" Order_Quantity="2" Terms="Trial 45 Days"/>
<!– … –>
</Detail_Collection>
</table1>
</Report>
… so now we create our second report, which looks like this, because all columns have lost their Hidden expressions. I'm showing both design and run time here, and I've changed the column color coding just to remind you. The screenshots are a bit truncated on the right side, so you can't see it, but all columns including Requested_Install on the right are present and accounted for, as fixed columns, here:
… and we plan to get our source XML for exporting purposes from this version of the report. OK?
Conditional columns using XSLT
Let's start creating an XSLT that will provide separated-value output with column-hiding to match the report's original intent.
You will see below that I'm using a prefix of "sp" in this XSLT file, for a custom element that doesn't belong to XSLT for my own data. To do this (and this part of the implementation is optional, as I explain below), I add a suitable definition of this prefix to the root stylesheet element. (The location http://spacefold.com/xslt doesn't really exist, and doesn't need to exist.) At the top level, I also provide an output method instruction, so that my separated value file is output as text rather than xml or html:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sp="http://spacefold.com/xslt">
<xsl:output omit-xml-declaration="yes" method="text"/>
Let's say Steve's report parameters are called Group1 and Group2. Our XSLT will have matching parameters:
<xsl:param name="Group1" select="'FT'"/>
<xsl:param name="Group2" select="'TF'"/>
<!– more here, as many groups as you have —>
I'm showing default values here that match my example report run in the screen shot, but obviously each group would have as many characters in the value as there are columns in the group's set of columns, and obviously at runtime you pass the parameter values to the report, with different combinations for each run.
If you look carefully, you'll see single quotation marks (apostrophes) inside the select attribute's value ("'FT'"). This is only necessary for the literal value, so that the xslt processor knows the value is a string; when you pass your string argument into the stylesheet object you don't have to add those extra marks.
For good measure let's add some more parameters to let the user customize the report value separators and delimiters, starting with a default of comma for the separator and nothing for the delimiters:
<xsl:param name="Separator" select="','"/>
<xsl:param name="Delimiter"/>
Now let's set up a variable to represent all the columns in the report, concatenating all the group parameters togethers and adding the "fixed" columns into the full sequence, wherever they happen to appear in the layout. You can give the "fixed" columns the literal value "T" in this concatenated version, since they always appear in the report. In my example, there's one fixed column at the left of the table, and two more in between the two groups:
<xsl:variable name="Groups" select="concat('T',$Group1,'TT',$Group2)"/>
<!– add 'T' in proper position for all columns that aren't parameterized —>
… and now let's tell the XSLT about the full set of columns. Here, I'm doing it as a variable which I will refer to later on as a lookup set. This is the part of the stylesheet that uses my custom namespace (aliased as "sp"):
<sp:GroupCols>
<!– the group and sequence information aren't really important,
it's just for clarity in the example, with "Group 0" being fixed columns.
The only important thing is that the full set of columns are represented
in the same order as the concat'd groups above so that the full sequence
of T's and F's matches the column sequence here —>
<col group="0" sequence="1" name="Sales_No"/>
<col group="1" sequence="1" name="Revenue_Type"/>
<col group="1" sequence="2" name="Order_Type"/>
<col group="0" sequence="2" name="Order_Quantity"/>
<col group="0" sequence="3" name="Terms"/>
<col group="2" sequence="1" name="Requested_Ship"/>
<col group="2" sequence="2" name="Requested_Install"/>
</sp:GroupCols>
<!– *******************************************
end of non-generic code here
******************************************* —>
There are many other ways to reference custom data, especially if you want to use the same strategy for many different reports. In this scenario, in production use, you would probably pass the set of columns into the transform as another, XSLT-specific argument. You can even pass the RDL as a parameter, and let the XSLT look up the column information! You can also pass the location of an external lookup XML document holding the information about the columns, in whatever simplified format you like, something like what you see in the variable. For this tutorial, I'm putting this information directly into the transform; it will help you see what I'm doing more clearly.
So that's the end of the information that is report-specific in this XSLT file. Now come the templates that actually do the work. There's only about 30 lines of code, as you'll see in the complete file (ConditionalColumnsExport.xslt (3.84 kb)), and those 30 lines look more scary than they need to because I decided to parameterize the separator and delimiter.
There are two loops, one for the header line, and one for each detail row, that handle all the possible columns in the report and toss out any that are not marked with a "T" in the Groups variable, like this:
<xsl:for-each select="document('')/xsl:stylesheet/sp:GroupCols/col[substring($Groups,position(),1) = 'T']">
… the expression will look a little different depending on how you provide information about the full column set to the XSLT, but not very different. The code inside each loop to provide the actual header line and rows is trivial.
With the parameters set as shown in the default strings, you'll get a result like this:
Sales_No,Order_Type,Order_Quantity,Terms,Requested_Ship
1-67901846,New,8,NET 30,2007-05-25T00:00:00
1-65162786,New,32,Special,2007-05-25T00:00:00
1-67980171,New,2,Trial 45 Days,2007-05-26T00:00:00
1-67992057,New,3,Trial 45 Days,2007-05-26T00:00:00
1-66282631,Used,10,Rental with Purchase Option,2007-05-21T00:00:00
So that's it.
That's the bug, and there's the workaround.
Notice anything?
If you look at the last column above, Requested_Ship, and compare it with the displayed output in the screenshots earlier in this post, you'll see that the data in the XML doesn't "know about" any of the formatting options you may have picked for display purposes in the RDL.
That's as expected. You can take care of this in the XSLT with a little more work — and you can provide more intelligence about the formatting for each column in your custom lookup element than I have — or you can change the SELECT statement for the special export version of the report to output the columns pre-formatted.
The "real" CSV handler would have handled this for you… but, come to think of it, in many cases the raw data item is going to be exactly what you would prefer for a separated values result. So we win, actually, we can have it either way.
Before anybody asks
No I haven't checked to see how this works in RS 2008. To tell you the truth, until the whole mess with the report viewer control versions and Visual Studio dies down, and unless a client wants to upgrade, I'm not all that tempted. I would if I had any free time, but I'd just be playing around. Meanwhile, the RDLs are not compatible and I'm kinda busy with "real" work. I'd rather spend my play-round time answering questions like Steve's (I get a few almost every day) for other people who are also doing "real" work.
"Faster would be better?" Not this time, I don't think so.
Set curmudgeon mode off.