Geeks With Blogs
Jim Giercyk

Occasionally I am asked to create a report is SSRS, and there are a few common user requests that are not exactly intuitive to a developer.  Since I constantly find myself looking at other reports and “borrowing” code, this blog entry is mostly for me so that I can change my thieving ways, but I hope that it helps you as well.  



Alternate background colors on each line in a table


How many times have you heard “In Excel I can……”?  In my case, too many to count, but as far as styling a table to have alternating colors, our users have a point.  It is extremely easy for a user to choose a style for a table, click a button and voila!  Doesn’t that make you angry?  Well fear not, we have a way and it is rather simple, but rather cumbersome.  We must change the Fill color for each text box in the Tablix:



As you can see, we replace the Fill color with an expression that indicates the 2 colors to alternate:


I’ll  paste the formula here so that you can cut it and put it directly into your report.  Of course you can change the colors to anything your user wants.  In this case the first line of the tablix will be white, the second will be lightgray and they will alternate from there.  If you want to be really fancy you can make the colors variables and let the user style the table (just like Excel!!!). 

=iif(RowNumber(Nothing) Mod 2, "White", "LightGrey")



Changing Visibility based on the existence of a value in a dataset


This is a handy trick when you have a file containing, for example, Inserts, Updates and Deletes with the action type based on a value in your dataset.  Based on the value, you display the record in one of 3 tables, but what happens when you have no records of a given type??  How do we make the table headers and boarders disappear? 

To do this, we need to count the number of records with each change type (D,I,U) to decide whether to show the table or not.  If the record count is 0 for any type we will hide the tablix….makes sense, right?

Open the properties of each tablix and choose the Visibility tab:


Under show or hide based on expression , we do our magic:


In the case of this expression, we are counting the number of “I” records in the recordset, and when the count is <=0 we will hide the table. 

=sum(iif(Fields!ChangeType.Value="I",1,0)) <= 0

TIP: Note the expression indicates when the tablix will be HIDDEN, not when it will be SHOWN.

This is a simple example, but this method can be used to count any value in any field or part of a field in a dataset.

Posted on Wednesday, August 7, 2013 9:06 AM | Back to top

Comments on this post: SSRS Odds and Ends

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Jim Giercyk | Powered by: