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", null, null, parameters,
null, null, out 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).
