You are not logged in.
Hello,
I have created a Dynamic List which calls a Function in Microsoft SQL Server. Now, to this Dynamic List, I have created few Datasource parameters as the function accepts Six input parameters. Once we execute the report, User can select multiple values from each of the Datasource parameters but then if multiple values are selected, it results in the function having more arguments then what it can accept resulting in error.
Could someone advise please as to how can I can execute the function with multiple values to some of the parameters? Thanks.
Aiden
Offline
Hi hx25523,
I don't quite understand the issue. Why don't you create 6 single-select parameters as the function expects 6 arguments ?
Can you please post some screenshots? you can use http://imgbb.com for this.
Regards,
Eduardo
Offline
Hi Eduardo,
Thank you for your response.
Let me try and explain this through a simple example. Hopefully, you can understand my question then.
Example:
Say I have a function called Test in MS-SQL, it accepts 3 parameters - start_date, end_date and product_id
The function accepts 3 parameters so I create 3 parameters in ReportServer accordingly. start_date and end_date are date parameters and product_id is a datasource parameter.
I call this function in ReportServer, the way I will call it is:
Select * from Test(${start_date},${end_date},$P!(product_id))
Now, when I execute this report in ReportServer, it expects me to pass parameters. I pass one value each for start and end date but for my product_id, I have multiple values to choose from so I choose about 3 values from the list or popup box as follows:
start_date: 2020-07-01
end_date: 2020-07-3
product_id: I choose 1,2 and 3 from the list of ids
After passing in the above values, when I run the report, it gives me an error. It says the function accepts 3 parameters but I am passing 5 parameters. So basically, the multiple values I pass for product_id, it does not allow me to create it as a single string of values to parse later, the value 2 that was passed, was considered as the 4th parameter and value 3 as fifth resulting in error.
I am looking at a solution like the $X{IN,...} but this works well with the Where clause, not when passing as an argument to a function.
Hope you understood my question? If yes, please advise of a solution, else will try and send images to explain.
Regards,
Aiden
P.S: Please note it works well if I pass single values to each of the expected parameter.
Offline
Hi Aiden,
thanks for the details. We will look this in ticket RS-4373. I will update here when we have more information.
Regards,
Eduardo
Offline
Noted, thanks Eduardo!
Offline
Hi Aiden,
a question: what would you expect exactly in your function when you select 1, 2, 3 ?
something like:
Select * from Test('start_date','end_date','1,2,3') ,
so you would expect a string containing '1,2,3' separated by commas ?
and what exactly do you get currently? I suppose something like:
Select * from Test('start_date','end_date',1,2,3)
is this correct? Can you pls check?
Regards,
Eduardo
Offline
Hi Eduardo,
Thank you for your response!
That is correct, I expect this - Select * from Test('start_date','end_date','1,2,3') but end up getting this: Select * from Test('start_date','end_date',1,2,3) because of which a function that expects 3 parameters in this example results in having more then 3 parameters and it gives an error.
Regards,
Aiden
Offline
Hi Aiden,
what happens if you write this query:
Select * from Test('start_date','end_date','$P!(product_id)')
note the ' ' surrounding the product_id parameter
Regards,
Eduardo
Offline
Hi Eduardo,
I tried few things to find a solution but nothing worked.
With regards the query you mentioned, it gives the following error:
"The report could not be executed: Could not increment cursor on resultset."
Regards,
Aiden
Offline