topic Re: Jan 2020 multi value Param Problem in Report Server

Jan 2020 multi value Param Problem

Anonymous — Tue, 18 Feb 2020 07:03:35 GMT

Hi all,

anyone experienced the following behavior in Jan 2020 update?

I have a multi-value parameter param and another parameter @ParamOpt

in the query for the dataset it looks like:

select * from table where ID = @ParamOpt

when I run the query I get an wired error message, so I checked in the query profiler and there I see

select * from table where ID = N'value1',N'value2'Opt

this didn´t occur before and seems to be a topic in Jan2020 release.

Best regards,

Thomas

Re: Jan 2020 multi value Param Problem

messerjc — Tue, 18 Feb 2020 23:42:11 GMT

I'm not in a position to test on our system, but just curious if you've tried

select * from table where ID = (@ParamOpt)

and if that gives you any different results? I have a vague old memory about parentheses being the proper form? Might not make a difference though...

Re: Jan 2020 multi value Param Problem

Anonymous — Wed, 19 Feb 2020 10:49:21 GMT

No this doesn´t solve the issue. The Params were obviosly replaced or inserted via string replace and there is something different compared to old versions.

Re: Jan 2020 multi value Param Problem

d_gosbell — Thu, 20 Feb 2020 00:10:00 GMT

This is apparently a bug which the product team are aware of and they are working on an updated Jan download. I don't think they have a specific timeline for when this will be available, but they do want to get it out as soon as they can. (so keep checking the date of the relase at https://aka.ms/pbireportserver and see when it changes from Jan 27th)

In the short term the only work around I am aware of is to rename your parameters so the names are not a subset of each other. (eg if you rename param to @Param1 it will no longer "overlap" with the start of @ParamOpt)

Re: Jan 2020 multi value Param Problem

Anonymous — Mon, 02 Mar 2020 15:51:59 GMT

d_gosbell : could you check another issue for me? When editing an subscription, where an old user, who left company already and is erased from AD, I get an error message indicating, that the user was not found. Surprisingly I was not able to find any hints, where the user was involved so I´m struggling in editing the subscriptions at the moment.

Any ideas?

Regards,

Thomas

Re: Jan 2020 multi value Param Problem

d_gosbell — Mon, 02 Mar 2020 23:25:40 GMT

When you edit the subscription check the owner for that subscription. If it is the deleted account then you will have to change this. Any subscriptions owned by deleted or disabled accounts will not run and I don't think you will be able to save edit to these subscriptions without changing the owner account as PBIRS will not be able to validate the owner (this is a security mechanism to prevent people somehow setting up reports to be forwarded to them after they have left a company).

For this reason we usually use special "service" accounts with non-expiring passwords when setting up data driven subscriptions.

Re: Jan 2020 multi value Param Problem

d_gosbell — Wed, 04 Mar 2020 00:57:31 GMT

Microsoft just released an update this issue along with a few others

See here for the change log https://docs.microsoft.com/en-us/power-bi/report-server/changelog

And you can download the update from here https://aka.ms/pbireportserver