Archives for: May 2009

As mentioned earlier in several posts on my blog the R2 add-on for ConfigMgr delivers integration with Microsoft SQL Server Reporting Services 2008 and 2005 (SSRS). This enables administrators and managers to generate built-in or custom reports with relevant data across the entire enterprise.

The top reasons to use SSRS with ConfigMgr are:
- the ability to configure timeouts on a report basis
- the ability to export reports to PDF, Excel etc
- to create custom reports with SQL Report Builder or BI Development Studio
- the ability to easily import and export SSRS reports
- the use to enable report caching
- the use to enable snapshots
- to enable report subscriptions to file-share or e-mail

Reporting site-system roles
By default all (more than 380) built-in reports are installed and stored in the ConfigMgr database. But to be able to run reports a Reporting Point- or Reporting Service Point site-system role needs to be configured.

The Reporting Service Point role is used for integration with SSRS and so only available when you’re running the R2 version of ConfigMgr. When this role is configured and you access all reports from the SSRS window there is no need to install the regular Reporting Point role.

When you want to run the reports from the console you need a Reporting Point which is configured as the default Reporting Point.

Adding the Reporting Service Point role
Perform the following steps in the ConfigMgr console to install the Reporting Service Point role:

1. Navigate to and expand Site Systems located under Site Database / Site Management / sitecode – site name / Site Settings

2. Right-click the appropriate server and from the menu select ‘New roles’. The New Site Roles Wizard will start.

3. In the General screen click Next

4. In the System Role Selection screen select Reporting Service Point and click Next

5. Type a name for the Report Folder and click Next

During the installation of this role ConfigMgr will query WMI to locate SSRS instances. The folder is installed on the first instance SSRS found by ConfigMgr.

6. In the Summary screen click Next

7. When the installation is finished click Close to close the Wizard

Configuring the Reporting Service Point role
When the role is added successfully you can see a new node under Reporting in the console named; Reporting Services with the site system under it. In my example this is server SR002.

After the role is added it needs to be configured with a so called Data Source. The Data Source is in this case the database of ConfigMgr. Perform the following steps from the ConfigMgr console to configure the datasource:

1. In the console navigate and expand Reporting Services located under Site Database / Computer Management / Reporting

2. Right-click the server and select Properties

3. In the server properties dialog box click the Data Source Settings tab

4. In the Server name text box enter the name and SQL Server instance of the server that is hosting the ConfigMgr database

5. In the Database name text box enter the name of the database. Default this is SMS_sitecode

6. To test the connection click the Test button

7. When the test is successful click OK to close the dialog box.

When the test was unsuccessful you probably need to configure authentication settings under the Data Source Authentication tab.

Upload built-in Reports
Now that the role is added and a Data Source is configured we can upload the built-in reports to SSRS to make them available through SSRS.

Perform the following steps from the ConfigMgr console to upload the built-in Reports:

1. In the console navigate and expand Reporting Services located under Site Database / Computer Management / Reporting

2. Right-click the server and select Copy Reports to Reporting Services. The Copy Reports Wizard will start.

3. In the Data Source Settings screen click Next to accept the earlier configured Data Source or provide an additional Data Source and Database name

4. In the Data Source Authentication screen click Next to accept the earlier configured Data Source Authentication settings or provide additional settings

5. In the Select Reports screen check the boxes of the reports which should be uploaded to SSRS. When done click Next

6. In the Security screen click Next to inherit the settings configured on the site system.
If you want to configure other settings uncheck the box ‘Inheriting rights from parent object’

7. In the summary screen verify your settings and click Next

8. The reports will now be uploaded to SSRS, when this is done click Next followed by Close to close the wizard.

Verify Reports in SSRS
After uploading the reports we can check them in SSRS. From your workstation or server browse to the following URL: http://SSRSservername/reports

At the home page of SSRS you will see a link with the name of your report folder. In my case the name is ConfigMgr_PC1, when clicking this link you will see a similar page as displayed below:

As you can see all reports are stored in folders to group them. These same folders are also located in the ConfigMgr console. You can also drag and drop reports in folders. Be aware that when deleting a folder also the reports will be deleted!

But you probably also think; what is that GUID / SSID kind of thing in the upper left corner. Well this is the data source, clicking on it you can configure the source, the connection string and security settings.

Remove that GUID from the page
Many SSRS users don’t like to see the Data Source in the home page of the reporting folder. Therefore this is often hide. To hide the Data Source perform the following steps from your ConfigMgr Report folder home page:

1. At the Report folder page click the Data Source GUID

2. On the Data Source properties page select Hide in List and click Apply

So now it is gone forever! No just kidding when you want to unhide it you can perform the same steps as described above you only need to click Show Details to see it again.

Generating Reports
Reports can now be generated in two very simple ways. One way is to browse in the Report folder through the SSRS homepage.

The other way is to generate the reports right from the ConfigMgr console. To generate a report perform the following steps from the ConfigMgr console:

1. In the console navigate and select All Reports located under Site Database / Computer Management / Reporting / Reporting Services / Report Server

2. In the All Reports folder right-click the report you want to generate and select Run

3. Provide the information required for the report and click View Report

Custom Reports
Although ConfigMgr comes with a lot of built-in reports it might not meet your needs. When this is the case you can build your own custom reports and upload these to SSRS as well.

There are several applications / tools which allows you to build custom reports:
- SQL report builder 2008 2.0
- SQL Server Business Intelligence development studio
- SQL 2005 report builder

In my opinion the integration is great. It delivers a lot of new possibilities and it is very easy to set up. In the next version of ConfigMgr, SSRS is the only option for reporting.

Enjoy!