March 28, 2012 at 8:09 pm
I am adding an ALL to the parameter drop down list which has an int data type in a report I have created. As a result when I select the ALL value in the drop down and run the report I am getting data conversion errors. Cannot convert nvarchar datatype to int.
the value in the drop down list are
ALL
160
172
171
212
223
MY question therefore is how do Convert the data type int to nvarchar and vice versa?
March 29, 2012 at 2:00 am
First of all, why are you adding an 'all' parameter yourself?
Can't you just set in your parameter options that the parameter allows multiple values?
If you really want to do it yourself, you can use the 'CInt' function to convert anything to an integer value.
But again, I would recommend to set the parameter as 'allow mulitple values' on the General tab of the parameter options...
April 3, 2012 at 11:08 am
One way to deal with this is to add a NULL to your parameter's dataset (if it's a stored proc, put a UNION into it) and make sure that the parameter setting to Allow NULLs is checked Yes. Then, in the code for the dataset for the report itself, add a CASE statement to return all rows if @param IS NULL.
You can do as someone suggested and allow selection of multiple values, but there are some pitfalls there: (1) you cannot pass a table of selected values (or an array) to the report's SP, so you usually wind up with the clunky string-concatenation-and-exploding method. Here are 2 links explaining this: http://sqlblogcasts.com/blogs/simons/archive/2007/11/22/RS-HowTo---Pass-a-multivalue-parameter-to-a-query-using-IN.aspx and http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/ssrs_multi_value_parameters.
The second issue is that I often have a report where the user either wants to select one value or everything. If you go the multi-select route and the user wants all the parameter values checked, the user has to check all the entries in the list, which could be annoying if it's long.
One of the benefits of NULL is I can have the report run with NULL as the default parameter value and show the user all the results; then they can select a specific value to zero in on a year they're interested in. Of course, this depends on what your users want, but this is often useful to me.
Rich
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply