CODEWITHSUNDEEP
reporting-services
Greg
Greg

Reputation: 4035

SSRS: How to use XML document data source with parameters

I am trying to create an SSRS report that has an XML data source, but am stuck.

I have a URL that accepts a parameter (Below, parameter is named Id with value param1) and returns the following XML data:

https://site1/test/GetInfo/param1 or https://site1/test/GetInfo?Id=param1

<Contract xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Dynamics.Ax.Application">
<Id>param1</Id>
<City>Los Angeles</City>
<Country>USA</Country>
<Customer>Customer1</Customer>
<State>WA</State>
<Street>150 Main Street</Street>
<Zip>99999</Zip>
</Contract>

This isn't a web service per se since it simply accepts parameter/query at the URI, so I am believing this to be an XML document.

In SSRS 2016 Report Builder, I did the following:

  1. In my report, I created a Datasource with ConnectionType = XML, and Connection string = https://site1/test/GetInfo
  2. Created a dataset pointing to above data source, with Query type = Text, with the following:
    <Query>
    <Method Namespace="http://schemas.datacontract.org/2004/07/Dynamics.Ax.Application" Name="GetInfo">
    <Parameters>
    <Parameter Name="Id">
    <DefaultValue>DefaultValue1</DefaultValue>
    </Parameter>
    </Parameters>
    </Method> 
    </Query>

Upon clicking OK button to complete the dataset, I get the following error:

Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.

Failed to execute web request for the specified URL.

Method Not Allowed: The remote server returned an error: (405) Method Not Allowed.

The remote server returned an error: (405) Method Not Allowed.

<Error><Message>The requested resource does not support http method 'POST'.</Message></Error>

The Method name in the query maps to the actual method name in the class, and and the operation is a GET method.

Upvotes: 1

Views: 2822

Answers (1)

jt000
jt000

Reputation: 3236

It's been a while since I played w/ the XML Data Provider, but my recollection is that the XML Data Provider will do a POST with a SOAP payload for any URL that doesn't have a file extension of xml (for example, http://www.example.com/mywebservice). Parameters in this case are included in the SOAP request payload. Since you don't have a web service, then your server likely doesn't support the POST method on the HTTP request which is likely why you're getting the 405 Method not allowed error.

If you have an extension of xml in your url, then the XML Data Provider will do a GET request and include the parameters in the query string of the URL (for example, http://www.example.com/mywebservice/myfile.xml?name=Joe).

If you don't have a way to process these parameters on the server, then it may be easier to return the entire XML data and do a table filter.

Or if you have a Power BI Pro account, then you can create a Power BI Dataset from the XML data, create a Power BI Dataset Connection in RB, then filter the data in a DAX query in Power BI Report Builder (note that Power BI Dataset connections are only supported in Power BI Report Builder and Power BI Premium Capacities, not in SSRS or PBIRS).

Upvotes: 2

Related Questions