SQL Server Reporting Services (SSRS) - Tips & Tricks

This article will demo using an XML Data Source type in Reporting Services 2008. We will then consume three different web service methods – simple web service method, parameter based method and a method that returns a dataset. 

Overview:

The Reporting Services, XML Data Provider extension, allows for you to query Web Services directly by parsing the XML structure of the SOAP response. You will need to have working knowledge of the Web Service Namespace, Method, SOAP Action, Parameters, and Schema of the response body. Once we have created our data source we can then specify what data is needed by using the XML Data Provider Query language, from within a dataset. You can learn more about using XML and Web Service Data Sources in Reporting Services here. The code download can be found at the end of this article.

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• Microsoft Visual Studio 2008 installed
• Experience with Reporting Services 2005/2008 
• Experience with ASP.NET and C#
• Experience with the ASP.NET Web Service development

Steps Taken:

1. Create ASP.NET Web Service Project 
2. Define the three methods we'll be using
3. Publish the Web Service
4. Create a new Report Server Project
5. Add a new Data Source the uses the XML type 
6. Create report
7. Add Dataset's - use XML Data Provider Query language
8. Run report

Create ASP.NET Web Service

1) Launch Microsoft Visual Studio 2008

2) Select File >> New >> Web site...

3) Select the ASP.Net Web Service Template

4) For the Location, enter "C:\WSHost" and click OK.

5) Delete the default Service.asmx, and Service.cs from the App_Code folder.

6) Create a New Item... select the Web Service Template and name it "DemoServiceRS.asmx". Your Soultion Browser should look similar:

7) Open the DemoServiceRS.cs file.

8) You will notice a method named HelloWorld(), which returns the string "Hello World". We will use this for our non-parameter based Web Service Method.

9) Copy and paste the following parameter based Web Service Method, under the HelloWorld() Method:

[WebMethod]

public string GetReturnValue(string retval)

{

return retval;

}

You'll notice the [WebMethod] attribute above the method; attaching the WebMethod attribute to a Public method indicates that you want the method exposed as part of the XML Web service. The GetReturnValue will accept a string parameter and return that value - simple.

10) Next, add the following namespaces to your file:

using System.Data;

using System.Data.SqlClient;

This is used for the Dataset we will return in the next method.

11) Copy and paste the following code, under the GetReturnValue(string retval) method:

    [WebMethod]

    public DataSet GetTerritorySales()

    {

        string sqlText = "SELECT [TerritoryID],[Name],[CountryRegionCode],[SalesYTD] FROM [AdventureWorks].[Sales].[SalesTerritory]";

        string sqlConn = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";

        DataSet ds = new DataSet();

        SqlDataAdapter daTerritory = new SqlDataAdapter(sqlText, sqlConn);

        daTerritory.Fill(ds, "TerritorySalesYTD");

        return ds;

    }

12) Save your work.

13) Right click your Solution Name and select "Publish Web Site".

14) The Publish Web Site dialog with prompt you to enter a Target Location... Enter http://localhost/WSHost, or the location you would like to publish this site to.

15) Click OK. Wait fot the site to complete the publishing process.

You have completed developing and publishing your Web Service, which will be used later in the demo.

Create Report Server Project

1) Create a new Report Server Project - I assume you have done this before. If not, please read our previous post Introduction to Report Builder 2.0 Part 2 - Creating a Report.

2) Name the project "DemoReportProject".

Create Shared Data Source

3) Add a new Shared Data Source and enter "AdventureWorksWSDL" for the Name. 

4) Select XML for the type.

5) For the Connection String, enter the location of the Web Service you published earlier in this article. For example, http://localhost/WSHost/DemoServiceRS.asmx. Your Shared Data Source Properties dialog should look similar:

6) From within the left pane, select Credentials.

7) Select the Use Windows Authentication (Integrated Security) option and click OK to close the dialog.

Create Report

1) Add a new report the project and name it "WSQuery.rdl".

Add Dataset's

Non-Parameter Based Query

1) Add a new dataset and name it HelloWorld

2) Within the dataset, select AdventureWorkdWSDL for the Data Source

3) Copy and paste the following script to the Query field:

<Query>
<Method Namespace="http://tempuri.org/" Name="HelloWorld"/>
<SoapAction>http://tempuri.org/HelloWorld</SoapAction>
</Query>

Note: The Method element requires you to pass the Web Service Method to be consumed. Notice the Namespace passed in the example above. You have the option to rename this in your ASP.NET Web Service project. The XML Data Provider will auto-generate SOAP Action by appending Method Name and Namespace. For non-.NET Framework Web services, the SOAP Action may differ and will have to be set explicitly in the query.

4) Click OK.

5) You should see the HelloWorld Dataset in the Report Data pane.

6) Add a Matrix to the report.

7) Drag and drop the HelloWorldResult field to the matrix.

8) Run the report. You should see something similar:

Parameter Based Query

1) Add a new Dataset from the Report Data pane.

2) Name the new Dataset "GetReturnValue".

3) Within the dataset, select AdventureWorkdWSDL for the Data Source.

4) Copy and paste the following code Query field:

<Query>
<SoapAction>http://tempuri.org/GetReturnValue</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetReturnValue">
   <Parameters>
        <Parameter Name="retval" Type="String">
  <DefaultValue>Test</DefaultValue>
 </Parameter>
   </Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>

Note: You will notice a couple of new elements in this query - Parameters and ElementPath. Keep in mind, Parameters are case-sensitive. An ElementPath indicates how to traverse the XML hierarchical nodes and their attributes in the XML data source. If IngnoreNamespaces is set to true, namespaces in the XML ElementPath and the XML document are ignored.

Report Parameter

5) Add a new Parameter; From the Report Data pane, right click the Parameters folder and click Add Parameter...

6) Enter "retval" for the Name value.

7) Enter "Parameter Value:" for the Prompt value.

8) Make sure the Data type is set to Text.

9) Click OK to save Parameter changes.

Dataset Parameter

10) From within the Report Data pane, right click the GetReturnValue dataset and select Dataset Properties.

11) Click the Parameters pane item on the left.

12) Click Add from the Choose Query Parameter values section.

13) Under the Parameter Name column, enter "retval" (without the quotes).

14) Under the Parameter Value column, use the dropdown to select [@retval].

15) Click OK.

Add Matrix

16) Add a Matrix to the report.

17) Drag and drop the GetReturnValueResponse field to the matrix.

Note: If you attempt to run the report, you may not see a rendered value for GetReturnValueResponse, after entering the Parameter Value and clicking View Report.

Common error: The dataset ‘GetReturnValue’ contains a definition for the Field ‘GetReturnValueResponse’. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The dataset ‘GetReturnValue’ contains a definition for the Field ‘GetReturnValueResponse’. The data extension returned an error during reading the field. There is no data for the field at position 2.

Resolution: This is due to the Field Source having the incorrect value. We need to change the Field Source from GetReturnValueResponse to GetReturnValueResult.

18) Under the GetReturnValue dataset, right click GetReturnValueResponse and select Field Properties.

19) Find the GetReturnValue row and change value under the Field Source column to "GetReturnValueResult".

20) Click OK and Save your work.

21) Run the report. You should see something similar:

You will notice the new Parameter field at the top. You may enter any value for this field and it should render to the report.

Return Dataset Query 

1) Add a new Dataset from the Report Data pane.

2) Name the new Dataset "TerritorySalesYTD".

3) Within the dataset, select AdventureWorkdWSDL for the Data Source.

4) Copy and paste the following code to the Query field:

<Query>
     <SoapAction>http://tempuri.org/GetTerritorySales</SoapAction>
     <Method Namespace="http://tempuri.org/" Name="GetTerritorySales"></Method>
     <ElementPath IgnoreNamespaces="true">GetTerritorySalesResponse{}/GetTerritorySalesResult{}/diffgram{}/NewDataSet{}/TerritorySalesYTD{TerritoryID, Name, CountryRegionCode, SalesYTD}</ElementPath>
</Query>

Note: You will notice the ElementPath has some new values. In short, the values passed here will return what fields we want returned from the dataset. You can learn more about XML and Web Service Data Sources here.

Also, if you are uncertain of the XML Schema, you may run this query in the Query Designer, but exculed everything between the <ElementPath IngnoreNamespaces="true"></ElementPath>. This will return the Dataset ID, Field Names, Data Types, etc. Once you have this information you may construct the element path accordingly.

5) Click OK.

6) Add a Matrix to the report.

7) Drag and drop the following fileds to the matrix: CountryRegionCode, Name and SalesYTD.

8) Save your work.

10) Run the report (dont' forget to pass a parameter, from the previous dataset). You should see something similar:

That's all there is to it!

Cheers!

Download code here: 

WSHost.zip (3.43 kb)

DemoReportProject1.zip (32.67 kb)

This is the continuation of Introduction to Report Builder 2.0 Part 1. This article will show you how to create your first report (SalesByFiscalYear) using Report Builder 2.0, and how to pulish the report to your Report Server. This article assumes the following:

• You have installed SQL Server 2008 and Report Builder 2.0 on the computer you are running this example
• You have created a shared data source and pulished the report server (optional). This article will create a data source using an embedded connection, however.
• You have the sample AdventureWorks database installed. You can get the sample database here.

Steps

Open Report Builder

1) From you desktop select Start >> All Programs >> Microsoft SQL Server 2008 Report Builder 2.0 >> Report Builder 2.0

You should see the following:

Configure Report Builder

1) From the upper left corner of Report Builder, click the Red Ribbon and then the Options from the lower left corner.

2) Select the Settings option from the left pane.

3) Enter your default Report Server location and set the other property values as desired. For example: http://localhost/ReportServer Click OK to save your settings and close the dialog box.

Creating a Data Source

1) From the Report Data pane, click New and select Data Source

the data source dialog will open

 Note: You may choose an existing shared data source, if you have previously created one. For this example, we will be using an embedded connection. 

2) Provide a name for your data source. We will use the default value provided in this example.

3) Select "Use a connection embedded in my report" option

4) Select your Connection Type from the drop down. For this example we are using Microsoft SQL Sever.

5) Click the Build button from the right of the "Connection string:" textbox. You will see the Connection Properties dialog below.

6) Enter the same values in the property fields, found in the image above.

1. Test the connection when you are done.
2. If test is successfull, click OK.
3. Click OK to close the Connection Properties window.
4. Click OK to close the Data Source Properties window.
5. You should now see your data source name on the left Report Data pane.

Note: the value in Server Name property is a period "."

Design Report

1) From the middle pane of the report designer click on the Table or Matix icon.

The New Tabe or Matrix window will open.

2) From the Data Source Connection pane, select the data source you created in the previous section Creating a Data Source. Click Next from the lower right corner when you are done.

3) You should see the Design Query view, similar to below. Expand the Sales node and then expand the Views folder. Place a check mark in the vSalesPersonSalesByFiscalYear.

4) From the top toolbar of the Query Designer, click Run Query to test the view. You lower Query Results pane should render results from the view.

5) Click Next from the lower right corner of the Query Designer window.

6) Arrange Fields the following way:

1. add SalesTerritory and FullName to the Row groups, respectively.
2. add ID2002, ID2003 and ID2004 to the ∑ Values

7) Click Next when you are done assigning fields.

8) Keep the next page default values and click Next.

9) Click Finish to complete the wizard.

Configure Properties

 This section we will apply a currency format to our sales field values. After the formatting of the currency values we will give the report a Title and run the report.

1) Place your cursor in the first [SUM(ID2002)] field. Right click on the field and select Text Box Properties. The Text Box Property window will open.

2) From the left pane of the Text Box Properties window, select Number.

3) Under the Category pane, select Currency. Adjust settings as desired.

4) Click OK.

5) Repeat this steps 1-4 for all the SUM fields.

Note: There is an easier way to do this; press and holding down the Ctrl key and select all the SUM fields with your mouse. From properties pane on the right, find the Format property and pass '$'0.00;('$'0.00) for the value.

6) Place your cursor in the "Click to add title" field.

7) Enter "Sales by Fiscal Year".

8) From the top left part of the Ribbon, click Run.

You should see a similar result.

 Publish Report

This section will show you how to publish your report to the Report Server.

1) From the upper right corner of Report Builder 2.0, click on the Save icon.

2) The Save As window will open. Select the folder you want to save the report to.

3) Enter SalesByFiscalYear.rdl for the name of the report.

4) Click Save from the lower left of the dialog.

Your report has been saved and published to the Rerport Server. Take the time to review the report in desing mode and explore the properties for each of the fields. This will help you gain a better understanding on how to format reports in the future.

Next post will discuss using parameters in Report Builder 2.0.

Cheers!

Features specific to Report Builder 2.0 are focused on simplifying the process of creating and editing reports and queries and include the following:

Microsoft Office based Interface and Intuitive Wizards

© 2009 Info-Toad Consulting LLC © 2008 Microsoft Corporation. All rights reserved.