Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
#SQLServer 46
SQL Server 44
BI 36
#BI 33
brh 28

Archive · View All
March 2011 24
July 2011 17
February 2011 15
April 2010 13
June 2011 11
May 2011 11
April 2011 11
July 2010 11
March 2010 8
December 2011 7

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

Apr 28 2011 10:15PM by Sherry Li   

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:


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.


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.   

Tags: SSRS, multi-value parameter, ListToTable, brh,

Sherry Li
14 · 12% · 3827


Your Comment

Sign Up or Login to post a comment.

"SSRS #46 – A case against using character field in multi-value parameters" rated 5 out of 5 by 1 readers
SSRS #46 – A case against using character field in multi-value parameters , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]