{"id":76,"date":"2010-09-19T10:36:00","date_gmt":"2010-09-19T10:36:00","guid":{"rendered":"\/lisa\/post\/2010\/09\/19\/Group-Totals-Redux-Updating-this-technique-in-RS-2008-R2.aspx"},"modified":"2010-09-19T10:36:00","modified_gmt":"2010-09-19T10:36:00","slug":"group-totals-redux-updating-this-technique-in-rs-2008-r2","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2010\/09\/19\/group-totals-redux-updating-this-technique-in-rs-2008-r2\/","title":{"rendered":"Group Totals Redux: Updating this technique in RS 2008 R2"},"content":{"rendered":"

As I mentioned last time<\/a>, Joe Carey has been using my group totals techniques from a relatively old walkthrough here, which was complicated enough to be a two-parter (https:\/\/spacefold.com\/lisa\/?s=pagetotals<\/a> will give you both parts).  It could not have been made any shorter without leaving out vital bits.  That’s because the whole thing was a giant kludge.<\/p>\n

Along comes RS 2008 R2 and makes the whole thing unnecessary, I’m happy to say. To re-work the original report example, here’s all you’d do instead:<\/p>\n

1. Use something like the following expression, instead of my bunch-of-code, to express your page numbers; I’ll take advantage of this opportunity to demonstrate again how clear multiple, well-labelled placeholders can make things in a single text box, but of course you can set up the expressions however you want: <\/p>\n

\"\"<\/p>\n

 2.  So what’s under those nifty placeholders? <\/p>\n

 GroupPgNo<\/span> and Group Pages<\/span> each use one of the Globals with which you’re familiar, and which have given you such unsatisfactory results in the past (=Globals!PageNumber<\/strong> and =Globals!TotalPages <\/strong>).  ReportPageNo<\/span> and Report Pages<\/span> use two new ones: =Globals!OverallPageNumber<\/strong> AND =Globals!OverallTotalPages<\/strong> .<\/p>\n

3. So how are we getting good results from the two well-known and ill-behaved Globals on a group level now?<\/p>\n

One simple change to the properties of the group in which we’re requesting a page break:<\/p>\n

\"\"<\/p>\n

OMG it’s so easy<\/h3>\n

Hard to believe, isn’t it?<\/p>\n

The only code left here is a little thing I did underneath This Region<\/span>, to get the right value in the page header for each region;  this probably wouldn’t have been necessary if I’d been able to figure out why I couldn’t get the RepeatWith<\/strong> property for my Region textbox to work reliably in PDF export.  In any case, the expression under the placeholder is =Code.GetThisRegion(ReportItems(“Region”<\/span>).Value)<\/p>\n

… and the code is pretty trivial, you’ll agree:<\/p>\n

Public<\/span> ThisRegion As<\/span> String<\/span> = “”<\/span>

Function<\/span> GetThisRegion(ByVal<\/span> tS As<\/span> String<\/span>) As<\/span> String<\/span>
   If<\/span> Len(tS) > 0 AndAlso<\/span> tS <> ThisRegion Then<\/span>
      ThisRegion = tS
   End<\/span> If<\/span>
   Return<\/span> ThisRegion
End<\/span> Function<\/span><\/p>\n

Back to Joe’s original question<\/h3>\n

While we don’t have to use the kludge for group totals, naturally some of you are going to want an updated example of SOAP usage anyway.  It’s often a requirement to pull a report from the server in code and then display the output to the user under your own steam. <\/p>\n

In the original example, I used the ReportingService class in the ReportServer 2000 API<\/a>, which was still available in 2005 and was a simple path to take.  (To be honest, I didn’t think about it very much because I generally prefer URL Access, but had shown that alternative in a number of other examples.)   Joe would like to do the same thing using the 2005 APIs.<\/p>\n

That’s not a typo.  Use the 2005 APIs to access RS 2008, or 2006 (I think, <\/em>never having done this) if you’re in SharePoint integrated mode.<\/p>\n

In the updated APIs, a different class (in a different WSDL) becomes the main entry point for the type of work I was showing you: ReportExecutionService.<\/p>\n

 <\/p>\n

 <\/p>\n

\"\"<\/p>\n

… Note that you would use the ReportingService2005 class (via ReportService2005.asmx), instead of or along side, ReportExecutionService ( ReportExecution2005.asmx) if you wanted to interrogate the report catalog, find out about a report’s parameters dynamically, etc.<\/p>\n

I’ll briefly sketch out the revised code under the two buttons in my client-side walkthrough sample, here, leaving the original code in comments for the first method:<\/p>\n

Protected<\/span> Sub<\/span> GetReportHTML _
   (ByVal<\/span> sender As<\/span> Object<\/span>, ByVal<\/span> e As<\/span> System.EventArgs<\/span> ) <\/p>\n

   ‘ Dim rs As New ReportingService2005()<\/span> 
   <\/span>‘ rs.Credentials = System.Net.CredentialCache.DefaultCredentials<\/span> 
   <\/span>Dim<\/span> rsE As<\/span> New<\/span> ReportExecutionService() 
   <\/span>rsE.Credentials = System.Net. CredentialCache<\/span>.DefaultCredentials <\/p>\n

   <\/span>‘ Let’s add the correct Render arguments 
   <\/span><\/span>Dim<\/span> result As<\/span> Byte<\/span>() = Nothing<\/span> 
   <\/span>Dim<\/span> reportPath As<\/span> String<\/span> = “\/Tests\/GroupTotals2008R2”<\/span> 
   <\/span>Dim<\/span> format As<\/span> String<\/span> = “HTML4.0”<\/span> 
   <\/span>” For the real fileKey value, <\/span>
   <\/span>” you would want to put something in <\/span>
   <\/span>” here that uniquely identifies the session and the user, <\/span>
   <\/span>” not just the output type as I am doing here… <\/span>
   <\/span>‘Dim fileKey As String<\/span> 
   <\/span>fileKey = “TestHTML”<\/span> <\/p>\n

   <\/span>” Prepare report parameters. You can still do this<\/span> 
   <\/span>” if your report has some parameters you want to pass,<\/span> 
   <\/span>” we just don’t have the need to pass our “special”<\/span> 
   <\/span>” ones for the group total code any more:… 
   <\/span><\/span>Dim<\/span> parameters(2) As<\/span> RSExec.ParameterValue 
   <\/span>‘parameters(0) = New ParameterValue()<\/span> 
   <\/span>‘parameters(0).Name = “FileKey”<\/span> 
   <\/span>‘parameters(0).Value = fileKey<\/span> 
   <\/span>‘parameters(1) = New ParameterValue()<\/span> 
   <\/span>‘parameters(1).Name = “PreProcess”<\/span> 
   <\/span>‘parameters(1).Value = True<\/span> 
   <\/span>‘parameters(2) = New ParameterValue()<\/span> 
   <\/span>‘parameters(2).Name = “ExportType”<\/span> 
   <\/span>‘parameters(2).Value = “HTML”<\/span> <\/p>\n

   <\/span>‘ This doesn’t have the same meaning any more<\/span> 
   <\/span>‘ because we aren’t double-calling for the preprocess<\/span> 
   <\/span>‘ to calculate pages:<\/span> 
   <\/span>‘Dim sh As New SessionHeader()<\/span> 
   <\/span>‘rs.SessionHeaderValue = sh<\/span> 
   <\/span>Try<\/span> 
      <\/span><\/span>‘result = rs.Render(reportPath, format, Nothing, Nothing, parameters, _<\/span> 
            <\/span><\/span><\/span><\/span>‘ Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)<\/span> 

      <\/span><\/span>rsE.LoadReport(reportPath, Nothing<\/span> ) 
      <\/span><\/span>‘ rsE.SetExecutionParameters(parameters, Nothing)<\/span> 
      <\/span><\/span>result = rsE.Render(format, Nothing<\/span>, Nothing<\/span>, Nothing<\/span>, _
            <\/span><\/span><\/span><\/span> Nothing<\/span>, Nothing<\/span>, Nothing<\/span>) 
      <\/span><\/span>‘sh.SessionId = rs.SessionHeaderValue.SessionId<\/span> <\/p>\n

      <\/span><\/span>” Now we’ll render a second time before displaying the results, <\/span>
      <\/span><\/span>” letting the report know that we are not in “preprocess” mode this time: <\/span>
      <\/span><\/span>‘parameters(1).Value = False<\/span> 
      <\/span><\/span>‘result = rs.Render(reportPath, format, Nothing, Nothing, parameters, _<\/span> 
      <\/span><\/span>‘ Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)<\/span> 
      <\/span><\/span>Response.OutputStream.Write(result, 0, result.Length) 
      <\/span><\/span>result = Nothing<\/span> 
   <\/span>Catch<\/span> ex As<\/span> Exception 
      <\/span><\/span>Response.Write(ex.Message()) 
   <\/span>End<\/span> Try<\/span> 
   
   <\/span><\/span>‘sh = Nothing<\/span> 
   <\/span>‘RS.Dispose()<\/span> 
   <\/span>‘RS = Nothing<\/span> 
   <\/span>rsE.Dispose() 
   <\/span>rsE = Nothing<\/span>
End<\/span> Sub<\/span>

Protected<\/span> Sub<\/span> GetReportPDF _
      <\/span><\/span>(ByVal<\/span> sender As<\/span> Object<\/span>, ByVal<\/span> e As<\/span> System.EventArgs<\/span> ) 
   <\/span>Dim<\/span> rsE As<\/span> New<\/span> ReportExecutionService() 
   <\/span>rsE.Credentials = System.Net. CredentialCache<\/span>.DefaultCredentials 
   <\/span>Dim<\/span> result As<\/span> Byte<\/span>() = Nothing<\/span> 
   <\/span>Dim<\/span> reportPath As<\/span> String<\/span> = “\/Tests\/GroupTotals2008R2”<\/span> 
   <\/span>Dim<\/span> format As<\/span> String<\/span> = “PDF”<\/span> 

   <\/span>‘ Dim parameters(2) As RSExec.ParameterValue – if needed,<\/span> 
   <\/span>‘ set up whatever params you want.<\/span> 
   <\/span>Try<\/span> 
      <\/span><\/span>rsE.LoadReport(reportPath, Nothing<\/span> ) 
      <\/span><\/span>‘ if needed: rsE.SetExecutionParameters(parameters, Nothing)<\/span> 
      <\/span><\/span>result = rsE.Render( _ 
         <\/span><\/span><\/span>format, Nothing<\/span>, Nothing<\/span>, Nothing<\/span>, Nothing<\/span>, Nothing<\/span>, Nothing<\/span>) 
      <\/span><\/span>Response.ContentType = “Application\/pdf”<\/span> 
      <\/span><\/span>Response.OutputStream.Write(result, 0, result.Length) 
      <\/span><\/span>result = Nothing<\/span> 
   <\/span>Catch<\/span> ex As<\/span> Exception 
      <\/span><\/span>Response.Write(ex.Message()) 
   <\/span>End<\/span> Try<\/span> 
   <\/span>rsE.Dispose() 
   <\/span>rsE = Nothing<\/span>
End<\/span> Sub<\/span><\/p>\n

 <\/p>\n

Pretty similar to what we did before, only much, much simpler. <\/h3>\n

I’m wondering if it is the “session” stuff that tripped up Joe.   In any case, we don’t need it any more because we’re not double-calling the report to do our preprocess run.  I’ll revisit this subject if it is seems necessary for anybody with a different scenario they want to share and discuss.<\/p>\n

If not, I think it’s time for me to get back to work on XMLRSDocs. <\/p>\n

Have I missed your question here?<\/h3>\n

I think I’ve pretty much cleared out my backlog of promised\/pending solutions for now.  If you’ve asked me a question and I didn’t answer it, chances are I thought it was spam (it’s getting hard to wade through it all). <\/p>\n

 Feel free to use the contact page to send me a message with details,rather than a comment, and I’ll do my best.<\/p>\n","protected":false},"excerpt":{"rendered":"

As I mentioned last time, Joe Carey has been using my group totals techniques from a relatively old walkthrough here, which was complicated enough to be a two-parter (https:\/\/spacefold.com\/lisa\/?s=pagetotals will give you both parts).  It could not have been made any shorter without leaving out vital bits.  That’s because the whole thing was a giant kludge. AlongRead more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6],"tags":[],"class_list":["post-76","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/76","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=76"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/76\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=76"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=76"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=76"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}