[This post comes to us from Bhaskar Krishnan and gives an overview of the new SQL Reporting Services feature and the benefits it provides over the ASP-based reporting point in Configuration Manager and previous releases.]
Background
Prior to Configuration Manager 2007 R2, the reporting solution provided out of the box was a custom ASP-based solution that included around 384 reports that got installed as part of the site server installation. Running any of the reports required the user to install a role called "Reporting Point" using the site role wizard. The site role wizard would basically create an ASP web site hosted under IIS and would allow users to run any of the reports from the ASP web site. However customers frequently ran into timeout issues with the web page timing out while attempting to render "expensive" reports - by expensive I mean reports returning large amount of rows from the Configuration Manager database.
http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/4c7d2829-3a8f-44d5-9b3...
In addition, the solution offered little flexibility when it came to supporting standard reporting functionalities like the ability to export the reports to various formats, to add custom branding to the reports (by branding I mean changing the look and feel of the reports) and more importantly scheduling the reports to run at specific times and allowing users to subscribe to those reports.
SQL Reporting Services, with its industry standard reporting solution, provided all of the benefits and proved to be a more cost effective and scalable reporting solution for System Center Configuration Manager.
Benefits of using SQL Reporting Services
SQL Reporting Services provides tremendous flexibility in terms of monitoring report execution performance, tuning report execution parameters and a boat load of value add functionality like:
Integration of SQL Reporting Services with Configuration Manager 2007 R2
With Configuration Manager 2007 R2, we introduced a new site role called "Reporting Services Point" that facilitates reporting using SQL Reporting Services 2005/2008. This is accomplished via a conversion wizard that ships with Configuration Manager 2007 R2 and allows the user to convert all the Configuration Manager reports that currently exist on that site server to SQL Reporting Services based reports and deploy them to the SQL Reporting Server.
Site Role Installation and Configuration
The following outlines the overall workflow in getting a SQL Reporting Services based reporting point up and running:
For the default SQL Reporting Server instance the URL to access the report and report folders would be:
http://[ReportServer]/Reports
For named SQL Reporting server instances the URL would be:
http://[ReportServer]/Reports_[InstanceName]
Other functionalities provided within the Configuration Manager administration console
The Configuration Manager 2007 R2 release ships two out-of-the-box report models one for Client Health Reporting and the other for Software Updates Management. The model based report wizard facilitates users to create custom reports using these report models.
The SQL based report wizard facilitates SQL savvy users to specify SQL queries and generate reports off of these queries. The wizard presents the users with a list of all available Configuration Manager database views and the corresponding columns to facilitate users to formulate SQL queries more easily and make the process less prone to errors and typos.
FAQs
1. Can the new Reporting Services role co-exist with the old reporting point on the same site server?
Answer: Yes
2. What are the minimum permissions required to be able to view and run Configuration Manager reports?
Answer: The security model for this feature relies on SQL Reporting Server's role based access model. Basically the user needs Browser privileges to be able to view and run the reports from the reporting server. In addition to having access to the report server the user will also need read access to the Configuration Manager database.
o How to grant access to reporting server:
http://msdn.microsoft.com/en-us/library/ms156034(SQL.90).aspx
o Pre-defined roles:
http://msdn.microsoft.com/en-us/library/ms156465(SQL.90).aspx
3. Does Configuration Manager 2007 R2 support SQL Reporting Services 2008?
Answer: Yes - Configuration Manager 2007 R2 release supports both SQL Reporting Services 2005 and SQL Reporting Services 2008. If you plan to use SQL Reporting Services 2008, you only need one hotfix to avoid an incorrect status message from being bubbled up to the site server. The hotfix details are available here: http://support.microsoft.com/kb/957576/
That is pretty much it for my first overview post and I earnestly look forward to your comments, feedback and queries. If anything particularly interests you from the above post, please let me know and I will be more than happy to provide more specific details in my next post.
-
Bhaskar Krishnan
This posting is provided "AS IS" with no warranties, and confers no rights.