Using SoftArtisans OfficeWriter with SQL Server Reporting Services 2005
page 9 of 10
by Mike Campbell
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 38608/ 106

Dynamically Modifying Reports on the Server

Once you are done looking at the report generated in Excel, click the Close Report View button from the designer toolbar.  If you want, you can return to your Report Server where you now have the option to export your sample report in rich Excel format.  You will also notice that you can output to any of the other rendering formats without any problems.  The custom formatting and functionality you added is completely ignored by other rendering extensions and is completely transparent unless explicitly requested.

As mentioned earlier, it is also possible to "intercept" rendered reports on your Reporting Server and modify them using OfficeWriter to add additional functionality and formatting.  In this way, an ASP.NET page (i.e. middle-tier application) can provide end-users with additional formatting options and the ability to further customize reports to meet their needs.  For example, sales reports could be modified to show regions failing to meet quotas or they could be color coded to specific individuals or regions.  Likewise, a company that sold software online could customize product quotes for customers by adding contact information and a personalized message or "touch" from the salesperson sending them the quote.  And best of all, using OfficeWriter to dynamically modify reports on the fly allows organizations to meet complex needs without the need to create hundreds of report variants.

If you would like to see this in action, download the sample code accompanying this article which will provide you with a rudimentary ASP.NET page that dynamically requests a rendered version of the sample report created in this article and then modifies it on the fly.  To get this sample site to work, you will need to add a copy of the necessary OfficeWriter DLLs to the sample site's /bin/ directory and you will also need to create a Web Reference to your local Reporting Server. (Instructions for how to do this are included in the download.)

Once you have the sample site configured and open in Visual Studio 2005, open up Default.aspx. This terribly simple page contains two drop-down controls, a Datasource, and a button that allows you to start the rendering process.

Figure 9

 

 

The SqlDataSource for this page links to the AdventureWorks database and executes the exact same query as defined in Code Listing 2 to provide a list of locations in order to pass in a location parameter to the sample report that will be dynamically requested and modified.  The second drop down on the page is statically populated and lets end users specify a tab color for their output report.

After you have taken a second to look at the markup, open up Default.aspx.cs which contains the code that will handle dynamic modification of the sample report.  In the code behind, when the Render Report button is pushed, control of the page is handed off to the ProcessExcelWriterReport() method (Listing 3) which wires up a few parameters and requests an in-memory copy of the rendered sample report from a helper method (Listing 4) that fetches the report from the Reporting Service's Web Service interface.

Listing 3

 
private void ProcessExcelWriterReport()
{
  string reportPath = "/SampleReport/Report1_OW";
 
  //specify paramter values (by name)
  ParameterValue[]parameters = new ParameterValue[1];
  parameters[0] = new ParameterValue();
  parameters[0].Name = "location";
  parameters[0].Value = this.DropDownList1.SelectedValue;
 
  using(Stream stream = this.LoadSRSSReport(reportPath, parameters))
  {
    ExcelApplication xlwriter = new ExcelApplication();
    Workbook wb = xlwriter.Open(stream);
 
    Worksheet ws = wb.Worksheets[0];
 
    // specify a tab color based on the drop-down value:
    switch (this.DropDownList2.SelectedValue)
    {
      case "Green":
        ws.TabColor = Color.SystemColor.Green;
        break;
      case "Red":
        ws.TabColor = Color.SystemColor.Red;
        break;
      case "Blue":
        ws.TabColor = Color.SystemColor.Blue;
        break;
      default:
        break;
    }
    // add the name of the location to the output report:
    ws.Cells[0, 1].Value = "Location: " + this.DropDownList1.SelectedItem.Text;
    xlwriter.Save(wb, Page.Response, "Dynamically Modified.xls"false);
  }
}

Once the ProcessExcelWriterReport() method has a copy of a specified report (hard coded to the sample report location in this case), it passes it in to a new ExcelApplication object where it then begins manipulating it on the fly.  The sample application does not showcase much functionality, but does determine what color the tab for the report in the workbook should be and dynamically populates cell B1 with information about the location being reported on, proving that it is very easy to modify reports as desired.

Listing 4

 
private Stream LoadSRSSReport(string reportPath, ParameterValue[]parameters)
{
  // create a new web service/instance of the report server:
  ReportingService rs = new ReportingService();
  rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
  string[]streamIDs;
  string optionalString = null;
  ParameterValue[]optionalParams = null;
  Warning[]warnings = null;
 
  byte[]output = rs.Render(reportPath, "XLTemplate"nullnull, parameters,
    nullnullout optionalString, out optionalString, out optionalParams, out
    warnings, out streamIDs);
  MemoryStream ms = new MemoryStream(output);
  return ms;
}

Once you have looked at the code and examined how things operate, press F5 in Visual Studio to launch your sample web page.  Specify a location from the dropdown and (optionally) a tab color, and then press the Render Report button to request your dynamically modified report.

Figure 10

The report should render quickly.  Once you open it you will see that all of the OfficeWriter formatting that you specified earlier is still intact.  You will also note that cell B1 (see Figure 10) has been populated with information about the location being reported on and if you specified a tab color other than default, you will see it displayed in the Sheet1 tab at the bottom of the spreadsheet (it is more visible/apparent if you switch to a different sheet in the workbook).


View Entire Article

User Comments

Title: RA   
Name: SA
Date: 2008-04-24 12:05:05 PM
Comment:
Can Office writer help in renaming the sheet names once the report is exported from Report server
Title: Dynamic Rendering   
Name: ST
Date: 2007-10-01 6:14:24 AM
Comment:
Hi,

I have tested the code given on the above. During the render, I get this exception "Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response." on the function Render(...). What am I missing on here?

Thanks.
Title: RE: Dynamic Rendering (@giovanni)   
Name: Michael K. Campbell
Date: 2006-08-31 8:37:50 PM
Comment:
Giovanni, glad you liked the article.

Make sure you've got the SoftArtisans SAXW6NET.dll copied in to your /bin/ directory - that will likely be your biggest issue.

I also generated a VB.NET conversion of the default.aspx.cs page, and posted it up in my uploads section:
http://authors.aspalliance.com/mcampbell/Default_aspx_vb.zip (but you'll need to do wire-ups for code-behind/partial classing etc...)

Cheers.
Title: Render Dynamic   
Name: GIovanni Rivas
Date: 2006-08-31 6:06:35 PM
Comment:
Hi, thx for the article, but i tryed to copy the code in cs that you give here and doesn't work, i have to so all the references to the Interop libary for excel and the methodthat you are calling there with the arguments doesn't exist. am i missing something, do you havea code in Vb.net that could work like this. thx
if you can reply at garf50@hotmail.com






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-12-02 11:29:41 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search