SQL Reporting Services Integration with System Center Configuration Manager 2007 R2

SQL Reporting Services Integration with System Center Configuration Manager 2007 R2

  • Comments 15
  • Likes

[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-9b37-a4c3a128d126/

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:

  1. Ability to export reports to any other formats like Word, Excel, PDF etc.
  2. Ability to create report subscriptions that can be scheduled to run at specific times and send out reports to interested people. A good user scenario around this would be to create a report subscription for the Software Updates reports and schedule them to run late on Tuesday night or early Wednesday morning after all the "patch Tuesday" updates are applied to all systems.
  3. Report authoring experience is very much enhanced with the tools that come with SQL Reporting Services like SQL Report Designer. You could either create report models or create SQL-based reports and run them off of the SQL Reporting Server.
  4. Timeouts can be configured on a per-report basis depending on which reports potentially return large amounts of data.
  5. Since the reports are standard SQL Reporting Services reports, they can be easily imported and exported from one SQL Reporting server to another.
  6. A common request from customers is to be able to run reports off of a Configuration Manager database replica before enabling them on the production environment. This is a gem of a functionality that can be easily accomplished by simply making the data source for the reports point to any valid Configuration Manager database; in this case point the data source of the reports to the database replica and once they have been verified just change the data source to point to the actual production database. This proves to be very useful for benchmarking environments.
  7. Report branding is another frequently requested functionality by many customers. This commonly entails customizing the look and feel of reports by changing fonts, font sizes, custom logos etc. With the ability to create custom reports using SQL Reporting Services, customers can now apply their own report branding to the reports.
  8. SQL Reporting Services provides the functionality to enable report caching to facilitate lower execution times on subsequent report execution requests. The cache timeout value can be configured appropriately depending on how often you expect the report data to change.
  9. Report snapshots that are an alternative to report caching and can be scheduled to execute at specific times. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database, and shows the data and layout that were current for the report at the time the snapshot was created.

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:

  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.

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.

 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • One of the big advances in ConfigMgr R2 has to be the use of SQL Reporting Services (SRS), for reporting

  • One of the big advances in ConfigMgr R2 has to be the use of SQL Reporting Services (SRS), for reporting

  • Posts on the new System Center Configuration Manager Team Blog - The official blog of the Microsoft System

  • Posts on the New System Center Configuration Manager Team Blog Feed: Configuration Manager Writers -

  • Will this work with SQL Reporting Services integrated with SharePoint?

  • Sorry for the wee bit late reply since I wanted to verify this first. I knew that this is not directly supported in R2 but was investigating a way to do this manually. As I said, this is not directly supported in the R2 release. However there is a manual way  to do this. Basically the steps to do this are as follows:

    a) Run the copy reports wizard to copy over the reports to any default reporting server (by default reporting server I mean a reporting server that is not configured in sharepoint integrated mode)

    b) Now edit the report and download the report rdl files using SQL Reporting Services Report Manager

    c) No go ahead and upload these rdl files to the sharepoint integrated reporting server.

    I verified that it works but didnt run through all the reports; just made sure that the infrastructure can be setup and the reports can be uploaded to the sharepoint site and made to run. Please let me know if you would be interested in more details and I can dig out or provide some sample code that would make this process a lot more simpler.

  • 此文翻译自System Center Configuration Manager 研发团队博客,英文原文请参见 SQL Reporting Services Integration with System

  • 此文翻译自System Center Configuration Manager 研发团队博客,英文原文请参见 SQL Reporting Services Integration with System

  • I wanted to add to Bhaskar's great information about SRS that the documentation library for Configuration Manager 2007 has been updated with further information about SQL Reporting Services: This documentation has been has been updated with information about creating Reporting Services Report models and now includes a procedure for creating advanced models using multiple views in the Configuration Manager database. Additionally, the procedure for deploying report models for use in the Configuration Manager console has been revised so that the instructions are more suitable for a variety of environments.

    The SQL Reporting Services content can be found at http://technet.microsoft.com/en-us/library/cc431393.aspx

  • Hello,

    I have a dedicated SSRS NLB cluster. Can I use this for SCOM 2007 R2 ? For SCOM 2007 RTM/SP1 this seemed not to be the case.

    Please clarify at ronald.top "at" topautomation.nl

    Thanks!

  • Hello,

    What is(are) the security/permissions needed in SRS for:

    - Administration: Copy from reports to Reporting Service

    - User: Run Report in Reporting Services

    Thanks,

    Dom

  • o    How to grant access to reporting server:

    http://msdn.microsoft.com/en-us/library/ms156034(SQL.90).aspx

  • Hi,

      In a large SCCM site where should the SRSS server be located?

    We have a central and 6 Primary servers. Many secondary sites under each Primary.The central site uses a separate SQL database for SCCM.

    I think I shouldn't put SRSS\IIS on the same server as the sccm database. Should I create a new site system under central site just for SRSS.

    Please help.

    Thanks

  • If you are looking for better xml reporting software, please take a look at Windward's Xml Reports. With Windward you design reports in Microsoft Word, Excel, or PowerPoint so report design is a lot faster and easier - and non-programmers can design reports.

    xml reports

    www.windwardreports.com/xml-reports.htm

  • Just a correction: You stated under the prerequisite that :

    Pre-requisites:  Any machine having a valid SQL Reporting Server 2005/2008 instance running on it.

    Whereas it has to be on a Default Instance, I had an issue where I found that one cannot install the ConfigMgr SRS on a named instance and if you do it'll not work.

    Please clarify if that's not true and you can install it on the named instance.

    Thanks

    Anurag