Preventing SSRS From Changing Text Size

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:

 1USE [Sandbox]
 2GO
 3
 4CREATE TABLE [dbo].[SoftDrinks](
 5	[Name] [varchar](50) NOT NULL PRIMARY KEY,
 6	[Style] [varchar](50) NOT NULL,
 7	[CaffeineLevel] [varchar](10) NULL
 8);
 9GO
10
11INSERT INTO [dbo].[SoftDrinks] VALUES (N'50/50', N'Citrus', NULL);
12INSERT INTO [dbo].[SoftDrinks] VALUES (N'7 Up', N'Citrus', NULL);
13INSERT INTO [dbo].[SoftDrinks] VALUES (N'Coca-Cola Classic', N'Cola', N'Medium');
14INSERT INTO [dbo].[SoftDrinks] VALUES (N'Diet Pepsi', N'Diet', N'Medium');
15INSERT INTO [dbo].[SoftDrinks] VALUES (N'Diet Rite', N'Diet', N'Medium');
16INSERT INTO [dbo].[SoftDrinks] VALUES (N'Jolt', N'Cola', N'High');
17INSERT INTO [dbo].[SoftDrinks] VALUES (N'Mello Yello', N'Citrus', N'High');
18INSERT INTO [dbo].[SoftDrinks] VALUES (N'Mountain Dew', N'Citrus', N'High');
19INSERT INTO [dbo].[SoftDrinks] VALUES (N'Pepsi', N'Cola', N'Medium');
20INSERT INTO [dbo].[SoftDrinks] VALUES (N'RC Cola', N'Cola', N'Medium');

Selecting from the table should now get you this:

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.

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:

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

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

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:

1INSERT [dbo].[SoftDrinks] ([Name], [Style], [CaffeineLevel])
2VALUES (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.

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.

1SELECT
2   Name,
3   Style,
4   --CaffeineLevel -- (The original query)
5   --ISNULL(CaffeineLevel,'') AS CaffeineLevel -- (This doesn't work)
6   ISNULL(CaffeineLevel,' ') AS CaffeineLevel -- (This works)
7FROM 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:

1=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:

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!