Home > SSRS Expert > SSRS #46 – A case against using character field in multi-value parameters

SSRS #46 – A case against using character field in multi-value parameters

In my previous post, Using Multi-value Parameters in SSRS Report, I blogged about how to write a table-valued function in SQL to take a multi-value report parameter. The function ListToTable can take a list such as this:

123,904,949,837,495,399

and convert them into rows in a SQL table, and then your stored procedure can use them in a where clause to limit the results to only what the user wanted.

  1. 123
  2. 904
  3. 949
  4. 837
  5. 495
  6. 399
    In the above example, I used IDs, instead of their text descriptions, with a purpose.
    In some of my SSRS reports, I used text descriptions. It turned out that there are two serious issues with using character field in multi-value parameters.

Issue #1

If the selection list is long, and your users tend to select ALL all the time, you might run into an issue where the list exceeds the maximum length you defined in your stored procedure.

image

Fortunately, you have the SQL keyword MAX to help you to keep your parameters running out of length. So this issue #1 doesn’t seem to be a show stopper.

Issue #2

Issue #2 has to do with the unpredictable nature of our character data. Have you ever noticed characters in your data that are sometimes “unusual”? Of cause you have. But I am talking about some very “usual” characters, such as quotes, single quotes or double quotes. If you are in IT for awhile, you already know that quotes are not just “usual” characters, they are special characters in programming world. They are used to indicate that the data type for your data is character, versus integer, for example. If you need to have quotes in your data, you will need to use “escape” characters to indicate that you want to keep your quotes. Here is where the ListToTable function failed. I did not take the quotes into consideration.

I found out this issue during my testing, noticing that I was missing some data in the report. Later I confirmed that those missing records are the ones that have single quotes in the character field. They failed to return to the SSRS report by the stored procedure.

For the above two reasons, I would suggest that you always use ID fields as your report parameters, where data is much more predictable.

A Side Note

Before I close this post, I would like put another side note here.

If you have some experience with creating reports in Microsoft Reporting Services, you know that you don’t need to use stored procedures as your dataset. You don’t need to use stored procedure to take multi-value report parameters either. So the question is if you write your SQL query in your RDL file to take multi-value parameter, would you run into the issue #2 when the parameter has single quotes in it? That will be a question for you.   

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: