TechSpoken

"Any ideas?" is the most frequently-asked question in technical forums. My answer is: yes.

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

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

Comments (12) -

  • Beth Charette

    1/22/2010 12:44:13 PM |

    We use the generic RDL to take care of pagination.

    The nature of RDL allows for simple clean interface without creating other protocol issues.

    These recommendations work very well in practice.

    Beth

    ToysPeriod is a leading online shop specializing in <a href="http://www.toysperiod.com">lego sets</a> and model railroad equipment.

  • Bryan Nash

    1/28/2010 6:36:44 PM |

    There are two versions of the Reportview control. The ReportViewer Web server control is used to host reports in ASP.NET projects. The ReportViewer Windows Forms control is used to host reports in Windows application projects.

  • Jim

    4/29/2010 8:48:30 AM |

    I tried using your code but I am getting an error unfortunately btnPDF_Click function is not working for me, is there an alternative variant.

  • >L<

    4/29/2010 10:10:02 AM |

    If it's not working, Jim, you need to say something about the error you're getting?  Or by "not working", do you mean this aproach won't work for you, can I provide a variant approach?  Probably, but what are your constraints?

  • >L<

    5/8/2010 12:56:06 PM |

    What crashes, I mean what error and where do you see it?

  • Alpinestar Jacket

    6/29/2010 10:49:46 AM |

    awesome template and wonderful post.thanks this can be fantastic info

  • Roger roof

    7/1/2010 2:37:30 PM |

    What is getrsparams method.Is it easier one to approach than this one you demonstrated here.Can you share even that method.



    ============

    Roger

    <a href="http://www.socalbronze.com">cast bronze plaque</a>

  • Markus Idleburg

    8/29/2010 11:54:34 AM |

    this is great, i was looking for something like this so i copied your code (and made a few adjustments to it) hope you don't mind! ;)

  • overstock coupon code

    9/9/2010 11:27:21 AM |

    You will find undoubtedly plenty of details like that to take into consideration. Which is an excellent point to provide up. I present the thoughts above as general inspiration but clearly there are questions such as the one particular you provide up in which probably the most critical factor are going to be operating in truthful excellent faith. I don?t know if finest practices have emerged all-around things like that, but I'm certain that your employment is clearly identified as a fair game.

  • easy shop

    11/11/2010 12:04:23 PM |

    Nice share about generating report, I'll bookmark this and read this again. thanks

  • kevin dominguez

    5/11/2011 11:01:08 AM |

    this is the holy grail for me. this really is a life saver.I've been having problems with that too. this is just a God send.

Pingbacks and trackbacks (1)+

Comments are closed