Wednesday, 17 November 2010

First Look at SQL Server Denali (Part 1 – Debugging)

This is a first look at some of the new features in SQL Server “Denali” (which will most likely become SQL Server 2011).

SQL Server “Denali” CTP1 is available for download from the Microsoft Download Center here.  The press release is here.

image

Today, I’ll be focussing on the improved debugging functionality in SQL Server Management Studio. Following is a straightforward query to loop through the SalesOrderDetail table 10 times and add one to the UnitPrice each time (I’m not too concerned in this example with why you would want to do that!). I have filtered the query to rows which have a SalesOrderDetailID of less than 10.

image

We can now add a breakpoint on the UPDATE statement.

image

So far, nothing has changed, but if you right-click the breakpoint you can add limit the breakpoint by Location, Condition, and Hit Count, specify a filter for certain processes and specify what to do when a breakpoint is hit.

image

In this example, I have added a Condition of 2’">2’">2’">‘@counter>2’ and a HitCount of 2. Therefore the break should occur the second time the @counter value is over 2. If we run the code in debug mode we can see (after moving the panes around to get it in one screen capture) that value of @counter is 4 which is indeed the second time it has hit a value over 2.

image

This is a very straightforward example, but you can see that this makes the ability to use breakpoints much more powerful and therefore much more useful.

Tuesday, 16 November 2010

The Async Modifier and the Await Operator - New Features in the Next Edition of C#

At the recent Professional Developers' Conference, Anders Hejlsberg gave a presentation that demonstrated some of the new language features that Microsoft are working on as part of the next version of Visual C# and Visual Basic. The thrust of the next build of these languages is oriented towards making it easier to write asynchronous code. These new features add native functionality to Visual C# and Visual Basic that are built on top of the TPL, introduced in the .NET Framework 4.0. This article concentrates on the syntax extensions proposed for C#, although similar extensions will be added to Visual Basic.

The purpose of the TPL is to make it easier to build applications that can take advantage of the now-commonplace crop of multicore processors to implement concurrency and multithreading. To recap, using the TPL you can implement a concurrent operation by defining a Task object that references the code to be run concurrently. When you execute the task by using the Start method, the .NET Framework uses its own scheduling algorithm to allocate the Task to a thread and set this thread running at a time convenient to the operating system, when sufficient resources are available. This level of abstraction frees your code from the requirement to understand and manage the workload of your computer (for more information about Tasks, see the post Make Your Applications More Efficient with MultiTasking.)

If you need to perform another operation when a specific task completes, you have a couple of choices:

  1. You can manually wait for the task to complete by using one of the Wait methods exposed by the Task type. You can then initiate the new operation, possibly by defining another task.

  2. You can define a continuation. A continuation simply specifies an operation to be performed when a given task completes. The .NET Framework automatically executes the continuation operation as a task that it schedules when the original task finishes. For information about continuations, see the MSDN article TPL and Traditional .NET Asynchronous Programming.

However, although the TPL provides the Task type as an abstraction for a concurrent operation, it is still often necessary to write potentially awkward code to solve some of the common problems that developers frequently encounter when building applications that contain sections that may run concurrently. Additionally, bear in mind that the purpose of the TPL is to implement concurrency (typically by running multiple threads) whereas asynchronicity is a subtley different problem that may require coordinating operations that run on a single thread. This is where the async modifier and the await operator come into play. As an example, consider the following simple WPF window (the XAML definiton is also shown).



<Window x:Class="TestApp.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="Asynchronous Demonstration" Height="204" Width="525" ResizeMode="NoResize">
<Grid>
<Button Content="Perform Work Synchronously" Height="23" HorizontalAlignment="Left" Margin="15,32,0,0" Name="synchronous" VerticalAlignment="Top" Width="187" Click="synchronous_Click" />
<Rectangle Height="142" HorizontalAlignment="Left" Margin="214,12,0,0" Name="rectangle1" Stroke="Black" VerticalAlignment="Top" Width="280" />
<Label Content="Status:" Height="28" HorizontalAlignment="Left" Margin="223,27,0,0" Name="label1" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="286,27,0,0" Name="status" VerticalAlignment="Top" Width="195" IsEnabled="False" />
<Label Content="Duration:" Height="28" HorizontalAlignment="Left" Margin="223,61,0,0" Name="label2" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" IsEnabled="False" Margin="286,61,0,0" Name="duration" VerticalAlignment="Top" Width="195" />
<Button Content="Perform Work using Async Task" Height="23" HorizontalAlignment="Left" Margin="15,74,0,0" Name="taskBasedAsync" VerticalAlignment="Top" Width="187" Click="taskBasedAsync_Click" />
<Button Content="Perform Work using C# Async" Height="23" HorizontalAlignment="Left" Margin="15,114,0,0" Name="nativeAsync" VerticalAlignment="Top" Width="187" Click="nativeAsync_Click" />
</Grid>
</Window>

This window defines three buttons that each perform the same long-running operation, but by using a different mechanism. The first button performs the work synchronously, in a naive manner, as shown in the following code:

public partial class MainWindow : Window
{
...
private void synchronous_Click(object sender, RoutedEventArgs e)
{
duration.Clear();
status.Text = "Running - Synchronous";
Stopwatch watch = Stopwatch.StartNew();
DoWork();
duration.Text = string.Format("{0} ms", watch.ElapsedMilliseconds);
status.Text = "Completed - Synchronous";
}
...
private void DoWork()
{
Thread.SpinWait(Int32.MaxValue / 2);
}
...
}

This code displays the text "Running – Asynchronous" in the status text box in the right-hand portion of the window before starting a System.Diagnostics.Stopwatch object (the code uses this object to time the duration of the operation). The code then calls the DoWork method, which simply performs a busy wait for a few seconds to simulate a long-running operation, before displaying the time taken and updating the text in the status text box to "Completed – Synchronous". When you run this code, if you click the "Perform Work Synchronously" button, you should observe the following phenomena:

  • The Window becomes unresponsive while the DoWork method runs; you cannot even move the window.

  • The status text box never actually displays the message "Running – Synchronous". The contents of this text box are only updated when the synchronous_Click method completes, after the contents of the status text box has been overwritten with the message "Completed - Synchronous".

Both of these issues are related to the same problem; the thread running the event loop that waits for Windows messages and handles them is kept busy by the DoWork method, and cannot process any events (such as requests to move the Window) until the synchronous_Click method finishes. Similarly, Windows cannot update the display until the thread running the event loop has finished the synchronous_Click method, so the status text box never displays the text "Running – Synchronous", only the message "Completed - Synchronous".

The usual strategy to avoid these problems and ensure that the user interface remains responsive while the application performs a long-running operation is to run this operation on a separate thread, thus freeing the thread running the Windows event loop to process other messages. You can achieve this with the TPL by using code such as the taskBasedAsync_Click method in the following example. This is the code for the "Perform Work using Async Task" button shown in the earlier image:

public partial class MainWindow : Window
{
...
private void taskBasedAsync_Click(object sender, RoutedEventArgs e)
{
duration.Clear();
status.Text = "Running - Async, Task-Based";
Task t = new Task(() => DoWork());
Stopwatch watch = Stopwatch.StartNew();
t.ContinueWith((task) => UpdateStatus(watch, "Completed - Async, Task-Based"));
t.Start();
}
...
private void DoWork()
{
Thread.SpinWait(Int32.MaxValue / 2);
}

private void UpdateStatus(Stopwatch watch, string message)
{
this.Dispatcher.Invoke(new Action(() =>
{
duration.Text = string.Format("{0} ms", watch.ElapsedMilliseconds);
status.Text = message;
}), DispatcherPriority.ApplicationIdle);
}
...
}

When you run the application and click the "Perform Work using Async Task" button, the user interface remains responsive, even while the DoWork method runs. This is because the DoWork method is performed by a task on a separate thread. Additionally, the status text box successfully displays the message "Running - Async, Task-Based" while the DoWork method runs. The tricky part is arranging for the display to be updated with the time taken to complete the DoWork method and the message " Completed - Async, Task-Based". This is achieved with a continuation that runs when the task performing the DoWork method completes. However, because this task runs on a different thread from that responsible for managing the user interface, it cannot directly modify the properties of the duration and status text boxes, but has to queue updates to the WPF Dispatcher object for the Window instead. When the thread handling the Windows event loop is free, it runs the code specified by the Dispatcher.Invoke method.

So, although this code works, it is messy; if you want to update the user interface, you have to define a continuation that invokes the Dispatch.Invoke method because the continuation does not run on the thread that owns the user interface elements. This is where the async modifier and await operator can prove very useful.

The async modifier to a method indicates that the method contains functionality that can be run asynchronously. The await operator specifies the point at which asynchronous operations can begin inside an async method. As a final example, consider the nativeAsync_Click method shown below. This method runs when the user clicks the "Perform Work using C# Async" button.

public partial class MainWindow : Window
{
...
private async void nativeAsync_Click(object sender, RoutedEventArgs e)
{
duration.Clear();
status.Text = "Running - Async, Native C#";
Task t = new Task(() => DoWork());
Stopwatch watch = Stopwatch.StartNew();
t.Start();
await t;
duration.Text = string.Format("{0} ms", watch.ElapsedMilliseconds);
status.Text = "Completed - Async, Native C#";
}
...
private void DoWork()
{
Thread.SpinWait(Int32.MaxValue / 2);
}
...
}

This code looks like a hybrid combination of the first two examples. It displays the message "Running – Async, Native C#" in the status text box before creating and running a task to perform the DoWork method on a separate thread. However, the await operator is where the clever stuff kicks in. Notice that the nativeAsync_Click method is defined with the async modifier. At run-time, the await operator causes the async method to return immediately from whence it was called without waiting for any subsequent code to run. When the subject of the await operator (the task running the DoWork method) completes, the nativeAsync_Click method resumes running at this point and the statements following the await operator are performed, updating the display with the time taken to perform the operation and the message " Completed - Async, Native C#". In fact, this magic is nothing more than an exercise in the reworking of your code by the C# compiler. When the C# compiler encounters the await operator in an async method, it effectively reformats the code that follows this operator as a continuation that runs on the same thread as the async method. And because the thread that was running the async method was the thread running the Windows event loop, it has direct access to the controls in the Window and can update them directly without routing them through the WPF Dispatcher object for the Window.

Although this approach looks quite simple at first glance, it is important to bear in mind a few points to avoid some possible misconceptions:

  • The async modifier does not signify that a method runs asynchronously on a separate thread. All it does is specify that the code in the method can be divided into one or more continuations. When these continuations run, they execute on the same thread as the original method call.

  • The await operator specifies the point at which the C# compiler can split the code into a continuation. The await operator itself expects its operand to be an awaitable object. An awaitable object is a type that provides the GetAwaiter method which returns an object that in turn provides the BeginAwait and EndAwait methods. The C# compiler converts your code into statements that uses these methods to create an appropriate continuation. The Visual Studio Async CTP provides extension methods for the Task class, and the code shown in the example above invokes the await operator on a Task object.

To conclude, the async modifier and await operator are powerful constructs that enable you to simplify asynchronous code, and as such they are natural partners with the TPL. Using the async and await keywords, you can concentrate on the logic of your application and let the compiler worry about how to divide your code into one or more continuations.

Thursday, 11 November 2010

What are Microsoft’s real virtualization technologies?

OK, so something is either real or virtual, right? But behind the contradiction implied by this title, is a serious question. What exactly is virtualization?

Not only are virtualization technologies becoming more and more prevalent in all kinds of organizations, from small businesses through to global enterprises, but the term "virtualization" is also being used more and more, and sometimes to describe things that may not be virtualization as this term has commonly been understood. For example Microsoft, and other vendors, has applied the "virtualization" brush to a range of technologies, not all of which are really virtual! Or have they …

One definition of virtualization is: The capability to run an application or a computer in a virtual environment without affecting the components that already exist on that particular desktop or server.

This definition mostly works for desktop virtualization technologies, such as Virtual PC 2007 and Windows Virtual PC on Windows 7, VMware Workstation, and server virtualization technologies such as Microsoft Hyper-V and VMware Server, that enable several operating systems to co-exist on the same physical hardware, and in protected "bubbles" so that one OS does not affect any others.

However, as we shall see when we go through some of Microsoft's current "virtualization" technologies, the above definition doesn't always fully explain things.

Server virtualization is probably the least ambiguous technology area for Microsoft. Server virtualization enables multiple servers, such as domain controllers, DNS servers, and Web servers, to run as discrete virtual machines on the same physical computer host. Server virtualization enables the physical consolidation of multiple traditional servers to improve and maximize hardware utilization, and to consolidate workloads onto fewer enterprise class servers. Server virtualization is also about reducing costs, by using fewer physical computes, with reduced electrical costs for cooling and server power consumption.

But what about desktop virtualization? Desktop virtualization enables software applications, operating system, and hardware configuration to be packaged to run in a self-contained, virtual environment, enabling users to run multiple operating systems and applications on a single computer. With desktop virtualization technologies, such as Microsoft Virtual PC 2007 SP1 and Windows Virtual PC, including Windows 7's XP Mode, the virtual machine files are installed and executed on the local client desktop computer. In enterprise environments, desktop virtualization can be augmented with technologies such as MED-V that are used to deliver and manage client virtual machines over the network from central servers.

Isn't this clear and unambiguous? Yes, if you just include "traditional" definitions of desktop virtualization, but what about approaches such as using Microsoft's Remote Desktop Services (RDS) to deliver desktops and applications hosted on central servers, but presented on user's desktops as if they were running locally? This approach de-couples the user desktop from their physical workstation, but importantly there are not separate virtual machines on the server; rather every user is running a session on the server, and any applications they run must be compatible with the server OS. This approach is sometimes called presentation or session virtualization. And where shortcuts to remote applications are provided on user's desktops, this approach is sometimes also referred to as a type of application virtualization.

And then there is the whole area of Virtual Desktop Infrastructure (VDI) …. VDI is an alternative server-based desktop virtualization method that uses virtualization technologies enable users to run their own client desktops, but hosted on a central server. In Microsoft's VDI, multiple client operating systems run in virtual machines on a Hyper-V server; the server then remotely presents each desktop to the client computer, over RDS. By using virtual machines running on the server, every user can run their own desktop OS version (and not be limited to a server OS), and customize their own unique desktop settings.

So, there's more to virtualization than virtual machines; we've also got session-based virtualization and, perhaps most importantly, there's also the concept of de-coupling, where elements such as hardware, OS, applications, and user state and data, that in a traditional rich desktop environment would be all packaged together, are now potentially in their own separate spaces.

Does considering virtualization to be of different types help with some of the other "virtualization" technologies?

Let's consider user state virtualization. The term user state virtualization describes the ability to separate user data and settings from applications and operating systems, so that users can access their own personalized environment, no matter which device they are currently using. So, in this case, it is the de-coupling element of virtualization that is key – we are not talking about multiple concurrent user states here, and there are no virtual machines involved. Instead there's the ability to separate user state data from the applications and operating system so that users can move from environment to environment, or device to device, and automatically pick up their saved data, favorites, application settings and so on no matter where they are. In Windows environments, user state virtualization technologies include Folder Redirection, Offline Files, and Roaming User Profiles.

How about application virtualization? As a generic term application virtualization describes technologies that deliver applications across the network to end-users, without the need to install these applications on the user's desktop. There are two key methods for virtualizing applications; through specific application virtualization tools and through presentation virtualization.

Application virtualization tools typically package a software application so that it runs in a self-contained, virtual environment. This virtual environment contains all the information required to run the application on the client computer, without installing the software application locally. So, again the decoupling element of virtualization is important, as the application is not "installed" on the host operating system. In typical implementations, such as Microsoft's App-V, application code is downloaded on demand from a central server and executed on the local desktop computer. Although the application is running within a virtual OS environment, this is not a complete virtual machine; rather it provides virtualized interfaces to OS components such as the Windows Registry, and is a great approach for dealing with incompatibilities between applications; but is not useful for dealing with incompatibilities between an application and the host OS; this is because the application it does still make use of OS features, and must still meet the same platform and system requirements as a regular application; for example, if an application won't run under Windows 7 as a traditional installed application, it will still fail when run under App-V on a Windows 7 workstation.

Presentation virtualization typically describes technologies where the execution of application code takes place remotely from the end-user's interaction with the application interface. In Windows environments, presentation virtualization is used by applications delivered using RDS, such as traditional terminal services applications. Using technologies such as Microsoft's RemoteFX, such applications can appear to the end-user as if they are running locally, particularly if configured as a RemoteApp, where the individual application is launched from a regular application shortcut on the user desktop; in this way the user may be unaware that the application is actually a terminal into a remote session running on a server. So, again we've got de-coupling, and with sessions rather than virtual machines.

Looking at Microsoft as an example vendor, what we need to do is think of different types or classes of virtualization. The key feature in all types of virtualization is perhaps the de-coupling of elements such as hardware, OS, applications, and user state and data. Using this as a principle, we can see that the "traditional" approach, such as desktop and server virtualization, achieves this de-coupling by using distinct virtual machines. With user state virtualization, the de-coupling is achieved using remote central data storage; with application virtualization, the de-coupling can be from virtual application environments or through sessions in the case of remote applications and desktops. And implementations using VDI, can achieve de-coupling by using all these tools; virtual machines, centralized storage, and session-based technologies.

So, as long as we can think of the core virtualization concept as a separating or de-coupling approach, then there's no reason not to use the term for a wide range of approaches – and if this is case maybe all Microsoft's virtualizations are real ….


Thursday, 28 October 2010

Make Your Applications More Efficient with Multitasking

Multitasking is the ability to do more than one thing at the same time. As far as Windows applications are concerned, it is one of those concepts that is easy to describe but that, until recently, has been difficult to implement without getting embroiled in the threading mechanism provided by the .NET Framework and the operating system. A good implementation of multitasking achieves two objectives:

  1. It helps to ensure that an application remains responsive; a desktop application can wait and respond to user input on one thread while another thread performs any processing required.
  2. It assists in making the application scalable; if the application is deployed to a computer with a multicore processor, it can take advantage of the additional processing power to perform operations concurrently.

In the optimal scenario, an application running on a multicore processor performs as many concurrent tasks as there are processor cores available, keeping each of the cores busy. However, there are many issues you have to consider to implement concurrency, including the following:

  • How can you divide an application into a set of concurrent operations?
  • How can you arrange for a set of operations to execute concurrently, on multiple processors?
  • How can you ensure that you attempt to perform only as many concurrent operations as there are processors available?
  • If an operation is blocked (such as while it is waiting for I/O to complete), how can you detect this and arrange for the processor to run a different operation rather than sit idle?
  • How can you determine when one or more concurrent operations have completed?
  • How can you synchronize access to shared data to ensure that two or more concurrent operations do not inadvertently corrupt each other’s data?

To an application developer, the first question is a matter of application design. The remaining questions depend on the programmatic infrastructure—Microsoft provides the Task Parallel Library (TPL) to help address these issues.

Tasks, Threads, and the ThreadPool

The most important type in the TPL is the Task class in the System.Threading.Tasks namespace. The Task class is an abstraction of a concurrent operation. You create a Task object to run a block of code. You can instantiate multiple Task objects and start them running in parallel if sufficient processor cores are available.

Internally, the TPL implements tasks and schedules them for execution by using Thread objects and the ThreadPool class (in the System.Threading namespace). Multithreading and thread pools have been available with the .NET Framework since version 1.0, and you can use the Thread class directly in your code. However, the TPL provides an additional degree of abstraction that enables you to easily distinguish between the degree of parallelization in an application (the tasks) and the units of parallelization (the threads). On a single-processor computer, these items are usually the same. However, on a computer with multiple processors or with a multicore processor, they are different items. If you design a program based directly on threads, you will find that your application might not scale very well; the program will use the number of threads you explicitly create, and the operating system will schedule only that number of threads. This can lead to overloading and poor response time if the number of threads greatly exceeds the number of available processors, or inefficiency and poor throughput if the number of threads is less than the number of processors.

The TPL optimizes the number of threads required to implement a set of concurrent tasks and schedules them efficiently according to the number of available processors. The TPL uses a set of threads provided by the ThreadPool, and implements a queuing mechanism to distribute the workload across these threads. When a program creates a Task object, the task is added to a global queue. When a thread becomes available, the task is removed from the global queue and is executed by that thread. The ThreadPool implements a number of optimizations and uses a work-stealing algorithm to ensure that threads are scheduled efficiently.

Note: The ThreadPool was available in previous editions of the .NET Framework, but it has been enhanced significantly in the .NET Framework 4.0 to support Tasks.

You should be aware that the number of threads created by the .NET Framework to handle your tasks is not necessarily the same as the number of processors. Depending on the nature of the workload, one or more processors might be busy performing high-priority work for other applications and services. Consequently, the optimal number of threads for your application might be less than the number of processors in the machine. Alternatively, one or more threads in an application might be waiting for long-running memory access, I/O, or a network operation to complete, leaving the corresponding processors free. In this case, the optimal number of threads might be more than the number of available processors. The .NET Framework follows an iterative strategy, known as a hill-climbing algorithm, to dynamically determine the ideal number of threads for the current workload.

The important point is that all you have to do in your code is divide your application into tasks that can be run in parallel. The .NET Framework takes responsibility for creating the appropriate number of threads based on the processor architecture and workload of your computer, associating your tasks with these threads and arranging for them to be run efficiently. It does not matter if you divide your work up into too many tasks as the .NET Framework will only attempt to run as many concurrent threads as is practical; in fact, you are encouraged to "overpartition" your work as this will help to ensure that your application scales if you move it on to a computer that has more processors available.

Creating, Running, and Synchronizing Tasks

You can create Task objects by using the Task constructor. The Task constructor is overloaded, but all versions expect you to provide an Action delegate as a parameter. The task uses this delegate to run the method when it is scheduled. The following example creates a Task object that uses a delegate to run the method called doWork:

Task task = new Task(doWork);
...

private void doWork()
{
// The task runs this code when it is started
...
}

The default Action type references a method that takes no parameters. Other overloads of the Task constructor take an an Action<object> parameter representing a delegate that refers to a method that takes a single object parameter. These overloads enable you to pass data into the method run by the task. The following code shows an example:

Action<object> action;
action = doWorkWithObject;
object parameterData = ...;
Task task = new Task(action, parameterData);
...

private void doWorkWithObject(object o)
{
...
}

After you create a Task object, you can set it running by using the Start method, like this:

Task task = new Task(...);
task.Start();

The Start method is also overloaded, and you can optionally specify a TaskScheduler object to control the degree of concurrency and other scheduling options. It is recommended that you use the default TaskScheduler object built into the .NET Framework, although you can define your own custom TaskScheduler class if you really need to take more control over the way in which tasks are queued and scheduled.

You can use also a TaskFactory object to create and run a task in a single step. The constructor for the TaskFactory class enables you to specify a task scheduler and additional task creation options. The TaskFactory class provides the StartNew method to create and run a Task object. Like the Start method of the Task class, the StartNew method is overloaded, but all overloads expect a reference to a method that the task should run. Even if you do not currently specify any particular task creation options and you use the default task scheduler, you should still consider using a TaskFactory object; it ensures consistency, and you will have less code to modify to ensure that all tasks run in the same manner if you need to customize this process in the future. The Task class exposes the default TaskFactory used by the TPL through the static Factory property. You can use it like this:

Task task = Task.Factory.StartNew(doWork);

When the method run by a task completes, the task finishes, and the thread used to run the task can be recycled to execute another task.

A common requirement of applications that invoke operations in parallel is to synchronize tasks. The Task class provides the Wait method, which implements a simple task coordination method. It enables you to suspend execution of the current thread until the specified task completes, like this:

task.Wait(); // Wait at this point until task completes

You can wait for a set of tasks by using the static WaitAll, and WaitAny methods of the Task class. Both methods take a params array containing a set of Task objects. The WaitAll method waits until all specified tasks have completed, and WaitAny stops until at least one of the specified tasks has finished. You use them like this:

// Wait for both task1 and task2 to complete
Task.WaitAll(task, task2);

// Wait for either of task1 or task2 to complete
Task.WaitAny(task, task2);

In summary, the TPL makes it easy to build multitasking applications without having to be concerned about the details of multithreading. The TPL provides a large number of features beyond those described in this simple overview, such as the Parallel class that implements a concurrent version of some common programming constructs. The TPL also includes a number of collection classes in the System.Collections.Concurrent namespace that support synchronized concurrent access to data shared by multiple tasks.

Wednesday, 13 October 2010

Using a Transparent Background in Reporting Services

While watching the Japanese Formula 1 Grand Prix on Sunday, it struck me that TV sports broadcasters make a lot of use of transparent overlays when showing scores, results, times, statistics, or whatever. In the case of the Grand Prix, the driver rankings in the world championship were displayed on a semi-transparent overlay, behind which the live footage of the race circuit could be seen.

So, naturally I started to wonder how I could achieve a similar visual effect in a Reporting Services report, like this:

Product Sales Report

My first thought was to look at the BackgroundColor property of the Tablix data region and set the Transparency level. However, when I looked at the color picker control for the property, this is what I saw:

ColorProperties

Note that the Transparency control is disabled. It turns out you can only set a transparency level for gauges and charts in Reporting Services – not for shapes or data regions. So, I needed to find an alternative approach.

The answer I came up with was to create a semi-transparent .png graphic, and use it as the background image for the data region. I created this with PowerPoint, though of course you can use any graphics tool you like. I also used PowerPoint to find a suitable clipart image to use as the background for the report (on which the semi-transparent data region will be overlaid). In this case, I’m using the Adventure Works Cycles sample data, so a photo of a cyclist seems like a good choice.

PowerPoint

You can take one of two approaches when it comes to sizing the semi-transparent image – you can make an extremely small image and then set the BackgroundRepeat property of the data region to Repeat, or you can make it bigger than the data region is ever likely to be and set the BackgroundRepeat property to Clip (or Repeat – it won’t matter since the image will be bigger than the data region anyway!). I found that PowerPoint tends to add some whitespace to the edge of a shape when you save it as a .png image, which showed up when repeating the background image, so I went with a large background image. Of course, had I used a more comprehensive graphics tool,  could have easily avoided this issue and got away with repeating a smaller image.

To embed the images to the report, I added them to the Images folder in the Report Data pane in Report Designer.

ReportData

Then I set the BackgroundImage property of the tablix data region in which the report data is displayed, like so:

TablixProperties

I’ve also used the semi-transparent image as the background for the report title textbox, which appears above the tablix data region.

The next challenge was to apply the cyclist image to the background of the report, and ensure that the layout of the report overlays the data neatly. If you have a small data set with a known number of records(for example, in a “top 10 products” report), then this is relatively straightforward. However, for a dataset with an unknown size, the data region will be resized dynamically, and automatic pagination may break the report into multiple pages. In my case, I want to ensure that the report title appears on all pages, and that the table of data has a suitable space above and below it on all pages.

To accomplish this, I added a page header and footer to the report and put the report title in the header. This ensures that if the report is paginated, the table on the second (and all subsequent pages) doesn’t start right at the top of the page. Similarly, the report footer ensures that there’s always a space after the table – it never goes all the way to the bottom of the page. I set the BackgroundImage of the report to the cyclist picture (clipped so it doesn’t repeat), and I set the InteractiveSize property of the report so that when viewed in the browser, the report has a maximum size that will keep the tablix well within the background image area. This was made tricky by the fact that Report Designer does not show the background image of the report in design view, so I had to preview the report and assess the right size through trial and error.

Report Designer

Obviously, the report size is optimized for interactive viewing, and though you can set the PageSize property of the report to an appropriate size for any other renderers you plan to use, my experience is that using background images and contrived layouts in reports you intend to render to a different format can result in some pretty horrible looking exported reports. One solution I have used in the past for this is to create the version that’s tailored for online viewing, and include a link to an offline version that has more conventional formatting for printing or exporting.

You can download the sample report I created from here. You’ll also need SQL Server 2008 R2 with Reporting Services (you can get the free Express edition from here) and the AdventureWorksDW2008R2 sample database (which you can get from here).

del.icio.us Tags: ,