I came across the need to test whether or not ASP.NET Web API would work with SQL Server Reporting Services (SSRS) 2008 recently. ASP.NET Web API can push XML, and if you write clever enough reports, you can take advantage of this XML when needed. I figured I'd share this little tidbit with you all in case you ever had the burning question of how to do it.

So how do you do it? Well it's pretty simple, but requires some configuration of your Web API. Web API will  NOT transmit XML to SSRS properly. It will ALWAYS pass JSON to SSRS, because the SSRS web client library for retrieving XML doesn’t specify the content type, or if it does, it just allows the default (JSON) to come in instead of XML. So, you need to approach changing your Web API configuration slightly to make this work. There is at least one way to do this, and I’m going to show you here.

Note that this method will make your entire Web API project return XML, as it removes the JSON formatter. You can actually clear all formatters if you wanted, but in this case we on

You will need to configure your Web API to always transmit XML to the caller. You can do that this way in your Global.asax (in VB.NET):

Sub Application_Start()
    AreaRegistration.RegisterAllAreas()

    WebApiConfig.Register(GlobalConfiguration.Configuration)
    FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters)
    RouteConfig.RegisterRoutes(RouteTable.Routes)
    BundleConfig.RegisterBundles(BundleTable.Bundles)

    GlobalConfiguration.Configuration.Formatters.Remove( _

      GlobalConfiguration.Configuration.Formatters.JsonFormatter)
End Sub

As you can see in the last line for Application_Start(), you simply remove the formatter JsonFormatter from the global formatters and you’re good to go. SSRS 2008 will always look at the data and see XML, not JSON.

posted on Monday, January 21, 2013 11:05 AM

Comments

No comments posted yet.

Post A Comment
Title:
Name:
Email:
Comment:
Verification: