Visual Studio 2012 Report Project: Using dataset web service data source step by step


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:

image

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:

image

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:

image 

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:

image 

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:

  1. http://tempuri.org/ is the default xmlns replace it with the one from your web service response.
  2. 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>.
  3. Parameters must be passed as shown in the query to retrieve the right data.

I hope that helped.

Ahmed

Advertisements

One thought on “Visual Studio 2012 Report Project: Using dataset web service data source step by step

  1. I see a lot of interesting content on your blog. You have to spend
    a lot of time writing, i know how to save you a lot of
    work, there is a tool that creates unique, google friendly posts in couple of minutes, just search in google – laranita’s free content
    source

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s