Tag Archives: SSRS

ExcelWriter, Knowledge Base, Reporting Services, SSRS

How to create a drill-down report in Excel with hyperlinks

Problem

Reporting Services provides drill-down report behavior, where clicking on a populated data field generates another report based on the value of the data field clicked. This post addresses how to acheive similar behavior within Excel with OfficeWriter using Reporting Sevices URL access and Excel’s HYPERLINK function.

Solution

Overview

When the OfficeWriter renderer populates the Excel report with data, it inserts new rows for each row of data. This means that all Excel formulas are updated, including the HYPERLINK function formula. This approach uses this formula update functionality and imported data values to dynamically create hyperlinks that point to the Reporting Services URLs.

For the sake of example, “SalesReport” is the original report that contains a list of invoice IDs. “InvoiceReport” is the linked report that takes an InvoiceID as a parameter.

Reporting Services URL Access

Typically, Reporting Service reports are rendered through an application, such as the Report Manager. Reporting Services also allows for rendering reports using just URLs. The basic syntax to render a report wtith a parameter is:

 http://[servername]/reportserver?/[File path to report on Report Server] &rs:Command=Render&rs:Format=[Rendering Format]&[ParamID]=[Param Value]

The URL for rendering the “InvoiceReport” in the OfficeWriter for Excel format, with Invoice ID 12345, is as follows:

 http://myservername/reportserver?/ InvoiceReport&rs:Command=Render&rs:Format=XLTemplate&InvoiceID=12345

Notes:

ASP.NET, ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, SSRS, WordWriter

How to create a custom SQL Server Reporting Services error page

Problem

When SQL Server Reporting Services (SSRS) encounters an error, it returns an error message which is displayed by Report Manager. The error message can include technical details that reveal more information than necessary or are potentially confusing or intimidating to a user. One example is the scenario in which the user attempts to export a report from Report Manager using the “Excel designed by OfficeWriter” option, but the report is not created using the OfficeWriter Designer. In this case, SSRS would return the following error:

 Error SA008050: This report was not designed for the selected OfficeWriter rendering extension.

Depending on configuration, Report Manager may also display a detailed error message and a stack trace. In this case, the error message is self-explanatory. However, often it is desirable to provide a friendly custom error page that hides the technical details of the internal exception and provides more helpful instructions to users.

Configuring a custom error page for Report Manager is no different from doing so for other ASP.NET applications. The custom error page is simply a web page that you create. You can configure ASP.NET to display a custom error page to all users. Alternatively, the custom error page can be displayed only to users accessing Report Manager from a remote machine, which is usually the case in production. For users accessing Report Manager from the same machine, the full error would be displayed. Typically, a detailed error message is helpful for debugging purpose during development.

Additionally, you can configure a custom error page to be displayed for specific HTTP status codes. For example, the OfficeWriter error above is an internal server error, which results in HTTP status code 500.

Solution

To configure a custom error page for Report Manager:

  1. Browse to Report Manager folder.
    • For SSRS 2005, its typical location is DRIVE:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportManager, where x is the instance
    • For SSRS 2008 and SSRS 2008 R2, DRIVE:\Program Files\Microsoft SQL Server\MSRS10.SSRS\Reporting Services\ReportManager .
  2. Make a copy of the existing Web.config as backup
  3. Open Web.config in a text editor, or in Visual Studio to get Intellisense support
  4. Under the section, locate the `1 section
  5. Modify the existing section, or insert a new one, with the appropriate settings
  6. Restart the Report Server

The possible values for the mode attribute of the tag are Off, On, and RemoteOnly. The default is Off. In order to enable a custom error page, mode must be set to On or RemoteOnly. For example, the below section specifies the following: 1) a custom error page be displayed for remote users only, 2) a specific page for HTTP status code 500, and 3) a default page for all other HTTP status codes. Note that attributes are case-sensitive.

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.

Events, Featured, OfficeWriter, Programming, SQL Server, SSRS, Webinars

[Webinar] Making Reporting Easier with SSRS Designer Ribbon

SSRS Webinar

With data sets tied to different data sources and multiple people handling one report, the process of building a report can get messy. OfficeWriter’s built-in Designer Ribbon makes it easier to interact with SQL Server Reporting Services (SSRS), helping you access reports from your applications on time and with ease. Join us Friday, March 22nd as our Senior Sales Engineer, Chad Evans, shows you how this feature makes using SSRS simpler.

In this webinar you will learn:

  • How to build an SSRS report from start to finish using the OfficeWriter designer ribbon
  • How to easily open existing reports, create new ones, and view reports in process with SSRS

We will also have a question and answer period at the end of the webinar. Feel free to send in questions prior to the webinar so that we can include them in the presentation.

When: Friday, March 22nd at 1 P.M. EST

Can’t attend, but still want a copy of the recording and slides? Register below and we’ll email it out following the webinar.

**Spots are limited. So please register early to secure your seat.






ASP.NET, Business Intelligence, Enterprise Software, Excel, Featured, OfficeWriter, SharePoint, SQL Server, SSRS

Everything You Need to Know About OfficeWriter: The Whitepaper

Looking for a comprehensive overview of OfficeWriter? You’ve come to the right place. We’ve partnered with Andrew Brust of Blue Badge Insights to give you an inside look at how OfficeWriter can benefit your company’s business intelligence.
OfficeWriter Whitepaper
Microsoft Office and Data: A Love Story
Excel and Word are the Will and Kate of the Office software world, but if you try to use them for data analysis, they’ll morph into Milli-Vanilli. OfficeWriter prevents that from happening.
In this whitepaper, you’ll learn:
  • How OfficeWriter’s API and templates provide bridges between Microsoft Office & databases
  • How OfficeWriter enhances SQL Server Reporting Services
  • How OfficeWriter turns SharePoint lists & libraries into full-fledged Office docs
That is simply the beginning. Get a full overview of OfficeWriter today.

Business Intelligence, SQL Server, SSRS

Automating Reports with SSRS Subscriptions

SQL Server Reporting Services subscriptions are an easy way to automatically run and deliver SSRS reports. You can schedule reports to run at a regular interval and deliver to file share, e-mail, or a SharePoint document library (if you’re using SharePoint integration mode).

How it’s done:

Here is my SSRS 2008 R2 instance, with a couple of prepared reports:

I’m going to create a subscription for SSRS_example_multivalue, which has a parameter with multiple values to select from. Continue reading Automating Reports with SSRS Subscriptions →

Business Intelligence, SQL Server, SSRS

Twitter Roundup: Talking About SSRS

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.

Business Intelligence, OfficeWriter, Reviews, SharePoint, SQL Server, SSRS

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

[Click here to read the full review]

SSRS

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 →

SSRS

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