Walkthrough, Part II: Dynamic Interactive Paging and another fierce look at parameters

Posted on by LSN 12 Comments

Last time we got as far into Karen's scenario as defining the way the report will handle her pagination requirements.  Now, it's time to show how the reports are invoked and how — the critical part — we make sure that we swap one parameter value when the user wants to export.

I'm going to do this by telling the ReportViewer control  not to display the Print and Export buttons — here's my simple definition —

<rsweb:ReportViewer ID="ReportViewer1" runat="server"
   Height="480px" ProcessingMode="Remote" Width="680px"
   ShowExportControls="False"
   ShowPrintButton="False">
   <ServerReport
      ReportPath="/BlogTests/PaginationSQLWorld"
      ReportServerUrl="http://localhost:8080/reportserver" />
</rsweb:ReportViewer>  

… and I'm going to add a couple of buttons to the page to perform Export instead:

<asp:Button ID="btnExcel" runat="server"
   onclick="btnExcel_Click" Text="Export Excel" />

<asp:Button ID="btnPDF" runat="server"
   onclick="btnPDF_Click" Text="Export PDF" /> 

Method 


We'll get to the code in a moment.  (You'll find the full CS/ASPX/RDL files here: DynamicInteractivePagination.zip (13.04 kb).)

Basically, these buttons are going to get a copy of the parameters that the user has currently selected and clone them all, except for our special parameter.

For PerPageLimit, it will always pass -1.  Something like this (in my version anyway) :

   p2[ii] = new RS.ParameterValue();
   p2[ii].Name = p.Name;

   if (p.Name == "PerPageLimit")
   {
      p2[ii].Value = "-1";
   }
   else    // continue here with cloning any other param…

Having set up its copy of the parameters, it's going to request an export from Reporting Services in the same way as the ReportViewer itself probably does the job: by using the RS SOAP API, with this adjusted set of parameters. In my simple version, it's going to write the result to Response, but you could think of a lot of other ways if you don't like what I'm doing.

You notice that I'm not bothering with a special "print" facility.  The printing ability of the ReportViewer control is really kind of hokey. We'll just have users print the way they are accustomed to doing, on grown-up sites like their on-line banking; they'll export/open a PDF and print from Adobe Reader.

If you're a better C# programmer than I am, you may prefer to trap events rather than suppress the standard buttons and add your own. I find it too difficult to manage, and to ensure that the sequence-of-evaluation is exactly what I would expect.  If you, or Karen, take this approach, I expect most of the concepts I'm going to explore here to remain relevant, if you want to do the job right. 

Either way… the reason this gets so interesting is that we have three ways of looking at the parameter set for the report, to do this properly:

  • 1- A parameter object that includes the current choices that the user has selected in the ReportViewer UI — whether they have displayed the report interactively with these values already, or not… 

    List<Microsoft.Reporting.WebForms.ReportParameter> p1 =
    GetClientParameters(this.ReportViewer1);

This one is actually the tough one to get. If you're displaying your own query interface, obviously, you don't need it, just query your own .NET controls for their current contents. But the built-in parameter panel doesn't yield this information easily. Luckily, I didn't have to write the method you see called above.  I got it from http://forums.asp.net/t/1057747.aspx?PageIndex=2 (and you'll see the methods that make up this wondrous technique in full, but clearly marked out, at the bottom of my .CS code).

  • 2- … a parameter object in the proper format to send through the SOAP method…


    RS.ParameterValue[] p2 =
    new RS.ParameterValue[ this.GetCurrentPCount(p1)]; 

This one isn't tough to get, it's just … diferent from the other two.  So there's a little messing about to fill it.  

The "RS" prefix you see above is a web reference to the RS SOAP API.  You want to use ReportExecution2005 when you go after the WSDL. Yes, I said 2005.  ReportExecution2006 (yes I said 2006) has the SharePoint integration mode versions. Check http://msdn.microsoft.com/en-us/library/ms154052, or thereabouts, for more information.

  • 3- … and the full set of defined report parameters, for the currently-loaded report in the viewer control.    We can get this from a standard call. easiest of all :

     ReportParameterInfoCollection p3 =
    this    .ReportViewer1.ServerReport.GetParameters();

We iterate through this collection and determine if it is okay to go ahead and make the web service call (are all required parameters filled out, etc).  

We can't do that by using p1; if the user hasn't filled something out yet, this collection doesn't have an entry for it.  This might be okay for you; you can still pass the parameters to the RS API and trap the SOAP error on the way back, but I preferred to display some immediate error-handling feedback and not waste the call.

As you'll see in my GetRSParams method in the full code, besides checking to see if we have all required values, the main difficulty (as usual) is making sure that we treat a multi-valued parameter with sufficient respect for its, um, special needs.

À la carte

I'm not going to post my GetRSParams method here, because I'm sure you're going to read through the code and realize that you can write it better.  Here's the high-level for the code under those two buttons, so you can see basically how the user invokes the export they want, and how we serve it up:

protected void btnPDF_Click(object sender, EventArgs e) { 
   this.GetExport("PDF", "application/pdf");
}

protected void btnExcel_Click(object sender, EventArgs e) {
   this.GetExport("Excel", "application/vnd.ms-excel");
}

protected void GetExport(string ExportType, string ContentType) {
 
   Byte[] result = null; string encoding;
   string mimeType; string extension;RS.
   Warning[] warnings = null;string[] streamIDs = null;  
   RS.ParameterValue[] rp = GetRSParams();

   if (rp == null || rp.Count() < 1) {
     
      lblFeedback.Visible = true; lblFeedback.Text =
         "Cannot export: You have omitted at least one required parameter value.";
      return;
    }

   RS.ReportExecutionService rs =
      new ReportExecutionService();
  
   rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
   rs.Url = "http://localhost:8080/reportserver/ReportExecution2005.asmx";
   ExecutionHeader execHeader = new ExecutionHeader();
   rs.ExecutionHeaderValue = execHeader;
 
   try
   {
      rs.LoadReport(this.ReportViewer1.ServerReport.ReportPath, null);
      rs.SetExecutionParameters(rp, "en-us");
      result = rs.Render(
         ExportType, null, out extension, out encoding, out mimeType,
         out warnings, out streamIDs);
      Response.ContentType = ContentType;
      Response.OutputStream.Write(result, 0, result.Length);
   }
   catch (Exception ex) {
      Response.Write(ex.Message);
   }
   finally
   {
      rs.Dispose();
      rs = null;
    }
}
 

 Let's eat

… et voilà, the Excel result has only one worksheet, the PDF has proper-natural pagination, and all other parameters are obeyed, no matter what the original PerPageLimit value was.

Although you can't see it in the screen shots, the summary text boxes show up at the end of the data, just where they should with no problems (on the same page, without the annoying page break between the tablix and the subsequent controls that Karen experienced, IOW).

Hooray!

Brandy and cigars

As Karen suggested, there's probably a way to do this with a nested region, rather than a group, to control the pagination, but it didn't seem worthwhile messing with this.  The problems-to-solve are going to be the same no matter where or how you force the page break to occur.  The parameter-handling issues, likewise, are going to be the same, and similarly determined by whether you roll your own query interface or use the in-the-box ReportViewer capabilities.

Since Karen's reports appear to be simple as far as what she's explained to me (maybe in real life they're not?) another possibility occurred to me that might keep maintenance down: a generic set of tablix controls something like what I have described elsewhere on this blog as a generic RDL or RDLC that can handle many different datasets, aka a "Quick Report".  Karen could use her overlaid approach without any changes here, but maybe she could use a single report to handle many different report outputs, so it's not jillions of tablices (is that even a word?) to maintain in total.

I didn't bother to figure out what the tradeoffs were, how to fix the summary textboxes at the end of the tablix data regions for that scenario, etc. I think the idea I'm showing here is more generically useful, because the report design isn't constrained.

Oh — and if Philippe has read this far?  It isn't specific to RS 2008 either.  I'd expect it to work just as well for VS/RS 2005.