After you learn to walk, you can run: a postscript to the QnD XSLT walkthrough

When I posted about using XSLT to create customized Excel in SQL Server Reporting Services, starting from RS's XML Data format, I deliberately included a step that allowed the reader to automagically attach the XSLT to your Reporting Services process:

Now attach your XSLT document to the report by putting its name in the Data Output tab of the Report Properties dialog.

Telling the RDL about your XSLT allows you to do things in one step.  In the context of the walkthrough, the reader is not distracted by thinking about adding any wrapper process or additional code to perform the transformation step. 

Which is fine, as far as it goes. 

But I really want people to understand that adding an XSLT transform explicitly, in your own code, is far better. It takes only a few lines of code, in pretty much any programming environment to do, but that's why it's easy, not why it's better.

Why is it better?

When you extract data as XML from your reporting resource, you have the data you need for multiple presentations. XSLT supplies the means to create those multiple presentations. If you add the name of the XSLT to your RDL as a part of the Data Output handling, you give up the ability to have, well, multiple outputs.  If you wanted a second result, you'd need to load a different RDL with a different XSLT specified.  You could probably mess with the original RDL and load it dynamically with a different XSLT value, but "mess" is the operative word here.

This division between the "automagic" attachment of a single XSLT and the real extension behavior we'd prefer isn't a particular failure of RDLs or of Reporting Services. It's pretty common among generic processes to try to make things easier for you to try out, and to lose some significant functionality in the process of making things easier.  The idea is that you'll get the hint of how much more you can do, by using the in-the-box capability and that you will… go on to do more.

When you don't load the XSLT yourself, you lose more than just the flexibility to specify the XSLT to load. You also lose the ability to parameterize one XSLT to handle different features or sets of options. 

Imagine, for example, that your report has a dynamic colorset feature, or localization feature, and you'd like the XSLT to know what the user has specified for this run.   Or imagine that you'd like to pass on the User!UserID value from Reporting Services, to lookup access rights associated with UserID values and apply visibility dynamically on that basis.  (One of the things that most people don't realize about XSLT is that you can supply a whole document, such as the XML for those user rights, as a parameter, and you can search the contents of that document, along with the XSLT document itself, using XPATH, just as you search the source XML document.)

The automagic processes usually don't make room for parameterizing XSLT.  This is true even for automagic as far afield from RS, and as tightly integrated with XML, as Oracle BPEL-Workflow. Apparently it is possible in that environment with a kludge — but I can't imagine why they didn't build in parameter handling in as soon as they decided to expose an XSLT task type. Caveat: they may have done it by now, I haven't checked recently.  

This particular omission really bugs me, and I could make a case for how RS, at least, could have automagically supplied the report's parameters to the XSLT, whether they were needed or not, since XSLT processors will accept such an assignment even when the parameters aren't defined. 

Never mind.  The point is not to Monday-morning-quarterback the automagic processes offered by vendors.  The point is using them as a starting point, and continuing your journey on from there. In this case, again, it's really easy to do.

So, how is it easy?

You can apply an XSLT transformation using COM or .NET or Java or PHP or whatever you like.  In RDLDocumenter's production processing, as you can see when you explore the DTSX file,  I'm using an SSIS XML task to do the job and frankly I have no idea what classes it decides to use for the purpose. 

In RDLDocumenter's TestHarness, I'm using a .Net Framework class called Xml.Xsl.XslCompiledTransform. I like this class very much and would recommend that you look into it for your own production work. You don't lose the dynamic value of XSLT parameters just because you've pre-compiled the transformation document; the parameters are applied fresh on each transformation action.

RDLDocumenter also comes supplied with a couple of sample SQL and VBS scripts, one of which shows how you can use COM objects in VFP or VBS to apply a transformation.  The VBS script which supplies this code is a sample showing you how you might transform RDLs into the docRDL format without going through the TestHarness form interface or anything fancy to do it. (Similarly, one of the SQL sample scripts shows you how to use SQL to shred the XML into database tables without any external or CLR code if you happen not to want to use any.  The idea was to stress that it's not the technology you use to do it that's important in XMLRSDocs. Not by a long shot.) 

The VBS script does something like this:

Set ox = WScript.CreateObject("Microsoft.Xmldom")
ox.Load(source)
Set oy = WScript.CreateObject("Microsoft.Xmldom")
If args.Count > 1 Then
   oy.Load(args.Item(1))
Else
   ' ..\Deploy\EditableResources\xmlRSDocs.xslt
   oy.Load(ofs.GetFile(WScript.ScriptFullName).ParentFolder.ParentFolder & _
      "\Deploy\EditableResources\xmlRSDocs.xslt")
End If
target.Write(ox.TransformNode(oy))

However, as the notes in the script make abundantly clear, I don't really recommend that particular TransformNode syntax, it's just the one I know most COM-XSLT people are used to seeing. As the notes show you, this approach is better:

' processor = CREATEOBJECT(Msxml2.XSLTemplate")
' styleSheet = CREATEOBJECT("Msxml2.FreeThreadedDOMDocument")
' and use the following syntax:
' styleSheet.Load(args.Item(1))
' processor.styleSheet = styleSheet
' now you can ask the processor for a compiled transform when you
' need to process a source file, applying arguments specific to that
' processing instance:
' ox = CreateObject("Msxml2.FreeThreadedDOMDocument")
' ox.Load(source)
' oy = processor.CreateProcessor()
' oy.AddParameter(…)
' oy.input = ox
' oy.Transform()
' oy.output is your result…

So, really, no matter what coding environment and approach you choose, it is truly just a few lines of code.  My java version doesn't look a heckuva lot different. VFP users can find pretty much the same lines of code you see in the comments above inside the FFC XMLListener class.  XMLListener has all the necessary facilities in public methods so you can borrow it to transform any XML source, even if you aren't doing reporting.

Your COM, or ASP.NET, or WinForm environment gets some choices from the user, and it uses those choices to request the data from the report server.  The data XML export it receives from RS has various calculations and data transformations taken care of, by the original RDL expressions and rules. XSLT gives your app the opportunity to apply those same user choices, plus any other conditions of interest. You can create the presentations of your users' dreams, or re-format the XML to fit some other application's schema requirements.

Check this stuff out.  You won't be sorry.  And you won't be limited by whatever is exposed by the vendor-provided formatting and automagic du jour.