Tag Archives: reporting services

Events, Featured, OfficeWriter, Reporting Services, SQL Server, SSRS, Webinars

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

Excel, ExcelWriter, Knowledge Base, OfficeWriter, SQL Server, SSRS, WordWriter

How to open a report from SQL Server Reporting Services with the Application object

Problem

You want to modify your reports with the WordApplication or ExcelApplication object after they’ve been populated with data by SQL Server Reporting Services. The ExcelApplication or WordApplication object’s Open method can’t pull a report from a report server, so how do you open a SSRS report with OfficeWriter?

Solution

For simplicity and ease of reading the code examples and text in this article refer to the ExcelApplication object exclusively. However, the exact same techniques can be used with the WordApplication object. The only strict requirement for opening reports from SSRS is for the Open method to have an overload that takes a Stream argument, which both objects have.

SQL Server Reporting Services exposes a web service that lets programs access and work with the reports on the server. The web service has a number of functions for interacting with a report server and in fact is the same API that the Report Manager tool is built on top of. One of its functions will let us retrieve a fully rendered report that we can then pass to the ExcelApplication object. Once the ExcelApplication object has opened the report, it can be used as if it were any other file.

Getting a Reference to the Report Server

There are a number of different ways to access a report server to manage your reports. The MSDN documentation has detailed instructions for all the different techniques. The most powerful way, and the one that the Report Manager application uses, is to use the report server’s web service, or SOAP API. The easiest way to use the web service is to add the report server as a web reference in Visual Studio. This will allow us to make web service calls to the report server as though it were any other object in a .NET project. I’ll provide a brief overview of the steps to add a report server as a web reference; details can be found at MSDN. Continue reading How to open a report from SQL Server Reporting Services with the Application object →

Excel, ExcelWriter, Knowledge Base, OfficeWriter, SQL Server, SSRS

How to create hyperlinks between cells in an Excel spreadsheet

Problem

Your application requires cells to contain hyperlinks to other cells in the same spreadsheet, but the Cell.CreateHyperlink function only makes links to URLs on the internet. This article discusses a workaround using Excel’s HYPERLINK function.

Solution

Excel has a function named hyperlink() that, with some special syntax, can create links to other cells in a spreadsheet.

Normally, the hyperlink function creates a link to a URL, much like the CreateHyperlink function. Simply passing the name of a cell to the function causes it to form a bad link; Excel will interpret it as a URL.

The solution is to enclose the name of the destination cell in quotes and preface it with a pound sign. For example, a cell with formula =HYPERLINK(“#Sheet2!C3”, “Link to C3”) will contain the text “Link to C3”, and function as a hyperlink to that cell in Sheet2. The formula can be set either by editing an Excel file directly, or through setting the Cell.Formula property in ExcelWriter.

When the user clicks on the link, Excel’s focus will move to Continue reading How to create hyperlinks between cells in an Excel spreadsheet →

ExcelWriter, Knowledge Base, Reporting Services

How to display parameters in a Reporting Services report

Problem

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.

Solution

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.

ExcelWriter, Knowledge Base, Reporting Services, WordWriter

How to export SSRS reports to XLSX, DOCX file formats

Problem

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.

Solution

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.

ExcelWriter, Knowledge Base, Reporting Services

Using Report Models as DataSources in OfficeWriter Designer

Problem

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 http://technet.microsoft.com/en-us/library/cc678411.aspx.

Solution

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.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, SharePoint, SSRS, WordWriter

Error installing OfficeWriter for Reporting Services 2012 in SharePoint integrated mode

Problem

When installing OfficeWriter for SQL Server Reporting Services 2012 in SharePoint mode (SharePoint 2010), the OfficeWriter installer aborts with message:

SoftArtisans OfficeWriter Setup Wizard ended prematurely because of an error. Your system has not been modified. To install this program at a later time, run Setup Wizard again. Click the Finish button to exit the Setup Wizard.

Solution

Starting in SQL Server 2012, the SharePoint integration for Reporting Services was re-architectured: SSRS 2012 is configured as a shared service against SharePoint 2010. Configuration information is stored in a database and can be managed through SharePoint Central Administration or with Powershell.

Due to this change in architecture, the OfficeWriter installer is not currently equipped to run against SSRS 2012 in SharePoint integration mode. We will be adding support for this configuration to the OfficeWriter installer in a future release.

Update 4/11/2013: Instructions for manually installing OfficeWriter in this configuration are now available in our documentation: Manual Installation for SSRS 2012 with SharePoint 2010. The fix to the installer will be available in a soon to be released version of OfficeWriter.

Note:

This does not happen outside of SSRS 2012 with SharePoint 2010. OfficeWriter has full compatibility with SQL Server Reporting Services 2012 in native mode starting in v8.2. OfficeWriter also works with other combinations of SSRS and SharePoint.

Knowledge Base, OfficeWriter, Reporting Services, WordWriter

Reporting Services Error: The following bookmark cannnot be found in the template

Problem

When rendering an OfficeWriter for Word Reporting Services report, after removing all bookmarks from the template for a specific data source, the following error occurs:

Reporting Services Error ---------------------------------------------------------- An error occurred during rendering of the report. (rrRenderingError) Get Online Help An error occurred during rendering of the report. WordWriter Error: The following bookmark can't be found in the template: DataSourceName The following bookmark can't be found in the template: DataSourceName -------------------------------------------------------------------------------- SQL Server Reporting Services

For example, someone may decide to remove a table of data from their report which they no longer want to display. To do this, they would delete the table, thereby removing the merge fields in it and the bookmark around the table row that defined the Selection Query Range for that data source.

Solution

As mentioned above, this error occurs when a Selection Query Range is defined for a data source and then removed from the template. Once a Selection Query Range for a data source is added to the template using the OfficeWriter Designer toolbar, a bookmark is added to the template to define what portion of the document should repeat and that data source is linked to the template in the report’s RDL file. If all the bookmarks for this data source are then removed from the template, the references to that data source in the RDL file that the Designer added are not automatically removed.

This behavior is planned to change in a future version, so that it will be possible to define a Selection Query Range for a data source in a template, and then later decide to remove it. In the meantime, there are two workarounds to this issue:

Remove the relevant block from the RDL for the data source that is no longer used

Important: Back up your RDL file before making manual changes to it in case you make a mistake.

For each data source used in the template, the OfficeWriter Designer adds a DataSourceName">... block in the RDL file.

If a data source is no longer used in the template, simply open the RDL file in a text editor and remove the correspoding Table block.

For example, if you want to remove this block for a data source called Contacts, look for the ... block and delete it.

Design your report from scratch

Build a new report from scratch (both RDL and Word template). When designing the template, be careful to only add Selection Query Ranges for data sources that will definitely be used in the report. (Don’t plan on removing all the bookmarks for any data source.)