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

Multiple measure based KPIs for Performance Point

This blog can be considered as a continuation to the earlier blog that I had put up regarding the various ways of using Multiple Measures in Performance Point. This blog deals more with the monitoring part of of the performance point trio and walks through the process that one could possibly use to create a KPI that consumes data from a model that handles more than one measure. The focus here is on the first approach, "Option One: Structure does the trick", i.e. using a dimension which is solely there to hold the various measures in the model (cube). The other approach of using the assumption model is pretty straight forward as one has to just include the appropriate assumption model in a particular model and s\he can use it right away in the dashboard designer.

So continuing with the previous scenario, we are interested in using two measures say the PowerCount & the ChairCount for all the rooms and want to show that in a KPI, which could later on be a part of a scorecard. These KPIs help us find how well we were able to match the number of member count in a room with the present number of power output or chair counts. This is just an example to put the concept across and shouldn't be related to anything in real life. The trick here is to understand the basics of the KPI and leverage it to come up with a business solution.

Let's start from the basics...when one creates a KPI he typically uses two metrics, the actual and the target. After he mentions the two metrics, he has to do the data mapping for each. Step one would be to create KPIs for both the measures and then step two would be to use them in a scorecard along with other dimensions; mostly a time dimension.

Creating the KPI...

The process for creating the two KPIs would be the same except a slight change as mentioned below. Individual steps follow...

1. Create a blank KPI.

2. Use the published cube as a data source via the data source mapping for the actual.

3. Select the default measure, "value".

4. In the "Select a dimension" section select the "measure" dimension.

5. Change the member selection from "default" to either ChairCount Or PowerCount. This along with step# 4 does the trick and gets the slice out of the cube that we are interested in.

6. Repeat the same steps for target. One could possibly use a different data source & dimension combination for this.

7. Repeat the said sequence for the other KPI as well; the difference would be in the step# 5. One would have to choose the other member in the dimension than that he choose earlier.

Creating the ScoreCard...

Once the KPIs have been created, one could use them in a scorecard and also add another dimension(s) in the columns, typically time or something else as per requirement to generate an appropriate context in the scorecard.

In the following figure, I have used a day-wise hierarchy to display the two KPIs for a couple of days. The target value that I have used is 10 with the scoring pattern being, closer to target is better; also, all of the days don't have data to show. One could alter the values displayed in the blank cells by changing couple of properties.

Once this is in place the other dimensions can be added as per requirement.

Stay tuned some more stuff to follow...:-)

Multiple Measures in Performance Point

Here is an interesting stuff I came across recently while working on the PP-Planning module. The deal was to create more than one measures in a model so that one could go beyond the basic "value" in the default measure group. Following are the various options that I came to know from the books\articles, the community experts and little bit of experimenting. My sincere thanks to all of them for sharing the knowledge. The intention here is that if someone comes across similar issues, he can find all the information in a single place and I think this is the best way of passing it on :-)

To start with in the current version there is only a single "value" field for measure created by default and I don't know of any way to add a new measure or even change the name from value to something else. There are couple of workaround for it though. Out of the three option that I am aware of and that are listed below, two of them are supported and are "gentlemanly" workarounds and third one is something that is not supported and maybe removed from the future versions, and there is a good reason for it (in a minute).

Option One: Structure does the trick

Structure here means the dimension's records, properties and the way they are used in the model. The idea is to create a dimension that would hold the "measures" and then use it in the input form (in excel) in the "columns" along with the other dimensions.

Say we have a dimension RoomMeasureDim which has two members\rows, ChairCount & PowerCount & another dimension Customer which holds some names, say ESP & Visa (for example sake). Using these two dimensions we create a generic model RoomModel. This model would have 4 dimensions; the above two and the default time & scenario dimensions. If one would create an input form using the RoomModel and "hide" the Value & the Scenario row (using excel), it would look something like the snapshot below (the gray band is due to the report formatting)...

So in short we are using the structure to give an impression of two measures to the user. If we talk in DB terms, there would be two rows created for each value per row in the RoomMeasureDim.

Option Two: It's the Assumption

The trick is to create an assumption model and have the measure value in it. This assumption model when used along with the main model would bring along the measures present in the assumption model and expose them in the excel form in the value section.

Option Three: ExportXML\ImportXML (not supported)

This is an PPSCmd option that has not been published. The pseudo flow of this (command-line based) approach is as follows...

1. Run the ExportXML command and export the content into an XML file on the drive.

2. Edit the XML file and add the child-nodes (new measures) into the MeasureGroups node.

3. Delete the original model

4. Import the above updated XML file into PP using the ImportXML option.

Imp Note: The ImportXML is know to create new Ids internally for the objects when it is executed and this might create unknown problems.

 

Still exploring the interesting world of PP...will keep posted :-)

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