Ashutosh Pathak
Blog: http://catchcode.blogspot.com Please mark it as answer if it helps, as clicking on the button can save time of others :)
MCP,MCAD,MCSD,MCTS
I have a look and it seems that it requires XSL transformation. Is there a direct way to consume XML/JSON output from web api into SSRS?
I try with very simple HelloWorld action in Home Controller, it gives output : <string>hello, test</string> through Firefox browser. I set up XML data source with :
http://localhost:40000/api/home/?name=test.
Controller for hello world is :
namespace
MvcApplication1.Controllers
{
publicclassHomeController
: ApiController
{
publicstring
GetHelloWorld(string
name)
{returnstring.Concat("hello,
",
"
", name);
}
}
}
I use XPath query here in report query as follows :
<Query>
<XmlData>
String {}
</XmlData>
</Query>
when I execute the query, query designer gives error : Data at the root level is invalid. Line 2, Position 8.
you can consume the xml but I do not have enough knowledge the way you can consume json in your SSRS report. a typical XML structure could be like below as a query in your SSRS dataset:
Ashutosh Pathak
Blog: http://catchcode.blogspot.com Please mark it as answer if it helps, as clicking on the button can save time of others :)
MCP,MCAD,MCSD,MCTS
Johana123
0 Points
8 Posts
web api data source for reporting services
Nov 19, 2012 02:26 AM|LINK
Hi
Could web api service (for JSON output) feed data to report in reporting services?
THanks.
Johana
Ashutosh Pat...
Contributor
5737 Points
1105 Posts
Re: web api data source for reporting services
Nov 19, 2012 05:04 AM|LINK
Hi, you can give a try to below post:
http://spacefold.com/lisa/post/2011/10/14/Actual-Factual-JSON-data-in-SSRS.aspx
Blog: http://catchcode.blogspot.com
Please mark it as answer if it helps, as clicking on the button can save time of others :)
MCP,MCAD,MCSD,MCTS
Johana123
0 Points
8 Posts
Re: web api data source for reporting services
Nov 20, 2012 03:56 AM|LINK
Hi Ashutosh
Thanks for the link.
I have a look and it seems that it requires XSL transformation. Is there a direct way to consume XML/JSON output from web api into SSRS?
I try with very simple HelloWorld action in Home Controller, it gives output : <string>hello, test</string> through Firefox browser. I set up XML data source with : http://localhost:40000/api/home/?name=test.
Controller for hello world is :
namespace
MvcApplication1.Controllers
{
public class HomeController : ApiController
{
public string GetHelloWorld(string name)
{return string.Concat("hello, ", " ", name);
}
}
}
I use XPath query here in report query as follows :
<Query>
<XmlData>
String {}
</XmlData>
</Query>
when I execute the query, query designer gives error : Data at the root level is invalid. Line 2, Position 8.
Any idea?
Ashutosh Pat...
Contributor
5737 Points
1105 Posts
Re: web api data source for reporting services
Nov 20, 2012 05:35 AM|LINK
you can consume the xml but I do not have enough knowledge the way you can consume json in your SSRS report. a typical XML structure could be like below as a query in your SSRS dataset:
http://beyondrelational.com/modules/2/blogs/109/posts/11173/generating-an-ssrs-report-from-an-xml-datasource.aspx
http://msdn.microsoft.com/en-us/library/ms345251.aspx
Blog: http://catchcode.blogspot.com
Please mark it as answer if it helps, as clicking on the button can save time of others :)
MCP,MCAD,MCSD,MCTS
css-chlukito
Member
412 Points
51 Posts
Re: web api data source for reporting services
Dec 03, 2012 05:29 AM|LINK
Hi Johana,
Currently SSRS not accept JSON format for processing, but you can use WebAPI XML output.
You can use this link to create simple WebApi as testing: http://www.asp.net/web-api/overview/getting-started-with-aspnet-web-api/tutorial-your-first-web-api, then modify file Global.asax.cs like below: (add two line of code that I highligthed in bold)
public class WebApiApplication : System.Web.HttpApplication { protected void Application_Start() { AreaRegistration.RegisterAllAreas(); WebApiConfig.Register(GlobalConfiguration.Configuration); FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters); RouteConfig.RegisterRoutes(RouteTable.Routes); BundleConfig.RegisterBundles(BundleTable.Bundles); GlobalConfiguration.Configuration.Formatters.Clear(); GlobalConfiguration.Configuration.Formatters.Add(new System.Net.Http.Formatting.XmlMediaTypeFormatter()); } }Then create SSRS report with setting as explained by this link: http://spacefold.com/lisa/post/2011/10/14/Actual-Factual-JSON-data-in-SSRS.aspx.
1). Datasource
- Type: XML
- Connection String: http://localhost:xxxx/api/products (Replace "xxxx" with the actual port number.)
2). Query Dataset : ArrayOfProduct {}/Product
Hope this can help.
Please 'Mark as Answer' if this post helps you.