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 :-)

SQL Server 2005 Reporting Services - Reports Management & Execution Web Services

One of the important and interesting features in SQL Server Reporting Services is the web services support. They can be divided into two parts; one for the reports execution and one for the reports management.

The ReportingService2005 class is responsible for the reports management web service and contains all the required methods & properties for the same. Similarly ReportExecutionService class holds the methods & properties for the execution part. For all the member details and what each of them does, one can refer the MSDN docs; nicely documented so won't reproduce the same stuff again :-) For quick reference (http://msdn.microsoft.com/en-us/library/ms155071.aspx)

The example mentioned below is a small POC that I was working on recently and I thought that someone could benefit from what I learned during experimenting with the web services. The example is not very fancy and there are definitely better ways of doing the stuff...no arguments about that. If anyone feels that this code could be of any use to them, s\he is free to go ahead and do so.

Here is the storyline that is used. The reports server has all the reports published and organized in folders based on the sensitivity of the data that the reports holds. The user would only have access to the reports under a particular folders to which he has been granted access. Mostly, the administrator would allow\restrict access to the reports and\or folders using the management studio for reporting services, but over here we are dealing (and had to mimic) with a third party web access manager who decides the access to the reports\folder based on the current user login. Based on the logged in user it would decide what reports he can see and what not.

In other words the account used to access the reports on the reports server is different than the user login account. It has access to all the reports on the server. The "logged in user's" access is decided by the third party web access software and is "not" dependant on the windows login account.

This is different than the way it would have behaved if the rights access would have been done by the management studio. In that case the windows account and the role to which he belongs is used for deciding the access he has to a particular report.

SSRS Management Web Services

Based on the above mentioned story line, in this example we have two users Tom & Joe. They have access to the "AdventureWorks Reports" folder and "WellsReport" folder respectively. When a user is logged in and runs the app, he is only shown the respective folder\reports to which he has access. This control would be a characteristics of a web access manager and is mimicked by a dummy function (WebAccessManagerBlackBox), which takes a parameter of username. We get the current user by making a call to "System.Security.Principal.WindowsIdentity.GetCurrent().Name" and passing it as a parameter while calling the said function. The trick is that the report's path is set to the folder to which the current user has access and thus only the reports below in the hierarchy to which the user has access are displayed.

The web service method, ListChildren returns the list of the catalogs (report items) which is then passed to populate the tree control.

This would display only the reports to which the user has access and populate the tree view control on the left. Once the user clicks on one of the report, the adjacent reports viewer control would display the selected report. To add some spice to the story line it is assumed that the user might have to open more than one report or even the same report twice so that he can do some comparitative analysis. This feature is made available by the use of the tabbed controls along with multiple reports. When the user clicks on a report in the tree view the current tab control displays the report.

Also, when the user runs the application, he is shown a dummy report which has nothing but a message asking him to click on one of the report in the tree node.

Here is the code...

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using MServices.ReportingService; namespace MServices { public partial class MServicesMain : Form { private static string reportServer = "/reportserver" ;?=";?">/reportserver" ;?=";?">/reportserver" ;?=";?">/reportserver" ;?>/reportserver";'>http://<machine name>/reportserver"; private static char separator = '/'; private static char[] separators = { separator }; private static string separatorString = new string(separator, 1); private static string reportPath = "/"; public MServicesMain() { InitializeComponent(); } private void MServicesMain_Load(object sender, EventArgs e) { // added this peice to handle the restriction of the reports per user basis. WebAccessManagerBlackBox(System.Security.Principal.WindowsIdentity.GetCurrent().Name); ReportingService.ReportingService rService = new ReportingService.ReportingService(); rService.Credentials = System.Net.CredentialCache.DefaultCredentials; CatalogItem[] catalogItems; catalogItems = rService.ListChildren(reportPath, true); PopulateTree(catalogItems); // added this peice to handle the restriction of the reports per user basis. if (!reportPath.Equals("/")) { ReportTreeView.Nodes[0].Text = reportPath.Substring(1); } ReportViewer1.RefreshReport(); } private void WebAccessManagerBlackBox(string username){ if (username.Equals("ADV-SQL2\\Tom")) { reportPath = "/AdventureWorks Reports"; } if (username.Equals("ADV-SQL2\\Joe")) { reportPath = "/WellsReport"; } } private void PopulateTree(CatalogItem[] catalogItems) { foreach (CatalogItem item in catalogItems) { if (item.Type == ItemTypeEnum.Report && item.Name!= "DummyReport") { string path = item.Path.Remove(0, reportPath.Length); string[] tokens = path.Split(separators); AddNodes(tokens, 0, ReportTreeView.Nodes); } } } private void AddNodes(string[] tokens, int index, TreeNodeCollection nodes) { TreeNode node = null; for (int i = 0; i < nodes.Count; i++) { if (nodes[i].Text == tokens[index]) { node = nodes[i]; break; } } if (node == null) { node = new TreeNode(); node.Text = tokens[index]; nodes.Add(node); if (tokens.Length - 1 == index) { node.Tag = String.Join(separatorString.ToString(), tokens); node.Text = tokens[tokens.Length - 1]; } } index++; if (tokens.Length > index) { AddNodes(tokens, index, node.Nodes); } } private void ReportTreeView_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e) { string tag = String.Empty; if (e.Node.Tag != null) { tag = e.Node.Tag.ToString(); ((Microsoft.Reporting.WinForms.ReportViewer)tabControl.SelectedTab.Controls[0]) .ServerReport.ReportServerUrl = new Uri(reportServer); ((Microsoft.Reporting.WinForms.ReportViewer)tabControl.SelectedTab.Controls[0]) .ServerReport.ReportPath = reportPath + tag; ((Microsoft.Reporting.WinForms.ReportViewer)tabControl.SelectedTab.Controls[0]).RefreshReport(); tabControl.SelectedTab.Text = tag.Substring(tag.LastIndexOf("/")+1); } } } }

The image shows the form in action...

SSRS Execution Web Services

The sample in the online documentation does an excellent job of getting the concept across. Please refer to the same at the following URL for more details.

http://technet.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx