In the movie Cast Away, the character (by Tom Hanks) danced and jumped around a bonfire on beach and shouted many times: “look at me! I made the fire!”, even though he knew that nobody could look at him at that isolated, far away island. He simply could not stop celebrating his exciting achievement. That is exactly same feeling on a late Friday afternoon, after I figure out how to solve two of my problems at same time. Those two problems are: first. let SSRS pass a parameter to a query in DB2, and second, that parameter could be one or several account_ID.
My users want to get data for one or several account_ids. My best friend, Google.com, gave me this useful post: https://stackoverflow.com/questions/13764010/how-to-pass-multiple-values-to-single-parameter-in-stored-procedure . From this post, I learned several ways to convert the string parameter (several account_id seperated by comma) to integer data and pass the data to the query. However, I could not apply this knowledge to my query because it use a DB2 database as data source. DB2 database has many restrictions and is not as flexable as SQL server database.
But, I found a perfect way to solve the problem, using same concept as dynamic SQL–construct select statement from string parameters.
Step one: set up the normal test (string) parameter to hold several account_IDs that are separated by comma.
Step 2: set up another parameter that hold the select statement:
For this text/string parameter, let the visibility property be “Internal” because we don’t want the user to touch it. Also, set its Default value like this:
Of Course our real life query is much longer than this. I worried that my long statement would exceed the limit of text parameter size of SSRS. So, I asked by best friend google.com again. She (google is a knowledgable woman, just like Siri and Cortana) told me to relax. Somebody tested that the string can hold more than 65535 characters. If my sql statement is less than 3 page in a word document, it should be safe.
Step 3: set up the data set to obtain the column names.
You need to put a real query text in the text box first. Run the query and RRSR will return all result columns for the data set. (I skip getting a picture for this step but it is very important. If you skip this step, step 4 data set will not give your column name, and you have to come back to this step).
Step 4: after I tested the report preview with my example account_Ids. I came back to change the query text to the value of our SSRS parameter:
Now the dataset properties window looks like this:
STEP 5, sit back and enjoy the great feeling of accomplishing something important and impossible. “Look at me. I made this fire”
At the end of the movie Cast Away, Tom Hanks was back into the civilized world. He repeatedly flickered a lighter on and off, on and off, on and off … …
Same for me, after I yelled many times of “look at me” inside of my chest, I asked google.com who is the smartest one in the world (should be me, right?). She gave me this page, showing me that all I did was documented more than 5 years ago by another geek (who is smarter than me). http://jaliyaudagedara.blogspot.com/2013/10/sql-server-reporting-services-ssrs.html