Report Preprocessing: SQL Server Reporting Server Group PageTotals, Part II: A Walkthrough on the Client Side

by Lisa Nicholls Mon, March 03 2008 17:14

Last time, as you remember, we handled the server-side and coding details of a preprocessed report (and gory details they were).  You saw that we set up the appropriate information for values that we might need in a preprocess run, and then the right information is available to you on the followup run.

While this technique is useful for the "Group Page Totals" problem under discussion, it is applicable to many more cases.  For example, you could apply it to a report that required a table of contents or an index.  So even if you don't need group page totals you might want to give this some thought -- and it's really a pity that there isn't an obvious built-in way to tell the RS reporting engine "please do all the work according to the needs of a particular renderer, but don't give me any output, then perform a second run". 

The key thing here is according to the needs of a particular renderer. You can provide for a "null output" rendering extension pretty easily; in fact I think there is one built-in to the Report Server configuration file, although it is excluded, by default, from the Export formats that will show up in the drop down.  Maybe this type of job is what it's for.

But you have to render according to a particular export format, even when you're not getting any physical output, to get the pagination that corresponds to that export format.  And that's an entirely different thing.

So let's get to it.

In this example, as I explained earlier, I've chosen to use an ASP.NET client, but the type of client should not matter. ASP.NET just has one more wrinkle, displaying a PDF appropriately in the browser, so I thought I would include that in my example code.

I've also chosen to access the report using the RS web service, simply because in previous examples I've used URL Access, and I thought it would make a nice change.

So let's say we have two buttons on a web page, looking something like this:

<input type="submit" name="Button1" value="GetPreprocessedReport" id="Button1" /> &nbsp;
<input type="submit" name="Button2" value="GetPDF" id="Button2" />

... that's pretty much it.

Now we have to write the code under those two buttons.  The key thing is pushing the parameters over appropriately for each run (preprocess and "real"), for each type.

Here's the code for Button1:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click 

  ' most of the report-accessing code here 
  ' is pretty much straight out of  
  ' http://msdn2.microsoft.com/en-us/library/aa258532(SQL.80).aspx
 

  Dim rs As New ReportingService()
  rs.Credentials = System.Net.CredentialCache.DefaultCredentials

  ' Let's add the correct Render arguments
  Dim result As Byte() = Nothing
  Dim reportPath As String = "/ReportProject1/SqlWorldGroupTotals"
  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.
  Dim parameters(2) As 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"
  Dim sh As New SessionHeader()
  rs.SessionHeaderValue = sh

  Try
      result = rs.Render(reportPath, format, Nothing, Nothing, parameters, _
         Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)
      ' We've just done the preprocess run. We're not going to do anything
      ' with those results, though.

      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

End Sub

Nothing too fancy there.

And, as you can probably figure out, the code for Button2 is almost exactly the same, except for our various parameter values and the Render format, but we'll also add an appropriate content type so that the browser will display it properly:

[Excerpted] Protected Sub Button2_Click( ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
  ' ...
  fileKey = "TestPDF"
  ' ...
  Dim format As String = "PDF"
  ' ...
  parameters(2).Value = "PDF"
  ' ... we'll get our second result as before:
  result = rs.Render(reportPath, format, Nothing, Nothing, parameters, _
               Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)
  ' but then:
  Response.ContentType = "Application/pdf"
  ' followed by the same:
  Response.OutputStream.Write(result, 0, result.Length)
  ' ... See?

When you examine your results in the browser you can clearly see the difference in overall page total for the two rendering targets.

Preprocessing results 

We're all done, and it wasn't too tough, except that I somehow forgot to work in my requirement of at least one Serenity quote per post, at least so far.  So here's our Serenity thought for the day:

We've done the impossible, and that makes us mighty.

Doesn't hardly seem like this was the impossible.

But if you look back at the previous post and put the whole effort together... you realize that these are words for RS folks to live (and code) by.

' Til next time.

Tags:

Reporting | SQL Server