Building Custom Reports in DPM

The Need:

You need to get detailed reports on your Data Protection Manager. The reports that are included with DPM are not enough and do not contain the data that you need to report on.

The Solution:

There is a way to create custom reports for DPM. I am not a SQL guy and will never claim to be but I was still able to figure out how to generate custom reports for DPM thanks to Microsoft SQL reporting services. Let’s cover the process of doing this.

The DPM Report Views

Views are like tables in SQL in the way they look. Views can be considered virtual tables that pull data from multiple tables. They dynamically generate their data when the view is referenced. Someone typically will build a view for non-SQL users that do not know how to write SQL queries but need to pull data together.

Microsoft has included a bunch of SQL views in the DPM database by default. They included these so that DPM administrators could create custom reports. These Custom Report Views for DPM can be found here: http://technet.microsoft.com/en-us/library/ff399120.aspx . On that link it will also describe what type of data each view will give you in your report. This is what the views look like and where they are stored in SQL management studio.

These views need to be used by SQL Reporting Services to generate the custom DPM reports.

Accessing and Setting up Reporting Services for DPM:

SQL Server Reporting Services (SRS) is a web based tool that resides on a SQL server and can be used to generate reports. These reports can be interactive, can be printed and can be exported into other formats. It is accessed through a web services interface. It gives a user the power to generate custom reports from data stored in SQL databases.

If your DPM is using a local SQL instance you will find the DPM SQL Reporting Services site here: http://NAMEOFYOURDPMSERVER/Reports_MSDPM2010/

The Report Builder is typically what is used to design and run the DPM reports. By default SQL Reporting Services comes with Report Builder 1.0. Report Builder 1.0 cannot access SQL views so that version of the tool will not work. We need to upgrade the Report Builder to versions 2.0 or higher. The latest version is 3.0. In this example we are going to use 2.0. The first step is to download the newer version of Report Builder that you are going to use and install this on your DPM server (or the server that SQL is on.) Then what we need to do in is change the The SQL Reporting Services site Report Builder from opening version 1.0 to opening 2.0. Follow these steps to change this to open up to 2.0.

Setting the default Report Builder

1.Open a web browser and go to the SQL Reporting Services site.

2.On the Home screen click Site Settings in the top right.

3.In the General tab, change the Custom Report Builder launch URL to /ReportBuilder/ReportBuilder_2_0_0_0.application

4.Click Apply

Building a custom DPM Report:

Ok lets go through the steps of creating a custom DPM report using SQL Reporting Services and Report Builder.

1. Go to http://NAMEOFYOURDPMSERVER/Reports_MSDPM2010/

I recommend creating a folder for your custom reports.

a) To do this click on New Folder in the SRS site.

b) Give it a name and click the Ok button.

c) You will now see this listed along with the DPMReports folder. (The DPMReports folder is where the default DPM reports live.)

2. Now click on the new folder that you created.

3. Click on New Data Source. We need to create a connect to the SQL DPM database.

NOTE: This is the typical string to your data source: Data Source=BUCHDPM\MSDPM2010;Initial Catalog=DPMDB

Use the settings in the following screenshot. You also need to make sure either the account you are using has permissions to the DPM database or else you need to provide the credentials of an account that has the proper access. Click Ok when you are done.

4. Now you can build a report. Click on the Report Builder button. This will launch the Report Builder application.

5. In this example we are going to create a Chart based report. Click on Chart.

6. You should see the datasource that you created. Select the datasource to highlight it. Click Next.

7. On this screen is where you can access the DPM views. Expand Views under Database view.

8. In this example we are going to generate a report to show us what computers have the DPM agent installed and the version that is installed. Select the DPM views that contain the data you will need in your report. Click Next.

9. Choose your chart type and click Next.

10. The AvailableFields contain the data that you can add to your chart. To add just drag a field from the available fields area to either Categories, Series, or Values. Click Next.

a. Categories

Lists the fields that are in column groups.

b. Series

Lists the fields that are in row groups.

c. Values

Lists the fields that display as values.

11. Choose your chart style. Click Finish.

12. The Report Build is similar to Microsoft Word so it is easy to work with. The challenging part if getting the data laid out the way you want. This will take some time of playing around with it to get the look you want. Build and customize your report.

13. Click on Run to generate your report. The report may take some time to generate depending on the amount of data you are pulling from SQL. You will see status update as the report is being generated.

14. You will then see the the finished report with the live data.

Building a custom DPM Report:

Now you have your report but do you use this same report so that you don’t have to recreate it every time? No problem you can save the report as a file or on the SRS site. Note that you cannot view these reports in DPM reporting. You can only see these custom reports by opening the report file in Report Builder or on the SRS site. It is more convenient to host these reports on the SRS site. To save the report to the SRS site follow these steps:

1. Click on the Orb in the upper left hand corner. Choose Save As.

2. Save the report to your SRS site. Be sure the your SRS site is listed in the Look in: field and then click Save. It is recommended to save this in the custom folder that we created earlier on our SRS site.

3. Now browse to your SRS site. Click on the custom report folder we created earlier. You will see the report.

4. Click on the new report to view the report in the browser.

To see this report or other reports you create just open them up on the SRS site. The data will be live as the report is connected to your DPM database through the datasource connection. I am sure that a SQL guru could go in and do more with the data and these SQL tools to generate some really cool reports. This information and example is intended to get DPM Administrators who typically are not SQL gurus up to speed on DPM custom reporting. Essentially this should be enough to get you going on creating custom DPM reports using the views as well as give you a basic understanding of SQL Reporting Services.