Changing the Sheet names in SQL Server RS Excel: QnD XSLT

October 3, 2007 by LSN

I had a nice note from Gilda Tramontana this week, asking for more information about something I'd posted about revising the Excel output you get from SQL Server Reporting Services.

Someone had noted, in the forum thread that Gilda referenced, that the way that documentation acknowledges the limitations of the default Excel output is "amusing". Apparently the term they use in the docs is "unique attributes" and, believe me, they're not talking about primary keys here.

In another dev universe, long ago and far away, we used to refer to these "unique attributes" as "infelicities" or "devolutions". They're not bugs, they just… are.

Whatever. In the thread, I responded that the Excel default output is not the only Excel output available to you from SQL Server RS:

What I mean: you can use the data-xml export from RS and associate an XSLT stylesheet with it. That means you can take the data and make presentation in Excel you want, just go out to SSML. SSML is Excel 2003's XML dialect. It's pretty straightforward, although Excel's debugging abilities are minimal — .it's hard to see what you did wrong, so get used to reading an XSD!

… and you can read the rest of the thread if you want… but Gilda wrote asking for more information and I said I would do my best. I'll now provide a walkthrough here of how you apply the XSLT and how you create it.

I hope this walkthrough will not only show you that you can do it, but also why it is hard to do it generically. My example will be complete, and will provide you with some generic bits to start you off. But you will have to adapt a bit. Ready?

First, realize that I said the data-xml export option. Not the Excel export option.

Second, realize that you can take any Excel export document that you get from a Reporting Services report and Save it As SSML (Excel 2003 Spreadsheet XML dialect), to give you a reasonable starting point. IOW, you really don't have to figure out how to do this from scratch.

So here is what you do:

Export your report as Excel and, from Excel, Save a copy of the sample output As SSML. Like I said. This is your example of your almost-perfect target result.
Also, from Reporting Services or the preview, Export your report as Data XML, so you know what the underlying data you are going to have to work with at runtime looks like. Now you have an example of your source, from which you are going to derive your perfect target result.
Now you need to create an XSLT style sheet that will reproduce what you have in the SSML, with one simple change (in this case: the name of each worksheet), and plugging in your source Data XML into the Excel rows instead of the literals you see in your SSML sample.
Try not to faint. The samples you just saved give you almost everything you need. I'll provide an illustration, and walk you through this part, below.
Now attach your XSLT document to the report by putting its name in the Data Output tab of the Report Properties dialog.
You can test your results at this point by choosing Export to XML file with report data from Report Preview. If you've done this right, the result is SSML.
Deploy your XSLT document to your report server.
You can do this by rightclicking on the XSLT within your Visual Studio project, the same way you deploy the report , or you can do it by uploading the XSLT as a file in the Report Manager (which of course you can also do with the RDL).
Re-deploy your report to the Report Server, too, because you've adjusted its properties to include the XSLT reference.
When you Export this report to Data XML, your result is now Excel in your preferred format !

Here's the zoom-in instructions for step #3 above, the part you're scared about doing.

In my example, the report is Recipients grouped by Locale, so Locale is the group-break value for which I would like to change the worksheet names.

Remember: Excel creates different sheets in response to your explicit data page breaks, usually on a group within a data region.

When I examine my report data sample, I might see something that looks like this:

It's a pretty straightforward structure. You can see where the label for locale that corresponds to my group break with the paging instructions is located (there is a table1_Locale element for each, with a Detail_Collection underneath). You can see where the elements for each detail row are going to come from (there are Detail nodes in each Detail_Collection).

When I examine my sample SSML export, I see something that looks superficially much more complicated, but actually has a very logical structure . It has a Workbook root, under which are three first-level children with global information (DocumentProperties, ExcelWorkbook, and Styles) followed by a succession of Worksheet nodes.

Your XSLT is going to preserve much of this sample intact. It's going to repeat the three first-level "prolog" children pretty much verbatim (actually, I recommend that you strip out much of the ExcelWorkbook node — you'll see why when you check out its contents — and you can remove as much of DocumentProperties as you want). After the "prolog", it will provide a template for the Worksheets, as you'll see below.

Start your XSLT document with something that declares all the necessary namespaces from Excel's point of view, and create a template that matches any root element ( "/*"). This is just an easy way to avoid specifying the namespace of the Report root node for the match. You can use the skeleton you see here, verbatim (just add the Styles from your sample):

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
     xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
     xmlns="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:o="urn:schemas-microsoft-com:office:office"
     xmlns:x="urn:schemas-microsoft-com:office:excel"
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:html="http://www.w3.org/TR/REC-html40">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="no" omit-xml-declaration="no"/>

<xsl:template match="/*">

      <?mso-application progid="Excel.Sheet"?>
      <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel"
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:html="http://www.w3.org/TR/REC-html40">

         <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <ProtectStructure>False</ProtectStructure>
            <ProtectWindows>False</ProtectWindows>
         </ExcelWorkbook>

<!– see below for your Worksheet template–>

</Workbook>
</xsl:template>
</xsl:stylesheet>

Now you need to add some information in where I've indicated the Worksheet template goes, into the skeleton I've provided above. This is the part that is quite difficult to do generically, because, depending on how you've nested and named your data regions, I can't tell you exactly what element you want to drill down to, to match what appears as the "page element" in your report's XML data.

In mine, it's an element named table1_Locale, a couple of layers deep. I'm going to iterate through all of these nodes in the data XML, and create a Worksheet node for each in the output. As before, I'm going to use * instead of literal names for the nodes, to avoid bothering with namespace discussions.

As you can see in this skeleton, I'm going to create my sheet names as an expression (the word "Locale-" plus the value of the Locale attribute that holds the value for this group in the source data XML. That's the one significant change we're making from the original content provided by the default Excel renderer; the original sample you saved would have had an attribute that said something like ss:Name="Sheet1".

<xsl:for-each select="./*/*/*"> <!– Report/table1/table1_Locale_collection/table1_Locale —>
  <Worksheet ss:Name="{concat('Locale-',@Locale)}">
<Names/>
  <Table >
  <!– see below for what goes in here —>
  </Table>
</Worksheet>
</xsl:for-each>

The curly braces you see are a shorthand way to insert the result of an expression in an attribute. You may never have seen that syntax before. If you prefer, you can use xsl:attribute instruction to supply the sheet name as an expression.

Inside the Worksheet's Table node, I return to my SSML saved sample for more content that I can plop in verbatim: first, a set of Column definitions, followed by some Row nodes that hold the header rows for each sheet. You can just paste this in from your saved sample.

Anything that isn't the same throughout the report, such as the name of the Locale for each "page" in my case, can be replaced by an appropriate expression. Here's a header row from mine; you can see the xsl:value-of construct supplying the name of the Locale for this worksheet:

Finally, below your header rows, it's time to iterate through your details. Put another xsl:for-each construct in place at this point in the template and point it at your Detail nodes. Paste a sample row into the xsl:for-each, right out of your SSML sample content, and adjust the content of each cell to point at the appropriate data element. Mine looks like this:

<xsl:for-each select="./*/*"> <!– DetailCollection/Detail —>

… and that's pretty much all she wrote. Get the idea?

Now, as I said, you can start thinking about how many other forms of Excel you might support from the same data.

You don't have to design the formatting in an RDL at all, if you don't want to. Design it in Excel exactly the way you want it, save out the SSML, and then plug your data elements into it from the original report…

"Unique attributes", indeed. Hmph.

132 thoughts on “Changing the Sheet names in SQL Server RS Excel: QnD XSLT”

Darshan says:

November 5, 2007 at 2:09 am

Hi lisa,

the article which u have posted has help me bulit up the XSLT file. But the probelm is when i try to export my data as XML from RS ,it throws and unusual error : Problems came up during load i.e. worksheet setting error.

I would highly appreciate if u can help me trace this error.

Thank,
Darshan

Reply
L says:

November 5, 2007 at 2:41 am

Hi Darshan,

Unfortunately that’s NOT an unusual error. It’s the only error Excel ever throws and it is extremely hard to debug. There is supposed to be a log of what this error represents — but often the location of the log that is supposed to be created doesn’t exist. I have looked in vain for where it might be going instead. FWIW, I have found that it shows up more often if I try to open the file in Excel 2007.

In any case, I have learned not to rely on the log being available, but if you can find it, that’s the first place to go.

The second place to go is wherever the error told you the problem is! Excel’s message is usually able to tell you whether it can load the workbook at all and is finding a problem with the worksheet definition(s) or contents, so you can look wherever it points and figure out what you munged when you did your editing or excerpting.

If Excel doesn’t tell me where to look, I often comment out various portions of my stylesheet, for example eliminating the contents of the data tables, to see if that eliminates the issue. If it doesn’t, I know it’s something in my globals, or column definitions, etc. If it does, I know I need to fix the data representations that I used.

It’s tedious, but it is easier if you don’t rely on RS to give you the result; IOW, take your example data XML and apply the XSLT explicitly to it, outside the RS context. This makes it faster to test and try to re-load in Excel with each change you make. You can apply the XSLT using Visual Studio, or any XML tool that you prefer — or look at this post http://spacefold.com/lisa/After-you-learn-to-walk-you-can-run-a-postscript-to-the-QnD-XSLT-walkthrough for some more information on using quick and dirty VBS scripts to do it.

One of the things I’ve learned to watch out for is the case-sensitive names of styles. Another thing would be the datatypes of content you put in cells. If you get really stuck, you can post me your Excel result and I’ll try to see what’s wrong with it — no guarantees, though. I’ll just be doing it by trial and error or possibly attempting to validate your result against the Excel schema (SOMETIMES that works).

Reply
mitul says:

November 7, 2007 at 2:57 pm

Hi Lisa,

How do i save a excel export file from RS to ssml file format? Also you have one snapshot for the nodes. when i try to save an xls file to .ssml i cant find that nodes. Any help is appreciated

Mitul

Reply
L says:

November 7, 2007 at 4:35 pm

Mitul, when your Excel Export file is open in Excel, there is a Save As XML Spreadsheet. That’s the SSML !

What are the nodes you can’t find?

Reply
mitul says:

November 7, 2007 at 7:20 pm

hey lisa, thanks for the help…but when i tried to read my .xslt file from rs , it threw and error : cannot load the specified XSLT file. Expected end of expression.

Mitul

Reply
L says:

November 7, 2007 at 7:38 pm

Well, if you make a mistake in XSLT and load it to RS without testing it you have to expect that <s>.

XSLT doesn’t need to be loaded to RS without testing, Mitul! I don’t know what toolset you’re using so it is difficult for me to recommend, but if you’re in Visual Studio you can load an XML file (in this case your source RS data file) and give it a stylesheet reference so you can see what happens when you apply the stylesheet.

If you want other ways to do it, you can read this post

http://spacefold.com/lisa/After-you-learn-to-walk-you-can-run-a-postscript-to-the-QnD-XSLT-walkthrough. It discusses run-time application of an XSLT without RS doing it for you, but you can certainly use the script approach I threw in there for testing if you don’t have a better tool to do it with.

HTH…

Reply
Praveen says:

November 8, 2007 at 1:42 pm

Your article is superb. I just have one problem. My .xsl file works. conversion to SSML is perfect. The only problem is it can’t give the following xml tag in the final SSML file.

<?xml version=”1.0″ encoding=”utf-8″?>
<?mso-application progid=”Excel.Sheet”?>

I have to manually open the final SSML file and place these tags at the top and open it in EXCEL. Please advise

Reply
L says:

November 8, 2007 at 2:11 pm

Hey, thanks for the strokes, Praveen.

FWIW Excel doesn’t need the encoding specified in the xml declaration, although it does need the declaration.

You should be getting the declaration because of this line in the XSLT:

<xsl:output method=”xml” version=”1.0″ encoding=”UTF-8″ indent=”no” omit-xml-declaration=”no”/>

… and if you’ll notice I did specify UTF-8 as part of this output instruction, so if it were required you would still be okay.

FWIW UTF-8 is probably the default encoding when you *don’t* specify one, so you would be okay in any case.

Excel also does not need the prog-id specified to open the XML correctly as SSML, because it gets that information from the namespaces defined on the Workbook element tag.

It’s possible to force that line into the output, but honestly it works fine without it <s>.

>L<

Reply
Praveen says:

November 9, 2007 at 3:00 pm

I understand what you are point to.
But with out those two tag in place for the SSML the file wouldnt open directly in EXCEL when you double click on that.

I even tried all possibilities with <xsl:output />

some how xsl:output attributes are not used what I mentioned in my XSLT file. not sure why? I made lot of changes to my Reporting Services installations like Security Extensions and other extensions. Not sure if this is causing this problem.

The work around I used is

<xsl:value-of disable-output-escaping=”yes” select=”concat(‘<?xml version=”1.0″‘,’?>’)”/>
<xsl:value-of disable-output-escaping=”yes” select=”concat(‘<?mso-application progid=”Excel.Sheet”‘,’?>’)”/>

I manually tried to emit these tags in to output XML

Reply
L says:

November 9, 2007 at 3:52 pm

>>But with out those two tag in place for the SSML the file wouldnt open directly in EXCEL when you double click on that.

Sure it will, Praveen, you just need to write the file with an XLS extension.

I can’t remember if you told me whether you are requesting the output programmatically or not — but assuming you are there should be no problem changing the name of the output file. If you are not, tell me more about your scenario and remember that I did follow up this post with a recommendation to handle the application of XSLT yourself, rather than using the “automagic” RS version — which allows you greater flexibility. Since you are already using custom extensions to RS, you might be able to embed this right into the process without anything external.

FYI: I don’t think that your RS changes are the reason why the xsl:output tags are not being used. I think this is some limitation of the classes or methods they are using to apply the xslt. Not sure.

Re your workaround:

This is not the right way, ordinarily, to add processing instructions into your output. There is a right way, without cheating, and it will be something like this (forgive me if it doesn’t post correctly, or if I make a mistake — we’re packing to move and I don’t have any references to consult):

<xsl:processing-instruction name=”mso-application” >
progid=”Excel.Sheet”</xsl:processing-instruction>

Re the xml declaration, though: even though I don’t think this has anything to do with your custom extensions, it may have something to do with the versions of .NET components on your system, because that’s coming across just fine for me, even with no xsl:output instruction at all.

Reply
L says:

November 9, 2007 at 3:53 pm

PS Praveen — those encoded less than and greater than signs should not be encoded, this is a real xslt instruction we are trying to include in the stylesheet. I can’t make it come out right when I post a comment… just look up xsl:processing-instruction and you’ll see what I’m talking about…

Reply
sheetal says:

January 29, 2008 at 5:01 pm

Please cud you make a video of how to do this entire process..i am not able to get the 3rd part. When i create the ssml it doesnt show me the workbook, worksheet xml code. Also how to create XSLT?

Reply
cgracias says:

February 27, 2008 at 7:00 am

When i try to export to Export to XML file with report data, it only shows one line when i open it in Excel.
The output file when opened in a browser looks OK.
Any idea on this?
Thanks.

Reply
L says:

February 27, 2008 at 10:15 pm

Hi CGracias,

The output file looks okay in the browser, but does it look like your XSLT was correctly applied? Or is it still the original report data?

Also, what version of Excel are you opening it in? I think what you are describing would happen if it was Excel 2000. Even though 2000 had some limited XML support, SSML, the dialect we are using here, really got started with 2003.

Reply
CGracias says:

February 28, 2008 at 1:40 am

Hi again. I got it to work after applying the workaround used by Praveen. I can now open it in Excel. The only problem now that i have is the formatting (font sizes, colors, etc). It would be good if i can also insert images on the file.

I’m just new in XSLT so i’m not really sure what i’m doing here :).
Anyway, thanks again. Any notes on the formatting will be highly appreciated.

Reply
CGracias says:

February 28, 2008 at 5:36 am

Good news! I got the formatting working using the Style. I’m not sure yet about the images or logo i should say.

Reply
L says:

February 28, 2008 at 6:52 am

Hi CGracias,

You should be able to do most things with SSML that you can do in Excel. The only exception I know of is charts or other embedded ole objects. That being said, pictures might certainly fall into that category.

I do a lot of very fancy Excel work. But when I turn data into Excel it is because I am looking to do something that Excel does very well, better than other reporting formats. Since showing pictures isn’t one of the… I admit I haven’t used Excel for that particular purpose. I don’t know if this comment will help or not.

Reply
CGracias says:

February 28, 2008 at 6:22 pm

Hi,
That’s OK. I got a workaround on the logo. Luckily, the logo is only plain text and in plain color. The style will do for this.
Thanks.
CGracias

Reply
Sheetal says:

March 8, 2008 at 7:17 pm

Hi Lisa,

I m trying to apply this to an .rdl which has a matrix tool to group the records in the dataset so that each group appears in a different worksheet, but unable to do the renaming in this case. please help.

Reply
L says:

March 9, 2008 at 3:50 am

Hi Sheetal,

Are you saying that the original/standard Excel has the correct grouping and each group already does appear in a different worksheet, but you can’t apply the renaming technique? Or is the problem that you also need to move the groups into separate worksheets?

If the former (you don’t see how to apply renaming), remember that this was only a very simple example of what you can do. I used the following for my simple rename (I am going to substitute square brackets here for angle brackets in the XML):

[Worksheet ss:Name=”{concat(‘Locale-‘,@Locale)}”]

… but I could have said:

[Worksheet]
[xsl:attribute name=”ss:Name”]
*****
[/xsl:attribute]
[/Worksheet]

… and where you see **** there can be a considerable amount of code, depending on how you want to set the rule for naming each sheet.

Another thing you have to remember is that I deliberately picked a value (Locale name) that happened to provide legal sheet name values for Excel. In some cases, your group values wouldn’t be legal for a sheet name. For example suppose you had dates in your groups; the slash character might not be legal in the Excel tab name. Is this what you are running into?

Provide an example, and I’ll try to help.

Reply
Sheetal says:

March 16, 2008 at 2:19 pm

Hi Lisa,

Here is my requirement:

I have an report which when exported to excel sheet will have a summary data (all data ) in the first worksheet and later the subsequent worksheets will have data related to a particular group. For example we have a report called sales by industry, so first worksheet will contain all the industries and their sales …later worksheets will have detialed information regarding a particular industry…so i have in my report two matrix one for the first summary worksheet.

And the 2nd matrix is placed inside a list item whoes grouping is based upon the industry and has page break after facility checked, so that each time the industry changes the data is placed in another worksheet.

cud you provide me with a similar example of using matrix and list which groups the report data and creating an XSLT for it.

Please let me know if you need any code from my side.

Thnx,
Sheetal.

Reply
L says:

March 16, 2008 at 4:54 pm

Sheetal, have you tried to follow the steps that I outlined and found that for some reason it doesn’t work for a matrix or for a matrix inside a list?

What does the original export look like when you export this report using RS in-the-box Excel export output? As far as I can remember, when you export a matrix it looks pretty much the same as a table in the resulting Excel.

Is the problem that the page breaks don’t come out automatically, or what?

From my point of view, the original in-the-box Excel formatting does not matter very much. It is nice if it is pretty close to what you want, because you can use it as your “target” with a few tweaks. But if it is not, then create an Excel example that *is* your goal, save that as SSML and work from there as your target.

As long as the data export (XML) has the values you need you can do what you want. In fact I just posted a followup on this subject (http://spacefold.com/lisa/Another-postscript-to-QnD-Excel-in-RS-post) to say that, at least in my experience, the data export from RS-RDLs is extremely clean and easy to understand.

The only exception is OLE containers such as charts; you can’t translate those into SSML as far as I know. But if the problem is just that you want it to be a pivot table structure in Excel from the matrix, that should be possible to do.

In fact, as I said in my followup post, RDL data export is so clean, compared to some other environments in which I’ve done this, it’s a joy to handle the matrix output. You can easily see what the full set of contents is, what your column headers need to look like, you don’t have to figure it out using keys or grouping.

Is this the kind of thing that is bothering you (how to figure out the row headers? If so I could post about this.

No, I don’t want any code “from your side”. If you want me or somebody on my team to do this *for* you, you’ll have to pay the company that I work for ;-).

Reply
Barbara says:

April 9, 2008 at 9:53 am

I am having trouble with my style sheet. Once in reporting Services when I export to xml with report data, I don’t get the line: <?mso-application progid=”Excel.Sheet”?> in my xml output. When I look at the excel version of the xml the few lines are as follows:
<?xml version=”1.0″?>
<?mso-application progid=”Excel.Sheet”?>
<Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″>
<DocumentProperties xmlns=”urn:schemas-microsoft-com:office:office”>
When I look at the Reporting Services xml data the first few lines are as follows:
<?xml version=”1.0″ encoding=”utf-8″?>
<Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet” xmlns:o=”urn:schemas-microsoft-com:office:office” xmlns:x=”urn:schemas-microsoft-com:office:excel” xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet” xmlns:html=”http://www.w3.org/TR/REC-html40″>
<DocumentProperties xmlns=”urn:schemas-microsoft-
com:office:office”>
I can’t figure out what I’m doing wrong. I used the style sheet example provided here, but I can’t get the output correct. The first few lines of the style sheet are as follows:
<?xml version=”1.0″?>
<xsl:stylesheet version=”1.0″
xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”
xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″>

<xsl:output method=”xml” version=”1.0″ encoding=”UTF-8″ indent=”no” omit-xml-declaration=”no”/>

<xsl:template match=”/*”>

<?mso-application progid=”Excel.Sheet”?>

<Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″>

<DocumentProperties xmlns=”urn:schemas-microsoft-com:office:office”>

Could you please help.

Reply
L says:

April 10, 2008 at 7:16 pm

First I am having trouble following your request for help with all that extra XML stuff in there <g>.

Second, to get the processing instruction you need to use the following line (as I told somebody else, either in response to this entry or a different one in this “group” of entries):

<xsl:processing-instruction name=”mso-application”>progid=”Excel.Sheet”</xsl:processing-instruction>

[if this doesn’t work I will follow up with an escaped version]

Third, when you add the processing instruction, RS may strip out the xml declaration, so you may need to add it back in like this (try doing without it first!):

<xsl:value-of disable-output-escaping=”yes” select=”concat(‘<?xml version=”1.0″‘,’?>’)”/>
<!– this is a kludge needed if you’re going to export directly from the
the Report Manager interface instead of using URL Access; the Report Manager
takes off the XML declaration and Excel doesn’t like that. –>

Hope this helps…

Reply
sa says:

April 17, 2008 at 2:31 am

I tried this logic -renaming sheets – mentioned above to apply to my report generated on SSRS but when i finally export my excel the sheets names are not renamed, however when i export to xml i see the new sheetnames..what could be the problem? Any ideas

Reply
L says:

April 17, 2008 at 11:47 am

There’s no problem, sa. That’s exactly what you should expect! You are getting the default Excel from RS’s default Excel renderer. You’re getting the Excel you want from XML + XSLT.

If you’ll notice, I actually said:

>> When you Export this report to Data XML, your result is now Excel in your preferred format !

Reply
Domo says:

May 10, 2008 at 10:45 am

Hi Lisa

Thanks for your excellent posting, after a little fiddling with the various outputs I was able to produce what I wanted. I appreciate the clarity of your examples, much better than others I read.

I only wish Excel XML wasn’t such a “fat” format uncompressed though, the file size jumped from +4MB to +17MB!

Still, you can’t have everything, almost perfect is OK 🙂

Cheers!

Reply
L says:

May 10, 2008 at 3:20 pm

Hi Domo, You’re very welcome, thanks for writing. And you’re right: you can’t have everything — in Excel 2007 you can have a smaller file size at the cost of much greater complexity in creating the XML <s>.

Reply
senvy says:

May 23, 2008 at 7:30 am

Hi
I am new to Prolog, I seek a Prolog predicate that runs an Excel file ( like this: predicate …. c:\book1.xls).
If you know help me and please write the predicate.

Reply
L says:

May 23, 2008 at 11:39 am

Hi Senvy. The “Prolog” that I mentioned in my post has nothing to do with the Prolog programming language. It is just a way of saying the “beginning constructs in the spreadsheet definition” — prolog or prologue is an English word. In my XSLT files I happen to use the word “Prolog” as the name of the template that performs this service. I could have said “Preface” instead, or “FirstSteps”, or anything like that.

Having said that I am not sure what you think a “Prolog predicate that runs an Excel file” would do. Run the macros in the file? display the spreadsheet? What is your goal here?

Reply
Michele says:

July 8, 2008 at 2:31 am

Hi,

I’d like to thank you for this entire thread. I went from knowing absolutely nothing about xml, xsl, SSML, etc. to being able to produce a report from RS and render it into the Excel format I want including meaningful tab names. 🙂

Now here’s the situation that I find myself in. Like I said, I’ve gotten my report to work directly from RS. However, I’m not able to get it to render directly from SharePoint Services. The errors I get are either “Strict Parsing Error” or “Worksheet Setting” depending on which escaped version of code I use.

Can you help?

Thanks,

Reply
L says:

July 8, 2008 at 4:57 am

Hi Michele,

Try the version I posted above on April 19th (half unescaped and half Praveen’s workaround escaped version), here it is again:

<xsl:processing-instruction name=”mso-application”>progid=”Excel.Sheet”</xsl:processing-instruction>

<xsl:value-of disable-output-escaping=”yes” select=”concat(‘<?xml version=”1.0″‘,’?>’)”/>
<!– this is a kludge needed if you’re going to export directly from the
the Report Manager interface instead of using URL Access; the Report Manager
takes off the XML declaration and Excel doesn’t like that. –>

… and try including some specific xsl:output instructions, which I may not have mentioned before and usually help:

<xsl:output method=”xml” version=”1.0″ encoding=”UTF-8″ indent=”no” omit-xml-declaration=”yes”/>

… if this does not work for you, no I can’t help, unfortunately. I run screaming away from RS and SharePoint integration, there are many people who understand that stuff much better than I do.

Part of the reason I have avoided it is that it seemed like it might be totally different in RS 2008 (see section on Moving right along to the Next Great Thing in http://spacefold.com/lisa/Configuring-Reporting-Services-is-a-bear-Will-this-bear-sh~t-or-emerge-from-the-woods-in-Katmai).

There might be something else in your Sharepoint-RS integration that is causing this. There might not.

Here are some ideas:

* — test displaying some other XML document within Sharepoint that is *not* Excel-oriented. If you can get one to display properly, then tweak the xsl:output instructions above to match.

* — try displaying your Excel Saved as SSML as a document within Sharepoint (without any programmatic manipulation, and not created by RS). Can you get it to display properly? If so, what is different about it?

Best I can do…

Reply
Michele says:

July 13, 2008 at 9:02 pm

Hi,

I apologize for not getting back to you sooner but some other things took priority over this project.

In any case, I tried running the report out of Sharepoint again and it magically worked. Guess it’s one of those glitches.

Thanks again.

Reply
L says:

July 13, 2008 at 9:27 pm

Thanks for letting me know, Michele, I am glad to hear it. Maybe something was cached. Don’t you hate it when that happens!?!

Reply
Michele says:

July 27, 2008 at 8:43 pm

Hi again!

I’m sorry if I’m keeping this thread alive well past its perceived life, but I haven’t found a better or more comprehensive source for Reporting Services & Excel (Lisa, you totally ROCK!!!). Anyway, onto my questions….

First, I was wondering if there was a way of using this method to have the grouping that occurs when you render a report directly to Excel. They seem to get “dropped” when you save your SSML file. All you get are rows that are either visible or hidden. I keep getting an error when I use &ltxsl:for-each-group&gt – apparently this can’t be used as a child of a table. And the &ltfor-each&gt element doesn’t allow group-by as an attibute.

Second, I’m not exactly sure how to conditionally apply the styles. For example, I have columns that are mostly numeric, but for cases where there are no records that meet the criteria, I have “No Records Found”. I could just as easily make these “0” so that everything is numeric, but I’m trying to alleviate as much confusion as possible (e.g. one column is an average cost). I’ve tried using &ltxsl:choose&gt & &ltxsl:otherwise&gt, but can’t quite get it to work right.

Finally, I know that someone else stated that they were able to add their company logo using a style. I was able to add the URL into the spreadsheet, but not display it as an image. Any help with this would be greatly appreciated.

Thanks in advance.

Reply
L says:

July 28, 2008 at 10:54 am

Hi again Michele,

I don’t think the logo situation is what you think it is — CGracias would have used text with a style to emulate the picture/image, not include it. SSML doesn’t include support for OLE objects, so you can’t embed the graphic in the “normal” way.

You can definitely apply for-each inside a table, but you have to do it right <g>. I’m not going to guess at what you are doing wrong, because there are two many possibilities.

On the other hand, yes those groupings get “dropped” when you save your SSML file, and I’m not sure there is a way to put them in exactly as provided in the direct-to-Excel method from RS, which is a little hokey. There might be a decent replacement for it; I’ll try to think of one and post something. I’ll include an example of conditional style application. I think what you are probably missing is the ability to use <xsl:attribute>, although there are a couple of different ways to do this.

Don’t expect a quick post — but I will try this weekend.

Thanks for the “strokes” !

Reply
L says:

July 28, 2008 at 11:07 am

Oh — Michele — one more thing. There is actually a “group by” attribute in XSLT standard 2.0, which MS doesn’t support <shrug>.

The alternative is something called Muenchian grouping, which works fine in standard 1.x, You will also find gives you something more tools and more to think about, in terms of performance benefits, in XSLT in general. I’ll try to post about that too but you can google for it.

Reply
Michele says:

July 28, 2008 at 12:10 pm

Hey Lisa!

Thanks for the response. I’ll do more reasearch on the grouping thing – thanks for providing another route to try. It was the for-each-group that was giving me the error saying it couldn’t be a child of a table, not for-each (I’ve been using that like crazy thanks to you 🙂

Thanks, also, for setting me straight on the logo thing. Fortunately, this is a “nice to have” and not a “must have”.

You still rock!!

Reply
L says:

July 28, 2008 at 12:32 pm

>>It was the for-each-group that was giving me the error saying it couldn’t be a child of a table, not for-each

OK (?)… provide an example of what you’re trying to do that isn’t working — e-mail — and I’ll try to straighten this out.

Reply
Michele says:

July 29, 2008 at 7:07 am

Hi!

I’ve been able to conditionally apply the styles that I want so I’m good there. I have someone else in the office today that’s trying to help me with the grouping problem. He doesn’t think that the for-each-group will help resolve the issue so I don’t think I’m going to pursue that path anymore for the time being. I’ll post by Monday and let you know where we’re at & post the solution (if we’ve arrived at one by then). I’m still trying to plug away at getting the logo to display & might be able to enlist the help our resident guru with that.

Thanks again!

Reply
Michele says:

August 2, 2008 at 8:48 pm

Hi!

I give up. I can’t seem to figure out how to get the grouping to work. And just to clarify – my data is sorted correctly in Excel; it just doesn’t have the nice little +/- off to the left to expand & collapse the groups beneath the main group. If/when I do figure this out, I’ll be sure to post.

Thanks again.

Reply
L says:

August 2, 2008 at 10:10 pm

I totally understand what your question is about. And just to clarify on *this* side, I did spend *some* time on the weekend trying to figure out a good way to emulate the expand/collapse feature of binary Excel in SSML.

I don’t have a good solution for you, and didn’t have time to post my thoughts about this yet. I didn’t rush because I don’t think the conclusion will be immediately helpful to you; I’m looking for a substitute approach, not a way to make this work. Thanks for checking back in!

Reply
nick says:

October 31, 2008 at 11:29 pm

I just wanted to say thank you so much for this post.

You have made a great contribution to what would otherwise have been an entirely hellish project.

While I remain far more ignorant of xml and xslt than I would like, I hope to be able to offer some comparable bits of experience at some point down the road when I have more knowledge to impart.

Thanks again.

Reply
L says:

November 1, 2008 at 12:12 am

You’re very welcome, Nick, but please do check out the followup posts in this quasi-series, because it turned out to require a little more work to avoid “illegal sheet names” than I originally thought.

The latest contribution is from one of my co-workers at EC Wise, Tom Xie, who points out some additional “illegal characters” that must be masked out.

Please see http://spacefold.com/lisa/YAPS-(Yet-another-postscript)-on-Custom-Excel-for-RS for the full scoop — that goes for anybody else following the directions in this post.

Reply
Michele says:

November 7, 2008 at 8:47 am

Hey Lisa,

I just thought I’d let you know that you came through for me again 🙂

I was looking for how to do conditional page breaks & came across this http://tinyurl.com/5hshsc

You’re solution worked beautifully. I would have posted my comment there, but it seems to be locked down.

Thanks again.

Michele

Reply
>L< says:

November 27, 2008 at 9:27 pm

Oh, you’re welcome. That thread is actually on an MSDN forum someplace, it’s probably not locked down in the original version.

Thanks for stopping by, Michele!

Reply
joan says:

December 21, 2008 at 3:05 pm

Lisa,

I guess this should be obvious, but how do I create the XSLT file?

Also, the 5th exhibit, where you’ve opened the SSML file (XML Spreadsheet) – what tool did you use to open it?

thanks, joan

Reply
beth says:

December 21, 2008 at 11:15 pm

Dear Lisa,

First, I had the same question as Joan (“how do I create the XSLT file?”), but realized, finally, that I have to create it by hand…duh.

So, now that I’ve got it created, I attached it to my RDL document, and exported to “XML file with report data”. That does create a .xml file, but double-clicking it opens it in the browser, not Excel.

Have you got any ideas as to what I’ve done wrong?

I could send you my .xslt and resulting .xml, if that will help.

thanks

Reply
beth says:

December 21, 2008 at 11:41 pm

p.s.

I forgot to mention that I’m using Visual Studio 2005, in case that matters.

Reply
beth says:

December 22, 2008 at 11:16 pm

Dear Lisa,

After much head-banging over the last 2 days, trying to figure out what was wrong with my XSLT file, I decided to deploy it and the RDL to the report server, “just to see”, and I cannot believe it works (well, there are still some issues, but at least I get a readable spreadsheet now). All I ever got from within studio was “FalseFalse” in the A1 cell and nothing else; hmmm, maybe there is still a problem, or there might be a service pack or two we haven’t got.

I shall now stuff my entire body into the “duh” file.

Then, I’ll attempt to figure out how to get (dynamically) the worksheet row-count, and, one of the Detail values while on the group node.

thanks,

Reply
>L< says:

December 24, 2008 at 7:21 am

Hi Joan and Beth,

* — When I opened the XSLT to look at it, I used Stylus Studio. I use Stylus Studio and occasionally XML Spy (haven’t kept upgrading my subscription to XML Spy but it has certain capabilities in the couple-years-old version I have that I occasionally like. I talk about my tool choices in this respect, in another post, here http://spacefold.com/lisa/2008/03/22/Another-postscript-to-QnD-Excel-in-RS-post.

You can definitely do this work in Visual Studio 2005, including test transformations. But it’s not the best tool. There are lots of free ones, I have recommended different ones from time to time, depending on needs. But if you’re already using Visual Studio you are really fine there.

* — I am not sure why the file you created from Visual Studio didn’t open correctly in Excel. There are a number of possible reasons — I wouldn’t worry about this if it is working okay from RS! Usually it’s something to do with the declaration. We’re using an RS-specific trick to munge that, here, because of what RS does to the file on the way out. You could put in an XSLT parameter that told the XSLT whether you were on the server or not, and maybe create the declaration slightly differently depending. I’d look at that if it were a real issue for you.

* — Getting the rowcount for the worksheet dynamically is totally easy. If you get stuck, shoot me a question e-mail and I’ll write about that. Getting detail values while on the group node is easy, too. You may be thinking it’s hard because RS reports sometimes make it difficult (like getting page numbers into the report body, right? <g>) but you wouldn’t have a problem with it in XSLT and XPATH.

Cheers..

Reply
asalcoba123@yahoo.com says:

December 28, 2008 at 11:23 pm

OMG!! I’m so dizzy, my head is spinning!! But thanks for scripts

Reply
busby@intersindo.com says:

January 2, 2009 at 9:50 am

Sorry, I must read so slowly to get understand, thanks for that script.

Reply
dstudiobali@yahoo.com says:

January 4, 2009 at 3:53 am

I have try this before but I it’s failed. I hope after read this I could do it right. Thanks for sharing this

Reply
inventory management software says:

March 21, 2009 at 10:43 am

i really have to study your xml. It look interesting.

Reply
Abe says:

April 7, 2009 at 7:25 am

This article has been very helpful so far, but I’ve run into a roadblock. In the sample XML code you reference, the Table1_Locale node has a Locale attribute that you reference to define each table name. When I exported the XML data for the report I’m working with, there is no attribute like that:

<Report Name=”webapp test” xmlns=”webapp_x0020_test”>
<table>
<table_CreatorName_Collection>
<table_CreatorName>
<table_SASale_Collection>
<!— Table Data Here –>
</table_SASale_Collection>
</table_CreatorName>
<!- More groups here –>
</table_CreatorName_Collection>
</table>
</Report>

I’m looking for a way to define that attribute in the report so that it appears in the XML. Here’s the kicker, though – I only have access to the Report Builder, or to XML-like representation of the RDL file. I’ve got a request in to add BIDS to my workstation, but there’s no timeline for when that will happen. Have any thoughts on how to solve this? Thanks in advance!

Reply
>L< says:

April 7, 2009 at 6:31 pm

Hi there, Abe,

First, just to be clear: Locale was an example. It happens to be a field in the data I was using. It is not “Locale” like in .NET code <g>.

The attribute should appear in the table if you have grouped on something. Typically, remember, we’re looking for a page break on group to be translated in Excel on a tab. So are you saying that you *didn’t* group on something in your case? Or that you want to have some information available to you that is not your grouping expression?

Please realize that you can XPATH-select anything you want. If it happens to be (say) the first representation of a specific text box that appears within a table, you can do that. You don’t really need the attribute, it just happens to be what I used.

The issue should not be ReportBuilder versus RDL created in BIDS. If you want to discuss this more in email and explain, however, I’ll give it a shot.

Reply
Abe says:

April 8, 2009 at 10:15 am

I hadn’t thought about using XPATH-select, and that seems to have done the trick. Thanks!

Reply
billrainier says:

April 9, 2009 at 1:27 am

Continue your good work.

Reply
EDI says:

May 23, 2009 at 6:55 pm

This script is very nice. Thank you very much for it.

Reply
Ashish says:

June 12, 2009 at 9:28 pm

Hi Lisa

Thanks for providing such a nice article….
I am creating a rdl file in SSRS 2005. I have been able to create multiple sheets within a worksheet with no problem and now need to rename each individual sheet. I am facing some issue while following your steps/explanation. I am getting worksheet name and formatting in exported xml, however not getting the actual row’s of data.

I am relatively new with xml/xslt and would probably feel better with the original files, if you could provide me.

Thanks in advance.
Ashish

Reply
>L< says:

June 14, 2009 at 9:52 am

Hi Ashish,

My “original files” won’t help you. Your problem is your xpath, and your xpath will be different depending on the design of your report and its data. You’re not getting the data because your xpath doesn’t match your xml.

You need to look at your own collections, detail elements, etc, not mine, to do your xpath. The report is exported in a straightforward xml structure, as I said, but it is different for each report and the report’s data <s>.

Reply
Vicman says:

June 19, 2009 at 4:31 am

This is great i am learning more about SQL here and i am thankful i found this, thanks to all who are sharing their knowledge here.. it is free^^ i saves money for this…

Reply
Karen Roslund says:

June 23, 2009 at 9:37 pm

Exactly what i was looking for…

Reply
r4 revolution says:

June 24, 2009 at 5:04 pm

Hi,

I have designed a rdl form which contains 3 tables .. i gave page break for each table. when exporting the file to excel it generates 3 sheets .. three sheets name comes like sheet 1, sheet2 ,sheet3.. i dont want it to be like these.. instead of that i have to give my own name while generating reports from rdl form like this (s1,s2,s3)…

Reply
Susan says:

June 25, 2009 at 4:01 am

I couldn’t get it to work the file exports as XML and opens fine in the browser with all the data but won’t open with excel. I’m not sure what exactly I’m doing wrong I just an error in WorkSheet Setting with a path to a log file that does not exist.

Reply
>L< says:

June 25, 2009 at 5:30 am

Hi Susan,

Yes, that is the standard Excel unhelpful behavior <sigh>. You’ve done something that is not allowed in the SSML schema, and you get only minimal help from Excel’s error message (and that log file doesn’t exist for anybody I’ve ever met <g>).

First, pay attention to the error message, minimal as it is, because it might tell you whether the issue is in a worksheet, or the workbook settings, or the styles.

Second, here’s a manual debugging option:

Comment out as much of the output as you can in the SSML, save and try to open in Excel. If possible, target what you’re commenting out based on the error message, of course.

If it opens, start putting back stuff one thing at a time until you figure out what it is.

Here’s a proper alternative:

– try to validate your SSML associating it to the XSD. You’ll find some information on the SSML schemas and some helpful tools in this additional post: http://spacefold.com/lisa/2009/02/07/YAPS-Random-acts-of-kindness-regarding-Reports-Excel-XSLT-and-you. Probably any validating parser that you can use will be able to tell you what part of your SSML is invalid with respect to the Excel schemas more appropriately than Excel does itself.

Hope this helps, good luck!

Reply
>L< says:

June 25, 2009 at 5:31 am

Hi r4 revolution,

I’m not sure what you’re asking. WHat you’re trying to do is exactly what this post provides instructions to do <smile>. Please give it a try.

Reply
Search engine optimization says:

June 26, 2009 at 12:36 pm

Hi,
While I was building my site in Symphony, I had a bit of a learning curve with XSLT and even simple, little things were a challenge. One of those dumb, little things were getting Internet Explorer conditional comments to output in my XHTML. It took a bit of Googling, but I figured it out.

Reply
Susan says:

July 29, 2009 at 12:01 pm

I can get this to open but it still gets a WORKBOOK Setting error but if I say ok it will open and look fine. So I’m stumped.

Reply
>L< says:

July 29, 2009 at 10:41 pm

That’s interesting. You can send your SSML to me email if you want, I’ll take a look.

Reply
Josh says:

October 2, 2009 at 2:16 am

Hi Lisa,
Great article and thank you for the examples. I’ve got my XSLT producing SSML that looks exactly like what I want, but it won’t open in Excel for me. I’m wondering if you or anyone else has come across this particular error (from the excel log when opening fails):

XML ERROR in Table
REASON: Bad Value
FILE: C:\temp\inventory xlmdata 3.xml
GROUP: Row
TAG: Cell
ATTRIB: Index
VALUE: 1

I’ve spent the better part of the morning commenting out and tweaking various parts of the report, per your response to Susan, and I haven’t come up with any solution. Changing datatypes, getting rid of ‘Index’ attributes, filling in empty cells, all make no difference. The only obvious difference I can see between my SSML and the saved version from my original excel export is that empty cells look like

[Cell ss:StyleID=”s23″/]

in the latter and

[Cell ss:StyleID=”s23″]
[Data ss:Type=”Number”][/Data]
[/Cell]

in the former…obviously because the Data tag gets filled in by the “xsl:value-of” function. I’m just kind of assuming that’s okay, but maybe that’s what I should try next…it will just be a lot of hassle if I have to get avoid having empty Data tags. By the way, changing Type=”Number” to Type=”String” didn’t help either. 🙂

Reply
Josh says:

October 2, 2009 at 3:04 am

I was on the wrong track…empty data tags are fine, thank goodness. The cause of the error was that I left out a header row which I had decided I didn’t want in the final excel output. I had assumed each individual row wasn’t going to matter to the others and could be kept or left out, but when I copied in that row, everything showed up properly. Not sure why that would be a problem, but it’s working now. So make sure you include all header rows when you build the XSLT, I guess!

Reply
>L< says:

October 2, 2009 at 3:07 am

Hi Josh,

>>By the way, changing Type=”Number” to Type=”String” didn’t help either

Assuming that you meant “ss:Type” here, it should have helped. Which tells me that the style associated with that style ID probably has a number format, so you’d need change the style ID not just the type. But I’m just guessing.

You’re right that a numeric can’t be blank. You can either default it to 0 or some other value, and if the other value you want (including a blank) isn’t numeric, you need to change the type.

If you like, send me samples (xml and xslt) and I will fix and post the right resolution.

Reply
Simam says:

November 3, 2009 at 2:30 pm

Hi Josh,

I really appreciate if you can send me the .rdl, SSML, XSLT file of you report, Seems like I am totally lost.

Or if you want I can send u my report .rdl file.

Appreciate it.

Thank you

Reply
Simam says:

November 4, 2009 at 4:17 am

Sorry,

I mean Lisa, please help me.

Thank you

Reply
>L< says:

November 4, 2009 at 8:32 am

Simam, I can only tell you the same thing I’ve told other people here (see for example response to Ashish above): I don’t publish the report and XSLT because it won’t help you. Each one is individual.

And I really don’t want your RDL either.

What I can do to help is this:

* — what have you done so far?

* — I assume you have gotten as far as exporting the data xml from the report, right?

* — What is your goal, what is the thing you want to change in the Excel, from the default output? Is it the tab names or something else?

* — Are you getting an error in Excel, are you unable to figure out how to create the XSLT at all (based on my example) or what? If the latter, I’ve made some suggestions in http://spacefold.com/lisa/2009/02/07/YAPS-Random-acts-of-kindness-regarding-Reports-Excel-XSLT-and-you.

I will try to help you, honestly, but my own files won’t help you.

Reply
Simam says:

November 4, 2009 at 3:35 pm

Lisa,

I am trying to change the Excel Spread Sheet Name.

So far I am able to save a XML file and save it on my desktop.Do I need to rename the XML file?

I need help
1)How to create XSLT File.
2)How to plug in to source Data XML.

If you can show the baby step I really appreciate it.

Sorry I am new.

Reply
>L< says:

November 4, 2009 at 3:55 pm

1. In this post you see the “skeleton” of the XSLT file you need to create. You can do it in Notepad, if you like. Look in the post. It looks like this (Not sure how this comment will show it, but the root tag is xsl:stylesheet and the whole thing is shown in the post):

<?xml version=”1.0″?>
<xsl:stylesheet version=”1.0″
xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”
xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″>

</stylesheet>

2. This is a *walkthrough*. There are little camera icons for each step. Click them! They show *exactly* how to attach the XSLT to the RDL for use on the server. It does not matter that you are “new”.

3. For tests, development, and work with the XML output on your local box, rather than on a server or in the preview for developing the RDL in Visual Studio, I have already provided the information here: /lisa/2009/02/07/YAPS-Random-acts-of-kindness-regarding-Reports-Excel-XSLT-and-you.

4. No you don’t need to rename the XML file.

5. I will not do your work. But if you *try* to work with the XSLT and your XML and get stuck, I will help you figure out what is wrong. That is the best I can do.

Reply
simam says:

November 5, 2009 at 8:37 am

Hi,
Lisa seems like I am getting close, I have a question does it work if I have subreports ?

Reply
>L< says:

November 5, 2009 at 10:49 am

It should work. As long as you get only one file for the data xml output, everything should be in there. Take a look at the raw data output and you will be able to confirm this.

Reply
Sam Evans says:

November 6, 2009 at 6:47 am

For those people who may still be struggling, I just found a little ‘gotcha’ which had me stumped all yesterday. When I copied over the Styles and Worksheet from my SSML file, I didn’t pay much attention to a section that looked like this

<Table ss:ExpandedColumnCount=”13″ ss:ExpandedRowCount=”16″ x:FullColumns=”1″ x:FullRows=”1″>

The tag ss:ExpandedRowCount gave the number of rows of data in the first worksheet in my file. Unfortunately, many of the later worksheets had more rows and the file failed to open with the usual Worksheet error. Changing the 16 to 64000 solved the problem. Many thanks for the solution, by the way, it’s brilliant.

Reply
>L< says:

November 6, 2009 at 11:29 am

Hi Sam,

Thanks for mentioning this. It is a gotcha, and you can in fact just set the value to a high number. But you can actually remove those attributes — they’re not required — or you can make them a variable if you want to keep them! (there is an appropriate count function…)

This is one of the reasons I say “get rid of stuff that you don’t understand in the SSML that you’re porting” — or at least comment it out. Things open better stripped down, you can always add back once you have got the thing open in Excel and evaluate what doesn’t look quite right.

Reply
simam says:

November 9, 2009 at 9:01 pm

Hi Lisa,
May be you can help me to solve this problem Below is my simple SSML and XML file. I am trying to create XSLT file. I already spent more than a week for some reason its not working. I really appreciate it.

SSML:

<?xml version=”1.0″?>
<?mso-application progid=”Excel.Sheet”?>
<Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″>
<DocumentProperties xmlns=”urn:schemas-microsoft-com:office:office”>
<LastAuthor>Administrator</LastAuthor>
<Created>2009-11-14T01:27:27Z</Created>
<LastSaved>2009-11-14T01:27:15Z</LastSaved>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns=”urn:schemas-microsoft-com:office:excel”>
<WindowHeight>11640</WindowHeight>
<WindowWidth>15480</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>75</WindowTopY>
<ActiveSheet>1</ActiveSheet>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID=”Default” ss:Name=”Normal”>
<Alignment ss:Vertical=”Bottom” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID=”s21″>
<Alignment ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
</Style>
<Style ss:ID=”s22″>
<Alignment ss:Horizontal=”Right” ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
</Style>
<Style ss:ID=”s23″>
<Alignment ss:Horizontal=”Right” ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
<NumberFormat ss:Format=”[$-1010409]General”/>
</Style>
</Styles>
<Worksheet ss:Name=”Table1″>
<Table ss:ExpandedColumnCount=”3″ ss:ExpandedRowCount=”10″ x:FullColumns=”1″
x:FullRows=”1″>
<Column ss:AutoFitWidth=”0″ ss:Width=”156″ ss:Span=”2″/>
<Row ss:AutoFitHeight=”0″ ss:Height=”9″>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s22″><Data ss:Type=”String”>Gross</Data></Cell>
<Cell ss:StyleID=”s22″><Data ss:Type=”String”>Gain</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>55</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>10</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>130</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>50</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>77</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>20</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>150</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>60</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>99</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>30</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>170</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>70</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>110</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>40</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>190</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>80</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
</Table>
<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>
<PageSetup>
<Header x:Margin=”1″/>
<Footer x:Margin=”1″/>
<PageMargins x:Left=”1″ x:Right=”1″/>
</PageSetup>
<NoSummaryRowsBelowDetail/>
<NoSummaryColumnsRightDetail/>
<Print>
<ValidPrinterInfo/>
<VerticalResolution>0</VerticalResolution>
</Print>
<DoNotDisplayGridlines/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name=”Table2″>
<Table ss:ExpandedColumnCount=”3″ ss:ExpandedRowCount=”11″ x:FullColumns=”1″
x:FullRows=”1″>
<Column ss:AutoFitWidth=”0″ ss:Width=”156″ ss:Span=”2″/>
<Row ss:AutoFitHeight=”0″ ss:Height=”0.9375″>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s22″><Data ss:Type=”String”>Dividend</Data></Cell>
<Cell ss:StyleID=”s22″><Data ss:Type=”String”>ID</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>66</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>11</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>140</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>21</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>88</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>11</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>160</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>21</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>100</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>11</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>180</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>21</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>120</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>11</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>200</Data></Cell>
<Cell ss:StyleID=”s23″><Data ss:Type=”Number”>21</Data></Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row ss:AutoFitHeight=”0″ ss:Height=”261″>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
</Row>
</Table>
<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>
<PageSetup>
<Header x:Margin=”1″/>
<Footer x:Margin=”1″/>
<PageMargins x:Left=”1″ x:Right=”1″/>
</PageSetup>
<NoSummaryRowsBelowDetail/>
<NoSummaryColumnsRightDetail/>
<Print>
<ValidPrinterInfo/>
<VerticalResolution>0</VerticalResolution>
</Print>
<Selected/>
<DoNotDisplayGridlines/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>10</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>

XML:

<?xml version=”1.0″ encoding=”utf-8″?>
<Report p1:schemaLocation=”Master http://reportserver?/Master&rs:Format=XML&rc:Schema=True” Name=”Master” xmlns:p1=”http://www.w3.org/2001/XMLSchema-instance” xmlns=”Master”>
<table1>
<Detail_Collection>
<Detail Gross_1=”55.0000″ Gain=”10.0000″ />
<Detail Gross_1=”130.0000″ Gain=”50.0000″ />
<Detail Gross_1=”77.0000″ Gain=”20.0000″ />
<Detail Gross_1=”150.0000″ Gain=”60.0000″ />
<Detail Gross_1=”99.0000″ Gain=”30.0000″ />
<Detail Gross_1=”170.0000″ Gain=”70.0000″ />
<Detail Gross_1=”110.0000″ Gain=”40.0000″ />
<Detail Gross_1=”190.0000″ Gain=”80.0000″ />
</Detail_Collection>
</table1>
<table2>
<Detail_Collection>
<Detail Dividend_1=”66.0000″ ID=”11″ />
<Detail Dividend_1=”140.0000″ ID=”21″ />
<Detail Dividend_1=”88.0000″ ID=”11″ />
<Detail Dividend_1=”160.0000″ ID=”21″ />
<Detail Dividend_1=”100.0000″ ID=”11″ />
<Detail Dividend_1=”180.0000″ ID=”21″ />
<Detail Dividend_1=”120.0000″ ID=”11″ />
<Detail Dividend_1=”200.0000″ ID=”21″ />
</Detail_Collection>
</table2>
</Report>

Reply
>L< says:

November 10, 2009 at 3:11 pm

>> I am trying to create XSLT file. I already spent more than a week for some reason its not working

Hi Simam,

You really need to tell me

1 – what you did to create the XSLT file (post what you have so far)

2 – what “it’s not working” means. What is the error you get, and from what action?

… I promise I will tell you what’s gone wrong and correct it. But I will not write an XSLT for you from scratch. Not only because you have not hired me; <b>I really try not to do other people’s work for them</b>, instead of teaching them, even when I *am* working for my employer. Please see http://spacefold.com/lisa/2009/07/11/XSLT-and-your-mother-dont-speak-C, if you don’t believe me — and its followup, http://spacefold.com/lisa/2009/09/05/What-are-the-components-of-GSD, for why.

Reply
>L< says:

November 10, 2009 at 3:14 pm

PS — sorry you won’t be able to follow those links, I don’t know exactly why they get munged in the comments. In any case I’m serious: I won’t hand you the fish so that you can have one dinner, I’ll teach you to fish so that you can eat regularly. (quoting a proverb that my team tells me R. Hillel and a Chinese philosopher have in common)

Reply
simam says:

November 10, 2009 at 5:13 pm

Right now my XSLT file looks like below. It’s ok sorry to bother you, Thanks for your help.

<?xml version=”1.0″?>
<xsl:stylesheet version=”1.0″
xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”
xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″>

<xsl:output method=”xml” version=”1.0″ encoding=”UTF-8″ indent=”no” omit-xml-declaration=”no”/>

<xsl:template match=”/*”>

<?mso-application progid=”Excel.Sheet”?>
<Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″>

<DocumentProperties xmlns=”urn:schemas-microsoft-com:office:office”>
<!– etc –>
</DocumentProperties>

<ExcelWorkbook xmlns=”urn:schemas-microsoft-com:office:excel”>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>

<Styles>
<Style ss:ID=”Default” ss:Name=”Normal”>
<Alignment ss:Vertical=”Bottom” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID=”s21″>
<Alignment ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
</Style>
<Style ss:ID=”s22″>
<Alignment ss:Horizontal=”Right” ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
</Style>
<Style ss:ID=”s23″>
<Alignment ss:Horizontal=”Right” ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
<NumberFormat ss:Format=”[$-1010409]General”/>
</Style>
</Styles>

<xsl:for-each select=”./*/*/*”>
<!– Report/table1/table1_Locale_collection/table1_Locale –>
<Worksheet ss:Name=”{concat(‘Table1-‘,@Table1)}”>
<Names/>
<Table ss:ExpandedColumnCount=”3″ ss:ExpandedRowCount=”10″ x:FullColumns=”1″
x:FullRows=”1″>
<Column ss:AutoFitWidth=”0″ ss:Width=”156″ ss:Span=”2″/>
<Row ss:AutoFitHeight=”0″ ss:Height=”9″>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s22″>
<Data ss:Type=”String”>Gross</Data>
</Cell>
<Cell ss:StyleID=”s22″>
<Data ss:Type=”String”>Gain</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>55</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>10</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>130</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>50</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>77</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>20</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>150</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>60</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>99</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>30</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>170</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>70</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>110</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>40</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>190</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>80</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
</Table>
</Worksheet>
</xsl:for-each>
<xsl:for-each select=”./*/*/*”>
<!– Report/table1/table1_Locale_collection/table1_Locale –>
<Worksheet ss:Name=”{concat(‘Table2-‘,@Table2)}”>
<Names/>
<Table ss:ExpandedColumnCount=”3″ ss:ExpandedRowCount=”11″ x:FullColumns=”1″
x:FullRows=”1″>
<Column ss:AutoFitWidth=”0″ ss:Width=”156″ ss:Span=”2″/>
<Row ss:AutoFitHeight=”0″ ss:Height=”0.9375″>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s22″>
<Data ss:Type=”String”>Dividend</Data>
</Cell>
<Cell ss:StyleID=”s22″>
<Data ss:Type=”String”>ID</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>66</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>11</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>140</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>21</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>88</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>11</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>160</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>21</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>100</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>11</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>180</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>21</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>120</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>11</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>200</Data>
</Cell>
<Cell ss:StyleID=”s23″>
<Data ss:Type=”Number”>21</Data>
</Cell>
<Cell ss:StyleID=”s21″/>
</Row>
<Row ss:AutoFitHeight=”0″ ss:Height=”261″>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
<Cell ss:StyleID=”s21″/>
</Row>
</Table>
</Worksheet>
</xsl:for-each>
</Workbook>
</xsl:template>
</xsl:stylesheet>

Reply
>L< says:

November 10, 2009 at 6:09 pm

OK, the first thing I see is this on the stylesheet tag:

lns:html=”http://www.w3.org/TR/REC-html40″>”

… that extra quotation mark on the outside of the greater-than sign does not belong there. It’s then followed by some information that is part of the tag. The opening stylesheet tag should look like what you see in my example code in this post (I’ll try to repeat it here but don’t know what the Comment feature will do to it, so look in the colored syntax above to be sure) :
<xsl:stylesheet version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform” xmlns=”urn:schemas-microsoft-com:office:spreadsheet” xmlns:o=”urn:schemas-microsoft-com:office:office” xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet” xmlns:html=”http://www.w3.org/TR/REC-html40″>

… and this issue is repeated on the worksheet level so it may be fine in your source. It may be a problem in the Comments rendering here. Make sure of this.

Once I correct those things, I can run the XSLT but, predictably Excel won’t open the result, with a Workbook settings error. Is this what you are seeing, Simam? Or is it something else? You did not specify what your error is or where/when it occurs.

Anyway, here’s why: you don’t seem to understand the point of doing a loop, because you’ve put in explicit information into each loop, rather than any expressions or variables.

Simam, do you always have two tabs in your output (rather than an unknown number of tabs, based on group breaks)? IOW, do you want the tab names to be always called “Table 1” and “Table 2″, and there are no other choices?

If so, why are you looping over something here? I’m not sure what you are trying to accomplish, but what I see in the output is:

1- many, many worksheets
2- a tab-naming expression that looks like this (for table 2, table 1 is similar)

<Worksheet ss:Name=”{concat(‘Table2-‘,@Table2)}”>

… where @Table2, the attribute, doesn’t really exist, and therefore

3 – many worksheets have the same tab ss:Name attribute value, which means that Excel can’t open this; the tab names have to be unique.

… so I can fix this by changing the naming like this:

<Worksheet ss:Name=”{concat(‘Table1-‘,position())}”>
and
<Worksheet ss:Name=”{concat(‘Table2-‘,position())}”>

… now I’ll get 32 uniquely-named tabs, first 16 of Table1 and then 16 of Table 2, and Excel will open the workbook because it is “legal”.

But the contents of each worksheet will be static, at this point. And you haven’t really explained what you are trying to achieve with the names or the sequence of the tabs — so I don’t know how to give you additional advice, yet.

What is the output supposed to look like? Should there be only 2 tabs, or many pairs of Table1s and Table2s, or what?

Reply
simam says:

November 10, 2009 at 11:30 pm

I just want to rename the sheet name , right now I am using a simple report just to understand the concept. Problem is I don’t know much about XML.

Thank you for helping me.

Reply
>L< says:

November 11, 2009 at 12:13 am

Simam, I am not sure you have defined the right element for each tab as the path expression in the for-each.

You have a table1 element and a table2 element, one of each. But you’re doing a for-each for each detail row. I’m asking: in the final result, in this simple example, do you mean to be getting one tab for each detail row? Or do you mean to be getting one tab for each table?

Reply
simam says:

November 11, 2009 at 1:02 am

I am trying to get one tab for each table. Right now I have two table, I already rename the sheet1(Table1) and sheet2(Table2).

Thank you

Reply
>L< says:

November 11, 2009 at 1:06 am

OK, if you’re trying to get two tabs, as I said earlier:

1 – your xpath expression is wrong. It is giving you a tab for each detail item. And

2 – there is no need for @Table1 or whatever, right? You know what you want to name them. And

3 – I’m not even sure you need the for-each.

If you make the changes I suggested to use position() instead of @Table1 and @Table2 in your sheet-naming expressions, you should at least be able to run your stylesheet, open things up in Excel, and understand what I mean. Have you tried what I suggested yet?

Reply
simam says:

November 11, 2009 at 2:02 am

Iam trying to understand and follow your steps but for some reason I am not getting what I want. Seems like I need to take XML classes lol.

Thanx for ur help.

Reply
>L< says:

November 11, 2009 at 3:22 am

You are not telling me what you want <s>. You are also not telling me what you are getting instead.

This is just like giving a good bug report (although there is no bug involved, it’s the same steps):

1 – steps for the receipient to reproduce what you did.

2 – what you observed when you took those steps

3 – what was wrong with what you observed (IOW, what expectations you had, as compared to what occurred).

Please be specific.

Reply
simam says:

November 16, 2009 at 1:51 am

Lisa,
Right now my XSLT style sheet looks like below .
But still I am not able to change the sheet Name. My report and xslt style sheets are on report server .I already attached XSLT document to the report in the Data Output tab of the Report Properties dialog.

Any Idea?

<xsl:stylesheet
xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”
xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″
xmlns:df=”Master”
exclude-result-prefixes=”df”
version=”1.0″>

<xsl:output method=”xml” indent=”yes”/>

<xsl:template match=”/”>
<xsl:processing-instruction name=”mso-application”>progid=”Excel.Sheet”</xsl:processing-instruction>
<Workbook>
<DocumentProperties xmlns=”urn:schemas-microsoft-com:office:office”>
<LastAuthor>Administrator</LastAuthor>
<Created>2009-11-17T02:33:24Z</Created>
<LastSaved>2009-11-14T01:27:15Z</LastSaved>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns=”urn:schemas-microsoft-com:office:excel”>
<WindowHeight>11640</WindowHeight>
<WindowWidth>15480</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>75</WindowTopY>
<ActiveSheet>1</ActiveSheet>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID=”Default” ss:Name=”Normal”>
<Alignment ss:Vertical=”Bottom” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID=”s62″>
<Alignment ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
</Style>
<Style ss:ID=”s63″>
<Alignment ss:Horizontal=”Right” ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
</Style>
<Style ss:ID=”s64″>
<Alignment ss:Horizontal=”Right” ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
<NumberFormat ss:Format=”[$-1010409]General”/>
</Style>
</Styles>
<Worksheet ss:Name=”S1″>
<Table ss:ExpandedColumnCount=”3″ ss:ExpandedRowCount=”10″ x:FullColumns=”1″
x:FullRows=”1″>
<Column ss:AutoFitWidth=”0″ ss:Width=”156″ ss:Span=”2″/>
<Row ss:AutoFitHeight=”0″ ss:Height=”9″>
<Cell ss:StyleID=”s62″/>
<Cell ss:StyleID=”s62″/>
<Cell ss:StyleID=”s62″/>
</Row>
<Row>
<Cell ss:StyleID=”s63″><Data ss:Type=”String”>Gross</Data></Cell>
<Cell ss:StyleID=”s63″><Data ss:Type=”String”>Gain</Data></Cell>
<Cell ss:StyleID=”s62″/>
</Row>
<xsl:for-each select=”df:Report/df:table1/df:Detail_Collection/df:Detail”>
<Row>
<Cell ss:StyleID=”s64″><Data ss:Type=”Number”><xsl:value-of select=”@Gross_1″/></Data></Cell>
<Cell ss:StyleID=”s64″><Data ss:Type=”Number”><xsl:value-of select=”@Gain”/></Data></Cell>
<Cell ss:StyleID=”s62″/>
</Row>
</xsl:for-each>
</Table>
<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>
<PageSetup>
<Header x:Margin=”1″/>
<Footer x:Margin=”1″/>
<PageMargins x:Left=”1″ x:Right=”1″/>
</PageSetup>
<NoSummaryRowsBelowDetail/>
<NoSummaryColumnsRightDetail/>
<Print>
<ValidPrinterInfo/>
<VerticalResolution>0</VerticalResolution>
</Print>
<DoNotDisplayGridlines/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name=”S2″>
<Table ss:ExpandedColumnCount=”3″ ss:ExpandedRowCount=”11″ x:FullColumns=”1″
x:FullRows=”1″>
<Column ss:AutoFitWidth=”0″ ss:Width=”156″ ss:Span=”2″/>
<Row ss:AutoFitHeight=”0″ ss:Height=”0.9375″>
<Cell ss:StyleID=”s62″/>
<Cell ss:StyleID=”s62″/>
<Cell ss:StyleID=”s62″/>
</Row>
<Row>
<Cell ss:StyleID=”s63″><Data ss:Type=”String”>Dividend</Data></Cell>
<Cell ss:StyleID=”s63″><Data ss:Type=”String”>ID</Data></Cell>
<Cell ss:StyleID=”s62″/>
</Row>
<xsl:for-each select=”df:Report/df:table2/df:Detail_Collection/df:Detail”>
<Row>
<Cell ss:StyleID=”s64″><Data ss:Type=”Number”><xsl:value-of select=”@Dividend_1″/></Data></Cell>
<Cell ss:StyleID=”s64″><Data ss:Type=”Number”><xsl:value-of select=”@ID”/></Data></Cell>
<Cell ss:StyleID=”s62″/>
</Row>
</xsl:for-each>
</Table>
<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>
<PageSetup>
<Header x:Margin=”1″/>
<Footer x:Margin=”1″/>
<PageMargins x:Left=”1″ x:Right=”1″/>
</PageSetup>
<NoSummaryRowsBelowDetail/>
<NoSummaryColumnsRightDetail/>
<Print>
<ValidPrinterInfo/>
<VerticalResolution>0</VerticalResolution>
</Print>
<Selected/>
<DoNotDisplayGridlines/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>10</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</xsl:template>

</xsl:stylesheet>

Reply
troll beads says:

November 16, 2009 at 6:25 pm

I’m just new in XSLT so i’m not really sure what i’m doing here.

Reply
simam says:

November 17, 2009 at 3:59 am

Sorry I don’t know why there are smily faces, i just cut and paste my XSLT Style sheet.

<xsl:for-each select=”df:Report/df:table1/df:Detail_Collection/df:Detail”>

Reply
>L< says:

November 17, 2009 at 8:56 am

That’s okay, I understand what the characters are supposed to be.

Simam, you have the following:

<Worksheet ss:Name=”S1″>

… and later you have the following:

<Worksheet ss:Name=”S2″>

… can you see that the ss:Name attribute is where the name of each tab is coming from? What would you like the names to be instead?

You can change them one of three ways:

1. First, obviously, you can put in a different literal value, such as:

<Worksheet ss:Name=”My Tab Name”>

2. You can use attribute notation, to replace the value with an expression, which I show in my example like this (the curley braces tell the XSLT that the value inside the braces should be evaluated as an expression):

<Worksheet ss:Name=”{concat(‘Locale-‘,@Locale)}”>
the inside of the worksheet goes here
</Worksheet>

3. You can use the attribute element to put more code and work into defining the attribute name, directly after the opening Worksheet element, like this:

<Worksheet>
<xsl:attribute name=”ss:Name”>
.. your expressions and code to derive
the attribute name goes here…
</xsl:attribute>
the inside of the worksheet goes here
</Worksheet>

Hope this helps?

>L<

Reply
simam says:

November 17, 2009 at 9:58 pm

Lisa ,

I rename the Sheet1 to S1. Let me try to figure out again I will let you know. Thanx alot for helping me I don’t know how long it will take more to mix it lol.

</Styles>
<Worksheet ss:Name=”S1″>
<Table ss:ExpandedColumnCount=”3″ ss:ExpandedRowCount=”10″ x:FullColumns=”1″
x:FullRows=”1″>
<Column ss:AutoFitWidth=”0″ ss:Width=”156″ ss:Span=”2″/>
<Row ss:AutoFitHeight=”0″ ss:Height=”9″>
<Cell ss:StyleID=”s62″/>
<Cell ss:StyleID=”s62″/>
<Cell ss:StyleID=”s62″/>
</Row>
<Row>
<Cell ss:StyleID=”s63″>
<Data ss:Type=”String”>Gross</Data>
</Cell>
<Cell ss:StyleID=”s63″>
<Data ss:Type=”String”>Gain</Data>
</Cell>
<Cell ss:StyleID=”s62″/>
</Row>
<xsl:for-each select=”df:Report/df:table1/df:Detail_Collection/df:Detail”>
<Row>
<Cell ss:StyleID=”s64″>
<Data ss:Type=”Number”>
<xsl:value-of select=”@Gross_1″/>
</Data>
</Cell>
<Cell ss:StyleID=”s64″>
<Data ss:Type=”Number”>
<xsl:value-of select=”@Gain”/>
</Data>
</Cell>
<Cell ss:StyleID=”s62″/>
</Row>
</xsl:for-each>
</Table>
<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>
<PageSetup>
<Header x:Margin=”1″/>
<Footer x:Margin=”1″/>
<PageMargins x:Left=”1″ x:Right=”1″/>
</PageSetup>
<NoSummaryRowsBelowDetail/>
<NoSummaryColumnsRightDetail/>
<Print>
<ValidPrinterInfo/>
<VerticalResolution>0</VerticalResolution>
</Print>
<DoNotDisplayGridlines/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name=”S2″>

Reply
simam says:

November 18, 2009 at 12:04 am

Lisa,

My XSLT out put looks like same the table I have in report. Do I need to make changes in my xslt style sheet?

I am really not getting why I need to put more code and work into defining the attribute name, because I want to rename sheet1 to S1 and sheet2 S2.

Once again Thanks alot.

Reply
>L< says:

November 18, 2009 at 12:44 am

Oh, sorry, if you want the sheets to be named S1 and S2, that’s fine. But you told me you were not able to make changes in the sheet names, so which is it?

I think you are saying that you aren’t seeing S1 and S2 in the sheet names? Is that all?

Simam, you say:

>>
My report and xslt style sheets are on report server .I already attached XSLT document to the report in the Data Output tab of the Report Properties dialog
<<

Is it possible that you did not read the next step? Are you trying to Export to Excel?

The internal renderer for Excel has not been changed. You export to XML (which I have said very clearly in the walkthrough, I think) and the XSLT is applied to the XML output to give you what you want.

Once you do that, you should get the right result as far as the sheet names go.

Of course, the rest of your stylesheet is still going to be wrong, because you have not done anything to make the rows use the actual values in your output, it is all literals. You do know that, right? I don’t want to confuse you, but you *will* need to use expressions.

Reply
simam says:

November 18, 2009 at 9:01 pm

lisa ,

I gave up, I don’t think so I have enough knowledge to fix it.I really appreciate your help.

Reply
Tenchy says:

November 22, 2009 at 12:45 am

Hi Lisa,
I’m trying to work this out in SSRS 2008. But don’t see a “Data OUTPUT tab” in the newer version. How would i attach my XSLT document?
Thanks !
Tenchy

Reply
sanket says:

November 23, 2009 at 6:42 pm

Hi,I want to change name of report file when exporting to excel.
i have got no idea how to do it. 🙁

Please help 🙂

Reply
>L< says:

November 24, 2009 at 2:16 am

Tenchy: see new blog post (yesterday) with answer….

Sanket: are you exporting using an application? Or is the user doing the export from ReportViewer or Report Manager? Or what?

Reply
simam says:

December 5, 2009 at 9:09 pm

Lisa,

I am back may be this time I am able to fix it.
Right now I am getting worksheet name and formatting in exported xml, however not getting the actual row’s of data. May be you can fix it.
I really appreciate if some one can show me his working XSLT file . All I want is change the sheet name.

<xsl:stylesheet
xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”
xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″
xmlns:df=”Master”
exclude-result-prefixes=”df”
version=”1.0″>

<xsl:output method=”xml” indent=”yes”/>

<xsl:template match=”/”>
<xsl:processing-instruction name=”mso-application”>progid=”Excel.Sheet”</xsl:processing-instruction>
<Workbook>
<DocumentProperties xmlns=”urn:schemas-microsoft-com:office:office”>
<LastAuthor>Administrator</LastAuthor>
<Created>2009-11-17T02:33:24Z</Created>
<LastSaved>2009-11-14T01:27:15Z</LastSaved>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns=”urn:schemas-microsoft-com:office:excel”>
<WindowHeight>11640</WindowHeight>
<WindowWidth>15480</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>75</WindowTopY>
<ActiveSheet>1</ActiveSheet>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID=”Default” ss:Name=”Normal”>
<Alignment ss:Vertical=”Bottom” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID=”s62″>
<Alignment ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
</Style>
<Style ss:ID=”s63″>
<Alignment ss:Horizontal=”Right” ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
</Style>
<Style ss:ID=”s64″>
<Alignment ss:Horizontal=”Right” ss:Vertical=”Top” ss:WrapText=”1″/>
<Borders/>
<Font ss:FontName=”Arial” x:CharSet=”1″ ss:Color=”#000000″/>
<Interior/>
<NumberFormat ss:Format=”[$-1010409]General”/>
</Style>
</Styles>
<Worksheet ss:Name=”S1″>
<Table ss:ExpandedColumnCount=”3″ ss:ExpandedRowCount=”10″ x:FullColumns=”1″
x:FullRows=”1″>
<Column ss:AutoFitWidth=”0″ ss:Width=”156″ ss:Span=”2″/>
<Row ss:AutoFitHeight=”0″ ss:Height=”9″>
<Cell ss:StyleID=”s62″/>
<Cell ss:StyleID=”s62″/>
<Cell ss:StyleID=”s62″/>
</Row>
<Row>
<Cell ss:StyleID=”s63″>
<Data ss:Type=”String”>Gross</Data>
</Cell>
<Cell ss:StyleID=”s63″>
<Data ss:Type=”String”>Gain</Data>
</Cell>
<Cell ss:StyleID=”s62″/>
</Row>
<xsl:for-each select=”df:Report/df:table1/df:Detail_Collection/df:Detail”>
<Row>
<Cell ss:StyleID=”s64″>
<Data ss:Type=”Number”>
<xsl:value-of select=”@Gross_1″/>
</Data>
</Cell>
<Cell ss:StyleID=”s64″>
<Data ss:Type=”Number”>
<xsl:value-of select=”@Gain”/>
</Data>
</Cell>
<Cell ss:StyleID=”s62″/>
</Row>
</xsl:for-each>
</Table>
<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>
<PageSetup>
<Header x:Margin=”1″/>
<Footer x:Margin=”1″/>
<PageMargins x:Left=”1″ x:Right=”1″/>
</PageSetup>
<NoSummaryRowsBelowDetail/>
<NoSummaryColumnsRightDetail/>
<Print>
<ValidPrinterInfo/>
<VerticalResolution>0</VerticalResolution>
</Print>
<DoNotDisplayGridlines/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name=”S2″>
<Table ss:ExpandedColumnCount=”3″ ss:ExpandedRowCount=”11″ x:FullColumns=”1″
x:FullRows=”1″>
<Column ss:AutoFitWidth=”0″ ss:Width=”156″ ss:Span=”2″/>
<Row ss:AutoFitHeight=”0″ ss:Height=”0.9375″>
<Cell ss:StyleID=”s62″/>
<Cell ss:StyleID=”s62″/>
<Cell ss:StyleID=”s62″/>
</Row>
<Row>
<Cell ss:StyleID=”s63″>
<Data ss:Type=”String”>Dividend</Data>
</Cell>
<Cell ss:StyleID=”s63″>
<Data ss:Type=”String”>ID</Data>
</Cell>
<Cell ss:StyleID=”s62″/>
</Row>
<xsl:for-each select=”df:Report/df:table2/df:Detail_Collection/df:Detail”>
<Row>
<Cell ss:StyleID=”s64″>
<Data ss:Type=”Number”>
<xsl:value-of select=”@Dividend_1″/>
</Data>
</Cell>
<Cell ss:StyleID=”s64″>
<Data ss:Type=”Number”>
<xsl:value-of select=”@ID”/>
</Data>
</Cell>
<Cell ss:StyleID=”s62″/>
</Row>
</xsl:for-each>
</Table>
<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>
<PageSetup>
<Header x:Margin=”1″/>
<Footer x:Margin=”1″/>
<PageMargins x:Left=”1″ x:Right=”1″/>
</PageSetup>
<NoSummaryRowsBelowDetail/>
<NoSummaryColumnsRightDetail/>
<Print>
<ValidPrinterInfo/>
<VerticalResolution>0</VerticalResolution>
</Print>
<Selected/>
<DoNotDisplayGridlines/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>10</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</xsl:template>

</xsl:stylesheet>

Reply
Thiru says:

January 3, 2010 at 4:48 am

I have tried this excel sheet name changes.i am unable to change the name of the sheet. i have a dobut. when i try to save the ‘XML Spreadsheet’ file, It is showing the below message.
“The following features from your workbook will not be saved in XML Spreadsheet.
Autoshapes, Other Objects or charts”. Please let us know Is it gives any trouble when i change the sheetname in excel?

Reply
>L< says:

January 3, 2010 at 10:43 am

>>Please let us know Is it gives any trouble when i change the sheetname in excel

Hi Thiru,

I already have. There are some items you can’t create in SSML, and the error you’re getting tells you what they are. I have already covered this subject here:

spacefold.com/lisa/2009/02/07/YAPS-Random-acts-of-kindness-regarding-Reports-Excel-XSLT-and-you

[See the section titled: <b>What can SSML (Excel 2003 XML Dialect) do? What can’t it do? And how do you know?</b>]

… and ordinarily it doesn’t cause a problem for the technique we are discussing here.

Reply
cost of wind energy says:

January 26, 2010 at 10:45 am

This is some nice info. I guess you can teach an old dog new tricks. 🙂 Thanks for this.

Reply
Cliff Leighton says:

February 1, 2010 at 8:23 am

This blog post was very helpful! I followed the steps above, but I kept getting the errors in Visual Studio:
“Can not load XSLT specified at” “The XSLT path is invalid.” It turns out that the XSLT file HAS TO BE IN THE PROJECT in Visual Studio to make this work! It wasn’t until I saw the little camera icons next to each step which showed me that the XSLT was in the Visual Stuio Project!

Using this XSLT export has now saved my department a hour worth of work every week! Instead of using a bunch of Excel Macros to make this work, we can now send the export directly out of SSRS to the client!

Thanks!

Reply
>L< says:

February 1, 2010 at 1:56 pm

Thanks for letting me know, Cliff. I don’t remember having any issues with the XSLT not being in the project, but it always seemed like the XSLT was automatically added. I wonder if I’m attaching it a certain way that causes this. I usually press the elipsis in the Property Window for the XML file and navigate directly to the file. Good luck!

Reply
Cliff Leighton says:

February 1, 2010 at 6:04 pm

Lisa,

I found that in Visual Studio 2003 and 2005 the XSLT file had to be in the Project to work correctly.

Also, does anyone know how to make the output filename end with .XLS in an emailed SSRS Subscription with the “XML file with report data” option? The emailed file from the Subscription comes out as *filename*.xml and my users will be very confused with an XML file versus a XLS file.

Reply
>L< says:

February 1, 2010 at 6:19 pm

Hi Cliff, I will check 2005. (I generally work in 2008; I’m not fond of VS XSLT tools in general, but 2008 is better than 2005.)

I will take a look at the subscription thing and give you instructions if I can; which version am I looking at? RS 2005 or RS 2008?

Please note this: if you change to an XLS extension for SSML, and at least if your users have Excel 2007 (I think it’s okay in Excel 2003), users will get a warning that says something like: “This file is in a different format than its extension indicates, are you sure it is safe to open it?” Harmless but annoying.

My point is that you end up with an education factor either way, you pays your money and you takes your choice. But I agree that the pain factor with an XLS extension tends to be less, especially since the behavior that your users will see is less dependent on their individual computer’s client settings. The processing instruction embedded in the file, which tells the client computer that the file “belongs” to Excel, will be recognized by some computers and not by others. I think the defining factor is whether Excel 2003 was ever on that installation of Windows or not, but really am not sure.

Cheers…

Reply
Cliff Leighton says:

February 1, 2010 at 6:27 pm

Lisa,

We are running RS 2005. I believe the file share export option allows a custom filename, but I need the output emailed out of RS as an attachment.

My users will have to be OK with the “file is in a different format” message!
Thanks

Reply
>L< says:

February 1, 2010 at 6:50 pm

I understand about the email attachment part. I’ll see what I can recommend.

Re the “my users will have to be okay”, we’ll start by using a proper email message saying “You may get this warning…” and explaining it, right? So, embedded user-ed <s>. (Email as a target has its advantages.)

Reply
>L< says:

February 7, 2010 at 7:44 am

OK — I’ve answered this now (I hope), Cliff and Sanket
http://spacefold.com/lisa/2010/02/07/YAPS-on-Custom-Excel-for-RS-Drive-Subscriptions-with-a-Stick-Shift…

Reply
>L< says:

February 7, 2010 at 7:45 am

Oops — try that again

spacefold.com/lisa/2010/02/07/YAPS-on-Custom-Excel-for-RS-Drive-Subscriptions-with-a-Stick-Shift

Reply
ashish says:

February 10, 2010 at 11:23 pm

Today I found one solution for this, however I am not implement this….

A new version (R2) of SQL Server 2008 released by Microsoft in November 2009, in which they resolved this renaming of the excel sheet tabs issue ?.

Please look into these links for more information:
http://www.microsoft.com/downloads/details.aspx?FamilyID=fe0c6a31-5ad6-4eea-a865-73bbe2608bd1&displaylang=en

https://connect.microsoft.com/SQLServer/feedback/details/235985/naming-worksheets-for-excel-export-from-reporting-services

Ashish Jain

Reply
>L< says:

February 11, 2010 at 2:42 am

Hi Ashish,

Unfortunately, the November build is a CTP, not a “release”. And R2 also has PowerPivot in it — which will solve some people’s Excel problems, but definitely not all.

But, most importantly: this technique is really not for tab-naming. It’s for making the Excel the way you want it, which can be different from what the in-the-box Excel export gives you, in many other ways.

Reply
TomP says:

July 29, 2010 at 2:18 am

I’m sorry to say that I’m stuck on the beginning of your instructions. I have exported my SSRS report in Excel format. I then need to open this file in MS Excel 2003 and save as SSML? I don’t see this file extension (.ssml) in 2003? I have also exported my report as “XML file with report data”.

What am I missing on the SSML?

Reply
Sachin Chopra says:

September 14, 2010 at 7:13 pm

Wonderful blog. Really helpful.

Reply
Pallab Gupta says:

July 18, 2011 at 11:28 am

Hi,
When I create my xslt and apply it to sample xml data produced by exporting from SSRS, I get a perfect output. I used the VS XSL debugger and XML Notepad for the transformation and in both cases, the output was readily opened by Excel. However when I applied the XSLT in step 4 and ran it, the save XML with data yielded only one line:
[quote]
<?xml version=”1.0″ encoding=”utf-8″?>[/quote]

The same happened after I uploaded the files to SSRS. Any idea what’s going on? There is no error.

Reply
LSN says:

July 19, 2011 at 1:59 am

Hi Pallab,

Did you follow the instructions in the comments here regarding the escaped processing instruction? Search for “kludge” in the comments in this page and you will see it.

I hope this helps,

>L<

Reply
Pallab Gupta says:

July 19, 2011 at 4:11 am

Thanks Lisa. I had to put in the processing instruction. It still did not yield the rest of the XML and I found out that the reason was completely different.

When I was applying the XSLT transform through the VS XSL debugger or through XML notepad, I had to explicitly declare the namespace in the match condition like this:
[quote]<xsl:for-each select=”.//tns:table1_Group1″>[/quote]
where [quote]tns:[/quote] had been declared in the namespace declarations at the top of the XSLT. However, the same did not work from withing the Report Viewer and I had to change it to [quote]<xsl:for-each select=”.//table1_Group1″>[/quote]. It works fine for both the VS Report Viewer as well as SSRS.

Thanks for all the help. You rock!

Reply
>L< says:

July 24, 2011 at 3:48 pm

hi again Pallab,

Glad you got it to work. Thanks for bringing up that issue.

You’ll notice that all my examples on this site that involve XSLT for RDLs use local-name() = [value] or */… or whatever to avoid namespace problems.

I try not to use the // and I think you should avoid it also, but that has nothing to do with RDLs. It’s an XSLT performance issue. If you think you don’t have an alternative, please post an example and I’ll explain. Good luck!

Reply
Patrik says:

November 6, 2011 at 8:47 am

Thanks for a great article.
You rock

Reply
MOJO says:

February 20, 2012 at 8:51 pm

http://blogs.msdn.com/b/robertbruckner/archive/2010/04/25/report-design-reset-page-number-on-group.aspx

Have a look at this link – worked for me…

Reply
Patricia says:

February 25, 2012 at 4:51 am

Hi Lisa,

When i save my excel export file as an XML spreadsheet, is that the same as an ssml file?

Reply
>L< says:

February 25, 2012 at 8:19 am

Patricia: Yes, I think so. Which version of Excel are you working in? >L<

Reply
>L< says:

February 25, 2012 at 8:25 am

Mojo — of course it did (worked for you). And of course I’ve seen that entry — And I’ve discussed it in another blog post… but this post and a number of others on similar topics were written *before* 2008 R2 and those capabilities (changing page names and resetting page number). Besides, you’ll find that using XSLT to build your own Excel can do much, much more :-).

>L<

Reply
sc says:

June 8, 2012 at 2:03 am

Hi Lisa,

After a few days of playing around, I've been generally able to follow this post (I'm new to SSRS, stylesheets – everything!). So, thank you! I am able to run my report and export to XML which opens as an Excel workbook with formatting matching my stylesheet. However, the actual report data is not populating. I've seen at least one more comment here about this, but no suggestions (I apologize if I missed it). Any thoughts on this? I am referencing my table's data using the name provided for each data cell in the table – example @ID2.

Reply
lsn says:

June 8, 2012 at 5:07 am

Hi SCook,

If you are a real person, you can send me the data XML from SSRS and your XSLT and I'll take a look at it quickly. Probably it would be good if you also sent me an example of the expected result (a PDF export would probably be fine for that). Send all 3 as a zip, I guess.

I'm sure it's something simple that you have to fix in the XPath; have you paid attention to the namespacing problem if you're explicitly referencing nodes, for example? This is discussed elsewhere in the comments on this page, but I don't mind checking quickly for you.

>L<

Reply

132 thoughts on “Changing the Sheet names in SQL Server RS Excel: QnD XSLT”

Leave a Reply Cancel reply