The title gives away the punchline on this one...

Cliff Leighton asks

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.

and at least one other person (Sanket) appeared to have a similar problem last November:

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

... which certainly makes it a question worth answering. I asked Sanket this:

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

... and you'll see why shortly.

Before I answer, the inevitable caveat

Folks, while an XML extension may be confusing, consider the following:

• If the user's computer has ever had Excel 2003 installed on it, chances are that a double-click will open the file in Excel by default, and the XML file will show an appropriate/specialized icon to show that it is an "Excel XML file", not a standard XML icon.  Not terribly confusing.  How does this work?  It's all down to the processing instruction that we added to the SSML:
  <?mso-application progid="Excel.Sheet"?>
  
  This screen shot shows you what it looks like when it works as expected (remember, this will be different depending on the client computer; for example, the Standard XML file icon you see here belongs to Stylus Studio, because that's my default owner for this type of file on this computer):
  
  

  

• If the user has Excel 2007 installed as the default owner for files with an XLS extension, and the file is really SSML rather than Excel binary, the result they will when they doubleclick is a warning like the following:
  

  
  
  Naturally, you can tell your users to ignore this warning and answer "Yes" to the dialog.  But it's not exactly what I would call un-confusing.

So, IMHO... you pays your money and you takes your choice.  With that understood, let's address the stated requirement.

The short answer is "no", but we don't care.

Try to remember that the default subscription interface you see in Report Manager is just a basic UI given to you by default. 

Just like the parameters panel for ReportViewer, which we all love to hate, there's nothing intrinsically required about it.

Just like the Excel renderer can be obviated by creating our own SSML from the XML output, the base Excel rendition isn't the one we have to accept.

A subscription is just a timed task or job. If you look at SQL Agent, it looks something like this: 

The one step in the job looks something like this: 

exec ReportServer.dbo.AddEvent
  @EventType='TimedSubscription',
  @EventData='512f89b1-7dd8-4b2f-806b-29927da7a81e'

... and the code underneath is probably not very complicated either, although I've never looked.  (That would be like trying to fix the default Excel renderer.)

Let's leave it alone, and think about the right way to fix our problem here, which (from Cliff's point of view) is that you are allowed to set the full name of the output file when it goes to a file share, but you're not allowed to set the extension when you send the subscription to an email address.

We won't need any grenades

This is actually an easy problem to solve, several different ways (remember the paying the money and taking the choice part?)

Here's a overview of how to do this thing.

Start with the knowledge that this is just a timed task, and write your own! It will be very simple, and SSIS is probably the most natural approach (but if you don't like it you don't have to use it, as you'll easily see).

1. Use a fileshare subscription, or...

   1. If you're using SSIS, you can invoke the subscription yourself. Or use a file watcher task (these are available from better SSIS jockeys than me, so check it out) and stick with the standard subscription schedule.
      Method notes: You don't need to use the code you see above, you can just use exec sp_start_job.  (That's one reference. You'll find others, I'm sure. It turns out this is pretty much an FAQ.)
   2. Pick up the file and rename it.
   3. Email it out. 
      1. If your subscription was data driven, you should be able to look at the same data as RS does, to figure out to whom you email. 
      2. Emailing is simple, whether you use a purpose-built SSIS task or your own code.

2. ... Skip the subscription altogether. Create a timed task or SSIS job that does what you need, without one:

   1. Retrieve the report yourself, with minimal code. 
      1. You can use either SOAP or REST to do this. 
         Method notes: I've shown several examples of how to do this on this blog already, but I'll add another example here, which shows a Q&D REST approach to doing this inside SSIS.  It comes from my xmlRSDocs processing code, but you should be able to see how to apply it. 
         I'm posting this bit because I think I remember having to help somebody with the correct classes to use for a low-level post approach like this, when working inside SSIS, once.
      2. Here's an alternative -- you'll find many -- for invoking RS inside SSIS, and this one uses SOAP.  I think this is a bit overcomplicated and I think you can apply some of my other instructions for SOAP invocations more easily, including one SOAP version done quite recently. But maybe this one is more "orthodox", so I don't want to push my methods on you..
   2. Obviously when you're picking up the file yourself, name it anything you want.
   3. As above, email it out.

That's about it.  Here's the REST example I promised; this is the code from an SSIS script task.

 
Public Sub Main()  
   ' a script task to grab an SSRS report
   ' from within SSIS, based on some
   ' specialized package variables that make
   ' sense in my scenario.  Your scenario is
   ' different, but the basic idea will work for you.
 
   Dim outputFile As String, requestURL As String
 
   If Dts.Variables("ExportRSType").Value.ToString().Length = 0 Then
      Dts.Variables( "ExportRSType").Value = "PDF"
   End If

   outputFile = Dts.Variables( "ExportRSFilename" ).Value.ToString()  
 
   If System.IO.Path.GetDirectoryName(outputFile) = "" Then
      outputFile = Dts.Variables("InputDir" ).Value.ToString() & _ 
         outputFile 
   End If

   outputFile = System.IO.Path.GetFullPath(outputFile)
   requestURL = _
      Dts.Variables("ExportRSURL" ).Value.ToString() 
   requestURL += _
      "&rs:Command=Render"
   requestURL += _
      "&rs:Format=" & Dts.Variables( "ExportRSType" ).Value.ToString()  

   ' this part is a little kludge because of 
   ' how I set up my URL template variable 
   in the SSIS Package... not really germane:

    If Len(Dts.Variables( "ExportRSParams").Value.ToString()) > 0 Then
      requestURL += _
         "&" & Dts.Variables( "ExportRSParams").Value.ToString().Replace( "^", "&" )
   End If

   Dim ox As System.Net.WebRequest = _ 
      System.Net.WebRequest.Create(requestURL)
   ox.UseDefaultCredentials =  True 'TBD...
 
   Dim oy As System.Net.WebResponse = ox.GetResponse()  
   Dim raw As System.IO.FileStream = _  
   New System.IO.FileStream(outputFile, IO.FileMode.Create)  
   Dim buffer(1024) As Byte
 
   Dim rs As System.IO.Stream = oy.GetResponseStream()  
   Dim read As Integer = rs.Read(buffer, 0, buffer.Length)  
   
   While (read > 0)
      raw.Write(buffer, 0, read)
      read = rs.Read(buffer, 0, buffer.Length)
   End While

   oy.Close()
   raw.Close()
   raw.Dispose()
   rs.Close()
   rs.Dispose()
   oy = Nothing
   ox = Nothing
   raw = Nothing
   rs = Nothing
   Dts.TaskResult = ScriptResults.Success
 
End Sub

... OK?

None of this stuff is magic.

December just flew by.

I last wrote at Thanksgiving.  Here it is January, a new year, and I'm still missing Kami and wondering why she's not visible.

I'm sure she's ignoring me, wherever she is.  I just miss watching her do it. I guess we'll just have to get on with it.  And maybe I'll take the occasion to discuss a topic that has often plagued me in integration work, not just with regard to cats.

A question of empty-ness and absence   In working with Linda a couple of months ago, to figure out how to stop data from appearing when you don't want it, I found I needed to have just such a discussion: How does the evaluating code know when you want to do this? Usually it comes down to figuring out some sort of negative condition.  When the users haven't provided parameter values yet, the code should realize this and not do any unnecessary work.  Reporting Services has a default way to deal with this; you omit a default value for a parameter, and you don't allow nulls for this parameter.  The user is required to fill in that value before the work continues.  However, the default UI for this is not pleasing to many people. In this scenario, and in many integration scenarios that have nothing to do with reporting, you can be moving between environments that don't understand "null", "nil", "nothing", "blank", or even a straightforward boolean "false" exactly the same way.  You have to find some way that the receiver can understand a boolean false, a blank, or an absence of viable content, as provided by the sender.

Just now, I started to write that this is the most annoying type casting and massaging scenario I know about -- but.  Don't get me started on dates and date times. Let's stick to one annoyance at a time.

So...  

What's an appropriate strategy?

Usually I find two things have to be considered if I want my code to work with multiple calling environments:

1- The receiving environment has to define the values it considers to be a boolean false and/or a boolean true for a string according to the senders it expects.  And it has to define these generously, and widely, not according to its own ideas (so, no, the default behavior of Boolean.TryParse isn't good enough, thank you very much). 

2 - The receiving environment has to figure out what it considers blank and what it will do with values that evaluate to null-or-empty.  And here, as well, the receiving environment can publish its limitations and stick to its guns, but if it wants to have a usable and popular interface, it should bend over backwards to consider other people's ideas of proper input values, and offer some reasonable amount of choice (so, no, the default behavior of String.IsNullOrEmpty isn't good enough, either, thank you very much).

How do you carry it out?

Below is an example, in VB.Net, of how you can allow the caller to specify what "empty" content should look like, in various ways:

• Do spaces "count", are they significant to the value being evaluated? and, as a result of this,
• Does a string of spaces "count" as empty or not? 
• Should "emptiness", by default, result in an assumed True or an assumed False?

You can also see that I've set up a relatively wide set of values that will evaluate as a Boolean True (including an Xbase version, .T.), as appropriate for the potential callers of this code.

I don't say that the code is perfect, or that it fits every purpose.  I'm just saying that you really have to do this and that the concept can be "translated" into any environment. 

If you don't like my particular way of organizing the logic in the VB code, I should probably add that I've chosen this "contains" strategy because it translates particularly well to XSLT, another environment in which I code frequently. 

I'll add another example below, slightly different, but you'll see how the same ideas are preserved.  I've tried to make this obvious by naming the constants at the top of the VB snippet the same way as I've named the parameters at the top of the XSLT snippet, in these examples.

In the case of the specific XSLT template I'm showing, I'm also rendering some HTML content in either a readonly or editing form for the calling application; that's why you see two branches that send back either a straight value or a checkbox with the current boolean result filled in.  Not relevant to the current discussion, but you might find it useful.  Also, I haven't handled the "trimming" issues because I know that my callers have already applied xslt's normalize-space to what they're sending.  If I want to include a blank as a "true" value, I can do so by including a single blank in the strings evaluating to true ("| |").

From each according to its abilities, to each according to its needs.  Which brings me to one more point...

Be gracious to your less-capable partner technologies

While I personally like to set the rule that the server should bend to benefit the caller, and provide the most  flexibility possible, sometimes you don't have this option.  Sometimes the caller has to do the work.

Here's what I mean: the code you see below does translate really well to multiple environments. That includes creating an expression, such as Code!EvaluateStringToBoolean(...), for use in an RDL. 

But you don't always have the luxury of including the logic in that function at exactly the reporting-point where you need it most. Consider the scarcity of usable functions when you're writing code to create a calculated column in a Semantic Model:

The somewhat ridiculous expression you see above takes care of considering a Percentage value that may be null.  Null, not 0; zero is no problem. 

Believe it or not, casting the Percentage to a text value and then checking to see if its length is 0 is the best way I've found of saying the equivallent of IS NULL, IsBlank(), IsNothing, ==null, nil, or what-have-you, in the limited syntax allowed. 

If you have a better idea, let me know.  Meanwhile, I'll use what I can, whatever it is.

And now for those examples...

Const TheseStringsEvaluateToTrue = _
   "|YES|TRUE|ON|1|.T.|Y|RIGHT|CORRECT|YESSIREEBOB|"
Const TrueEvaluationsDelimiter = "|"

Public Shared Function EvalStringToBoolean( _
   ByVal tSource As String, _
   Optional ByVal tDefaultTarget As Boolean = False, _
   Optional ByVal tTrimString As Boolean = True) As Boolean

Dim result As Boolean

If String.IsNullOrEmpty(tSource) Then
   result = tDefaultTarget
Else

   If tTrimString Then

      Dim source As String = _
      TrueEvaluationsDelimiter & _
      tSource.Trim().ToUpper() & _
      TrueEvaluationsDelimiter

      result = TheseStringsEvaluateToTrue.Contains(source)

   ElseIf tDefaultTarget AndAlso _
      Len(tSource) > 0 AndAlso _
      Len(tSource.Trim()) = 0 Then

      result = tDefaultTarget
      ' return default if we have a string of spaces
      ' and we're not trimming.
      ' According to this library's assumptions
      ' (your mileage may vary),
      ' this condition wouldn't
      ' be handled properly by our set of "True" strings
      ' if the default is True.
   Else

      Dim source As String = _
      TrueEvaluationsDelimiter & _
      tSource.ToUpper() & _
      TrueEvaluationsDelimiter

      result = TheseStringsEvaluateToTrue.Contains(source)

   End If

End If

Return result

End Function

Here's the XSLT version...

<xsl:param name="StringsEvaluateToTrue" select="'|1|true|True|T|Y|y|.T.|.t.|'"/>
<xsl:param name="TrueEvaluationsDelimiter" select="'|'"/>

<xsl:template name="Boolean">
 <xsl:param name="condition" select="false()"/>
  <xsl:param name="trueRepresentation" select="'Y'"/>
 <xsl:param name="falseRepresentation" select="'&amp;nbsp;'"/>
 <xsl:param name="editname" select="''"/>
 <xsl:param name="editable" select="false()"/>
       <xsl:choose>
          <xsl:when test="string-length($editname) &gt; 0">
               <input type="checkbox" name="{$editname}" value="ON">
                  <xsl:if test="contains($StringsEvaluateToTrue,
                      concat($TrueEvaluationsDelimiter,
                             string($condition),
                             $TrueEvaluationsDelimiter))">
                       <xsl:attribute name="checked">ON</xsl:attribute>
                  </xsl:if>
                  <xsl:if test="$editable=false()">
                     <xsl:attribute name="disabled">yes</xsl:attribute>
                  </xsl:if>
              </input>
          </xsl:when>
           <xsl:when test="contains($StringsEvaluateToTrue,
                         concat($TrueEvaluationsDelimiter,
                          string($condition),
                          $TrueEvaluationsDelimiter))">
                          <xsl:value-of select="$trueRepresentation"/>
           </xsl:when>
           <xsl:otherwise>
              <xsl:value-of select="$falseRepresentation"
              disable-output-escaping="yes"/>
           </xsl:otherwise>
      </xsl:choose>
 </xsl:template>