Category Archives: Reporting Services

[Webinar] Grand Slam Your SSRS Reports

June Webinar

Tune in next week Friday, June 28, 2013 at 1 P.M. for our June webinar as we give you another inside look at how OfficeWriter enhances SQL Server Reporting Services (SSRS), making your reports a home run. Senior Sales Engineer, Chad Evans, will walk you through using both single and multi-value parameters and formulas in SSRS. Using baseball data on players’ batting averages and salaries, this webinar is sure to impress. Don’t miss out! Seating is limited.

*Can’t attend? Register anyway, and we’ll send a copy of the slides and recording following the webinar.

What are the system requirements for OfficeWriter?

The system requirements for OfficeWriter are outlined in our documentation for the two major versions that are currently supported:

OfficeWriter 8

OfficeWriter 8 Documentation Home

OfficeWriter 4

OfficeWriter 4 Documentation Home

How to display parameters in a Reporting Services report


Many customers want to display the parameter values that were selected when a particular Reporting Services report was run. This is possible through Reporting Services formulas.


To display the parameters in a report:

  1. Open the report in Excel or Word with the OfficeWriter Designer
  2. Go to ‘Insert Formula’ > Build New Formula from the OfficeWriter Designer toolbar
  3. There will be a drop-down field that contains the report’s parameters. You can create a formula that includes the report parameters.
  4. This process will create a formula with the report parameter. To insert the formula you just created into the report, go back to Insert Formula on the OfficeWriter Designer toolbar.
  5. Select the formula you just created. This will insert a data marker (for Excel) or a merge field (for Word) into the report that will display the parameter value when the report runs.

How to export SSRS reports to XLSX, DOCX file formats


In Office 2007, Microsoft introduced the OOXML file formats (XLSX, XLSM), which come with benefits, such as an increase in the number of rows allowed in a file. Reporting Services reports designed for OfficeWriter allow for exports to the Office 2007 and 2003 file formats.


To export a report in a particular format, you need to explicitly save the report with the format you want to export it as:

  1. Open the report with the OfficeWriter Designer
  2. Click ‘Save As’ on the OfficeWriter Designer toolbar (for Office 2003) or ‘Save’ > ‘Save to disk’ (for Office 2007/2010)
  3. There will be three options for Reporting Definition files: Office 2007(exports to XLSX, DOCX), Office 2007 with macros (exports to XLSM, DOCM), Office 2003 and earlier (*exports to XLS, DOC)
  4. Select Office 2007 to save the RDL
  5. Publish the saved report to the server

Note: Office 2007 (XLSX, DOCX) is only available in OfficeWriter 4.0 and above. Office 2007 with macros (XLSM, DOCM) is only available in OfficeWriter 4.1 and above.

Using Report Models as DataSources in OfficeWriter Designer


Prior to version 3.9.2, OfficeWriter designer did not support reports with queries based on Report Models. Publishing such a report from OfficeWriter Designer caused some fields to show gibberish or not show at all.

For more information about Report Models visit Microsoft web-site at


From version 3.9.2 and above, OfficeWriter supports Report Model DataSources. Reports created using Visual Studio (2005 and 2008) or ReportBuilder version 2.0, that use a report model as a datasource are now parsed correctly by the OfficeWriter designer.

Note: Support for newer versions of Reporting Services (2008, 2008 R2) and Report Builder (3.0) have been added in later version of OfficeWriter. For more information, refer to the change log in the OfficeWriter Docs.

Reports may contain both regular SQL queries and semantic queries (Report Model-based queries). This support is seamless to the user with no change to the toolbar. However, a feature was added to improve the user experience.

In Report Models, all fields are mapped to entities (instead of tables). We added a feature that allows the user to know to which entity a certain field belongs. For example, if the user encounters a field First Name, it will have a way of knowing whether this is an employee or a customer first name. When a report model is the datasource, its fields will be displayed as a submenu of their entities (rather than the usual flat list of fields), as in the image below:

Figure 1.

Below is the same query, but in the unmapped view:

Figure 2.

The RDL alone does not contain sufficient information to map the fields to entities. Therefore, when a Report Model-based query is selected from the Select Query drop down, we attempt to retrieve the model (as an XML file) from the server. If the address to the model on the server is embedded in the RDL, we try to retrieve it immediately. If we are not able to get the model, or if there is no address embedded in the RDL, the following window will open:

Figure 3.

This dialog offers the following options:

  • Continue will continue without retrieving the model. This will not affect the functionality of the report, the only effect is that the fields will not be mapped to entities, but will be displayed in a single list (as in Figure 2).
  • Retry will attempt to retrieve the model from the server specified in the RDL if the address exists.
  • Browse… will open a window (as in Figure 4 below) that will allow the user to browse to a server and choose a model from a list of available models on the server.

Figure 4.

Note that each model has a unique ID which is not visible to the user. Two models, even if they are created based on the same database with the same entities and fields, will have different IDs. Therefore, mapping the fields of a report created with one model against another model will fail. However, each model preserves its ID when deployed to different servers, so fields can be mapped using a model from another server if it was deployed from the same source model.

Once the model is retrieved, the fields are mapped to entities under the Insert Fields drop-down list (as in Figure 1). Formulas created in Visual Studio or ReportBuider are not related to any entity and will show as regular fields at the bottom of the Insert Field drop-down.

This change applies to the client-side OfficeWriter designer. The server-side components of OfficeWriter require no special modification to work with Report Models. However, we recommend always using matching versions of the designer and the server-side installation of OfficeWriter.

Working with Reporting Services in SharePoint integration mode


From version 3.9.2 and above support has been added to OfficeWriter Designer to work seamlessly with Reporting Services running in SharePoint integrated mode. All operations such as retrieve, publish and view reports are supported.

This feature is available in the client-side OfficeWriter Designer for Excel and Word. The server-side components of OfficeWriter require no special modification to work with Reporting Services running in SharePoint integrated mode with one exception, outlined below. We recommend always using matching versions of the designer and the server-side installation of OfficeWriter.

There are a few things to note when using the Designer in SharePoint integrated mode that differ from regular use, which are outlined in this post.


The Designer

When publishing or retrieving a report using the Designer, enter your SharePoint root site in the server URL textbox, like in the image below. Enter the address without the additional /ReportServer usually added for Reporting Services not running in integrated mode:

Please Note: Adding /ReportServer after the SharePoint site address will cause the operation to fail with the following error message:

The operation is not supported on a report server that is configured to run in SharePoint integrated mode.

For more information about using the OfficeWriter Designer, refer to our documentation on the OfficeWriter SSRS Integration.


In SQL Server Reporting Services 2012, Microsoft changed the structure of SharePoint integrated mode for SharePoint 2010 and above. Essentially, SSRS is run through SharePoint service applications, rather than through the traditional Reporting Services configuration. All configuration settings are stored in a database, rather than config files.  This affects how OfficeWriter’s server-side renderer is installed in SSRS integrated with SharePoint.

Starting in 8.5.1, OfficeWriter can install against SSRS 2012 with SharePoint 2010 or 2013. During installation, the user will be prompted to select their SSRS instance as well as the SharePoint service application to install the renderer to.

There are no other changes to how OfficeWriter behaves in SSRS and SharePoint integrated mode.

Designer Install Error: This software requires Excel 97 and/or Word 97 (or higher)


The installer for the OfficeWriter Designer v8.0 and below throws the following error:

This software requires Excel 97 and/or Word 97 (or higher), with Visual Basic for Applications (VBA) and MSQuery installed. This system does not appear to have Excel or Word installed. Please install Office before continuing.


Office 2010 is installed

The OfficeWriter Designer v8.0 and below is not supported in Office 2010. Please install v8.1.1 or higher for Office 2010.

No Office is installed

The OfficeWriter Designer is an Office Add-In and requires a version of Office to be installed to use.

Reporting Services Error: Report must be created with OfficeWriter Designer version or later


When a report is exported from SSRS 2008 after upgrading to OfficeWriter 4.x, the following error message appears:

OfficeWriter report was deployed to Microsoft SQL Server 2008 must be created with OfficeWriter Designer version or later. This report was created with version [version number] and must be run on SQL Server 2005 or earlier.


There are two known causes for this message, depending on the [verison number] of the Designer:

Designer version 3.8.x – 3.9.x

Full support for SQL Server Reporting Services 2008 was officially introduced in OfficeWriter 4.x. Exporting reports that were designed with an earlier version of the OfficeWriter Designer to SSRS 2008 will result in the error message above.

This message indicates that the report needs to be upgraded with a later version of the Designer.

To upgrade a report:

  1. Follow the instructions for removing the old version of the Designer and installing the new version of the Designer.
  2. Open the report in the new version of the Designer.
  3. Save or publish the report from the OfficeWriter Designer toolbar.

This will make several changes to the RDL to make it compatible with SSRS 2008.

Designer version 4.x

This is a known issue that occurs when the following conditions are met:

  • The report was created in Visual Studio 2008 for SQL Server 2008
  • OfficeWriter Designer version is 4.5.0
  • OfficeWriter v4.1.0 is deployed on the report server

The solution is to upgrade the OfficeWriter on the server from v4.1.0 to v4.5.0 or later.

NOTE: We generally recommend that the version of OfficeWriter on the server matches the version of the OfficeWriter Designer.

OfficeWriter Designer Warning: Feature is only available in Enterprise Edition


OfficeWriter Enterprise Edition is installed on the server, but the warning message: WARNING! This feature is only available in Enterprise Edition of OfficeWriter pops up when using certain features of the Designer.


This warning message displays when using any features that are only supported with Enterprise Edition of OfficeWriter, such as Reporting Services formulas. This is because the OfficeWriter Designer does not have the capability to detect what version of OfficeWriter is installed in Reporting Services.

The common scenario is for OfficeWriter for Reporting Services to be installed on a server, with the OfficeWriter Designer distributed to end-users, who design reports and upload them to the server. The Designer has no means of determining if the OfficeWriter on the server is Enterprise or Standard Edition, but end-users should be aware that if the OfficeWriter on the server is not Enterprise Edition and they use Enterprise-only features, their report may not render properly.

The OfficeWriter Designer can be used on the same machine that has OfficeWriter for Reporting Services installed, such as on a development machine, but the Designer does not require OfficeWriter to be installed. This is why that alert displays regardless of what version of OfficeWriter is installed.

To avoid seeing the warning message when using features that are only available in OfficeWriter Enterprise Edition, un-check ‘Show this warning when Enterprise Edition feature is used.’

If only Standard Edition is installed on the server, to avoid the warning message and hide Enterprise Edition-only features, click ‘Hide EE Features’ button at the bottom of the pop-up window.

OfficeWriter Designer prompts to publish during preview


The OfficeWriter Designer keeps prompting to publish the report when attempting to view the report from Excel. This happens even when the report is retrieved from the server and no changes are made to the report before trying to preview.


How View in the OfficeWriter Designer works

When the View button is clicked, the OfficeWriter Designer does several things:

  1. If the report has not been published, prompt the user to save and publish the report.
  2. If a previously published report has been changed in Excel, prompt the user to save and re-publish
  3. Make a request to Reporting Services to export/render the published report using the OfficeWriter renderer (OfficeWriter for Excel)
  4. Download a copy of the rendered report and open in Excel

In order for the OfficeWriter Designer to view a report, the most updated version of the report must be published to the server.

Designer still prompts when no changes have been made to the report

Certain Excel features, such as macros and pivottables, can be set to automatically execute or update when the workbook is opened. This is considered to be a change to the file, even though there are no visible changes to the report.

The OfficeWriter Designer uses the same logic as Excel when determining if a change has been made to the file. This means that any time Excel would prompt a user to save changes made to a file, the OfficeWriter Designer will prompt to publish the changes to the server.

For example, if a report contains a pivottable that is set to update the data when the workbook opens, Excel will interpret this a change to the file, even though it appears as though nothing has changed. In this case, the Designer will ask the user to publish the report before the View function can be run.