Apr 122012
 

Every once in a while I find myself building a report in SQL Server Reporting Services. I know my way around SSRS enough to get a report looking the way I like, but I’m definitely not an expert in the subject. While developing a report recently I noticed some very strange behavior that I have a hard time believing is intended: SSRS is changing font sizes on me depending on what I’m viewing! I’m happy to provide screenshots and instructions to reproduce this behavior, and also the solutions I found. If you’re curious, I first observed this in SQL Server 2008 R2 SP1 CU2 (10.50.2772), though I’m guessing it appears in earlier builds as well. It is still present in the latest build, SQL Server 2008 R2 SP1 CU5 (10.50.2806).

Let’s start off by creating a very simple table about soda pop soft drinks in a test database (mine is named “Sandbox”) and fill it with some data:

USE [Sandbox]
GO

CREATE TABLE [dbo].[SoftDrinks](
	[Name] [varchar](50) NOT NULL PRIMARY KEY,
	[Style] [varchar](50) NOT NULL,
	[CaffeineLevel] [varchar](10) NULL
);
GO

INSERT INTO [dbo].[SoftDrinks] VALUES (N'50/50', N'Citrus', NULL);
INSERT INTO [dbo].[SoftDrinks] VALUES (N'7 Up', N'Citrus', NULL);
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Coca-Cola Classic', N'Cola', N'Medium');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Diet Pepsi', N'Diet', N'Medium');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Diet Rite', N'Diet', N'Medium');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Jolt', N'Cola', N'High');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Mello Yello', N'Citrus', N'High');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Mountain Dew', N'Citrus', N'High');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Pepsi', N'Cola', N'Medium');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'RC Cola', N'Cola', N'Medium');

Selecting from the table should now get you this:
Query Result

I should add that the values in the CaffeineLevel column are completely made up. I just wanted a few different values with some NULLs mixed in.

Now that the table is set up, we need a report. To briefly describe the report I created, I’m using the List object with text boxes for Name and CaffeineLevel in the detail section. The report is grouped by Style and the groups are expandable and closed by default. The name of each group also displays a count of the number of records it contains. (Click to enlarge).
Visual Studio

To make things a bit quicker, you can download the report project here. It’s setup to use Windows authentication to connect to the Sandbox database on localhost. You may need to make adjustments to the connection information depending on your test environment. I wasn’t able to observe this behavior when previewing in Business Intelligence Development Studio, so you will need to deploy it to a report server.

Once the report is deployed, run it and you should see the 3 Style values with counts, however the details aren’t visible:
Report

Open the Cola and Diet groups and you’ll see the values for Name and CaffeineLevel. Everything looks fine, right?
Report

Now open the Citrus group. Notice how the size of the text in the CaffeineLevel column shrinks?
Report

Closing the Citrus group will return the text sizes for the other groups to normal. For fun, let’s try this again with a non-null value for the first row of the CaffeineLevel column. Add another row to the SoftDrinks table:

INSERT [dbo].[SoftDrinks] ([Name], [Style], [CaffeineLevel])
VALUES (N'123_NotNull', N'Citrus', N'Low');

Refresh the report and open all groups. You’ll see the font size doesn’t change, which should be expected.
Report

It appears that the cause of this is related to the top value in the column. If the top value is null, the font size gets smaller.

Solutions

I found a couple of ways to resolve this. The first is to remove the NULL values from the CaffeineLevel column by adjusting the query. I used the ISNULL() function and tried replacing the NULL values with an empty string, but that had no effect. Replacing null values with a single space did fix the problem, however.

SELECT
   Name,
   Style,
   --CaffeineLevel -- (The original query)
   --ISNULL(CaffeineLevel,'') AS CaffeineLevel -- (This doesn't work)
   ISNULL(CaffeineLevel,' ') AS CaffeineLevel -- (This works)
FROM dbo.SoftDrinks;

Another solution is to filter out the NULL values by using an expression in SSRS. For this I used the IsNothing() function to replace NULL values with a string containing a single space, which resolved the issue. Much like above, replacing NULL values with an empty string had no effect. The expression I used was:
=IIF(IsNothing(Fields!CaffeineLevel.Value)," ",Fields!CaffeineLevel.Value)

Whichever of the above you choose, you’ll arive at the correct (and visually pleasing) result of having the font sizes be the same:
Report

Personally I prefer to alter the query rather than add an SSRS expression, as I am not a fan of seeing <<expr>> in report definitions when it’s not truly necessary.

If anyone else has encountered this situation, I hope you find this solution helpful!

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)