Hello! Welcome to my first post. I’m Elise, lover of social media and self-proclaimed coffee addict. As a newcomer to SoftArtisans, and to the MSFT tech arena in general, I’ve been trying to absorb as much info on the technologies we run on as possible. Since one of OfficeWriter’s main features is its SSRS designer, I decided to tackle this reporting beast first. Luckily, the Twittersphere is rife with helpers. Some of my favorite SSRS-related tweets (and tweeters) are below. (Click the picture to see the full list.) If you have any favorite SSRS bloggers, tweeters, or posts I’d love to hear about them! Send me a tweet or leave a comment in the comments section so I can check it out.
Category Archives: SSRS
How to display all selected values for an SSRS multi-select parameter in an Excel report
It is very common when designing reports that you may need to define a multi-select report parameter in order to give the users some filter options and generate the report based on the user-selected values. You may also want to display those selected values on the Excel report. This can easily be achieved with OfficeWriter by creating an SSRS formula that uses the required report parameter and inserting that formula into the report template through our OfficeWriter Designer add-in in Excel, as described in the following steps:
- Open the RDL file using the Open Report button in our add-in Continue reading How to display all selected values for an SSRS multi-select parameter in an Excel report
Jason Thomas Reviews OfficeWriter’s SSRS Integration
The following is a review of OfficeWriter written by Jason Thomas, a BI consultant specializing in SSRS. Read the full review here.
“As a BI consultant specializing in SSRS, I have had lots of frustrations and hard times because of Excel. Every now and then, I have some or other business user coming up to me and asking for some feature which is there in Excel but not in SSRS. If you have been following my blog, you would already know that I am more of a work-around man, trying to find some alternative for features which are not supported out of the box. But when it comes to Excel related features, most of my attempts end in disappointment. So naturally, my ears perked up when I was asked to review a plugin which claimed to build SSRS reports using Excel and Word.
So I downloaded OfficeWriter v8 and spent close to a week playing around with it. Even though I encountered some minor quirks (v8.0 doesn’t run on the 64 bit version of Office 2010 yet – luckily I had a home pc with a 32 bit version of Office; got some minor issues when editing and deploying an existing SSRS report with shared data sources – got around it by setting the data sources once again from the report manager), overall I have been very pleased and of course, excited at the different prospects that this plugin opens up.”
Post-Processing SSRS Reports using OfficeWriter in .NET
Using OfficeWriter‘s integration with SSRS in conjunction with the Designer is typically a straightforward process with no programmatic manipulation of the reports. A developer designs the report in Visual Studio BIDS, opens the .rdl using the Designer, designs the template in Word/Excel, and publishes the report. The report is then rendered inside the Report Manager using the custom OfficeWriter export option. However, there are times that situations call for post-processing the report programmatically and that’s where the ExcelApplication and WordApplication objects come in. Accessing and rendering the reports through the SSRS API is straightforward and the resulting byte array can be turned into a MemoryStream and passed to OfficeWriter.
Adding the SSRS Web Service
The first step necessary to tapping into the SSRS API is to add the Report Execution Service to your web references inside of Visual Studio. The URL for the web service is likely along the lines of *http://localhost/reportserver/reportexecution2005.asmx*, where localhost/reportserver is the hostname and virtual directory of the SSRS server. Note that this is for SQL Server 2008, despite the 2005. This web service is located in the directory C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer in the example instance I am using. Continue reading Post-Processing SSRS Reports using OfficeWriter in .NET
No more row limitations: Exporting XLSX with SQL Server 2008 R2 Reporting Services
The hard-at-work folks on the SQL Server Reporting Services team recently announced that SQL Sever 2012(formerly codenamed “Denali”) will feature the ability to export reports to XLSX and DOCX. These new OpenXML-based file formats were introduced in Microsoft Office 2007, and they contain a number of interesting features and capabilities beyond the older, binary XLS and DOC files.
But, if you can’t wait for the SQL Server 2012 release (expected to be released in the first quarter of 2012) to get XLSX and DOCX from Reporting Services, you can use OfficeWriter.
No more 65,536 row limitation!
This is probably the biggest reason that people want XLSX instead of XLS. XLSX supports over a million rows.
What is OfficeWriter?
OfficeWriter is a set of tools for Excel and Word document generation, manipulation, and reading. OfficeWriter consists of three main pieces:
1. OfficeWriter API – For .NET developers who need to build custom Excel and Word document generation, manipulation, and reading into custom applications.
2. OfficeWriter SharePoint Solutions – Packaged, no-code solutions for SharePoint that add document generation capabilities to SharePoint lists
3. OfficeWriter for Reporting Services – Custom extensions to SSRS that allow you to publish, populate, and deliver Excel and Word reports from the report server.
The last option is what I will focus on. Specifically, how to generate XLSX reports from SSRS.
How to Generate XLSX from SQL Server 2008 R2 Reporting Services
With OfficeWriter for Reporting Services, you can publish Excel and Word templates to the report server. When the report executes at runtime, SSRS merges the Excel or Word template with data and delivers it to the client.
First, I’ll open Excel and flip to the Add-Ins tab where the OfficeWriter toolbar resides:
You can either build a new DataSet for the report by clicking the Add Query button, or import a DataSet from an existing RDL by clicking Open Report. I have an RDL with a specific query already defined in it that shows all 121,317 rows from the AdventureWorks 2008 R2 sample database. So, I’ll click Open Report and just use that. Once I open that RDL, the DataSets and all fields from those DataSets will appear in dropdown menus:
The RDL I opened has only one DataSet called TerritorySalesLineItems, and two fields called Price and TerritoryName. To build the report, add any headers or styles you want using regular Excel formatting techniques, and then drop data markers into cells where you want data to go by clicking on items in the Insert Field dropdown:
Publish this workbook to the report server with the Publish button:
When it’s published, it will appear just like a regular report which can be secured, managed, and delivered as you would any conventional RDL:
To export the report, select the Excel designed by OfficeWriter option:
The output generated is the template merged with the data by OfficeWriter on the report server. Note that the exported Excel has well over 100k rows:
Conclusion
Using OfficeWriter and Reporting Services together lets you pump a huge amount of data into Excel workbooks in a high-performance, highly scalable manner while preserving all Excel formatting and leveraging the features of the report server.
How to Generate XLSX from SQL Server 2008 R2 Reporting Services
With OfficeWriter for Reporting Services, you can publish Excel and Word templates to the report server. When the report executes at runtime, SSRS merges the Excel or Word template with data and delivers it to the client.
First, I’ll open Excel and flip to the Add-Ins tab where the OfficeWriter toolbar resides:
You can either build a new DataSet for the report by clicking the Add Query button, or import a DataSet from an existing RDL by clicking Open Report. I have an RDL with a specific query already defined in it that shows all 121,317 rows from the AdventureWorks 2008 R2 sample database. So, I’ll click Open Report and just use that. Once I open that RDL, the DataSets and all fields from those DataSets will appear in dropdown menus:
The RDL I opened has only one DataSet called TerritorySalesLineItems, and two fields called Price and TerritoryName. To build the report, add any headers or styles you want using regular Excel formatting techniques, and then drop data markers into cells where you want data to go by clicking on items in the Insert Field dropdown:
Publish this workbook to the report server with the Publish button:
When it’s published, it will appear just like a regular report which can be secured, managed, and delivered as you would any conventional RDL:
To export the report, select the Excel designed by OfficeWriter option:
The output generated is the template merged with the data by OfficeWriter on the report server. Note that the exported Excel has well over 100k rows:
Conclusion
Using OfficeWriter and Reporting Services together lets you pump a huge amount of data into Excel workbooks in a high-performance, highly scalable manner while preserving all Excel formatting and leveraging the features of the report server.
Get started:
Learn more about SSRS integration or start your free trial of OfficeWriter today.
OR 
OfficeWriter and Excel Services: When to Use Which
Current and potential customers often ask our sales team: “what is the difference between OfficeWriter and Excel Services?” The high level answer is that that they are very different products designed to address very distinct scenarios. In brief, OfficeWriter is a tool that developers can use to generate, read, and manipulate Excel and Word documents in .NET code. Excel Services is a SharePoint-based server product for sharing, managing, and securing Excel workbooks.
Note that the title of this article is “OfficeWriter and Excel Services”, not “OfficeWriter vs. Excel Services”! There are scenarios where they are complementary to one another, and can be used together to solve some pretty interesting problems.
Excel Services Core Capabilities
Excel Services is a server-based workbook sharing, viewing, and calculation service. It’s a server product that is part of the SharePoint platform. It requires an enterprise-level SharePoint license to use.
The features discussed here pertain to Excel Services in SharePoint 2010, which is the latest version. Excel Services focuses on several key scenarios:
-
Sharing and viewing workbooks through a browser– Excel Services allows for thin, web-based viewing of Excel 2007 and 2010 workbooks. This feature works only with modern XLSX files, not the legacy XLS files. The key benefits of this are:
- Excel is not required to be installed in order to view the file since it’s rendered in a web-based view
- Web-based views are interactive to a certain extent. Certain features including filtering, sorting, and page-level slicers are supported.
- Since an XLSX file viewable by Excel Services is just another item in document library, it can be secured and managed with SharePoint permissions just as any other document.
- The web-based view can be hosted in SharePoint webparts to become part of a larger dashboard.
Continue reading OfficeWriter and Excel Services: When to Use Which
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.
Reporting Services Error: Could not load file or assembly ‘x’ or one of its dependencies
Problem
You want to use a newer version of ExcelWriter or WordWriter with OfficeWriter’s Reporting Services integration. A common scenario is upgrading to an intermediate build of ExcelWriter or WordWriter which fixes a bug or adds a new feature.
To provide context, OfficeWriter’s Reporting Services integration consists of the following components:
- ExcelWriter (SoftArtisans.OfficeWriter.ExcelWriter.dll)
- WordWriter (SoftArtisans.OfficeWriter.WordWriter.dll)
- A custom rendering extension (SoftArtisans.OfficeWriter.RS2008.dll for SQL Server Reporting Services 2008)
When upgrading to a different version of ExcelWriter or WordWriter, normally you also have to upgrade to the matching version of the custom rendering extension. This is because the custom rendering extension is built against a specific version of ExcelWriter and WordWriter and only works with that version. If the version of ExcelWriter or WordWriter does not match the version of the custom rendering extension, you will get this error when exporting the report using ExcelWriter or WordWriter:
Could not load file or assembly ‘x’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference.
If you are performing a regular upgrade in which you replace the custom rendering extension DLL as well as the ExcelWriter or WordWriter DLL, use the manual installation instructions as a guideline.
However, if there are no changes to the custom rendering extension, it is convenient to use the same custom rendering extension DLL and replace just the ExcelWriter or WordWriter DLL. This approach also allows you to easy test any version of the ExcelWriter or WordWriter DLL. In this case, follow the below instructions.
Solution
To upgrade the ExceWriter or WordWriter DLL without having to replace the custom rendering extension, you can create an assembly binding redirect so that request for the old DLL are redirected to the new DLL.
The solution is also applicable to any ASP.NET application for which you want to replace a dependent assembly with another version. Use the instructions below as guidelines for making modifications to the application’s web.config.
The process for creating an assembly binding redirect is slightly different for ExcelWriter and WordWriter, because by default WordWriter is installed into the Global Assembly Cache (GAC).
Upgrading ExcelWriter
1. Navigate to the Reporting Services directory, typically `DRIVE:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services’
2. Copy SoftArtisans.OfficeWriter.ExcelWriter.dll to ReportServer\bin, replacing the existing DLL
3. Determine the version of the new DLL; for example, 7.5.1.2770
4. Edit ReportServer\web.config and add the following section under the section:
XML Config Script
<configuration> <runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="SoftArtisans.OfficeWriter.ExcelWriter" publicKeyToken="f593502af6ee46ae" culture="neutral" /> <bindingRedirect oldVersion="7.5.0.1-7.5.1.9999" newVersion="7.5.1.2770"/> </dependentAssembly> </assemblyBinding> </runtime> </configuration> Note: If there is an existing section, add only the ” section inside it, so that there is only one assembly binding definition.
In the example above, set the newVersion attribute to the version of the new DLL. Set the oldVersion attribute to a single version or a range of versions which are to be mapped to the new version.
5. Perform the same steps for ReportServer\bin\ReportingServicesService.exe.config
6. Restart the Report Server
Upgrading WordWriter
If WordWriter is installed in the GAC, as done by the installer, you must also install the new DLL into the GAC. Because the GAC supports multiple versions of a DLL, you do not have to uninstall the old DLL. However, by using assembly binding redirect, you can force Reporting Services to use the new DLL. Follow the procedure below to upgrade WordWriter:
1. Install the new DLL into the GAC using, for example, the gacutil tool.
2. Navigate to the Reporting Services directory, typically ‘DRIVE:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services’ Copy SoftArtisans.OfficeWriter.WordWriter.dll to ReportServer\bin
3. Determine the version of the new DLL; for example, 4.5.1.1648
4. Edit ReportServer\web.config and add the following section under the section:
XML Config Script
<configuration> <runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="SoftArtisans.OfficeWriter.WordWriter" publicKeyToken="f593502af6ee46ae" culture="neutral" /> <bindingRedirect oldVersion="4.5.0.1-4.5.1.9999" newVersion="4.5.0.1567"/> </dependentAssembly> </assemblyBinding> </runtime> </configuration> Note: If there is an existing section, add only the section inside it, so that there is only one assembly binding definition.
In the example above, set the newVersion attribute to the version of the new DLL. Set the oldVersion attribute to a single version or a range of versions which are to be mapped to the new version.
5. Perform the same steps for ReportServer\bin\ReportingServicesService.exe.config
6. Restart the Report Server
If WordWriter is not installed in the GAC, skip step 1 and follow steps 2-7 in the above procedure.
How to create a dynamic Reporting Services Shared Data Source Using Linked Servers
When using SQL Server Reporting Services (SSRS), wouldn’t it be nice to be able to specify the data source at run time? For example, you could deploy a report on a single Report Server, but have the ability to retrieve data from any database on any server. Without a dynamic data source, you would have to deploy multiple instances of the report, one for each database server you want to use. Additionally, if you are using stored procedures, you must also duplicate them on each database server.
SSRS already allows for a dynamic data source through an expression-based connection string. The main limitation is that an expression-based connection string can only be used with an embedded data source; i.e., one that is contained in the report definition. You cannot use it with a shared data source. However, a shared data source offers advantages over an embedded data source and is often a necessity. Imagine having to modify the data source for hundreds of reports. With an embedded data source you would have to edit every report, while with a shared data source you could do so in just one place.
This post discusses a solution that lets you specify the database server at run time, while still being able to use a shared data source. The solution uses linked servers. A linked server is essentially an alias for a remote server on a local server. Once defined, a linked server allows you to execute queries on the remote server locally. This solution consists of the following steps: Continue reading How to create a dynamic Reporting Services Shared Data Source Using Linked Servers
SSRS: Improve Performance by Automating Report Caching Using Null Delivery Provider
If you’re familiar with subscriptions in SQL Server Reporting Services, you may have wondered what the “Null Delivery Provider” is for. While it may seem odd to deliver the reports to a black hole, it does come in handy from time to time. In this post, I’ll show you how the “Null Delivery Provider” can help you increase performance by automatically creating cached reports. The basic idea is to create a data-driven subscription to run the report with a number of different parameter values to build the cached copies. This is similar to creating Execution Snapshots, but in this case we can specify a number of different sets of parameters, where as Execution Snapshots allow only one set of parameters. The “Null Delivery Provider” allows you to do this without having to save the reports somewhere, taking up valuable disk space, or have them emailed to someone, taking up valuable sanity.
In case you’re not familiar with cached reports, SSRS lets you create copies (or “caches”) of a report after the data has been gathered, but before the report has actually been rendered. When a user runs the report, it’s rendered from the cached copy which already has all the data. This means the queries don’t have to be run every time the report is generated. If you’d like to read more about report caching, there are some good articles on MSDN.
This example uses the Product Line Sales SSRS 2005 sample report. Everything for this example still works the same in SSRS 2008. Continue reading SSRS: Improve Performance by Automating Report Caching Using Null Delivery Provider