6

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.

enter image description here

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)

enter image description here

and here is how I am passing the parameter

enter image description here

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

Please advise.

Rajeev Bera
  • 2,021
  • 1
  • 16
  • 30

4 Answers4

6

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
Retired_User
  • 1,595
  • 11
  • 17
  • I have tried that but no luck, again getting......... The remote server returned an error: (400) Bad Request. The request is invalid.The parameters dictionary contains a null entry for parameter 'Id' of non-nullable type 'System.Int32' for method GetReport(Int32)' An optional parameter must be a reference type, a nullable type, or be declared as an optional parameter. ---------------------------- Failed to execute web request for the specified URL. Bad Request: The remote server returned an error: (400) Bad Request. – Rajeev Bera Nov 06 '13 at 08:50
  • I have add more comments to my question , please have a look . And Thanks for your help. – Rajeev Bera Nov 06 '13 at 08:56
  • from your screenshot, seems that you are missing the = and ", try to use exactly as tiped here: ="http://localhost:5698/api/Report?id=" & Parameters!Id.Value – Retired_User Nov 06 '13 at 12:15
  • I tried but now I am getting - Connection string expressions cannot be evaluated at design time. You must preview or deploy the report to verify the connection. – Rajeev Bera Nov 06 '13 at 12:42
  • 1
    I get the same, and clicking "ok" saves the connection and when I click "run" it works as expected :/ – Retired_User Nov 06 '13 at 14:06
  • Thank you for all of your help , I tried again but not sure whats the root cause , I will do a fresh start to this. Thanks again for your help. – Rajeev Bera Nov 07 '13 at 14:12
  • You're welcome. When the error shows up, are you able to continue? or it won´t let you go any further? – Retired_User Nov 07 '13 at 16:28
  • It works fine, the only catch is before adding dynamic value, generate the fields with static value and add the parameter. Have a look at this post where it is described step by step http://spacefold.com/lisa/post/2011/10/14/Actual-Factual-JSON-data-in-SSRS.aspx – Faiyaz Jun 21 '18 at 06:50
1

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

Community
  • 1
  • 1
djangojazz
  • 14,131
  • 10
  • 56
  • 94
  • 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. – Rajeev Bera Nov 06 '13 at 08:57
  • 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. – djangojazz Nov 06 '13 at 16:46
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.

Rajeev Bera
  • 2,021
  • 1
  • 16
  • 30
0
<Query>
   <Method Name="TransformData" Namespace="http://tempuri.org/">
   <Parameters>
       <Parameter Name="parameters">
           <DefaultValue>[@PARAMETER NAME]</DefaultValue>
       </Parameter>
            --- similarly pass more params here
   </Parameters>
   </Method>
<SoapAction>http://tempuri.org/ControllerORInterface/MethodName
</SoapAction>
   <ElementPath IgnoreNamespaces="true">
TransformDataResponse{}/TransformDataResult{}/diffgram{}/NewDataSet{}/yourdatasetname{all columns returned from service(comma-separated)}
</ElementPath>
</Query>
Harshit
  • 13
  • 4