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.
 |
| 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.
 |
| 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.
 |
| 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.
 |
| 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.
 |
| Data Source |
And in the DataSet, from the Query Designer, let’s execute the query and see.
 |
| 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
