Rest api as sql server reporting service datasource with paramters

Dimitri Vandoorne 1

I would like to use rest api as datasource for ssrs report.

If I use my api (for ex: "https://localhost/products") the it is possible to het the information.

But if i want to use a parameter (for ex: "https://localhost/products?product=123"), how can i define the parameter in my ssrs report?

Thx!

1 answer

AniyaTang-MSFT 12,306 Reputation points Microsoft Vendor

2022-11-22T02:04:33.39+00:00

Hi @Dimitri Vandoorne
Actually, I'm not particularly familiar with the REST API, but I found this link for you: unable-to-pass-in-parameters-to-dataset-using-ssrs-2017-rest-api-feature. It provides a way to pass parameters to Dataset.
Hope this can help you.
Best regards,
Aniya
Please sign in to rate this answer.
Dimitri Vandoorne 1 Reputation point

2022-11-22T08:57:01.223+00:00

Hi AniyaTangMSFT-7891,
I'm not using the SSRS 2017 APi, I want to use a custom rest api as datasource in my report.
So if I use a datasource of type XML, I can use my custom api as long it does not require a parameter.

If I use the method with a parameter, for ex: https://localhost:7201/v1/rproducts?product=123. And I want to set the value of the paremeter ("123") with a parameter from my report.
Then I get an error. Even if I change it to a post method with a bodyrequest as in your example.
This a way, that is working wit a SOAP api, but not with a Rest API.

AniyaTang-MSFT 12,306 Reputation points Microsoft Vendor

2022-11-23T06:45:07.693+00:00

Hi @Dimitri Vandoorne
From what I've searched, for the parameters of the xml data source, the query is not parsed to identify the parameters. To add parameters, you must manually create the parameters through the Parameters page of the Dataset Properties dialog box.
For more details, you can refer to this link: xml-connection-type-ssrs.
Best regards,
Aniya
Sign in to comment