Calling an API from SSRS

[DonW]

Hello, all.  I'm struggling with an SSRS report (something I absolutely adore </sarcasm font>).  The datasource of the report is an internal API.  When I create the dataset that uses it, I can get results if I use a hard-coded query (below) but I need to be able to pass parameters and so far everything I've done results in the message "An error has occurred during report processing".  Very helpful.

Nothing shows up in the Error List tab in Visual Studio, so I suspect the problem is on the API side.  It doesn't like what is coming across.

I've tried replacing the dates and database with parameters using the @ format from parameters passed in the dataset definition.  I've tried making the command text dynamic and concatenating it with Parameters!name.Value.ToString.  I've even tried creating the command text in a parameter and calling the parameter as the command text.  So far nothing works.  I plugged the parameters into text boxes to validate the output and everything looks good, but the report apparently isn't passing the query to the API in a format the API understands.

I've googled every way I can think of and got nothing that helps.

Any suggestions or hints?  Thanks!  Don

Command text in the dataset (works when hard-coded):

<Query>

               <Method Name="ReportPotentialMissingBillsForGroup" Namespace="http://<internal info>">

                              <Parameters>

                                             <Parameter Name="firstDateForProcessing">

                                                            <DefaultValue>2018-09-23T00:00:00-04:00</DefaultValue>

                                             </Parameter>

                                             <Parameter Name="groupDatabaseName">

                                                            <DefaultValue>name</DefaultValue>

                                             </Parameter>

                                             <Parameter Name="lastDateForProcessing">

                                                            <DefaultValue>2018-10-23T23:59:59-04:00</DefaultValue>

                                             </Parameter>

                              </Parameters>

               </Method>

               <ElementPath IgnoreNamespaces="true"></ElementPath>

</Query>

[Lance England]

Hi Don,

I found this

https://docs.microsoft.com/en-us/sql/reporting-services/report-data/xml-query-syntax-for-xml-report-data-ssrs?view=sql-server-2017#specifying-query-parameters-for-xml-data

I think you leave the query text as is (meaning, don't try to substitute @param names). But on the DataSet, add parameters with the same names as defined in the query. I would try that with a hard-coded date string (but different than the default so you can tell if it works). If you can get that to work, then its a matter of being able to set the parameter dynamically with the correct format.

In addition, if possible I would run Fiddler or Wireshark when you run the report so you can see exactly what is being sent over the wire.

Good luck!

[Rashid Sajjad]

Its been years since i messed with SSRS but here are some general tips:

1. Think outside the box with SSRS - evaluate all your pre-conceived notions one by one again

2. How you think the order of evaluation is going on is not necessarily how SSRS is evaluating them. In your case, the engine could be parsing the XML before you think its doing so the end resultant XML might not be what you thought it would be.

3. one MAJOR difference i found is that the Preview engine does not behave like a server deployed SSRS engine. I found sub-reports to be very slow on the preview engine vs deployed one, cannot say it would cause a difference in functionality.

4. Not sure if the ReportLog table (in the RS database) captures the run-time RDL (not the design time you can view), is there a way to enable detailed logging so you can see that? I would stress on debugging.

--
You received this message because you are subscribed to the Google Groups "AtlantaMDF" group.
To unsubscribe from this group and stop receiving emails from it, send an email to atlantamdf+...@googlegroups.com.
To post to this group, send email to atlan...@googlegroups.com.
Visit this group at https://groups.google.com/group/atlantamdf.
For more options, visit https://groups.google.com/d/optout.

[Pete Dolan]

hi admin

you hope you doing well

thanks for contacting me

i really was a wonderfull day on your office

i hope we can talk together again

vanessa