Consume web api in SSRS with parameter

Question

I am trying to consume webapi in ssrs ( XML source ).

If will use parameter in the url (for testing purpose ) then its working really fine for example - http://some_xyz_url.com/Api/Report/GetReport?id=7 . so I can consume web api in SSRS

Real problem - I do not know how I can pass parameter to webapi from SSRS. I tried hard but no luck. Also I have searched stackoverflow there is no question near to my requirement.

I am using VS2012, (.net framework 4.0), SQL server 2008 (using Microsoft SQL server report builder)

Any help really appreciated.

I have upload the error message when I will try to pass the parameter from SSRS to webapi.

Updates

I have tried to pass parameter but for some reason the value of parameter is always null, Even after setting the default value. ( see below the attached jpg)

and here is how I am passing the parameter

but still getting 404 ( but I will use hard coded Id , its working fine in browser)

Please advise.

Solution 1

After a long try I was no able to solve this , here is what i follow at the end and it works I can not change web api , so I added a wcf service in the solution which was consuming webapi ( just a wrapper ). i know the wcf wrapper is not wise , but it solve the purpose to pass parameters to the webapi.

and then I consume wcf service in my SSRS application.

Solution 2

Connection String can be a expression. So, you can create a parameter and set your data sourceconnection string to something like that:

="http://some_xyz_url.com/Api/Report/GetReport?id=" & Parameters!ReportParameter1.Value

Solution 3

For an XML Source for the 'Data Source' I have done it with WCF which I would assume is similar to what you are describing if you are wanting to consume a web service for your data you are obtaining and you just want to pass in parameters into the signature of a method call you are making.

Four things when consuming a web service with SSRS:

1. You need to set your Data Source to use XML (looks like you got that part).
2. Your web service must be using basic HTTP as it's binding type.

3. If you are consuming a service the 'Connection String' needs to be the service address. EG for a locally hosted service it would be:

   http: //localhost/Reporting/ReportService.svc
   

   for a service deployed under my default directory with the project named 'Reporting' and a service with the interface of ReportService. I would also test you can get to this service in Visual Studio by trying to hunt for it and ensure it is working. Or if it is discoverable find it with a browser.

4. Once I have this I need to create a 'DataSet' querying this service correctly. MS has a 'query' xml blob that does this. If you have parameters your service will take it is important to list them in the 'Parameters' node.

   <Query>
   <Method Name="GetStateLike" Namespace="http://tempuri.org/">
   <Parameters>
   <Parameter Name="state"></Parameter>
   </Parameters>
   </Method>
   <SoapAction>
   http://tempuri.org/IReportingService/GetStateLike
   </SoapAction>
   </Query>
   

Most of the problem is how delicate it is to consume. I got it to work but deemed it so fragile I did not want to use it in the end for my problems. More on it here too: How to consume a WCF service with SSRS 2008 R2

Comments

[Rajeev Bera]

Thank you for detailed answer, I have already consume WCF service with SSRS 2008 R2 in previous project, but in this we are not using wpf, we have wep api and i am getting problems in consuming it.

[djangojazz]

That is too bad, sorry it handles it differently. I wonder if you upgraded to 2012 if it would make a difference? I know they made small changes in the web service but not really that much on the product designer or report format. Maybe try a lab of SQL 2012 with advanced tools. Sorry I could not be more help.

[Rajeev Bera]

Thank you for all of your help