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 during design.

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 report.

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 or better.

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.

1.      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.

Figure 1

2.      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 to continue.

Figure 2

3.      Paste the following SQL statement into the Query string box in the Report Wizard and click Next.

Listing 1

SELECT

  loc.Name [Location],
  prod.ProductNumber [Part Number],
  prod.Name [Part name],
  prod.ReorderPoint [Reorder Point],
  inv.Quantity [Current Count],
  prod.ReorderPoint - inv.Quantity [Deficit]
FROM
  Production.ProductInventory inv
  INNER JOIN Production.Product prod ON prod.ProductID = inv.ProductID
  INNER JOIN Production.Location loc ON inv.LocationID = loc.LocationID
WHERE
  inv.Quantity < prod.ReorderPoint AND
  inv.Shelf <> 'N/A' AND
  inv.LocationID = @location
ORDER BY 
  1,5 DESC

4.      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.

5.      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.

1.      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.

Listing 2

SELECT
  LocationID,[Name]
FROM
  Production.Location
 
 

2.      Click the OK button to save your changes.

3.      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.

Figure 3

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.

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).

Figure 5

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:

1.      Open up Microsoft Excel on your development machine that has Office and the OfficeWriter Designer installed.

2.      In Excel, click on the Open Report button in the OfficeWriter Designer Toolbar as shown in Figure 6.

Figure 6

3.      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.

Figure 7

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:

4.      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.

5.      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.

6.      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.

7.      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.)

Figure 8

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 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).

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.