SQL Server reporting services..Best advise for integrating with other technologies?

Question

Hi,

I'm looking to implement SQL Server Reporting Services as our standard reporting platform in our company. We were trialing Crystal Reports, but atlas it seems to be plauged by issues.

SQL Server reporting services looks to be a great product, but I have a concern or two.

I have some existing web apps in coldfusion, and the backend is in MySQL. If I move forward with SQL Server reporting services, how should I setup my environment? Is there a JDBC connector or is ODBC the only way to talk to this?

How does it integrate for the user? Will I need to re-authenticate the user to view the reports? Will I need to put a link in coldfusion to link to the Reporting Services system? Is there a way to make it seamless for the user?

Should I port all the backend to SQL Server to fully leverage the SQL Server platform? Should I convert my existing apps to ASP.NET, and make the entire platform SQL Server / ASP.NET?

It's not too bad in the fact that the existing apps / MySQL aren't of a size that is too big to port. So I guess just looking for some best practive advise to see if its ok to use the reporting services component on its own, or if I'm much better to consolidate everything into a Microsoft solution.

Many thanks,

Brett

Answer 1

I think it'll be easier than you think!

• Reporting Services will happily pull reports from any OLEDB or ODBC source, and MySQL has ODBC drivers, so there's no problem getting at your data through an SSRS report.

• You can set up the authentication in a number of ways. If your users are already authenticated in your Windows domain, this will be easier:

  • SSRS needs to know who is viewing the report. It will allow anonymous viewing (if you enable it; it's off by default), but if you're using IE and are logged in to the same domain as the IIS server, it's completely transparent
  • SSRS can then use this identity to connect to the data source, or it can use another identity. This is configurable per report or per data source.

• One thing you could do is embed your reports within iframes in your coldfusion pages. This would make the whole thing seamless. The reports are accessible by sending an appropriately formed URL to the server, so it's quite flexible.

As for changing everything to ASP.NET, you'll really only get benefits from that if you ever need to write (and integrate with) your own code to manage the reporting server, or write custom extensions (data providers, delivery extensions and the like), but in my experience this is so rare as to be not worth considering. Go with what you have for now.