January 27, 2010

Why MS integrated SQL Reporting Service with Configuration Manager 2007 R2?

With Configuration Manager 2007 R2, a new site role called "Reporting Services Point" was introduced 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:

1.Pre-requisites: Any machine having a valid SQL Reporting Server 2005/2008 instance running on it.
2.Run the site role wizard and install the "Reporting Services Point" on the SQL Reporting Server. The site role wizard asks for a root folder name which is basically the folder on the reporting server under which all the reports will be deployed.
3.Once the site role wizard is completed successfully, you should see the server appearing under the Reporting Services node under the Reporting node in the administration console.
4.Right click on the server and launch the "Copy Reports Wizard"
5.Run through the "Copy Reports Wizard" and select all the reports that you want to convert to SQL Reporting Services based reports.
6.The wizard will then go through the selected reports, convert them into SQL Reporting Services based reports and deploy them to the reporting server under the folder specified in step 2. above.
7.The copy reports wizard groups all the reports based on report categories creates a folder for each report category and deploys the reports under the respective report category folder.
8.Once all the reports are deployed, you can see all the report folders in the administration console and run any of the reports from any of the folders. You have the option of running the reports from within the administration console or run the reports directly from SQL Reporting Services using the SQL Report Manager (web UI). The SQL Reporting server report manager URL has the following naming convention:
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

1.Report subscription wizards to create subscriptions for any of the Configuration Manager reports

2.Report authoring tools:

Model based report wizard
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.

SQL Based report wizard
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.

1 comment: