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

Business Intelligence Solution Architecture using Microsoft Technologies

In the last two weeks, I was working on a POC and solution architecture for a client that has a Business Intelligence needs.

I can’t go so much in the details for confidentiality reasons :), but I’ll share a common architecture for a business intelligence solution.

First, to be able to follow up with this post, I suggest to go and search for the following common concepts:

  • Business Intelligence
  • ETL
  • EAI
  • Scorecards
  • KPIs
  • Info cubes
  • OLAP
  • Data marts
  • Data warehouse

I won’t speak so much in here on these concepts, because I’ll ISA in the following posts talk about the POC I had made for the client piece by piece with sample code 🙂

 

Solution Architecture

As you can see in the above image we got a lot of layers and technologies involved, of course not all these technologies and layers required all the time but its need come from the actual solution requirements and business needs.

Below is a very abstract description about each layer in the above diagram:

Presentation layer

Service as the web interface fro the BI solution, it’s a MOSS portal with the Report Center template.

We can use SSRS reports, PerformancePoint Server scorecards, KPI lists and Excel Services to provide the end user with interactive dashboard and BI scenarios. 

Also the SSRS and PerformancePoint Server serves as the core components in storing the report templates, rendering the reports  to the portal and design the reports itself.

We can use the report builder for the SSRS and the Dashboard designer for the PerformancePoint Server.

Business Intelligence layer

The OLAP, SSAS and SQL Server database serves as the BI and data analysis layer.

In this layer all the complex analysis, data warehouse activities and OLAP activities done to provide the multi dimension data to the above layers.

We can use Business Intelligence Development Studio to develop SSAS packages and databases.

Integration layer

Two things we should take care while investing time in this layer EAI and ETL.

For most of the scenarios and as a good practice too, that we use BizTalk for EAI and SSIS for ETL, that’s when we experience the true power of each technology.

We can use Visual Studio to develop both BT Orchestrations and SSIS Packages.

Database layer

SQL Server 2008 is a great tool and so much enhanced to be able to serve data warehousing and BI solutions.

As our data store and DBMS, it will connect the whole solution technologies and make them interact together.

Finally, as I said this was made for a quick POC for a client, so I’ll post in 3 or 4 posts piece by piece of my final solution and in each piece I’ll explain all the related concepts and technologies.

Thanks for my friend Debashish – the data warehousing expert – for his continues support and education 🙂

I hope that helped

Ahmed