Matrix reports in Reporting Services 2005

Matrix reports are extremely useful when you have data organized in rows that you want to transpose to columns (similar to creating a Pivot Table in Excel). Here’s an example of how to use a Matrix report and how to hack your way through some additional features you’re likely to need.

The Matrix
Say you have a dataset of user transactions, with {Username, TransactionDate, Amount}. Instead of seeing this information as a long table, you want to see one row per user, with the total amount spent per day in columns. This would let you quickly compare who spent what by day.

Doing this as a Matrix report is easy. Select “Add New Report” from Visual Studio 2005, enter your query or sproc, and pick ‘Matrix’ report type. The next page is where all the magic happens; using our example, put {Username} into ‘Rows’, {TransactionDate} into ‘Columns’, and {Adjustment} into ‘Details’. Click ‘Finish’, and you’ll see that Visual Studio has created the table, assuming you wanted to SUM the Amount for each user. In this case that is exactly what we want, but if we needed a different aggregate operation we’d just edit the details Textbox and change ‘Sum()’ as desired.

The first thing you’ll notice when you preview the report is that this isn’t exactly what we want, since the TransactionDate column is using the full DateTime value, which creates a column for each distinct DateTime. Instead we just want a single column per unique day. So let’s add a calculated field by going to the ‘Data’ tab, clicking the ‘…’ button, and adding a new calculated field TransactionDateFormatted with value =Format(Fields!TransactionDate.Value, "g"). We then have to change the report to use this new field in two places:

1. In In the textbox displaying the date. Right-click on the column header textbox and change the Value from =Fields!TransactionDateFormatted.Value to =Fields!TransactionDateFormatted.Value.
   


2. In the grouping definition. Right-click on the table, select ‘Properties’, go to the ‘Groups’ tab, edit the column grouping definition that was automatically created, and change the expression to =Fields!TransactionDateFormatted.Value.
   

Viola! All is good.

Unplug from the Matrix
Well, almost. And here’s the part you’ve been waiting for, where we take off the gloves, agree that Reporting Services 2005 does do lots of things well, and that for what it is, we like it. Thing is, sometimes to get the job done you have to get a little dirty.

Additional Columns
Let’s say we have the same dataset, but this time we also have UserId, and we want to show both the Username and the UserId in each row. Sounds straightforward enough. But you’ll notice that this isn’t so simple, since you can’t add a ‘Column’, only a ‘ColumnGroup’, which would add a column for each unique UserId (the exact opposite of what we want). You have to tilt your head to the side a bit and think like the Matrix (report). What we want here is actually another ‘RowGroup’, since the UserId value will appear in a row, but we want to group by a constant value so as not to create a second group for each user. So right-click on the table, choose ‘Add Row Group’, and enter =1 as the grouping expression. Then on the report, change the newly created Textbox’s value from =1 to =Fields!UserId.Value. Taking a look in Preview mode, we see that we now have Username and UserId in each row, and we’re happy.

Column Headers
Kind of. What we really need now is a column header for each of these columns, since it’s a bit unclear what’s what. So we return the Layout tab and go to edit the value of the column header Textbox… but alas! There’s only one, merged Textbox for the two columns. You’ll see that if you add additional RowGroups using the same technique there is still just a single, merged Textbox for the entire span of the column header.

Time to bust out the heavy guns. Open the Toolbox and drag a Rectangle into that single, merged Textbox. You’ll see the dots in the background of the report now. Then drag two Textboxes onto the Rectangle, and position them so that one is above the first column (Username) and the other is above the second column (UserId). Adjust the widths of the Textboxes so that they exactly match the widths of the RowGroups, edit the Textboxes’ values to be “Username” and “UserId”, and go back to preview mode. Now we’re cooking.

Enter Chris Hays
Almost. Now what we say is “What’d be really great here is zebra-striping”, so that we can easily see each row. But you’ll notice that the traditional technique of zebra-striping a reporting services report (setting the background color to =iif(RunningValue(Fields!UserId.Value,CountDistinct, Nothing) Mod 2, "WhiteSmoke", "Ivory")) just doesn’t work. This time tilt your head to the other side, and keep tilting. For this hack we turn to Chris Hays and his Reporting Services Sleazy Hacks Weblog, which is far and away the best (only?) source of “How-Do-I”’s on Reporting Services 2005. The blog is from 2004-2006, but it’s relevance hasn’t changed. Read through everything he’s written.

Conveniently, Chris has a Green Bar Matrix posting that will step us through this (yes, it’s going to get real ugly). Following his instructions:

1. Add another RowGroup (at the innermost position, after the UserId RowGroup we added) with a constant (=1) grouping expression. Change the name of the newly created RowGroup’s TextBox to "ColorNameTextbox".
   


2. Put the background color calculation (=iif(RunningValue(Cstr(Fields!UserId.Value), CountDistinct, Nothing) Mod 2, "WhiteSmoke", "Ivory")) into the Value property of ColorNameTextbox.
   


3. Set the BackgroundColor property of this entire new RowGroup to =Value.
   


4. Set the BackgroundColor property of the matrix data cell Textbox (the one that does the Sum()) to =ReportItems!ColorNameTextbox.Value.
   


5. Set the BackgroundColor property of the other RowGroups (for UserId and Username) to the same grouping expression that we used for the Value of ColorNameTextbox (=iif(RunningValue(Cstr(Fields!UserId.Value), CountDistinct, Nothing) Mod 2, "WhiteSmoke", "Ivory")).
   


6. Hide the newly created RowGroup by setting the FontColor to =Value, the FontSize to 1pt, and the Size to Width of 0px. You can also hide the borders as Chris suggests if you mind them.
   

Go Wash Your Hands
Phew! We now have exactly what we wanted, and we only had to feel a little bit dirty about getting it. It’s not deal-with-the-devil kind of stuff, but still, it’s pretty nasty.

But that’s OK. We love Reporting Services. Come back to reality (if you remember what that is) and plug back into the Matrix. Life is good.

Microphone Access in Silverlight App: LIVE

Just a heads up that the technique described earlier (using Flash and Javascript for microphone support in a Silverlight app) is live now in the Tutor.com Classroom!

You'll need to create an account and connect to a tutor to see it in action (click the 'Connect to a Tutor' button in the classroom to get started)--

don't save foreign key relationships for "the end"

consider adding foreign keys part of creating a table; if you haven't set up foreign key relationships, you haven't finished creating your table.

indexes, on the other hand, can be saved for "the end", once you get a better sense of how you're querying the tables and what's most optimal. but even with indexes, if you have a good sense when you're creating the table of how you're most likely to access the data, you're better off taking an educated guess early than never doing it late--

using SQL Profiler to monitor database calls

just in case anyone’s not familiar with SQL Profiler: this is the app that gives you low-level database monitoring so you can trace all database calls. you can customize what you monitor to get a variety of information.

so for example, if you want to set up a trace to see all database calls from your web server, launch Profiler, click the New Trace… button, connect locally, and go to the second tab:

• check checkboxes in the DatabaseName column
• click the Column Filters… button and add filters for:
  o DatabaseName Like => your database
  o NTUserName Like => nt identity you run your webserver as
  o TextData Like => exec

use format codes in report files

the best way to format datetime/number values in an RDL file is with the textbox’s Format property. you use the same format codes that you use in .NET and elsewhere, and you don’t have to worry about null values (null’s appear as empty strings). for example, common date format codes are:


d – 2/26/2009
D – Thursday, February 26, 2009
g – 2/26/2009 11:52am

some number/currency codes:

g – 1234
N – 1,234.00
C – $1234.12

if you forget the codes, you can get the VS2005 to show you them by right-clicking on the textbox and choosing ‘Properties’, going to the ‘Format’ tab, and clicking the ‘…’ button next to ‘Format code’.

report server rdl files in visual studio 2008

hacked my way through this mostly by accident; if you rename a .rdl file to .rdlc, then you can open the file in visual studio 2008 and use the same GUI for report file edits that you have in 2005. you don’t get the data and preview tabs, so you need to make data source edits manually in notepad, but if you find yourself with only vs 2008 and need to make .rdl edits, this is a good way to get some GUI help--

Microphone access in Silverlight via Flash and JavaScript

Silverlight is unquestionably a powerful tool for .NET application developers; using almost of all the same WPF skills,.NET developers can create rich internet applications (RIA) right out of the gate. However, Silverlight has no microphone/webcam support, and if you need this functionality in a cross-browser RIA, the common thinking is cross out Silverlight and pencil in Flash or Java.

While it's true that you'll need to dip into Flash or Java for microphone/webcam support (at least until Silverlight 3, and possibly not even then), there's no reason you can't take advantage of Silverlight as your base application host and selectively use Flash as necessary for Silverlight's missing features. Then, using a JavaScript bridge, you can get these browser technologies communicating with each other fairly painlessly. This integrated solution is what we're considering at Tutor.com when we add voice communication support to the Tutor.com Classroom, and this post will detail how to use Flash for microphone support inside a Silverlight application, where the button controlling the microphone on/off switch is in the Silverlight application.

Flash: Microphone management
First, we'll create a simple Flash movie (I'm using ActionScript 2) that manages and outputs microphone input. Thankfully, there's really very little code you need to write to do this; just note that your Flash movie must be at least 250x150 to allow room for the microphone Allow/Decline dialog, and if you don't want the movie to be visible, you need to set its background color to transparent.

As an aside, note that there's similarly very little code you need to write to connect to a Flash Media Server (FMS) for real-time communication, although the example here omits the FMS part. As another aside, note that the code for Flash webcam support is almost identical, except instead of a Microphone object you're working with a Camera object.

OK, so in the first frame of your Flash movie, write a function to access the microphone and monitor microphone activity:

var mic:Microphone = null;

function toggleVoice(isOn:Boolean)
{
  //setup the mic
  if (mic == null)
  {  
    //call Microphone.get() to access the microphone and prompt user with Allow/Decline dialog
    mic = Microphone.get();

    //Microphone.get() will return null if user declined access
    if (mic == null)
      return;

    //setup onActivity handler to get notification of mic activity
    mic.onActivity = function(active:Boolean)
    {   
      //call out to JavaScript bridge via ExternalInterface
      flash.external.ExternalInterface.call("IsTalking", active);
    };

    //create movie clip and attach mic to clip so we can hear output
    this.createEmptyMovieClip("sound_mc", this.getNextHighestDepth());
    sound_mc.attachAudio(active_mic);
  }

  //set the microphone gain as per the isOn input variable
  mic.setGain(isOn ? 50 : 0);
}

Below the function, add a callback handler that makes the function callable via JavaScript:

  flash.external.ExternalInterface.addCallback("ToggleVoice", this, toggleVoice);

Html: Application host page
When you add a Silverlight application to your solution in Visual Studio, the boilerplate html makes your application full-screen in the browser window (as in the Tutor.com Classroom); this is exactly what we ant. Next, we need to add our Flash html object tag. Silverlight is a bit picky about where you add this html tag since you need to preserve this 100% height style, so slot the Flash object tag immediately before the div tag, and style it with "position:absolute" so that it doesn't interfere in the html layout:

  <form id="form1" runat="server" style="height:100%;">
    <asp:ScriptManager ID="ScriptManager1" runat="server" />
            
    <object 
      id="voice" 
      style="position:absolute;"
      classid="clsid:d27cdb6e-ae6d-11cf-96b8-444553540000"
      codebase="http://fpdownload.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=8,0,0,0" 
      width="250" 
      height="150" 
      align="middle"
      ...
    />

    <div style="height:100%;">
      <asp:Silverlight     
        ID="Xaml1" 
        runat="server"                 
        ...
      />                                
    </div>
  </form>

Javascript: Flash/Silverlight bridge
Next, we'll create the JavaScript function that our Silverlight app will call, which will in turn call into our Flash movie to set the Microphone input status. Since our Flash movie registered the ToggleVoice() callback function using ExternalInterface, Flash added a JavaScript entry point to function when the Flash movie loaded:

<script language="javascript" type="text/javascript">
function toggleVoice(on) 
{
    var flashVoiceObject = thisMovie("voice");
    if (!flashVoiceObject)
        return;

    flashVoiceObject.ToggleVoice(on);
}

function thisMovie(movieName) {
    if (navigator.appName.indexOf("Microsoft") != -1) {
        return document.getElementById(movieName);
    }
    else {
        return document[movieName];
    }
}
</script>

We can also very simply call from JavaScript into Silverlight, and we'll do this to alert our Silverlight application when Flash notifies us of microphone activity:

function IsTalking(active) 
{
    //access the Silverlight application
    var control = document.getElementById("Xaml1");

    control.Content.Page.ToggleIsTalking(active);
}

Silverlight – Microphone toggle button
Finally, in Page.xaml, add a toggle button with Checked and Unchecked handlers, and an indicator that we'll show when the microphone is active:

<ToggleButton Content="Toggle Microphone Input" Checked="ToggleVoice_CheckedToggled" Unchecked="ToggleVoice_CheckedToggled" />

<TextBlock Text="You are talking..." Visibility="Collapsed" x:Name="IsTalkingIndicator" />

Then in Page.xaml.cs, implement the handler and call into the JavaScript function:

private void ToggleVoice_CheckedToggle(object sender, RoutedEventArgs e)
{
    //call js layer to set microphone state
    ScriptObject voiceToggleScriptObject = (ScriptObject)HtmlPage.Window.GetProperty("toggleVoice");

    ToggleButton tb = sender as ToggleButton;
    voiceToggleScriptObject.InvokeSelf(tb.IsChecked.Value);
}

Lastly, implement the ToggleIsTalking() function that we'll call from JavaScript when the Flash movie responds to the microphone's onActivity() event. You just need to decorate this function with the [ScriptableMember] attribute so that Silverlight registers the function with the JavaScript runtime:

[ScriptableMember]
public void ToggleVoiceOn(bool On)
{
    IsTalkingIndicator.Visibility = (On) ? Visibility.Visible : Visibility.Collapsed;
}

And that's it! You now have a relatively straightforward Flash <=>JavaScript <=> Silverlight solution that affords you the best of all worlds.