SQL Server Reporting Services and Working with Overlay Data

CODE DOWNLOAD AVAILABLE FROM THE MSDN CODE GALLERY.

Introduction

As a part of their activities, companies often need to generate reports and forms from scanned images and various government supplied formats. It is a common requirement that these documents have exact formatting, and even very minimal changes in textbox or label sizes or font faces are not acceptable. Meanwhile, the accurate reproduction of the original form by creating new report from scratch might be a costly and error prone task. The data to populate these forms is often stored in a relational database which needs to be rendered in an exact position on the form. This scenario is sometimes referred to as a fixed layout reports, or pixel-perfect reports.

This paper discusses a solution to this problem based on SQL Server 2008 Reporting Services (SSRS). It is assumed that we have an input form in PDF or image format supplied by government or some other authority. The form consists of constant elements (titles, labels, and explanations) and variable text or image elements, which depend on the data stored in the enterprise database. Also, this form can be single-page or multi-page.

Scenario

An enterprise scans or downloads a government form to produce statements for their employees. The format could be either image or PDF; note that any forms supplied in PDF format can easily be converted to an image. For our example we use "EMPLOYER'S REPORT OF WORK-RELATED INJURY/ILLNESS - State of New York - Workers' Compensation Board"

As PDF files are easily converted to image formats, we get the image and use it as a background for new SSRS reports. This image will contain all constant items of the source form. In SSRS Report Designer, we add all the textboxes and checkmarks where the information from our database will be shown, and position them according to the lines of the background image. Thus the image will be overlaid with data from our database in the correct locations on the form image. The report will be displayed correctly without distortion on a web page for browsing in all web browsers and be able to be printed without loss in quality. A user will also be able to export the reports to TIFF, PDF, or other formats supported in SQL Server Reporting Services.

Solution Overview

The flexibility to support all typical scenarios of the report usage is a primary requirement. This means the report should be displayed correctly without distortion on a web page for browsing in all web browsers as well as should be correctly printed without loss in quality. Also there should be an option to export reports to various formats such as TIFF or PDF.

Challenges

There are a few different challenges presented in this solution, primarily due to our requirement for a report to be rendered in a web browser and sent to the printer. The browser does not typically understand or honor dots-per-inch (DPI) encoded in images and will always render images in the browser at a 96 dpi resolution. 96 dpi is not typically acceptable when printing the reports and forms, and a higher resolution, commonly 300 dpi or more, is often required to achieve acceptable print quality.

A: A 96 dpi resolution raster for HTML rendering is used by the ReportViewer component to browse images on screen. Meanwhile, other scenarios, such as printing require a higher resolution (300 dpi or more) for acceptable printed document quality.

B: When using high resolution images, the Print Preview will consume large resources and in some cases it will not even be usable because of low performance and memory overflow.

C: HTML renderer does not support item overlap. This means that if a report is composed with a textbox (representing a field on a form) placed on top of an Image element (representing a background or image of the form) it will be rendered in the browser with the textbox not on top of the image, but far to the right. Some other built-in renderers do support item overlap.

Tradeoffs and Decisions

Businesses needing fixed layout reports often have many different requirements and priorities associated with these requirements. Given all the diverse requirements and the various options available we will discuss the pros and cons of a few different approaches. We will not cover all approaches in detail and will instead discuss the design and implementation of a solution that meets our requirements for a report being rendered in the browser and printed in high resolution.

Using a single 96 dpi resolution background picture for all scenarios

Pro: This approach implies the simplest way of solving the problem.

Con: The report with 96 dpi BackgroundImage would be displayed correctly in ReportViewer, but when printing or exporting to PDF, the quality would be very poor.

Using a single high resolution background picture for all scenarios

Pro: The report based on high resolution image component would be printed in high quality.

Con: When viewed in the browser the report would not look like it does when printed. The preview process would likely consume very large resources resulting in poor performance and is likely to end up with out-of-memory exception.

Combining low resolution and high resolution images in one report

Pro: Putting both low resolution and high resolution images into one report covers most of the typical scenarios.

Con: This approach requires additional effort when designing individual reports and additional reusable development. Another option with this approach is to implement a customized report viewer(s) for web and/or Windows applications.

High and Low Resolution Solution Approach

This solution uses two images of different resolution in a single report, a 96 dpi image and high resolution images, 300 dpi in this case. The out-of-the-box ReportViewer functionality was also customized so that the correctly processed image is chosen in different scenarios, such as browsing and printing.

The challenge of poor print quality with a low resolution image (see challenge A above) was solved by using two different versions of images for each page in two different resolutions and by implementing a special logic to switch between these images during the browsing and printing.

The second challenge with properly viewing the report on screen at high resolution image (see challenge B above) was solved by not using high resolution images for browsing and print preview. We customized the preview by displaying low resolution image in WinForms report viewer and by using PDF preview when the printing is activated from a web page. This allows us to significantly reduce the impact on performance and the memory pressure.

The third challenge with the HTML renderer not supporting item overlap (see challenge C above) can be eliminated by not using HTML renderer and instead using the built-in JPEG renderer to render a jpeg image of a single page of the report at a time and building a custom report viewer to navigate between individual pages of the report.

Thus, we created the two customized ReportViewers: one for Windows applications based on WinForms ReportViewer control, the other one for web scenarios that is based on a custom aspx page that leverages SSRS functionality through web requests and also internally uses WebForm ReportViewer control. Each time the report is rendered on screen, 96 dpi images are used while when the report in printed or exported the high resolution images are used. Since the report code itself cannot determine in which context it is used, we added a boolean parameter named ForPrint to the report. The parameter is set by our custom code to False when the report is viewed on screen and True when it is printed or exported.

Our solution is depicted on Figure 1 for Windows applications and on Figure 2 for web scenarios.

Figure 1 Solution Overview for Windows applications

Figure 2 Solution Overview for web scenarios

Implementation Notes

As mentioned, one of the challenges (challenge C above) that we solved was related to image distortion in web browser: the default SSRS HTML renderer slightly moved the designed positions of textbox and checkmark controls relative to the background image. The reason of this effect is that the HTML markup generated for the report contains an HTML table/grid and the overlaying controls (such as edit boxes) are placed into the cells of this grid. Coordinates of these cells are rounded by various browsers in a different way (for instance Internet Explorer 7 appears to be rounding it up to a next integer), and with each next row and column the displacement increases. Different browsers add different kinds of distortion to the final image, but none of them is able to produce a pixel perfect representation of the report.

A possible solution would be to develop a custom HTML renderer (see this MSDN article for more information on custom renderers) that would render reporting items that are typically used by fixed layout reports, such as text boxes, images, tablix etc. A custom renderer was out of the scope of our sample and we decided to use a simpler solution: a custom aspx page that could be used instead of the default ReportViewer.aspx for fixed layout reports. For browsing such reports, this custom page invokes SSRS through web requests by crafting appropriate urls to get individual pages of the report in JPEG format rendered using images in 96 dpi. For navigating between pages of the report and for invoking printing and exporting, we added custom buttons at the top of the page. The printing is based on behind-the-scenes export to PDF so it is the generated PDF document that is actually sent to the printer.

Our solution focused on addressing the fundamental issues with generating reports that overlay data on form images and we demonstrated the viability of this approach. Some individual reports may require additional custom code. For instance some forms have fields that span a few rows and the length on each row can be different, which make it difficult to handle by standard reporting items. Similarly, some forms have certain fields, such as a SSN composed of individual cells. These additional requirements could be addressed by implementing Custom Report Items with the design-time behavior that would allow defining exact dimension of individual lines and exact location of individual characters for strings "splitted" into cells, and the runtime behavior that would take into the account the dimensions and "spacing" defined in the design-time. More information about Custom Report Items can be found here, but we decided that implementing them is out of scope of this work.

Implementation Details

Our solution consists of 3 parts, each represented by a Visual Studio 2008 project:

1. A sample multi-page fixed layout report that overlay data on form images
2. A sample generic solution for browsing, printing and exporting this kind of reports from Windows applications
3. A sample generic web based solution for browsing, printing and exporting this kind of reports

Requirements

To open and run the samples it is required to have Visual Studio 2008, SQL Server 2008 and the correct integration between these two products. The easiest way to achieve it is to have both products installed locally.

For viewing pages in PDF format and printing a Web page from the web, you will need Adobe Reader 6+ installed.

Creating a SSRS report that overlay data on form images for the C-2 Form

Below are the step-by-step instructions for creating a sample multi-page fixed layout report that overlay data on form images using SQL Server 2008 Reporting Services. In this sample we use a multi-page form named "EMPLOYER'S REPORT OF WORK-RELATED INJURY/ILLNESS - State of New York - Workers' Compensation Board" - we refer to it below as the C2 form. Creating a single page report can follow the same steps as below.

The complete sample report is available and can be viewed by opening the "Fixed Layout Image Overlay Report.sln" solution in Visual Studio 2008. To make the data source schema and sample data for this report available, open "C2SampleData.sql" script from "SQLScripts" folder and execute it in SQL Server Management Studio.

Step 1 - Create Form Images

Create two images for each of the page of a form: a 96 dpi variant (referred below as c2_Page_<n>_96, will be used for screen viewing) and 300 dpi variant of each page (referred as c2_Page_<n>, will be used for printing).

We also, created a small image for the check pictogram ("Check.jpg").

Step 2 - Create Project

Open Visual Studio 2008 and create a Report Server project. Add a new report to the project without using Report Wizard (Right click on Report in Solution Explorer and choose Add->New Item. In the Add New Item - Report Project 1 window choose Report and click the Add button).

Step 3 - Add the Images to the Project

Add all the images created in Step 1 to the report project. We would use embedded images in this sample project as the easiest way, but note that this method won’t work if the total size of images exceed Report Server limits for rdl filesize - in such case external images should be used.

Step 4 - Add a Mode Parameter

Add a hidden report parameter named ForPrint with boolean type and "false" as the default value. This will be used to programmatically switch low and high resolution background images. To add the parameter right click Parameters in Report Data window and choose Add Parameter.

Step 5 - Add the Report Main Tablix

Add List report item from Toolbox to Body area. Provide Data Source info in Data Source Properties window (which will appear automatically after dragging the List control) and write query which will supply the data for the Data Source. Rename tablix to BaseTable. Drag Rectangle control from Toolbox to the BaseTable area and rename this inner Rectangle to BaseRectangle.

Step 6 - Set the Page Size and Page Break

In Report Properties select the paper size (8.5in * 11in) and set all margins to 0.

Resize Body to the combined size of all pages for one query record (e.g. 8.5in * 11in for one Letter page, 8.5in * 22in for two Letter pages, 8.5in * (11in * N) for N Letter pages)

Set BaseTable location to 0in; 0in and resize to same size as Body. Set KeepTogether property of BaseRectangle to False to allow soft page breaking.

Step 7 - Add Pages

For each report page add one rectangle to BaseRectangle container (check this rectangle Parent property, it must be "BaseRectangle", not "Body"!), name it Page_<n>.

The size is based on the original report page size and location is x=0, y=<sum height of prev pages>, e.g. (0in;0in), (0in; 11in), (0in; 22in).

Step 8 - Add the Low-resolution Background Images

Low-resolution images will be used for screen report rendering only - such image gives too poor quality when printing.

We will use BackgroundImage property of Page rectangle, because it is supported in all renderers. Change properties for Page_<n> rectangles

• Source = "Embedded";
• Value = "=IIF(Parameters!ForPrint.Value = false, "c2_Page_<n>_96", "")", where n is is the actual Page number represented by a given Rectangle
• BackgroundRepeat = "Clip"

This way we show the low resolution background image of this page during screen viewing and hide it during printing (based on the value of ForPrint parameter).

Step 9 - Add the High-resolution Background Images

For print operation we will use high-resolution images and the Image report items as the background for each page rectangle (BackgroundImage properties of rectangle do not support image stretching, so we need additional Image for each report Page). Such high-resolution image background is not supported by HTML renderer and consumes large system resources, so we will use it only during printing and PDF export operations.

For each report page add one Image to BaseRectangle, name it Page_<n>_Image) . Size is based on the original report page size and location is x=0, y=<sum height of prev pages>, e.g. (0in;0in), (0in; 11in), (0in; 22in), so it is placed and sized exactly as corresponding Page rectangle. Express the size and location in inches or millimeters and not in points.

Change properties for each Page_<n>_Image:

• Source = "Embedded"
• Value = "c2_Page_<n>", < n> is appropriate page number
• Sizing = "FitProportional"
• Hidden = "= IIF(Parameters!ForPrint.Value = true, false, true)"
• Use Send To Back toolbar button on each image (right click the image->Layout-> Send To Back or to show the Layout panel: View->Toolbars->Layout)

I.e. Image becomes visible only when ForPrint parameter has "true" value.

Step 10 - Add the Textboxes

Variable report data can be easily emulated with Textbox report items. Drag Field from Report Data tab to selected Page_ rectangle (do not forget to check Parent property!). You can customize textbox location, size and text font to meet the original report appearance. Repeat this for each data field.

Please make sure to set CanGrow property to false (default is true) and keep CanShrink as false.

Step 11 - Add Checkbox Items

To emulate checkboxes of the original PDF form we will use a small image that becomes visible ("checked") based on custom data logic (e.g. Gender = "Male").

To create a checkbox add an Image element, set location and resize it. Change properties to

• Source = "Embedded"
• Value = "= IIF(<some expression> = true, "Check", ""), using logic for this field
• Sizing = "FitProportional"

Step 12 - Deploying

The report is now ready to be deployed at Report Server. Set Project Deployment >properties

• TargetDataSourceFolder = "Data Sources"
• TargetReportFolder = "FixedLayoutReport"
• TargetServerURL = http://localhost/ReportServer (or use your own Report Server address)

After that, deploy it:

In the next sections, we will demonstrate how the deployed report can be used in different scenarios.

If you have not created the report from scratch and want to use the sample C-2 report from ImageReportC2, you need to open the report in Visual Studio 2008 and execute Step 12 to deploy it on the report server.

Before running this report with the next samples, please make sure that the report data source correctly references the SQL Server 2008 instance where the database sample is installed. To do that, see the connection properties of DataSource.rds. The database name should be C2 and Test Connection should be successful for the supplied SQL Server credentials.

Note that in the case when the report is to be generated for many (10,000+) database rows, the resources consumed on the report server significantly grow. It is recommended to use 64-bit systems when producing such reports.

Creating a generic solution for browsing, printing and exporting fixed layout reports from Windows applications

The next step is to create a generic application for browsing, printing and exporting fixed layout reports from Windows applications. In our sample we will use WinForms ReportViewer control with remote processing at Report Server and custom code to automatically handle the ForPrint parameter.

To do so, you can create a new WinForms project and drag the ReportViewer control from Toolbox onto the Form, set the ReportServerURL and ReportPath properties:

Then you need to implement few event handlers to properly manage the ForPrint parameters. In the ReportExport and Print event handlers the parameters should be set to true, for instance:

private void rvMain_Print(object sender, CancelEventArgs e)
{
    //Reset report to high-resolution mode for printing
    rvMain.ServerReport.SetParameters(new ReportParameter[]
	{ new ReportParameter("ForPrint", "True") });
}

In the Rendering Begin and Form Activated event handlers the parameter needs to be set back to false, for instance:

private void rvMain_RenderingBegin(object sender, CancelEventArgs e)
{
    //Reset report to low-resolution mode for screen rendering
    if (rvMain.ServerReport.GetParameters()[0].Values[0] == "True")
    {
	rvMain.ServerReport.SetParameters(new ReportParameter[]
{ new ReportParameter("ForPrint", "False") });
    }
}

To view the sample application, go to WinFormsViewer folder and open the FixedLayoutReportWinFormsViewer.sln solution in Visual Studio 2008. As this solution uses the sample C-2 report, this report (or some other reports) should be already deployed on the report server (Step 12 in the previous section describes how to do that).

Before running the sample application, specify the actual location of the report in the app.config file. You can edit two settings in ReportWinForms.Properties.Settings element body.

• "ReportServerURL" - here we place address of report server where we deployed the C-2 report;
• "ReportPath" - name of the report. For the sample report, it is /FixedLayoutReports/c2

If you are not sure about the value of the report server URL, you can find it on Web Services URL page in Reporting Services Configuration Manager.

Figure xx shows a C-2 form generated by the sample application with the data overlaid on the C2 form image

Creating a generic web based solution for browsing, printing and exporting fixed layout reports

As mentioned in the Implementation Notes section above a key part of our web based solution is a custom aspx page that plays a similar role as the default ReportViewer.aspx, but is specialized to handle fixed layout reports and address the issues that are occurring when using the default ReportViewer for this kind of reports. Our custom page consist of:

• a single standard Image WebControl
• few controls to perform navigation between report pages (First, Prev, Next and Last buttons, and CurrentPage and LastPage textboxes);
• DropDownList containing the list of available export formats: MHTML, PDF and TIFF. We did not include Word or Excel formats here because they do not seem relevant to fixed layout reports
• Export button which starts exporting to the selected format;
• Print button which exports the page in PDF format and forwards the result to the Adobe PDF print preview.

The "Web Viewer" folder contains our web based solution. To view it, open the "FixedLayoutReportWebViewer.sln" solution file in Visual Studio 2008. Before running the example, open Web.config file and specify actual URL of the report server in the configuration/appSettings section.

The report name and path should be specified as an additional parameter in the URL string. The report name for the sample solution should be "/FixedLayoutReports/C2". To set this name as a report parameter, go to report Property Pages > Start Options > Specific Page and put the report URL there with the encoded slashes and other special characters in the report path:

FixedLayoutReportViewer.aspx?%2f FixedLayoutReports%2fC2

Alternatively, you can specify the same string in the Address box in you web browser.

Note that this solution relies on the report server URL Access mechanism. In particular, the commands and formats are specified as URL arguments, for instance to get a rendering of a particular page in JPEG format we use the following URL arguments:

(rs:Command=Render&rs:Format=IMAGE&rc:OutputFormat=JPEG&rc:StartPage=<current_page_number>).

As mentioned earlier, getting the rendering of a given page as a single jpg image for the whole page and not using HTML renderer allowed to eliminate the distortion (challenge C above).

Exporting is handled by including export command to the report URL ("rs:Command=Export") together with format parameter (e.g. "rs:Format=MHTML"). For PDF output the high resolution image is used because we set the ForPrint parameter to True in the report URL. To print the document we perform an intermediate export to PDF and then a javascript code is started, which calls the PrintWithDialog() method of PDF object.

Conclusion

After reading this paper, you know how to use SQL Server 2008 Reporting Services to generate a precise version of an authority report form. Our solution minimizes the effort necessary to reproduce the fixed elements of the form because it uses a ready image as a background. We demonstrated how such report can be handled in a Windows application and in a web based solution.