Thursday, October 22, 2015

Using ASP.NET Web API as the Data Source for SSRS Reports

Imagine that you want to grab data for your SQL Server Reporting Service Report from calling an end point in ASP.NET Web API. This is a post to show how you can achieve it.

Let’s start from scratch. I have created ASP.NET Web API application from Visual Studio, and there I have the following controller named EmployeesController.
public class EmployeesController : ApiController
{
    public HttpResponseMessage GetEmployees()
    {
        List<Employee> employees = Employee.GetEmployees();
 
        return this.Request.CreateResponse(HttpStatusCode.OK, employees);
    }
}
 
public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
 
    public static List<Employee> GetEmployees()
    {
        return new List<Employee>()
        {
            new Employee() { Id = 1, FirstName = "Jaliya", LastName = "Udagedara" },
            new Employee() { Id = 2, FirstName = "John", LastName = "Smith" },
            new Employee() { Id = 3, FirstName = "Jane", LastName = "Smith" },
        };
    }
}
In the GetEmployees() action, I am returning a HttpResponseMessage with my List of Employees.

Now let’s create a simple SSRS Report using SQL Server Report Builder. I am opening up the SQL Server Report Builder, and from the Report Data toolbox, I am adding a new Data Source.
image
Report Data
In the Data Source dialog, for the Data Source Name, you can give what ever a name you want. For the Connection Type, I am selecting XML and for the Connection String, I am simply providing my Web API employees end point url.
image
Data Source
I am clicking on OK, and adding a new DataSet. Again for the DataSet Name, you can give any name, and I am selecting the “Use a dataset embedded in my report.” radio button. For the Data Source, I am selecting my previously created Data Source.
image
Data Set
And now I am clicking on the Query Designer. And from there, let’s click on the Execute Query button to see what happens.
image
Query Designer
I am thrown with a error, “Data at the root level is invalid.”. This is basically because since we have used Data source connection type as XML, SSRS is requesting data in XML format. And the Web API is retuning data in JSON format. To resolve the issue, we can simple add following line of code in the Web API Application_Start method.
GlobalConfiguration.Configuration.Formatters.XmlFormatter.MediaTypeMappings.Add(new QueryStringMapping("type", "xml", new MediaTypeHeaderValue("application/xml")));

This will modify the Web API content negotiation by checking if the request has a query string named type and it’s value, set to xml, and then the response will be sent in “application/xml”.

Now let’s modify the Connection String, in the Data source, as follows.
image
Data Source
And in the DataSet, from the Query Designer, let’s execute the query and see.
image
Query Designer
Now we are receiving the data as expected. Once you click on OK, it will notify that the query is blank. Doesn’t matter and let’s click on OK. Now you can see that the fields are generated in the DataSet and next step is to create the report. So that’s about it.

I am uploading the full sample to my OneDrive, and you can try it yourself.


Happy Coding.

Regards,
Jaliya

9 comments:

  1. Awesome man.. saved lot of time for me..

    ReplyDelete
  2. Great work. Thanks for sharing this solution. You should try and get this up on code project. I'm sure it will get a lot more exposure on there.

    ReplyDelete
  3. Do u have a example for subreport with parameters against webapi?

    ReplyDelete
    Replies
    1. I am sorry, I don't. But I am sure the concept is basically the same.

      Delete
    2. This is actually not as straight-forward as it should be, but it is doable. This article has been very helpful for getting started, but a complete solution, as is needed in most cases, would also contain an example of how to send parameters,

      Delete
  4. Also, what if you need to authenticate against the web api service? Is there any way to do that? Nice article though.

    ReplyDelete
  5. Nice article. I have a problem with connecting to the "EmployeesController" from report builder, when it's having [Authorize]. How can we handle these type of scenario in SSRS?

    ReplyDelete
  6. Nice article. I'm having a problem with connecting to the "EmployeesController" in report builder, when it's having [Authorize]. Is there any specific way to set end point URLs need authorization in report builder ?

    ReplyDelete