Group Totals Redux: Updating this technique in RS 2008 R2

by Lisa Nicholls Sun, September 19 2010 16:36

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 (http://spacefold.com/lisa/search.aspx?q=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.

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:

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: 

 2.  So what's under those nifty placeholders? 

 GroupPgNo and Group Pages each use one of the Globals with which you're familiar, and which have given you such unsatisfactory results in the past (=Globals!PageNumber and =Globals!TotalPages ).  ReportPageNo and Report Pages use two new ones: =Globals!OverallPageNumber AND =Globals!OverallTotalPages .

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

One simple change to the properties of the group in which we're requesting a page break:

OMG it's so easy

Hard to believe, isn't it?

The only code left here is a little thing I did underneath This Region, 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 property for my Region textbox to work reliably in PDF export.  In any case, the expression under the placeholder is =Code.GetThisRegion(ReportItems("Region").Value)

... and the code is pretty trivial, you'll agree:

Public ThisRegion As String = ""

Function
GetThisRegion(ByVal tS As String) As String
   If Len(tS) > 0 AndAlso tS <> ThisRegion Then
      ThisRegion = tS
   End If
   Return ThisRegion
End Function

Back to Joe's original question

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. 

In the original example, I used the ReportingService class in the ReportServer 2000 API, 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.

That's not a typo.  Use the 2005 APIs to access RS 2008, or 2006 (I think, never having done this) if you're in SharePoint integrated mode.

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.

 

 

... 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.

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:

Protected Sub GetReportHTML _
   (ByVal sender As Object, ByVal e As System.EventArgs )

   ' Dim rs As New ReportingService2005() 
   ' rs.Credentials = System.Net.CredentialCache.DefaultCredentials 
   Dim rsE As New ReportExecutionService() 
   rsE.Credentials = System.Net. CredentialCache.DefaultCredentials 

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

   '' Prepare report parameters. You can still do this 
   '' if your report has some parameters you want to pass, 
   '' we just don't have the need to pass our "special" 
   '' ones for the group total code any more:... 
  
Dim parameters(2) As RSExec.ParameterValue 
   'parameters(0) = New ParameterValue() 
   'parameters(0).Name = "FileKey" 
   'parameters(0).Value = fileKey 
   'parameters(1) = New ParameterValue() 
   'parameters(1).Name = "PreProcess" 
   'parameters(1).Value = True 
   'parameters(2) = New ParameterValue() 
   'parameters(2).Name = "ExportType" 
   'parameters(2).Value = "HTML" 

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

     
rsE.LoadReport(reportPath, Nothing
      ' rsE.SetExecutionParameters(parameters, Nothing) 
      result = rsE.Render(format, Nothing, Nothing, Nothing, _
             Nothing, Nothing, Nothing
      'sh.SessionId = rs.SessionHeaderValue.SessionId 

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

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

   ' Dim parameters(2) As RSExec.ParameterValue - if needed,
 
   ' set up whatever params you want. 
   Try 
      rsE.LoadReport(reportPath, Nothing
      ' if needed: rsE.SetExecutionParameters(parameters, Nothing) 
      result = rsE.Render( _ 
         format, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing
      Response.ContentType = "Application/pdf" 
      Response.OutputStream.Write(result, 0, result.Length) 
      result = Nothing 
   Catch ex As Exception 
      Response.Write(ex.Message()) 
   End Try 
   rsE.Dispose() 
   rsE = Nothing
End Sub

 

Pretty similar to what we did before, only much, much simpler. 

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.

If not, I think it's time for me to get back to work on XMLRSDocs. 

Have I missed your question here?

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). 

 Feel free to use the contact page to send me a message with details,rather than a comment, and I'll do my best.

Tags:

Reporting | SQL Server

Comments (5) -

9/20/2010 1:22:51 AM #

Joe

L,
You were spot on about the session header Id catching me out Smile , this updated info is invaluable.... I will most likely update my reports using the new cleaner way it looks very nice compared to the xml crunching way, will let you know how I get on.  Many thanks  for taking time out to help.
Joe

Joe Ireland

9/20/2010 6:36:30 AM #

&gt;L&lt;

You're very welcome, Joe. Things changed a lot in the 2008 time period regarding how Reporting Services and Report Manager are hosted, and this may be why we're not seeing the obvious "session" information.  For anybody who really needs it, I'm sure it's there in the new APIs somewhere.  Fortunately we don't <g>.

>L< United States

10/29/2010 6:49:52 AM #

tc

We have implemented the ghostscript printer in a vfp applicaiton and it works and installs correctly in everything except windows 7.  the message says
operation could not be completed (error 0x00000705) windows could not locate a suitable printer driver

and it does not install the printer driver
can you assist please?

tc United States

10/29/2010 12:24:32 PM #

&gt;L&lt;

Yes, I think I can.  Please see spacefold.com/.../...kend-(or-Endless-Vistas).aspx -- I think you will find your question answered there.

>L< United States

7/23/2011 2:01:45 AM #

Wino Katowice

Hello

It's too complicated for me. I'm looking for a person who would do this for me...

Greetings

Wino Katowice

Wino Katowice Poland