Handling TIFF data in Reporting Services

So, here is an interesting stuff that we did recently as a part of the SSRS POC. The very nature of the requirement makes it very interesting and exciting.

The Requirement…

There are a couple of pages in the TIFF format (that might come from FAX or some other application) and they need to be brought in and displayed via SSRS along with the other data fields. The header and the footer along with the summary data stays constant though.

The challenge…

Out of the various formats that SSRS supports out of the box, TIFF is not present 🙂 Although we can use the BMP mime type to display the first page, but nothing beyond that. So the deal is to display and merge the individual pages along with the other data.

The Solution…( of course one of the possible ones 🙂 )

The solution can be broken into smaller parts consisting of the multiple technologies that come together to address this issue and each of them have their own significant impact.

To start with, the format of the file that we are dealing with has a major impact, then comes the set of API that is leveraged to break and extract the information; storing these individual pieces of information is another important aspect and then finally comes the SSRS part which displays the overall stuff. Let’s take a look at each of them individually.

TIFF is a flexible file format that  is capable of containing the images, text, JPEG and other types of data all in a single file. One can find more information here.

The other essential part of the solutions in the new wonderful stuff that windows presentation foundation has to offer as far as the processing of the various image format goes, check this Imaging Namespace. Here we get the APIs that can be utilized to break the TIFF file into individual frames (a frame is a term used in the APIs to address a single page in the TIFF file) and then merged along with the other relation database records.

As far as the storage goes, the solution is an attempt to do a proof of concept hence the basic way of storing the individual frames in files has been used; the focus was more on merging the TIFF files along with the relational database. This can be improved later on. The individual frames stored on the disk in the form of small files can then be saved into the database in a varbinary column.

Also, the process of breaking the TIFF file into smaller files and storing it into the relevant table can be automated via a trigger or in a batch form via a job.

Once the table has the data for the individual frames, it can then be (inner) joined with the other table holding the records for the header or footer and other summary data and can be displayed in the report using an image control. The sample code below doesn’t use that structure though. It dumps all the stuff in a single table for the sake of convenience.

The Code…

The code that is made available uses a sample TIFF file generated from the favorite Adventureworks database and gets the top 2 products for each sub category.

To generate this sample file

1. Use the following query as the dataset

2. Preview it in the simple SSRS report

3. Save it in the TIFF format from the preview pane

 

SELECT sc.Name AS subCategory, p.Name AS productName
FROM Production.Product AS p INNER JOIN Production.ProductSubcategory AS sc
    ON p.ProductSubcategoryID = sc.ProductSubcategoryID
WHERE p.ProductID IN
    (SELECT TOP (2) ProductID
    FROM Production.Product AS p1
    WHERE ProductSubcategoryID = p.ProductSubcategoryID)
ORDER BY subCategory, productName

 

Once the TIFF file is available, we would need a table to hold the individual pages. The following script can create one

CREATE TABLE [dbo].[TIFFTable](
    [CustomerName] [nvarchar](50) NULL,
    [SomeDetails] [nvarchar](50) NULL,
    [SomeMoreDetails] [nvarchar](50) NULL,
    [Image] [varbinary](max) NULL
)

 

After the table is created, the code available at this location creates a mini WPF application that, just for the sake of looking into the file and learning, allows one to browse the individual pages in the TIFF file and then later save it to the TIFFTable created above.

Before running the code remember to make changes in the code for…

1. FilePath value

2. Name of the sample TIFF file

3. DB connection string

Run the miniapp and after you are done browsing back and forth in the file, click the "Save" button; doing so would break the file and eventually it gets stored in TIFFTable. You can do so for a couple of different customers to generate the some data for the next report. This data can then be displayed using the SSRS report in a normal fashion. The image control can be used to point to the varbinary column. Note that even though the format of the individual file is TIFF, we can change the format using the appropriate API is required. The report RDL has one way of showing the data; it groups data based on the customers we enter using the miniapp. You can always alter it and use it as appropriate. A screenshot of the miniapp follows…

 

Hope this piece of information is of some use to you…and if you come across a better way of handling this situation please share it with me 🙂

Advertisements
This entry was posted in Reporting Services. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s