August 9, 2017 at 9:30 am
Hi,
When adding a new parameter to report, there is an option Allow multiple values.
If this option is checked, then the parameter will use an array of values.
I have a report which has sub report. Sub report uses multiple value parameter and its values come from the database.
Sometimes, there is no relevant data for this parameter and that is fine. But, report builder doesn't allow me to check "Allow null value" option if "Allow multiple values" option is checked.
Is there any way how I can send an empty array as a parameter if needed?
Thanks!
August 9, 2017 at 9:53 am
What would your NULL value represent? If you are supplying a list of items, what would you expect a list of no items to return?
I ask as, for Multiple Value parameters, the drop down gives an option of Select all. If your NULL value represents "All", then there is no need for a NULL value as SSRS provides an alternative. Hence why I wonder what it would represent instead.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 9, 2017 at 11:34 am
I have to send an empty array.
So, there is a parameter which allows multiple values. It is of type integer. I can send multiple integers as a value of that parameter.
But, I also want to send zero integers as the value of that parameter. So, the value will be an empty array.
August 9, 2017 at 12:22 pm
Boris Pazin - Wednesday, August 9, 2017 11:34 AMI have to send an empty array.
So, there is a parameter which allows multiple values. It is of type integer. I can send multiple integers as a value of that parameter.
But, I also want to send zero integers as the value of that parameter. So, the value will be an empty array.
You can't. Hence my question: Why?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 9, 2017 at 3:42 pm
Thom A - Wednesday, August 9, 2017 12:22 PMBoris Pazin - Wednesday, August 9, 2017 11:34 AMI have to send an empty array.
So, there is a parameter which allows multiple values. It is of type integer. I can send multiple integers as a value of that parameter.
But, I also want to send zero integers as the value of that parameter. So, the value will be an empty array.You can't. Hence my question: Why?
Hi Thom,
First, thank you very much for looking into this issue.
I have a table of users and table of users details. There is a user_id field in both tables, but the problem is that these two lists come from different databases.
Because of different databases, I have to have two datasets and somehow join tables on report side.
The best way I found is to have a report parameter which allows multiple values. I load list of users to first dataset and filter it by using different conditions. Then load user_id field from filtered first dataset into report parameter.
Finally use that parameter to filter values of second dataset. So far so good, it works well if filter returns some users. But if first dataset can't find any user which comply to criteria, then it sends an empty array to parameter which is not acceptable by SSRS and report will crash.
August 10, 2017 at 1:48 am
Boris Pazin - Wednesday, August 9, 2017 3:42 PMHi Thom,
First, thank you very much for looking into this issue.
I have a table of users and table of users details. There is a user_id field in both tables, but the problem is that these two lists come from different databases.
Because of different databases, I have to have two datasets and somehow join tables on report side.The best way I found is to have a report parameter which allows multiple values. I load list of users to first dataset and filter it by using different conditions. Then load user_id field from filtered first dataset into report parameter.
Finally use that parameter to filter values of second dataset. So far so good, it works well if filter returns some users. But if first dataset can't find any user which comply to criteria, then it sends an empty array to parameter which is not acceptable by SSRS and report will crash.
It seems, more that the problem isn't that you want one report parameter to pass a NULL value, but that you have 2 parameters representing the same thing (a user). Am I correct? If so, you need to combine your 2 parameters into 1, rather than 2. If not, I still don't really understand your needs, and need to understand why you expect a list of nothing to return a result.
My only other thought is that, if these two items need to be separate, then they should be in separate reports, as they are different things.
As I said, you can't pass a list of nothing, as in the format it's nonsensical. A lot of people use NULL to represent "all" in SSRS when using a single value parameter, and then the user can select an individual one if needed. With a list, you don't need to do that, as you pass every value through instead to represent "all". Passing a list of nothing is like stating "I'm not interested in any of these", which means every result is eliminated.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 10, 2017 at 5:22 am
Thom A - Thursday, August 10, 2017 1:48 AMBoris Pazin - Wednesday, August 9, 2017 3:42 PMHi Thom,
First, thank you very much for looking into this issue.
I have a table of users and table of users details. There is a user_id field in both tables, but the problem is that these two lists come from different databases.
Because of different databases, I have to have two datasets and somehow join tables on report side.The best way I found is to have a report parameter which allows multiple values. I load list of users to first dataset and filter it by using different conditions. Then load user_id field from filtered first dataset into report parameter.
Finally use that parameter to filter values of second dataset. So far so good, it works well if filter returns some users. But if first dataset can't find any user which comply to criteria, then it sends an empty array to parameter which is not acceptable by SSRS and report will crash.It seems, more that the problem isn't that you want one report parameter to pass a NULL value, but that you have 2 parameters representing the same thing (a user). Am I correct? If so, you need to combine your 2 parameters into 1, rather than 2. If not, I still don't really understand your needs, and need to understand why you expect a list of nothing to return a result.
My only other thought is that, if these two items need to be separate, then they should be in separate reports, as they are different things.
As I said, you can't pass a list of nothing, as in the format it's nonsensical. A lot of people use NULL to represent "all" in SSRS when using a single value parameter, and then the user can select an individual one if needed. With a list, you don't need to do that, as you pass every value through instead to represent "all". Passing a list of nothing is like stating "I'm not interested in any of these", which means every result is eliminated.
Please notice I never said I want to pass a NULL. I want to pass an empty array which is a different thing.
Yes, I have two parameters representing a list of users, but second parameter is filtered from first parameter. So second parameter is a subset of first parameter after different filters are applied. There are cases when filter will return an empty array.
These two datasets come from different databases, but due to business requirements, they must be on the same report.
If someone else has this problem, I found a solution:
1. Add one ID which doesn't exist, i.e. it could be ID = 0. So, in worst case parameter will contain one integer of value 0. You could do this by using UNION in dataset query, like this:SELECT 0 AS user_id
UNION
SELECT user_id FROM Users
2. Array will not be empty so report will not crash
3. After parameter is applied on dataset by using IN operator, it will have 0 rows just if you sent an empty array.
August 10, 2017 at 5:33 am
When SSRS passes a set a values, it doesn't use an array. Arrays don't exist in SQL Server. Values for Multiple Values Parameters are passed as a delimited string. So, for example, if you selected the ID's 1, 5, 9, and 15, it would pass the value '1,5,9,15'. An Empty string in SQL Server has the value of NULL (not to be confused with a zero length string ('')). This, again, is why you can't pass a NULL value, as that would implicitly return no results.
I'm pleased you found a solution. What you're doing there isn't passing an empty string though. The Query that SSRS will pass will, instead, send a string with the value '0' if only that user_id is selected in the drop down.
Hope that all makes sense to you.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 10, 2017 at 10:36 am
Thom A - Thursday, August 10, 2017 5:33 AMWhen SSRS passes a set a values, it doesn't use an array. Arrays don't exist in SQL Server. Values for Multiple Values Parameters are passed as a delimited string. So, for example, if you selected the ID's 1, 5, 9, and 15, it would pass the value '1,5,9,15'. An Empty string in SQL Server has the value of NULL (not to be confused with a zero length string ('')). This, again, is why you can't pass a NULL value, as that would implicitly return no results.
Thank you for the explanation of how parameters work internally. It makes sense.
Thom A - Thursday, August 10, 2017 5:33 AMI'm pleased you found a solution. What you're doing there isn't passing an empty string though. The Query that SSRS will pass will, instead, send a string with the value '0' if only that user_id is selected in the drop down.
Yeah, a user with ID = 0 doesn't exist, so when only '0' is passed nothing will be found in the dataset and the final result is same like I sent nothing. The only difference is that report will work.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply