Recently I was working on a project which involves creating SSRS reports using a web service which returns dataset as data source, after searching a lot and tries different bites of techniques, here is the final working solution step by step:
Problem definition:
You can’t get the actual values of the returned dataset fields or you are new to XML data sources and you need the step by step guide.
Solution:
Install Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012 from the following link: http://www.microsoft.com/en-us/download/details.aspx?id=36843 .
Once installed you can now see the following project types as below:
Install Fiddler from the following link http://fiddler2.com/get-fiddler (Fiddler helps you to inspect the web service output in real time in case your have no access to the local server which has the service hosted).
Install XMLQuire from the following link http://qutoric.com/xmlquire/ (XMLQuire helps you to get the exact ElemenPath which will be explained soon).
Now, get back to VS and create a new “Report Server Project” and then create a new “Shared Data Source” as shown below:
Replace the “Connection String” with the actual path of your service and supply credentials if required.
Now create a new “Shared Dataset”, select the data source created above and then add the following query:
<Query xmlns=”http://tempuri.org/”>
<SoapAction>http://tempuri.org/GetReportIFTSB07</SoapAction>
<Method Namespace=”http://tempuri.org/” Name=”GetReportIFTSB07″>
<Parameters>
<Parameter Name=”FiscalYear”>
<DefaultValue>2013</DefaultValue>
</Parameter>
<Parameter Name=”Agency”>
<DefaultValue>141</DefaultValue>
</Parameter>
<Parameter Name=”AccountPeriod1″>
<DefaultValue>03</DefaultValue>
</Parameter>
<Parameter Name=”AccountPeriod2″>
<DefaultValue>04</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNameSpace=”True”>*</ElementPath>
</Query>
Modify the query with the method name and required method parameters.
Run the query and get back to Fiddler (Fiddler will keep monitoring all the events while running), click on the web service call to get the XML results as shown below:
Switch to TextView and copy the whole response.
Open XMLQuire and copy the whole XML from previous step into the editor.
Click on the part of the XML response which contains the data as shown below:
Notice in XMLQuire in the XSD pane, it shows an element path like: /soap:Envelope/soap:Body/GetReportIFTSB07Response/GetReportIFTSB07Result/diffgr:diffgram/NewDataSet/Table[1]
Copy and edit the XSD to something like GetReportIFTSB07Response/GetReportIFTSB07Result/diffgr:diffgram/NewDataSet/Table{MINUS_LINE_AMT}
So the final query will be:
<Query xmlns=”http://tempuri.org/”>
<SoapAction>http://tempuri.org/GetReportIFTSB07</SoapAction>
<Method Namespace=”http://tempuri.org/” Name=”GetReportIFTSB07″>
<Parameters>
<Parameter Name=”FiscalYear”>
<DefaultValue>2013</DefaultValue>
</Parameter>
<Parameter Name=”Agency”>
<DefaultValue>141</DefaultValue>
</Parameter>
<Parameter Name=”AccountPeriod1″>
<DefaultValue>03</DefaultValue>
</Parameter>
<Parameter Name=”AccountPeriod2″>
<DefaultValue>04</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNameSpace=”True”>GetReportIFTSB07Response/GetReportIFTSB07Result/diffgr:diffgram/NewDataSet/Table{MINUS_LINE_AMT}</ElementPath>
</Query>
Now you can see the actual data from the dataset instead of the first results.
Notes:
- http://tempuri.org/ is the default xmlns replace it with the one from your web service response.
- You can edit the required fields to be retrieved by modifying the “MINUS_LINE_AMT” within the “ElementPath” to include more fields separated by “,” example <ElementPath IgnoreNameSpace=”True”>GetReportIFTSB07Response/GetReportIFTSB07Result/diffgr:diffgram/NewDataSet/Table{PLUS_LINE_AMT,MINUS_LINE_AMT}</ElementPath>.
- Parameters must be passed as shown in the query to retrieve the right data.
I hope that helped.
Ahmed
