SQL Server 2005

Jazz Up Your Data Using Custom Report Items In SQL Server Reporting Services

Teo Lachev

Code download available at:CRI2006_10.exe(235 KB)

Contents

Introducing the ProgressTracker CRI
The ProgressTracker CRI in Action
Implementing the CRI Design-Time Component
Initialization
Rendering
Verbs
Custom Property Editor
Implementing the CRI Run-Time Component
Deploying CRI
Debugging CRI
Wrapping It Up

When it comes to presenting information these days, a picture may be worth a lot more than a thousand words. And even though SQL Server™ Reporting Services does provide charting capabilities, some are not up to the task. For example, you may need to render the value or the trend of a key performance indicator (KPI) as an image or use a chart type that is not supported by the native Reporting Services chart region. But SQL Server 2005 Reporting Services has a number of new extensibility features. One of the most exciting is custom report items, which allow you to transcend the limitations of traditional text-based reporting.

For years, developers using the Microsoft®.NET Framework and COM have been relying on home-grown or third-party user controls to spice up their presentation layers. With the advent of custom report items in SQL Server 2005 Reporting Services, you can implement your own report "controls" to do the same. Then, your custom report item (CRI) can be used just like a native report item. You can, for example, bind it to a report dataset to display a field value graphically. As you can imagine, custom report items redefine the concept of a standard report and open a new world of possibilities. I'm sure it won't be long before report vendors develop cool report widgets to meet common data visualization needs. As you'll see, implementing a custom report item on your own is not that difficult.

A CRI is a report item written to extend the capabilities of the native report items provided by SQL Server 2005 Reporting Services. Anything that can be represented as an image (already existing or programmatically generated) can be converted to a CRI.CRI allows you to implement both regular report items and data regions. This article walks you through the process of building a regular custom report item you can bind to a dataset field inside a table, a matrix, or a list data region. Should your report requirements call for a full-blown custom data region instead, this won't be difficult to do once you grasp the concepts presented here.

Introducing the ProgressTracker CRI

Most requirements will call for generating the CRI image programmatically. When I was envisioning the ProgressTracker CRI sample presented here, I wanted to find what it would take to convert an existing .NET Windows® Forms control to a CRI. My hypothesis was that any custom .NET control that is drawn using GDI+ can be promoted to a CRI. It turned out that this was true, and it was simpler than I anticipated.

To test my hypothesis, I authored a simple .NET Windows Forms control called ProgressTracker. Like the ProgressBar Windows Forms standard control, ProgressTracker graphically displays a numeric value as a progress bar. As noted, such a CRI could help an end user visualize the details of a KPI.

I intentionally kept the control implementation simple. The drawing of both rectangles (outer bar and progress value) is encapsulated in a DrawBar method and takes only a few lines of GDI code. First, DrawBar paints the rectangle outline by calling the DrawRectangle GDI function. Next, the code fills the rectangle with the specified fill color:

public static void DrawBar( Graphics g, Rectangle front, Color fillColor, Color borderColor, bool outLine) { using(Pen pen = new Pen(borderColor, 1)) g.DrawRectangle(pen, front); using(SolidBrush brush = new SolidBrush(fillColor)) g.FillRectangle(brush, front); }

As Figure 1 shows, the control supports a subset of properties found under the ProgressTracker category. The user can set these properties in the Visual Studio® 2005 Properties window to configure the control at design time. Here's a tip: to minimize development and testing time, start by implementing your CRI as a standard Windows Forms control. This lets you test it and debug it inside a Windows Forms project. When the control is ready, convert it to a CRI.

Figure 1** ProgressTracker Design-Time Component **(Click the image for a larger view)

The ProgressTracker CRI in Action

At this point, you are probably eager to see the CRI version of the control in action. The KPIDemo report shown in Figure 2 uses the ProgressTracker CRI to graphically display the value of the Product Gross Profit Margin KPI which is defined in the Adventure Works cube (included in the SQL Server 2005 samples).

Figure 2** KPI Demo Report **(Click the image for a larger view)

For your convenience, the report shows both the actual KPI status value (Profit Margin Status column) and the graphical value (Status column). If you don't want to install the Adventure Works SSAS sample, you can use the ProgressTrackerDemo report for testing.

Now, let's see how to convert the ProgressTracker Windows Forms control to a CRI. As with most controls you've used or implemented, a CRI has a dual life. At design time, the user configures the CRI by setting its properties. Typically, the CRI would also draw a design-time image, so the developer can see the effect of setting up these properties. At run time, the CRI is responsible for rendering an image that will appear on the report. The design-time aspect of a component is, by far, more difficult to implement.

Implementing the CRI Design-Time Component

The CRI design-time component must inherit from the Reporting Services CustomReportItemDesigner class. This class participates in the underlying Windows Forms host designer infrastructure. (See the "Resources" sidebar if you want to learn more about implementing .NET custom designers.) The more sophisticated the CRI is, the more complex its design-time component is likely to be. Deciding which design services you need to implement is a trade-off between usability requirements and implementation time. Simple CRIs are likely to implement only control properties and design-time rendering. More complex implementations may include adornment services, verbs, and a custom property editor.

A well-designed CRI should allow developers to configure it by using the Visual Studio Properties window. If you have used the Windows Forms PropertyGrid control in your apps, you know how to implement CRI properties. If you are new to this control, you may find the article "Getting the Most Out of the .NET Framework PropertyGrid" useful (see the "Resources" sidebar).

Any CRI public property will be automatically exposed in the Visual Studio Properties window. Additional attributes can be used to change the UI property behavior. For example, Figure 3 shows what it takes to implement the Maximum property that specifies the Progress Tracker maximum value.

Figure 3 Implementing the Maximum Property

[Category("ProgressTracker"), DefaultValue(1f),Description("The maximum value.")] public float Maximum { get { string max = this.GetCustomProperty(Shared.PROP_MAXIMUM); return string.IsNullOrEmpty(max) ? 1f : float.Parse(max); } set { _progress.Maximum = value; SetCustomProperty(Shared.PROP_MAXIMUM, value.ToString()); Invalidate(); } }

The Category attribute assigns the property to the ProgressTracker group in the Visual Studio Properties window. The DefaultValue attribute assigns a default value to the property. The Description property provides description text to be displayed at the bottom of the Visual Studio Properties window. In the case of the ProgressTracker CRI, the Value property is the most difficult to implement. That's because it allows the user to type in either a constant value or the field name in case the CRI is bound to a dataset field.

All ProgressTracker custom properties persist their values in the Report Definition Language (RDL) by calling the SetCustomProperty helper method. Figure 4 shows what the serialized instance of ProgressTracker may look like. To read the property value when the control is instantiated, the property calls GetCustomProperty. When the value of a custom property is changed, the code calls the Invalidate method of the CustomReportItemDesigner class to force the control to repaint itself.

Figure 4 Serialized Instance of Progress Tracker

<CustomReportItem Name="ProgressTracker1"> <Type>ProgressTracker</Type> <Style> <TextAlign>Right</TextAlign> <FontSize>7pt</FontSize> <Color>Gold</Color> </Style> <CustomProperties> <CustomProperty> <Name>Value</Name> <Value>0.25</Value> </CustomProperty> </CustomProperties> </CustomReportItem>

The CustomReportItemDesigner class exposes standard properties, such as Font, Size, and so forth. For example, you can get the selected Font from the CustomReportItemDesigner.Style.Font property. As its stands, the CRI designer infrastructure provides no way of hiding the standard properties in case you don't need them. For this reason, before introducing a new custom property, check if you can reuse an existing standard property instead. For example, I decided to use the standard Color property for the progress color and the BorderColor property for the outline color.

Initialization

When the control is added to the Layout tab of the Report Designer, the CustomReportItemDesigner class fires the InitializeNewComponent event. ProgressTracker uses this event to initialize the design-time component with default values by calling the SetDefaults helper method. A common initialization task is setting the default component size. ProgressTracker overrides the CustomReportItemDesigner DefaultSize property to set the default size of the design-time component to a width of 150 pixels and a height of 30 pixels:

public override DesignSize DefaultSize { get { return new DesignSize( new RSDrawing.Unit(150), new RSDrawing.Unit(30)); } }

Rendering

To help the user visualize the effect of setting the CRI properties, ProgressTracker draws an image at design time (see Figure 5). As with the standard RS data regions, more sophisticated CRIs have different design-time and run-time presentations. In our case, ProgressTracker uses the same code to draw its image in both modes. The rendering code remains practically unchanged from the original Windows Forms control.

Like Windows Forms control programming, CustomReportItemDesigner invokes the virtual OnPaint method each time the CRI needs to be repainted, as in Figure 6.

Figure 6 onPaint Event

public override void OnPaint(PaintEventArgs e) { float value = 0f; _progress.Alpha = this.Alpha; _progress.ProgressColor = this.Style.Color.ColorRgb; _progress.OutlineColor = this.Style.BorderColor.Default.ColorRgb; _progress.Maximum = this.Maximum; _progress.Minimum = this.Minimum; _progress.Value = float.TryParse(this.Value, out value) ? value : (progress.Maximum + _progress.Minimum) / 2; _progress.ShowValue = this.ShowValue; _progress.Font = Shared.GetDrawingFontFromReportFont(this.Style.Font); _progress.Height = this.Height.Pixels; _progress.Width = this.Width.Pixels; _progress.DrawControl(e.Graphics); }

Figure 5** ProgressTracker Rendsers Itself at Both Design Time and Run Time **(Click the image for a larger view)

The OnPaint event configures the internal ProgressTracker instance and calls its DrawControl method to let the CRI draws its image onto the provided Graphics canvas.

The Reporting Services design-time infrastructure provides additional services you can implement to facilitate the user interaction with your CRI at design time. Take, for example, the Reporting Services chart data region, which sponsors a full-blown design-time component (see Figure 7). When you click on the chart region at design time, the control draws an adornment area outside the main control window. This area has three frames that act as drop containers for dataset fields. The Reporting Services adornment infrastructure is essentially a pass-through implementation of the Windows Forms Adorner class.

Figure 7** Services for Configuring the CRI **(Click the image for a larger view)

As you can imagine, rendering the adornment window and responding to user events could get rather complex. The good news is that the adorner infrastructure is entirely optional. For the sake of simplicity, the ProgressTracker CRI doesn't implement an adornment window. If you decide to adorn, the Chris Hays polygon sample demonstrates how you can implement adornment features in a CRI (see the "Resources" sidebar).

For convenience, besides allowing the user to type in the field name in the Value property, ProgressTracker supports dragging a dataset field onto the image to bind the Value property to that field. Behind the scenes, ProgressTracker uses the CustomReportItemDesigner drag and drop support. The drag and drop implementation is essentially the same as in Windows Forms so I won't spend much time discussing it. The only detail worth mentioning takes place in the DragEnter event:

public override void OnDragEnter(DragEventArgs e) { IFieldsDataObject fieldsDataObject = e.Data.GetData(typeof(IReportItemDataObject)) as IFieldsDataObject; if (fieldsDataObject != null && fieldsDataObject.Fields != null && fieldsDataObject.Fields.Length > 0) BeginEdit(); }

In the absence of the adorner window, BeginEdit draws a selection border around the CRI. The net effect is the same as when you drag a dataset field over a table cell of the standard table region.

Verbs

Another nice feature you may consider implementing is verbs. Designer verbs let you implement custom actions that can be launched by right-clicking on the control design surface and choosing the action from the context menu. For example, suppose you need to allow end users to reset the CRI properties to their default values.

To implement this requirement, override the CustomReportItemDesigner Verbs property. Next, add a new verb to DesignerVerbCollection and specify the desired caption and the callback event handler (see Figure 8). When the user selects the action from the context menu, CustomReportItemDesigner will invoke the callback event handler where you can execute whatever code is needed. In this case, the SetDefaults helper method (not shown) resets the CRI properties.

Figure 8 Implementing Verbs

public override DesignerVerbCollection Verbs { get { if (_verbs == null) { _verbs = new DesignerVerbCollection(); _verbs.Add(new DesignerVerb("Reset Defaults", new EventHandler(OnCustomAction))); } return _verbs; } } private void OnCustomAction(object sender, EventArgs e) { switch (((System.ComponentModel.Design.DesignerVerb)sender).Text){ case "Reset Defaults": SetDefaults(); Invalidate(); break; default: MessageBox.Show("Not supported"); break; } }

At the end, the SetDefaults method calls IComponentChangeService.OnComponentChanged to inform CustomReportItemDesigner that there are unsaved changes. This also causes a refresh of the Visual Studio Properties window to synchronize the CRI properties with their new values. You need to call OnComponentChanged when you set a property value programmatically. As noted before, when the user drags and drops a dataset field, the Value property calls OnComponentChanged to notify the host.

Custom Property Editor

Finally, more complex CRIs may need a custom property editor. There is a special verb (Properties) whose task is to launch the editor. For example, the standard chart data region comes with a custom property editor that gives the user more control over the region properties (see Figure 7). The ProgressTracker CRI includes just the stub code needed to implement this feature (see Figure 8).

Implementing a custom property editor is easy. First, you need to decorate the ProgressTrackerDesigner class with the Editor attribute that references a class that inherits from ComponentEditor. Next, you implement a Windows Forms dialog to collect the properties from the user. Finally, you instantiate the dialog inside the editor class, collect the new property values, and apply them to the CRI. Figure 9 shows how that looks.

Figure 9 Implementing a Custom Property Editor

[LocalizedName("ProgressTracker")] [ToolboxBitmap(typeof(ProgressTrackerDesigner), "ProgressTracker.ico")] [CustomReportItem("ProgressTracker")] [Editor(typeof(CustomEditor), typeof(ComponentEditor))] class ProgressTrackerDesigner : CustomReportItemDesigner { ... } internal sealed class CustomEditor : ComponentEditor { public override bool EditComponent(ITypeDescriptorContext context, object component) { MessageBox.Show("Implement CRI Properties Window here!"); return true; } }

Implementing the CRI Run-Time Component

Implementing the CRI run-time component is much simpler than implementing its design-time counterpart. That's because you only need to worry about drawing the run-time image of the CRI—the main purpose of having a CRI after all. The CRI run-time component must implement the ICustomReportItem interface, as you see in Figure 10.

Figure 10 Initializing the Component

public class ProgressTrackerRenderer : ICustomReportItem { public CustomReportItem CustomItem { set { _cri = value; } } public ChangeType Process() { // initialize CRI from properies in RDL Initialize(); // create a memory stream that stores the image using(MemoryStream stream = new MemoryStream()) { Bitmap bmp = new Bitmap(progress.Width, progress.Height, PixelFormat.Format32bppArgb); using(Graphics graphics = Graphics.FromImage(bmp)) { Color backgroundColor = ((ReportColor) _cri.Style["BackgroundColor"]).ToColor(); if (backgroundColor == Color.Transparent) backgroundColor = Color.White; graphics.Clear(backgroundColor); _progress.DrawControl(graphics); } bmp.Save(stream, ImageFormat.Bmp); // Rewind image stream stream.Position = 0; // Create a new RS image object _image = new Image(_cri.Name, _cri.ID); _image.MIMEType = "image/bmp"; // serialize the image stream into the RS image _image.ImageData = stream.ToArray(); } _image.Sizing = Image.Sizings.AutoSize; return ChangeType.None; } public ReportItem RenderItem { get { if (_image == null) Process(); return _image; } } }

When the report processor executes the report and discovers the presence of a custom report item, it instantiates the run-time component (ProgressTrackerRenderer in this case) each time the CRI needs to be rendered on the report. For each instance, the report processor passes a CustomReportItem object to the ICustomReportItem.CustomItem property. The CustomReportItem object contains a set of properties that were set at design time. It includes both the CRI custom properties and the standard properties. The properties are scoped at the custom report item level. Currently, it is not possible to reference other report items on the report or the report object itself.

It is important to note that if the property value is expression-based, it is evaluated and resolved by the report processor before the CustomReportItem object is passed to the run-time component. For example, if the ProgressTracker component is bound to a dataset field (its Value property is set to a Fields!<FieldName>.Value expression), the field value is retrieved by the report processor and made available under the CRI Value property. As you can imagine, this saves an enormous amount of plumbing work on your part.

At this point, the report processor executes the ICustomReportItem.Process method to ask the CRI to render the run-time image. The code starts by initializing the CRI from its properties. Next, the code creates a Graphics object from a bitmap image. Then it calls the same DrawItem method that we used to render the design-time image. Once the image is drawn, it is saved into a memory stream. The report processor expects back an instance of a Microsoft.ReportingServices.ReportRendering.Image class (the only CRI type that is currently supported). To comply with this requirement, the code instantiates the Reporting Services image class and loads it from the memory stream. It sets the image MIME type to "image/bmp" (you can choose another standard image format if needed).

Once the Process method executes successfully, the report processor retrieves the image from the RenderItem property and outputs it on the report.

Deploying CRI

The ProgressTrackerCRI project (ProgressTracker assembly) hosts both the design-time component (ProgressTrackerDesigner.cs) and run-time component (ProgressTrackerRenderer.cs) of the ProgressTracker CRI. Follow these steps to deploy and configure the ProgressTracker CRI (note that these steps may vary slightly based on which components of SQL Server are installed).

First, to use the CRI in the Report Designer, deploy the ProgressTracker assembly to \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies. To use the CRI in a managed report (running under the Report Server), deploy the assembly to \Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin. For your convenience, I've created a post-build script (see the Build Events tab in the project properties) that copies the assembly after the ProgressTrackerCRI is built successfully.

Next, to let the Report Designer know about your CRI, register the ProgressTracker CRI in the \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\RSReportDesigner.config file, as follows:

<ReportItems> <ReportItem Name="ProgressTracker" Type= "MsdnMag.RS.Extensibility.ProgressTrackerCRI. ProgressTrackerRenderer, MsdnMag.ProgressTracker" /> </ReportItems> <ReportItemDesigner> <ReportItem Name="ProgressTracker" Type= "MsdnMag.RS.Extensibility.ProgressTrackerCRI. ProgressTrackerDesigner, MsdnMag.ProgressTracker" /> </ReportItemDesigner>

Each CRI requires a separate ReportItemDesigner element in the RSReportDesigner.config file. For your convenience, I enclosed my versions of the configuration files in the \Code\Config folder. Use them for reference only. Do not overwrite your config files with mine, as there's machine-specific information encoded in the file.

Since at run time the report processor interacts with the run-time component only, it is sufficient to add only the ReportItem element to \Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rsreportserver.config.

Next, elevate the CAS permissions of the CRI assembly for run-time execution by adding the following CodeGroup to the \Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rssrvpolicy.config file, like so:

<CodeGroup class="UnionCodeGroup" version="1" Name="CRICodeGroup" Description="Code group for the CylinderBar CRI" PermissionSetName="FullTrust"> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\ Reporting Services\ReportServer\bin\ MsdnMag.ProgressTracker.dll" /> </CodeGroup>

Finally, to add the ProgressTracker CRI to the Visual Studio toolbox, right-click in the Toolbox and select "Choose Items...". On the .NET Components tab, select "Browse..." and navigate to \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\MsdnMag.ProgressTracker.dll.

If the CRI doesn't show up in the Preview tab of the Report Designer and there are no errors, it is probably not registered properly. Double-check the first two steps. Remember that if the CRI has dependent assemblies, they also have to be deployed to the Report Server bin folder. To troubleshoot errors related to missing dependent assemblies, use a trace listener and watch for Reporting Services exceptions.

Debugging CRI

When the report is loaded in the Report Designer, it locks the CRI assembly in the process space of the Visual Studio IDE that hosts the report project. Unfortunately, the only way to redeploy a new version of the CRI is to shut down that Visual Studio instance. As you can imagine, this can be quite irritating. For this reason, I recommend you employ the following debugging practice. First, you should exclude the report project from your Visual Studio CRI solution. Second, right-click on the ProgressTrackerCRI project in the Visual Studio Solution Explorer and select "Set as Startup Project". Third, you should open the ProgressTrackerCRI project properties, switch to the Debug tab, and set it as shown in Figure 11. Finally, you can set breakpoints in the CRI design-time and/or run-time components.

Figure 11** Set Project Settings to Load the Hosting Report Project **(Click the image for a larger view)

As a result, when you hit F5 to debug the ProgressTracker CRI, Visual Studio IDE will load the report project first. When you switch to the Report Designer Layout tab, you should be able to debug your design-time component. Similarly, when you switch to the Preview tab, the breakpoints in the run-time component will be hit. More importantly, when you stop your debug session, Visual Studio will shut down the IDE instance that hosts the report project. This allows you to recompile and redeploy the CRI assembly without locking issues.

Resources

See the following resources for tips on developing custom report items with SQL Server 2005 Reporting Services:

• Getting the Most Out of the .NET Framework PropertyGrid Control
• Chris Hays’s Polygon Sample
• Tailor Your Application by Building a Custom Forms Designer with .NET
• Create and Host Custom Designers with the .NET Framework 2.0

Wrapping It Up

You should consider implementing a CRI when your reporting requirements go beyond what the standard Reporting Services items and data regions can do. Custom report items help you convey information to your report users in the form of graphic elements and images. Anything that can be rendered as an image can be implemented as a CRI and rendered on reports.

Teo Lachev works as a technical architect for a leading financial institution where he designs and implements .NET-centric Business Intelligence solutions. He is an MVP for SQL Server. Teo is the author of the books Applied Microsoft Analysis Services 2005 and Microsoft Reporting Services in Action.