No rest for the wicked, apparently.

I admit to having given C some ammunition on this subject, from time to time, but I really try not to rant too much about UI in this blog.   I also try not to complain too much about MS documentation in general, having some sympathy for the User Ed people or, more specifically, the conditions under which they do their work.  Actually I try not to discuss MS documentation at all, unless it's some docs I personally had something to do with.

I don't even have an MS Office category on this blog.  I rarely talk about macro-oriented Office development.  I don't like admitting that I do a ridiculous amount of VB macro work on behalf of users or when -- as today -- it looks like it might help make a team member's documentation work more efficient.

But this one's a classic.  Is there any way the addition of a "Help" button in this dialog could be more pathetic?

Uma (whoever that is) has posted a question in response to http://spacefold.com/lisa/post/Dynamically-loading-reports-in-ReportViewers.aspx , which as far as I can tell is completely unrelated to that post <g>.

Hi i want to display subtotal of first page in the top of next page in rdlc report and i am using page break of 10 in every page.

Unrelated to dynamically loading reports or not, the question certainly covers a subject that I'm familiar with and will address here in case I haven't in the past.

First, just in case I haven't already talked about conditional page breaks on <item number>, this is just a variant of other ROW_NUMBER() techniques we've used to resolve past problems.  Let's say my SQL query looks something like this, in Uma's case:

 SELECT
             Sales_No, Customer,Order_Quantity,
             CEILING(CAST( ROW_NUMBER() OVER (ORDER BY Customer)  / 10  AS
             Numeric(10,2))) AS Grouper
 FROM OrderHeader
ORDER BY Customer

... where the "/10" in the above query is determined by the "page break of 10" in Uma's scenario.  OK so far?

So now my group break is simply the expression =Fields!Grouper.Value, and I set this group to page break at end. 

NB: I always think it's nice to let SQL do most of the work, if I can.

In your situation, you might be doing it differently, or in a more RS-oriented way.  That's okay, how you happen to be determining your page break is not really germane to Uma's question at all. The point is, however you're doing your grouping... the work is almost all done already.  It looks as though we have a lot of work left, maybe, but really, it's almost done.

So now let's say we have a subtotal expression (for Uma) that, in my example, looks like this, in the footer of this group: =Sum(Fields!Order_Quantity.Value).

And let's say we change that footer expression to do this: =Sum(Fields!Order_Quantity.Value) & Code.MyUdf(Sum(Fields!Order_Quantity.Value)).

And let's say we write the following (really minimal) amount of code:

   Public PrevPage As Double = 0

   Function MyUdf( t As Double) AS String
       PrevPage = t
   Return ""
   End Function

...   and now let's say we put a textbox in the group header with the following expression: =IIF(Code.PrevPage = 0,"",Code.PrevPage) (the IIF() is just to take care of the first page, because I'm too lazy to worry about whether this is the most elegant way to do it or not).

That's all she wrote, folks.  Seems to work just as you'd want.  No big deal, really.

Why the title of this post?

First, you should know it's a quote from "Little Big Man".  I'm taking a "Serenity" break, just for kicks.

Second, this is one of those times when multiple export types (Excel rendererer, PDF renderer, etc) don't get in the way. You notice I haven't put anything in the page header; I'm avoiding any issues of "what sequence does everything happen in" in the various rendering engines that way.  Sometimes the magic can... just work. 

I really can't believe I'm revisiting this subject... I thought I was done with it ... but this is worth saying because it's helpful to anybody generating SSML (Excel 2003 XML dialect) whether they are interested in RS or not.  I also can't believe I never figured this out before -- because I generate a lot of SSML, for all kinds of custom reports.

So last time I said this 

[...] It also happens to be a situation, alluded to in my walkthrough, where you have to be careful that your dynamically-driven tab names are actually legal tab names in Excel.  In this case, I used the following code to validate and change the tab names if necessary:

<xsl:template name="StripSheetName">
      <xsl:param name="item"/>
      <xsl:variable name="i1">
         <xsl:value-of select="translate($item,'&quot;“”','')"/>
      </xsl:variable>
      <xsl:value-of select="translate($i1,&quot;/\'&quot;,&quot;&quot;)"/>
</xsl:template>

... looks a bit wierd and it may not turn out to be sufficient, but basically all I'm doing is taking out characters I know to be illegal.  If I find more, I'll add more.

Guess what?  It didn't turn out to be sufficient. 

The items I'm using for tab names in this report are particularly long and (who knew?) Excel has a tab name limit of 31 characters. Before you ask, it's not in the Excel specifications and limits page as far as I can see. 

Also before you ask I figured out the exact number by typing 1234512345123451234512345123451 into a tab.  That last "1" is the last character Excel will let you type.  If anybody has a better source of information, I'm all ears.

So, I smartened up the template you see above a little bit.  In this particular workbook, we're adding some verbiage onto the end of entries for each tab, which you'll see referenced; other than that, you may be able to use this puppy. 

I'm stripping blank spaces out to conserve some tablength room in this version; this isn't required, because spaces are allowed in tab names, but I was trying to save some room for more meaningful content.   You'll also notice a use of two dots ("..") wherever I'm truncating a tab name, and your strategy might be slightly different... 

In any case, all-in-all, something like this should work for you.

 <xsl:template name="StripSheetName">

      <xsl:param name="item"/>
     <xsl:param name ="secondTable"/>

     <!-- get rid of smart quote and quote marks, not allowed
           and remove spaces too, to save room -->
      <xsl:variable name="i1">
         <xsl:value-of select="translate($item,' &quot;“”','')"/>
      </xsl:variable>

     <!-- get rid of other disallowed characters, including
            single-quote (apostrophe) -->
     <xsl:variable name="i2" select="translate($i1,&quot;/\'&quot;,&quot;&quot;)"/>

     <xsl:variable name="i3" >
       <xsl:choose>
         <xsl:when test="string-length($i2) &gt; 20">
         <!-- truncate, leaving enough spare room for suffix,
                and add dots -->
           <xsl:value-of select ="concat(substring($i2,1,18),'..')"/>
         </xsl:when>
         <xsl:otherwise>
           <xsl:value-of select ="$i2"/>
         </xsl:otherwise>
       </xsl:choose>
     </xsl:variable>

    <!-- add appropriate suffix for this tab -->
     <xsl:choose>
       <xsl:when test ="$secondTable">
         <xsl:value-of select ="concat($i3,'ByActivity')"/>
       </xsl:when>
       <xsl:otherwise>
         <xsl:value-of select ="concat($i3,'ByCategory')"/>
       </xsl:otherwise>
     </xsl:choose>

   </xsl:template>

Also before you ask: what you see up there with escaped characters is correct (for example where you see &gt; (for greater than, in XSLT) that's not an error of HTML formatting. 

The parts that look especially weird come from explicitly specifying delimiters, such as quotation marks, as string literals, but this really does work, and you may really need to do it now and again.

Enjoy... and god bless Excel and all its peculiarities <sob>.