topic Re: Passing parameters from power bi report server to SSRS RDL does not work in Report Server

Passing parameters from power bi report server to SSRS RDL does not work

pbiuser1234 — Thu, 12 May 2022 08:28:44 GMT

Hello,
I'm using ?rp:parameter=value in a URL of SSRS RDL uploaded to the power bi server,
But it doesn't work - the report is not filtered by the passed parameter.

Is it only working with power bi service? not on server,

or maybe I should use Report builder instead of SSRS to build the RDL?
Thanks in advandced

Re: Passing parameters from power bi report server to SSRS RDL does not work

d_gosbell — Thu, 12 May 2022 22:48:10 GMT

It does not matter what tool you use to author the rdl files. The issue is that Report Server uses the Reporting Services syntax for passing parameters via the URL which is slightly different to the Power BI service syntax. If you remove the rp: prefix your urls should work (see https://docs.microsoft.com/en-us/sql/reporting-services/pass-a-report-parameter-within-a-url?view=sql-server-ver15 )

Re: Passing parameters from power bi report server to SSRS RDL does not work

pbiuser1234 — Sun, 15 May 2022 11:17:50 GMT

WOW Amazing!! Thank you so much it works great now with one value passed in the url,
If I need multiple values, is there a way in DAX syntax for some kind of loop? If so do you have an example?
I need to pass multiple values for the parameter "Emloyee" like this:
https://ReportServer/Reports/ReportName?Employee=1&Employee=2
This is my code that works for one value:

PaginatedaReportURL =
var _baseurl = "https://ReportServer/reports/ReportName"
var _selectemployee = SELECTEDVALUE(Query1[Employee])
var _reportparameterEmployee = "?Employee="&_selectemployee
var _result = _baseurl&_reportparameterEmployee
return
_result

Also I want the rdl to open up automaticly in excel so I've added "&rdl:format=excel" to the URL - but this also doesn't work (also tried "&rs:Format=excel" or "&Format=excel"). Maybe syntax also should be different for the server version?

Thanks a lot!

Re: Passing parameters from power bi report server to SSRS RDL does not work

d_gosbell — Sun, 15 May 2022 23:13:44 GMT

pbiuser1234 wrote:

If I need multiple values, is there a way in DAX syntax for some kind of loop?

Yes, you could use the CONCATENATEX function

PaginatedaReportURL =
var _baseurl = "https://ReportServer/reports/ReportName"
var _selectemployee = CONCATENATEX(VALUES(Query1[Employee]), "&Employee=")
var _reportparameterEmployee = "?Employee="&_selectemployee
var _result = _baseurl&_reportparameterEmployee
return
_result

pbiuser1234 wrote:

Also I want the rdl to open up automaticly in excel so I've added "&rdl:format=excel" to the URL - but this also doesn't work (also tried "&rs:Format=excel" or "&Format=excel"). Maybe syntax also should be different for the server version?

The "rs:Format=EXCEL" should get you an xls file, if you use "rs:Format=EXCELOPENXML" it will return an xlsx file. (see https://docs.microsoft.com/en-us/sql/reporting-services/url-access-parameter-reference?view=sql-server-ver15 ) - I don't think the upper/lower case matters

Re: Passing parameters from power bi report server to SSRS RDL does not work

v-henryk-mstf — Mon, 16 May 2022 07:12:39 GMT

Hi pbiuser1234 ,

Whether the advice given by d_gosbell has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

Re: Passing parameters from power bi report server to SSRS RDL does not work

pbiuser1234 — Mon, 16 May 2022 14:12:38 GMT

Thanks for the reply,

About opening an excel file, I tried this code but it doesn't work:

https://<ServerName>/<Reports>/report/<ReportFolderName>/<ReportName>?rs:format=excel

If I change the question mark location anywhere before folder name - it leads me to the folder root. for example:

https://<ServerName>/<Reports>/report/<ReportFolderName>?/<ReportName>&rs:format=excel

What am I doing worong?

I have to say that "rs:embed=true" works fine written like this:

https://<ServerName>/<Reports>/report/<ReportFolderName>/<ReportName>?rs:embed=true

Thanks again

Re: Passing parameters from power bi report server to SSRS RDL does not work

pbiuser1234 — Mon, 16 May 2022 14:57:43 GMT

OK I found the solution 🙂

I relied on another post of yours that I found:

Note that you need to use the Webservice url (/reportserver by default), not the portal url (/reports by default)

https://community.powerbi.com/t5/Report-Server/How-to-export-Excel-file-from-Power-BI-report-server-using-URL/td-p/897605

Thanks again!

Mooving on to check the CONCATENATEX suggestion 🙂

Re: Passing parameters from power bi report server to SSRS RDL does not work

v-henryk-mstf — Tue, 17 May 2022 07:56:04 GMT

Hi pbiuser1234 ,

Thanks for your feedback.🙂

Best Regards,
Henry

Re: Passing parameters from power bi report server to SSRS RDL does not work

pbiuser1234 — Tue, 17 May 2022 10:26:42 GMT

Hi Henry,
So I've used the CONCATENATEX for multiple choice selection and it works great if I choose some of the values. But for all values selected I get an error (don't see any reference in the log files), I'm guessing the problem is the length of the URL.
1. Is there a way to shoten the URL?
2. I'm trying to write a DAX code to return URL with no filters if no selections were made, it doesn't work (returning any result without the IF statement works, also the ISFILTERED statement returns values correctly):
PaginatedaReportURL =
var _baseurl = "https://ReportServer/reports/ReportName"
var _selectemployee = CONCATENATEX(VALUES(Query1[Employee]), "&Employee=")
var _reportparameterEmployee = "?Employee="&_selectemployee
var _result_filterd = _baseurl&_reportparameterEmployee
var _result_all = _baseurl
return
if( ISFILTERED(Query1[Employee])="True", var _result_filterd, var _result_all )

Hope you can help...Thank you

Re: Passing parameters from power bi report server to SSRS RDL does not work

pbiuser1234 — Tue, 17 May 2022 12:27:33 GMT

Hope you can help...Thank you

Re: Passing parameters from power bi report server to SSRS RDL does not work

d_gosbell — Wed, 18 May 2022 02:41:51 GMT

pbiuser1234 wrote:

1. Is there a way to shoten the URL?

Not really, other than doing in IF in your code.

pbiuser1234 wrote:

2. I'm trying to write a DAX code to return URL with no filters if no selections were made,

If you leave the parameter empty then Report Server will want to prompt the user for the parameter value. One option is to have a special value like "<ALL>" to return all the values, but you would have to add logic in your report query to cater for this or configure your report query as an expression. It depends on how you are dealing the multiple values in your report query as to how the logic should be adjusted.

Re: Passing parameters from power bi report server to SSRS RDL does not work

pbiuser1234 — Wed, 18 May 2022 14:39:33 GMT

Hi again:)

I didn't leave the parameter empty, this is the code:

if( ISFILTERED(Query1[Employee])="True", _result_filterd, _result_all )

Why do I get an error for this code?

Thanks

Re: Passing parameters from power bi report server to SSRS RDL does not work

pbiuser1234 — Wed, 18 May 2022 14:56:49 GMT

OK found the problem...wrote "False" instead of False()

I'm new to DAX so learning the hard way I guess...