Microsoft's SQL Server Reporting Services (SSRS) is
revolutionizing the way organizations use data. Prior to the arrival of SSRS,
most reporting solutions were either costly or technically complex. This
typically kept reporting out of the reach of small to medium sized businesses
that were not able to afford custom "in house" solutions or
high-priced services and analysts to pour over their mountains of data and
render it intelligently. However, SSRS is changing all of that by putting a
dependable reporting platform easily within reach of small to medium sized
businesses that allows them to access powerful reporting functionality without
spending a fortune or making them wait on long development cycles.
Larger businesses and enterprises can also benefit from SSRS
and its extensible architecture. SSRS offers an open Web Services interface
which allows larger companies (with dedicated resources) to create custom Data
Processing, Delivery, and Rendering extensions to meet their own particular
needs. This open architecture also allows 3rd parties to offer software
products and services to help address recurring customer needs - generating an
entire market of SSRS add-ins and extensions.
As an example of meeting market demand, well-known ISV SoftArtisans
supports customized SSRS design and rendering extensions in their highly
acclaimed OfficeWriter product. With these extensions, businesses can create
native Microsoft Excel and Microsoft Word reports with SQL Server Reporting
Services. And, unlike the out of the box rendering extensions provided by SSRS
that output static "copies" of SSRS reports into Word and Excel,
reports generated by OfficeWriter enjoy full "native" functionality
including formatting, formulas, macros, data-bound charts, and a host of other
goodies. It is therefore no wonder that this extension has met with great
success even garnering a finalist position for Best Business Intelligence
Product at the 5th Annual Best of Tech Ed in Boston of 2006.
While SQL Server Reporting Services is without equal in
terms of affordability, approachability, extensibility, stability, and (increasingly)
ubiquity, it is not without a few warts. One of the main problems that SSRS
suffers from is the inability to render reports with "rich" Excel (or
Word) functionality. While lacking any MS Word support, SSRS does provide the
ability to export reports to Excel, Tiff, Adobe Acrobat, and a handful of other
document types which is really quite spiffy. The problem, however, is that
when reports are exported to Excel SSRS does not provide any support for
formulas, macros, conditional formatting, or a host of other "rich"
functionality that most Office users have come to rely upon in making sense of
data. Worse, while SSRS does output charts to Excel and Word, it renders them
as static images disconnected from the data they are graphing. Of course, this
is better than nothing, but in today's world where organizations and users
demand ever more complex solutions, static data and missing functionality just
is not enough.
This is where SoftArtisans enters the scene and shines. With
SoftArtisans' OfficeWriter, native SSRS functionality has been extended to allow
SQL Server Reporting Services reports to be faithfully rendered in Excel and
Word with all of their native formatting and functionality completely intact. Better
yet, leveraging OfficeWriter for natively rendered reports does not require you
to update all of your existing reports to a new proprietary format. Nor does
it "break" existing functionality when you do chose to upgrade. With
OfficeWriter-enabled reports, specialized rendering instructions for each
report are merely injected into the RDL descriptor for each report and
processed only when routed through the customized OfficeWriter Renderer.
With OfficeWriter, SSRS users get the best of both worlds
from a reporting standpoint. Because OfficeWriter just extends existing report
definitions, none of the existing SSRS benefits are lost. This means that SSRS
users retain access to scheduling, granular security, an easy-to-use web
interface, quick development cycles, support for heterogeneous data sources,
and the ability to export reports in a number of different formats.
With OfficeWriter, Reporting Services reports can be
designed directly from Word and Excel with the OfficeWriter Designer, a special
toolbar that is very easy to use. This gives Microsoft Office users the
ability to modify existing reports without requiring them to wait on
developers, open Visual Studio, or even leave Office. Office users can even
create their own reports, but this functionality is disabled by default as most
organizations prefer to use OfficeWriter Designer to help create a layer of
separation between logic and presentation. Under this paradigm, developers and
IT professionals can create data models by joining data from various sources
and placing it into a report definition where business users can modify the
presentation of the report data to their heart's content (assuming they have
been given necessary permissions).
Once designed, reports created or modified with the
OfficeWriter Designer are modified with a special bit of base-64 encoded
goodness that is "injected" in to the report's definition file. On
the reporting server, these extra instructions are completely ignored unless
the report is requested for export via OfficeWriter's custom rendering
extensions. This means that any existing reports modified by the OfficeWriter
Designer will continue to function just as they always did. In fact, they can
still be natively exported to a low-functionality version of Excel. However,
when requested for export via the custom OfficeWriter rendering extensions, the
base-64 encoded goodness is used to recreate a native Excel or Word document
that faithfully matches the template created in the OfficeWriter Designer. With
this template hydrated on the server, OfficeWriter renders extensions then
replaces specialized "data-markers" with actual report data pulled
from the datasets defined in the report's RDL file. The result is a beautiful
fusion of Reporting Services data into a completely native Word document or
Excel spreadsheet that preserves native formatting and functionality as defined
Even better, because OfficeWriter is just generating native
Word and Excel documents on the server and programmatically populating them
with Reporting Services data, it is possible to "intercept" rendered
reports and use the object models exposed by OfficeWriter to programmatically
add embellishments, modifications, and other formatting as desired. In this
way a single Reporting Services report can be customized for individual users
or specific regions and needs. For example, a sales company that wanted to
give customized quotes to customers could use a single SSRS report to output
data with various parameters, but contact information within the report could
easily be switched programmatically for each sales associate creating the
With all of the theory and background out of the way, it is
time to delve into some hands-on development to see OfficeWriter in action. To
follow along with the ensuing tutorial, you will need an existing instance of
SQL Server Reporting Services and the ability to install the trial version of
OfficeWriter available from the SoftArtisans site. I would also highly
recommend that you use either VMware Server or Microsoft's Virtual PC as a way to
create a suitable working environment for any testing you would like to do. These
virtualizations solutions are all 100% free products so there is no reason not
to use virtualization for testing purposes.
When you install OfficeWriter on your reporting server, it
will add three new rendering extension DLLs to your Reporting Server's \bin\ directory and will modify its web.config
to enable these DLLs as specialized rendering handlers. Once you have
completed installation of the server product, you will also be prompted to install
the OfficeWriter Designer which you will need to do on a machine that has both
access to your Reporting Server and is currently running Microsoft Office 2000
You are now ready to create a simple Reporting Services
report that we can use to see everything in action. To create this sample
report follow these steps.
Open Visual Studio 2005 and select File | New | Project.
Select the Business Intelligence Projects project type
and then select the Report Server Project Wizard as
depicted in Figure 1. Give your project a name, like SampleReport,
and click OK.
On the Report Wizard Welcome screen (if you get
one), just click Next and then create a new data source
pointing to your AdventureWorks database. To do
this, give your data source a Name of AdventureWorks, then click the Edit
button and provide the needed configuration. Once completed, your results
should look similar to those in Figure 2. Click Next
Paste the following SQL statement into the Query string
box in the Report Wizard and click Next.
prod.ProductNumber [Part Number],
prod.Name [Part name],
prod.ReorderPoint [Reorder Point],
inv.Quantity [Current Count],
prod.ReorderPoint - inv.Quantity [Deficit]
INNER JOIN Production.Product prod ON prod.ProductID = inv.ProductID
INNER JOIN Production.Location loc ON inv.LocationID = loc.LocationID
inv.Quantity < prod.ReorderPoint AND
inv.Shelf <> 'N/A' AND
inv.LocationID = @location
Click Next again to create a tabular report and
then move all of the available fields to the Details
section of the Table designer. Now click Next twice.
In the Choose the Deployment Location page,
specify the location of your Report Server as well as any folder you would like
to use for organization. I just chose the defaults. Click Next
and then click Finish.
Once the Wizard completes the process of creating your
report, go ahead and select the Preview tab from the
designer within Visual Studio 2005. Then enter the
literal value "20" (minus the quotes) into the location field and
press the View Report button.
The report you just created is a bit ugly. Switch back to
the Layout tab and drag columns around as needed to make things look a bit
nicer. You can keep toggling back and forth between Layout and Preview mode as
needed just keep entering "20" in as the location each time to get
the report to render.
Once you have formatted things to look a bit nicer, it is
time to address the issue of entering 20 into the location field. If you look
at the SQL that you used to generate the report, you will notice that it used a
@location parameter to specify the location of parts
for the query. Each time you go to render the report, SSRS detects that a
parameter is needed and prompts you for it before you can view the report. In
this case, "20" just happens to be the ID for the Frame
Welding "location" at AdventureWorks.
To make this a bit more user-friendly you can do the following.
Switch to the Data tab in the Designer.
From the Dataset dropdown at the top of the Data designer, select the <New
Dataset> option. In the Dataset dialog that
ensues, give your new datasource a Name of LocationLookup and enter the following code for the Query string value.
Click the OK button to save your changes.
Now, select the Report | Report
Parameters menu option in Visual Studio. The left hand side of the Report Parameters designer shows location as one of the
parameters. If multiple parameters were needed in this report, you could
define their behaviors and characteristics with this tool. In the Properties pane, specify Inventory Location
as the Prompt and then select the From
query radio button in the Available values
section of the designer. Then select LookupLocation
as the Dataset you wish to use for this parameter and
configure it to use the LocationID for the Value field and the Name column for
the Label field. Your results should look similar to
those in Figure 3. Click OK to save your changes.
Now when you switch to the Preview
pane, you are presented with a dropdown that lets you specify which location
you wish to see inventory levels for. Just select one from the dropdown and
you will get a tailored report.
In Visual Studio, save all of your changes once you are done
modifying the formatting and then right click on your Report in the Solution Explorer to click on the Deploy
menu option. Your report will now be up on your specified Report Server.
Open a browser and navigate to your reporting server (e.g., http://localhost/reports/). Once
there, navigate to the folder where you instructed the Report Wizard to place
your finished report and locate the Report1 report. Once
you click on the link, you will be greeted by a dialog requesting a parameter
for you. Select a location and then click View Report just
as you have done in the designer. After a few seconds of churning, your report
will be rendered.
Once it is rendered, try opening it up in Excel. To do
this, specify the Excel option from the Select a format
drop down as indicated in Figure 4.
Once you have selected the Excel export format, click
on the Export link and Reporting Services will provide
you with an available download containing the results of your report. Save the
report and then open it once it finishes downloading or just open it directly
if you would prefer. Once opened, you can see that SSRS has done a great job
of outputting your report to this Excel document. It has even preserved some
of the formatting (such as color).
While the data you wanted to output has been accurately
rendered, if you had placed any charts or graphs into your Reporting Services
Report, they would have been outputted to the Excel file as static images not
as data-bound graphs. Likewise, if you need to add any conditional formatting
or other formulas to your report you are free do to so; you will just have to
do it again each time you output the report. What would be nice would be a way
to make formatting changes and preserve them for future export operations. Happily,
with a sample report now created, you are ready to see what OfficeWriter can
bring to the table.
With OfficeWriter's specialized designer, you can easily
redesign existing Reporting Services Templates to enable rich functionality in
SSRS reports. To do this, you need to add additional instructions to the
Report's definition which lets it know how you would like it rendered when
exported through the custom OfficeWriter rendering extensions. Happily,
providing these instructions is terribly simple with the OfficeWriter Designer.
To modify your existing report:
Open up Microsoft Excel on your development
machine that has Office and the OfficeWriter Designer installed.
In Excel, click on the Open
Report button in the OfficeWriter Designer Toolbar
as shown in Figure 6.
Click on the Retrieve button to fetch your
existing report definition from your Reporting Server. You will need to
specify the location of your Reporting Server (just enter the server URL
without a path, i.e. http://myserver/). Then
navigate through the list of folders and locate the report you created earlier.
Once you have selected your sample report, click OK to
pull it down.
Once the report is opened, you will notice nothing but a big
empty screen. The OfficeWriter designer does NOT render your existing report
for you in Excel. While this may initially seem like a bad idea, it actually
makes perfect sense. Think about it: you do not want a copy of static data
being rendered in Excel since you already have that functionality. What you
want is the ability to completely change the layout of your report so that you
can infuse it with formulas, conditional formatting, macros, data-bound charts,
and a host of other goodies. Best of all, when you are done formatting your
new report, you can just save it back up to your server and all of the
formatting specifications you have made will be completely ignored unless
specifically requested by a client who wants the report output in "native"
Excel using the OfficeWriter rendering extensions.
To redesign your sample report, just build some formatting
loosely mirroring what you created in the Visual Studio designer (as depicted
in Figure 7) and make sure to add a new column, Visual
Deficit, as you will need this for Excel formula functionality.
Once you have laid things out as you like (you can meddle with
it as long or little as you would like - I chose some simple formatting and a border
with divider lines), it is time to specify where data will go when the report
is rendered. To specify where data should be placed:
Click on the Select Query drop down in the
OfficeWriter Designer toolbar and select the AdventureWorks
dataset. This is the default dataset created in your report and is being
interpreted directly out of the RDL for this report. Now, highlight the first
row underneath your header (Cell 4A in Figure 7) and
then click the Insert Field drop down. From the list
of fields, click on the Part_Number field. When you
do this, a new data marker is inserted which will be replaced with Reporting
Services data when the report is rendered.
Fill in the rest of the fields by inserting them as needed. Once you
have specified the contents for each column, you are ready for some fun. Select
the Reorder Point, Current Count
and Deficit columns then right click and specify the Format Cells menu option. Specify that the cells should be
formatted as Numbers and specify any number of decimal
places that you like.
Now add some conditional formatting to the Deficit
column. Select the cell where your Deficit column
data-marker is located (Cell 4E in Figure 7) and then
select the Format | Conditional
Formatting menu option. From the Conditional
Formatting dialog, set up a rule for values greater than 200- for
example, turn the text red.
Finally, add a bit of visualization in the form of a formula that will
create an inline bar graph using the REPT() function to
add a new | (bar) character for every 10 deficit products. Just add the
formula shown in Figure 8. (Note: Once you "mouse out" of cell F4, the formula will throw an error, just ignore it.)
With your report redesigned and with some native formatting and
functionality in place, it is time to publish your report back to the server. You
can save it with a completely different name if you would like, but there is no
negative in just saving it over the previous version. (It might make sense to
just append _OW to the end of it in real world scenarios so consumers can
easily see which reports will render with full Excel functionality.) To save
your changes, just click the Publish button in the OfficeWriter Designer Toolbar. Then navigate the ensuing
dialog and save the report as desired.
Once the report is saved, you can view it by clicking the View button from the OfficeWriter Designer Toolbar.
You will be prompted to enter a location which is still done with a dropdown
and once the report is rendered, you will see that it managed to populate your
template with Reporting Services data perfectly and formatted everything as you
specified. Had you placed any charts, formulas, macros, or other goodies
anywhere, they too would have remained in place just like your conditional
formatting and added formula for an inline "chart."
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
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.
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
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.
private void ProcessExcelWriterReport()
string reportPath = "/SampleReport/Report1_OW";
//specify paramter values (by name)
ParameterValueparameters = new ParameterValue;
parameters = new ParameterValue();
parameters.Name = "location";
parameters.Value = this.DropDownList1.SelectedValue;
using(Stream stream = this.LoadSRSSReport(reportPath, parameters))
ExcelApplication xlwriter = new ExcelApplication();
Workbook wb = xlwriter.Open(stream);
Worksheet ws = wb.Worksheets;
// specify a tab color based on the drop-down value:
ws.TabColor = Color.SystemColor.Green;
ws.TabColor = Color.SystemColor.Red;
ws.TabColor = Color.SystemColor.Blue;
// 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.
private Stream LoadSRSSReport(string reportPath, ParameterValueparameters)
// create a new web service/instance of the report server:
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
string optionalString = null;
ParameterValueoptionalParams = null;
Warningwarnings = null;
byteoutput = rs.Render(reportPath, "XLTemplate", null, null, parameters,
null, null, out optionalString, out optionalString, out optionalParams, out
warnings, out streamIDs);
MemoryStream ms = new MemoryStream(output);
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.
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).
While we just modified a single report in this sample, the
possibility exists to modify multiple reports and output them all in a single
spreadsheet. In fact, without even modifying a single report, many end users
would love to see a simple web app that would let them aggregate a number of
Excel reports for export into a single spreadsheet. Providing them with such
an option would be trivial using Reporting Services' SOAP interface coupled
with OfficeWriter's ability to stream the contents of reports into new Work Sheets.
With functionality like this, it is no wonder that OfficeWriter is gaining
critical acclaim for its ability to extend and improve upon the existing
success of SQL Server Reporting Services.