Tag Archives: sql server reporting services

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.

Knowledge Base, OfficeWriter, Reporting Services

OfficeWriter Designer toolbar is not visible in Excel or Word

Problem

After installing the OfficeWriter Designer, the Designer Add-In is not available in Excel/Word. This may occur if:

  • The OfficeWriter Designer installer was not run
  • The Designer was not installed properly
  • The Designer is currently disabled.

Solution

To run the OfficeWriter Designer installer

The OfficeWriter Designer is a component that is redistributible and does not require a license key. There is a separate installer for the OfficeWriter Designer.

The Designer installer is available for separate download from http://www.officewriter.com/product-updates or upon request.

To check if the OfficeWriter Designer was installed correctly:

Make sure that hidden folders are visible:

  • Open a folder and go to Folder options
  • Under ‘Files and Folders’ locate ‘Hidden Files and Folders’
  • Select ‘Show hidden files and holders’

Check the ApplicationData\Microsoft start directories for the Add-In files:

  • For Excel: look for SAExcelTemplateAddIn.xlam in Application Data\Microsoft\Excel\XLSTART
  • For Word: look for SAWordTemplateAddIn.dotm in Application Data\Microsoft\Word\STARTUP

If the files are there, the OfficeWriter Designer was installed correctly, but might be disabled. See below for how to re-enable the Designer if it’s been disabled.

If the files are not there, then the OfficeWriter Designer was not installed properly. Try uninstalling the OfficeWriter Designer through Add/Remove programs and try to re-install.

How to re-enable the Designer if it has been disabled:

Office 2007/2010:

  • Go to the Office menu button (2007) or File tab (2010) in the upper left hand corner.
  • Click Excel/Word Options
  • Select Add-Ins from the left menu
  • At the bottom, select ‘Disabled Items’ from the ‘Manage’ drop-down menu.
  • Click Go
  • If the OfficeWriter Designer is there, select and click Enable.

Office 2003:

  • Go to the Help menu and select About Microsoft Office Excel/Word
  • Click Disabled items*
  • If the OfficeWriter Designer is listed, select and click Enable.