Alternating Row Background Colors in a Tablix (aka the Green Bar effect)

UPDATE on 1/31/2013: Major overhaul of technique for formatting a matrix with the "green bar" effect. The new approach requires no pre-planning for the dataset query and uses scope designations that are agnostic to the specific names of the data region.

The "green bar" effect is a document is the alternating of one background color (usually white) with another background color (originally light green) on each printed line of a document in order to make it easier to read across each line of text. The term comes from the days of impact printers with continuous feed paper that was pre-printed with just such a scheme of alternating colors, and it remains a popular method of making long lists, particularly grids, easy to read. Here is an example:

The method of formatting SQL Server Reporting Services tables with a "green bar" effect is not difficult. Formatting a matrix is only a little more difficult.

Formatting a Table

1. In design mode, highlight all the text boxes in the (Details) section. Use a click-and-drag method to select them all in one step or hold down the Ctrl key and click each one in turn.
2. On the Properties pane click the down-arrow for the value of the BackgroundColor property, and then click Expression...
3. Enter the following expression. If you want colors other than white and light green, substitute your own color choices.
   
   =IIf(RowNumber(Nothing) mod 2 = 0, "LightGreen", "White")

The Mod function applies "modulo division," which returns the remainder of the division. All even numbers have a remainder of zero when divided by 2, so the IIF() function will return "true" for even numbers and "false" for odd numbers, resulting in the two different colors being applied to the background of the text box. That's all there is to it for a table. A matrix is a little more involved.

Formatting a Matrix

1. Create your matrix, and then right-click on the left-most column and select Insert Column > Inside Group - Left.
2. On the bottom-left text box of the matrix, which was just created when you added the new column, right-click and select Text Box Properties...
3. Change the Name property to RowNumber.
4. Enter the following expression as the Value property:
   
    =RunningValue(Min(1), Sum, Nothing)
    
5. Click OK to confirm the new settings for the text box.
6. If you do not want the running count of rows to appear in your final matrix, click on the tablix column marker to select the entire column. Then use the Properties pane to change the Hidden property to True.
7. Click on the text box that will show the aggregate data for your matrix (in an Excel PivotTable this would be called the "data region" cell).
8. On the Properties pane click the drown-arrow for the value of the BackgroundColor property, and then click Expression...
9. Enter the following expression.
   
   =IIf(ReportItems!RowNumber.Value mod 2 = 0, "LightGreen", "White")

If you want the row labels to alternate with the same colors, merely repeat the same formula for the background color of the row label text box(es) as well.

SSRS 2008 R2 Gotcha: Default Multi-valued Parameters

Recently a customer contacted me with a mysterious symptom in SQL Server Reporting Services (SSRS): a report displayed different results in VisualStudio than it did in Report Manager. He had verified the data source was the same for both contexts. He had verified that the RDL file was identical in both contexts. All the user-selected parameters were identical. And yet in Visual Studio certain values were included in the output that were not appearing when the exact same report ran in Report Manager. He had redeployed this report several times while troubleshooting, with no change in the output of the report in Report Manager.

After I connected to his system with a remote assistance session, I started drilling down through the report management options in Report Manager (report > Manage). I discovered that there were a couple of hidden parameters in the report design, and shortly after that I discovered that one of the hidden parameters was a multi-select parameter for which the Visual Studio report had two values selected by default and the Report Manager had only one.

Apparently the Report Manager context had cached the default setting for the parameter with one value only and redeploying the report did not change the setting. The only method we found that could fix the hidden parameter was to manually update it in the Report Manager context. Only then did the online report output match the offline report output in Visual Studio.

Later I tested the process by deploying a report that had a multi-valued parameter that used a query to select all the possible values by default. This parameter was not hidden. This report deployed normally and showed the parameter as having a default that was generated by query, the correct setting. But then I altered the report in Visual Studio to have a manually-defined default value instead of a query-based value. When I deployed the report and checked it again in Report Manager, the parameter had no default value at all. Changing the default value to a different manual value and redeploying had no effect; the online report continued to have no default value defined.

The bottom line appears to be that in SSRS 2008 R2 setting manual default values for a multi-valued parameter in Visual Studio is not a guarantee that the deployed report will have the same values set as defaults. The only way to be sure you have the correct manual default values for multi-valued parameters is to set them using the the Parameters property of the online report in Report Manager.

Repeating Headers in SSRS

Getting header rows to repeat in SQL Server Reporting Services (SSRS) is surprisingly tricky. This is due at least in part to the ambiguous use of the term "header" in the SSRS context. In some cases it refers to any row that is above the core data-driven section of a tablix item. In other cases it refers only to a group header row. Hopefully the explanations below will clarify things a bit.

NOTE: In all the examples to follow I will use a typical data source and a dataset, but I am only going to show screenshots of the tablix structure and options related to the topic of repeating headers. The actual data are moot to the process of getting the headers to repeat.

Before exploring the solutions for repeating headers, I will walk through steps to highlight the cause of much frustration with it comes to trying to make headers repeat. 

Step 1: Add a table to your report. Notice that the top row of the table is labeled "Header."

Step 2: Add whatever fields you want to the data row.

Step 3: Open the tablix properties of the table. Put a check mark next to "Repeat header rows on each page." Click OK.

Step 4: Run the report. Notice that the header rows do not repeat on each page!

This is the usually the beginning point of frustration with making header rows repeat. It has caused me (and probably others too) to shout at the computer, "Why aren't you doing what I'm telling you to do?!?!"

[Spoiler: "Repeat header rows on each page" doesn't really mean what it says. It means, "Repeat the header region of a row group on each page whenever the content is too wide to fit on one page and generates one or more extra pages." I guess that was too much verbiage for the programmers to fit.]

The following steps explain how to get Reporting Services to do what you want it to do and repeat the information you want repeated.

Repeat Rows or Columns for a Table with No Groups

A static row is any row that is outside of any data-driven section of the tablix. This means it is not the (Details) row and it is not inside any bracketed "grouping" section either. The tablix marker that appears to the left of the row is completely empty, as shown below.

Normally static rows do not appear as items in the Row Groups section at the bottom of the design screen, but to make them repeat we need to see them. So use the drop-down choice to the right of the Column Groups label and select "Advanced Mode" to turn it on.

Click on the (Static) item under the Row Groups pane that corresponds to the row you want to repeat. Then switch to the Properties pane (press F4 to open the Properties pane if it is not already open).

Change or verify these property values:

• FixedData to True (to "freeze" the row in a web browser environment; this is optional)
• KeepWithGroup to After (this may be the default, but it can be changed inadvertently)
• RepeatOnNewPage to True

Now the row will repeat on each new page of the report output.

If there are too many columns to print on one page, you can also choose to repeat (and freeze) one or more columns on each page. Simply make the same change to the column's (Static) item under Column Groups that is shown above for the static row.

Repeat Rows and Columns in a Table with Row Groups

When you add a row group to a tablix, a vertical double-dashed line divides the table and a bracket appears on the tablix frame to show the rows within the group. Within the group bracket, the region to the left of the double-dashed line is the "row header" region. To the right of the double-dashed line is the details region and, if you added a row group header, a static row (technically only a partial row) above the details region. The diagram below shows each region with arrows pointing to the corresponding item in the Row Groups pane. As mentioned above, to see the (Static) items, the pane must be in Advanced mode.

All static rows, including the partial header row inside the group, can be repeated on each page using the technique explained above. The row header region will reprint the field value at the top of each page by default, so normally there is nothing you need to do with it. However, if you have too many columns to fit the page, you can also repeat the row header region on the pages with the excess horizontal content. To do this, open the tablix properties and put a check beside "Repeat header rows on each page." (Of course it is not "rows" that are repeated but the row header column. This is part of what makes the option unclear.) If you want the row header region to stay on the screen when the report user scrolls horizontally, also check the box to "Keep header visible while scrolling."

Repeats Rows and Columns in a Table with Column Groups

Like a row group, a column group in a table (or matrix) also has a double-dashed line dividing the details region below the line from the column header region above the line. Making static rows and columns (or the partial "header" segments that are also static) works just as described above for static items. Making the column header region repeat works like making the row header repeat: open the tablix properties and select "Repeat header columns on each page." (Again, it is not "columns" that repeat but the column header row. This just maintains the confusion.) When you are dealing with a matrix (which is really just a table with row groups and column groups together), this is normally the option you want to choose to make sure the columns are labelled on every page. But this is not intuitive because the wording leaves you to believe that columns will be repeating, not the header row. 

Summary

Hopefully this post will help you take control of the headers you want to repeat across the pages of your report, both in print on on the screen. Here is a brief list of points to take from the discussion:

• The tablix properties for repeating headers refer only to group-header regions and have no effect on other rows or columns in the tablix.
• The phrases "header rows" and "header columns" in the tablix properties screen are misleading and basically mean the opposite of what they seem.
• To make non-group-header rows or columns repeat, first turn on the "Advanced" mode of the group pane at the bottom of the design screen, then set the properties of the corresponding (Static) item so that RepeatOnNewPage and FixedData are both true.

Bit and Sum() in Reporting Services

The bit data type is normally used to record Boolean true/false data, with a 0 for false and a 1 for true. It might also represent yes/no data, with a 0 for no and a 1 for yes. When it comes to reporting on this type of data, it is normally represented with a checkbox or a word. It is not normally an additive value.

But consider the circumstance of an attendance history table with a bit column for "attended" paired with a column for a "person_id" and a "date." In a report it would be logical to add up the "attended" column values to arrive at a "times attended" total. And that is exactly what I recently tried to do, only to discover what I am about to share.

In SQL Server Management Studio (SSMS), the bit data type is represented in the results display by a 0 or a 1. If you try to put a bit column into a SUM() aggregate function, you will receive an error message that lets you know that the bit type is invalid for the SUM() operator. But that is not a big problem because you can CAST the data type into tinyint, and then it adds up to yield a positive number that is the sum of all the 1s and 0s in the included set of rows.

By contrast, in Business Intelligence Development Studio (aka BIDS - Visual Studio optimized for Reporting Services), the bit data type is represented by the words True and False. Just like in SSMS, if you try to put a bit data type in a Sum() function, you will receive an error. Once again this seems like no big deal because you can use CInt() to convert the data type to an integer.

But here's the rub: BIDS considers a "true" bit to be a -1, not a 1. That means the Sum() of a converted bit data type will be a negative number that is the sum of -1s and 0s in the included set of rows.

I discovered this when my formula for "percent attended" was yielding negative values even though, when I checked the numbers in SSMS, all I saw were positive numbers. It took a little digging to figure out that SSMS and BIDS where interpreting "True" in equal but opposite ways (1 and -1 respectively).

So the conclusion is this: if you are going to use a bit data type as an additive value in BIDS and Reporting Services, convert it first in the SQL statement to a tinyint data type. That will keep everything positive.

SQL Saturday #96 Wrap-Up and Top 10

SQL Saturday #96 happened in Washington, D.C. yesterday, and it was a great event. It is a special event to me because it was my first opportunity to speak at a PASS event and give back to a community that has given me much and transformed my queries for the better time and time again. If you are visiting my blog because you came to one of my sessions, please leave me some feedback on the presentation -- constructive criticism is as welcome as praise.

If you would like my PowerPoint presentations along with the .sql files I used for the demos in SQL Server Management Studio, you can download them from the SQL Saturday #96 Schedule page.

And in the same vein as my comments on the PASS Summit, I'm going to post the top ten things I learned yesterday. These are in roughly chronological order.
1. If you ever go to a SQL Saturday event, definitely use the Speed Pass and print it off before you to the venue on the day of the event. If you don't, you'll likely be in a line waiting to check in before you can join the fun.

2. Apparently Twitter or some Twitter-related organization ranks people's "influence" based on their tweets and the number of people who respond to them. This ranking is quite important to many people, and it was a point of animated discussion in the speaker's room at this event.

3. I discovered that people who are coming to T-SQL and SQL Server from Oracle and other platforms can benefit from an introductory course such as my introduction to Common Table Expressions, even when their time of experience with SQL is greater than mine. I suppose this is self-evident, but it was a little eye-opening to me.

4. Even though features have been around since SQL Server 2005, some people who have been using T-SQL for a long time may have overlooked a feature that I take for granted now, such as the window functions. I was a little pleased when I heard a couple of attendees express surprise that it is possible to generate an aggregate value without a GROUP BY clause because it meant that I had something to share that I knew could help them tremendously.

5. Receiving applause for a presentation is truly gratifying, even when it is a customary act done for every presenter. It seemed authentic in both my sessions, and therefore it was meaningful to me.

6. It seems that for most Business Intelligence professionals, having the primary justification for having a data warehouse is the ability to create an OLAP cube for analysis. I'm not sure that will be my justification. I think its primary benefit as a companion to our OLTP database would be to capture historical data for slowly changing dimensions and a close second or even a tie for first would be to enable a clearer semantic model for our end users in report design.

7. The date dimension generally adds the most overhead to any OLAP cube because it generally adds 365 rows x however many years are tracked. If there is any way to summarize to the weekly granularity or less, that will be a huge win for the efficiency of the cube.

8. It is ever clearer that any data warehouse that might be built as a companion for the Shelby v.5 OLTP database will include a single "EntityNames" role-playing dimension that contains all the entity attributes across all the available modules supplemented with views that take the core table and breaks out the module-specific attributes.

9. Even a large software company like Microsoft can overlook a feature that the end user sees as patently obvious. The case in point for today is the ability to update automatically all the Reporting Services reports that use a report part that has been changed. It's a manual process for now.

10. The built-in change tracking mechanism in SQL Server may be a great option for customers who want to know when things have changed at a more detailed granularity than we capture in the OLTP tables. But it only captures three days' changes by default, so a secondary mechanism for moving that data to a flat file or supplemental table is needed to go further back from there.

There you have it, my top 10 things learned yesterday at SQL Saturday #96. I'll return to blogging more procedural stuff after today. Let me finish this entry by saying that I really enjoyed the privilege of sharing my knowledge with the PASS community, and I hope to have similar opportunities in the future. If I do, you'll definitely be reading about it here. :-)

Top 10 Things I Learned at SQL PASS on 10/10/2011

I have the priviledge of being here in Seattle for the SQL PASS Summit conference all week, including two days of "pre-convention" all-day training topics. Today was the first day of the conference, and I wanted to continue a tradition of summarizing the top ten things I learned today at PASS. These are in roughly chronoligical order.

1. It is good to get back on a treadmill and work out, even though a four-month break means that I could only go 2.6 miles in 45 minutes instead of 3.1 miles in about 40 minutes.
2. The scuttlebutt I heard last year is true: the Paramount Hotel is indeed a great place to stay for PASS, as it is reasonably priced and only a block or so from the convention center. And most of the walk there is under covered awnings. If you are familiar with Seattle weather in October, you know this is no small fact.
3. Talking to and singing to my almost-three-month-old daugher Destiny over the phone is a pale substitute for doing those things with her in my arms, but even through the phone her baby talk can melt my heart.
4. Apparently SQL PASS Summit attendees receive a new backpack every year.
5. The Guidebook app for Android phones (and probably iPhones too) is a great tool for an event such as this conference, and it is definitely something we should consider using for the International Shelby Conference next year.
6. In SQL Server Reporting Services, if you use a "cast" function (such as CDate or CStr) on a textbox value to give it a specific data type, it opens up a large set of VB.net methods that can make working with that value much easier. This is especially true for datetime values.
7. I really need to become comfortable with creating and using stored procedures over plain Jane SQL queries and views. They offer distinct benefits, which appear to be worth the trouble of being unable to create them easily with ShelbyQUERY.
8. In Reporting Services it is possible to create code blocks that do more involved VB.net functions than can be summarized into a single one-line of code in an expression. I knew this had to be the case, but today I learned how to make that happen.
9. Using a "post back" concept in the Action property of a Reporting Services report element creates the ability to have interactive "buttons" that change the parameters of the report content; for instance, it is possible to add "slicer" style buttons to a chart or matrix report design.
10. A walk from the hotel to Starbucks to get a refreshing shaken iced tea lemonade takes just enough time to wait out a false fire alarm at the hotel and return to see the firefighters climb into the fire engine and head out.

Multivalue Parameters in SSRS 2005

I just posted earlier today, but I need to post this topic while it is fresh on my mind. It took some digging, and I want to get this out there while I can remember it clearly. I want to thank Munish Bansal for his blog post on this topic, which was where I found the answer to my search on this question.

In SQL Server 2008 Reporting Services, handling multivalue parameters is relatively straightforward. You just set the parameter property to multivalue and make sure that the filter is set to the IN comparison instead of equals (=). But in SSRS 2005 it is not quite that easy. You set up the parameter the same way, but the filter works differently. Instead of adding it as a filter at all, you add the condition as a Parameter property and put the IN logic into the dataset query directly. For example, the query's WHERE clause would have a condition like this in it:

WHERE My_Column IN (@MyParameter)

Then in the Parameter property of the dataset query you use the JOIN function to turn the multivalued parameter information into a comma-delimited set of values, as shown below. (Click the image to see it full-size.)

Once this is done the parameter will work as desired.

Hopefully you are working in SSRS 2008 because the improved handling of multivalue parameters along with many other enhancements make it almost a non-decision to upgrade as soon as possible to the latest SQL Server version. Denali promises even more enhancements in Reporting Services, and I can't wait to see what's coming next.

Checking for NULL in SQL Server Reporting Services

Today I was tasked with replacing blank values on a report output with a double-dash. This was on a SQL Server Reporting Services (SSRS) report design I had made.

I knew this would involve an IIF() conditional. But I was not sure how to phrase the condition to check for the NULL value. I tried two approaches off the top of my head.

First, I tried the SQL syntax of Fields!FieldName.Value IS NULL. It was a long shot, and it didn't work. No real surprise, there, so I moved on.

Second, I tried the VBScript approach of IsNull(Fields!FieldName.Value). I was pretty sure this would work, because I have used some VBScript commands successfully with conditional formatting in SSRS before. However, this didn't work either.

What became apparent was that I needed to understand how to do this kind of condition in .NET, which is the actual programming language supported inside of SSRS for formula expressions. I did some searches on the Internet and eventually found the right syntax. It turns out that IsNothing() is the .NET equivalent to the VBScript function IsNull(). The working syntax for my report requirement is:

= IIf(Not IsNothing(Fields!FieldName.Value), Fields!FieldName.Value, "--")

Reporting Services and check boxes

Most of the reports I make are created using a slimmed-down version of Active Reports, which has been integrated into Shelby Systems v.5 software as part of the ShelbyQUERY tool. Active Reports has a "checkbox" control object, which displays a checkmark in a box when the value of the field is anything other than "false," or an empty box when the value is "false." This can come in handy for reports with lots of Boolean (true/false) values.

Interestingly, even though SQL Server Reporting Services is much superior to Active Reports in many respects, it is lacking in a "checkbox" object. This seems to me to be a significant oversight, but it not completely insurmountable.

Although there is no "checkbox" object in Reporting Services, you can emulate one using a TextBox, if you are willing to have an "X" instead of an actual check mark in the box. Just do the following.

Create a new text box, put an "X" in it, and change the following properties to the listed values:

• BorderStyle: Solid
  
• Color: = IIf (Fields!MyField.Value, "Black", "White")
  
• Size: .175in, .175in (this fits a 10pt font; make it larger for a bigger font)
  

With these setting the box will have an "X" in it for true values and be empty for false values. I haven't tried it yet, but I suspect it might even be possible to use a font like Wingdings and actually print a checkmark in the box instead of just an X.