SQL Server Reporting Services 2005 - How to report BIT data types in plain English

Many of you know that in SQL Server, if you want to record True or False data (the boolean), you have to define a column/field called the Bit Column (http://technet.microsoft.com/en-us/library/ms177603%28v=sql.90%29.aspx).

But how would you report such a little beast in SSRS? This is where the IFF() function comes in handy, because you need to convert the status of these columns into a choice between "something" and "nothing". The "something" in this case is the plain English I was referring to in the title. The "Nothing" gets more interesting because it can be NULL or something yet again.

However, the question really is how to do I treat Bit columns in SSRS to make sense to my readers of my reports?

The answer depends.

Lets say we have multiple Bit data types and each of them mean different things to the context of the database. For example a survey or multiple-choice question form. Such forms require a simple Yes or No answer to individual questions.

ie:

"What safety documentation did you fill in today? Tick the ones used."

[ ] JSEA?
[ ] Risk Assessment?
[ ] Work Method Statement?
[ ] Safety Procedures Form?
[ ] Other [details:____________________________]


Of course with the above question, you could tick one or multiples. The "Other" requires details of what 'other' means.

How do we report this in SSRS when the database has 5 BIT data types to record the True or False of each point, and when the report has to report it in a single TextBox control?

What we want to report in the SSRS TextBox is one or more of the above tick boxes. Yes at least ONE tick box must be selected. I also want to separate the selections made by commas.

Now, the problem becomes aparant when you cannot predict what selections were made and in what order (not that it matters). So you need to write a smart expression to handle all possible scenarios and handle which ones were chosen to be able to place the comma on the right one, if at all!

Introducing the power of the IFF() command in SSRS.

=IIF(Fields!JSEA.Value,"JSEA","") + IIF(Fields!JSEA.Value And (Fields!RiskAssessment.Value Or Fields!WorkMethodStatement.Value Or Fields!SafeOpProcedures.Value Or Fields!OtherFlag.Value),", ","") +
 IIF(Fields!RiskAssessment.Value,"Risk Assessment","") + IIF(Fields!RiskAssessment.Value And (Fields!WorkMethodStatement.Value Or Fields!SafeOpProcedures.Value Or Fields!OtherFlag.Value),", ","") +
 IIF(Fields!WorkMethodStatement.Value,"Work Method Statement","") + IIF(Fields!WorkMethodStatement.Value And (Fields!SafeOpProcedures.Value Or Fields!OtherFlag.Value),", ","") + IIF(Fields!SafeOpProcedures.Value,"Safe Operating Procedures","") + IIF(Fields!SafeOpProcedures.Value And Fields!OtherFlag.Value,", ","") +
 IIF(Fields!OtherFlag.Value,"Other: " + Fields!txtOtherFlag.Value,"") +
 IIF(Not Fields!JSEA.Value And Not Fields!RiskAssessment.Value And Not Fields!WorkMethodStatement.Value And Not Fields!SafeOpProcedures.Value And Not Fields!OtherFlag.Value,"","")

This perplexing looking expression string actually does the lot. Starting from the "top", it decides what to display based on what was chosen, but the cool thing is the IIF() with the And and Or to control if the comma should be placed at the end of each selection.

Basically saying, if JSEA was chosen and one of the other selections chosen as well, then place a comma. If none of the other selections were made, then do not show the comma. Note the space after the comma in the string literal (ie: ", "). That's simply to keep things apart and looking neat!

The last IIF() being rather large is actually there to handle the ELSE case, where if none of the selections were made then show a NULL string. And even if all are Selected, show a NULL string. In fact that last bit never happens, which is superflous, but it's necessary here to allow the IFF() function to have the proper syntax and for it to work. Without this last IFF() the SSRS engine will return "#error" as the ELSE response.

Conclusion:
SSRS has a small set of powerful but very useful functions, and one of the best is the IFF() function. By simply deciding what we want from top to bottom (old school) and catering for the situation where none of the above has been met, we can handle BIT like data types and report them in meaningful English. We can also do this within a single textbox control and not have to worry about concatenating or multiple textboxes to handle one response. It's also a useful way to create CASE WHEN THEN ELSE in one line. I hope this has been helpful.

Comments