Hello All, ConfigNinja here to write about Reports in System Center 2012 R2 Configuration Manager. One of the topics that I like the most is the creation of reports, at the beginning of the month we released the Creating Custom Reports by Using SQL Server Views in System Center 2012 Configuration Manager.
This documentation is split into three sections:
- SQL Server Views in System Center 2012 Configuration Manager – Provides a detailed list of the views contained in the Configuration Manager SQL database.
- Working with Reports in System Center 2012 Configuration Manager – Provides information and exercises about how to understand and create Configuration Manager reports by using SQL statements.
- Technical Reference for SQL Server Views in System Center 2012 Configuration Manager – Provides sample queries and SQL Server information that can help you to create your own Configuration Manager reports. Also provides a list of the built-in Configuration Manager Reports that you can use, or use as the basis for your own reports.
My Notebook (See something rare)
What I would like to do is go over and explain some of the steps to create a simple report.
As pre-requirement, we need to have the Reporting Point Installed on the Primary Site server or Central Administration Site.
To do this you can follow the following steps outlined on this TechNet Article:
Once you have installed and validated the Reporting Point, browse to the reporting point url http://localhost/reports and ensure you can see the new folder ConfigMgr_XXX.
Review the existing reports will be key; there around 400+ Reports make sure you know some of them or always review them before creating a new report. It may be better to customize one of the existing reports than start over with a new report.
Since we to review all the documentation, let’s go ahead and write a simple report in System Center 2012 R2 Configuration Manager.
There are 2 way to accomplish this, the first one will be to create the report from the reporting service url and the second one will be to create the report inside the monitoring workspace in ConfigMgr. Both will have the same end results that will be having that custom report created.
Steps to Create Report
1. Let’s start with the ConfigMgr Console.
2. Open ConfigMgr Console find and click on the Monitoring Workspace.
3. In the Monitoring workspace, expand Reporting and then click Reports.
4. Right click the Reports and click Create Report.
5. On the Information page of the Create Reports Wizard, Configure the following settings as shown on the image below.
6. Select SQL-Based Report, then enter the name of the report. Don’t worry about the name, you can change it later on the Report Builder.
7. Click on Browse and select the path where you want the report to be stored, you can also modify this location later on Report Builder.
8. Click Next, on the Summary Page, review the settings and click Next once ready.
9. Click Close and exit the wizard, once you do this the Report Builder will launch.
10. Wait until it’s ready.
11. Click Run if you have never run it before, this will install the Report Builder on your desktop.
12. Now that we have the Report Builder up and running the first thing will be to create the first Dataset for the report, to create the dataset just right click on Datasets and click Add Data Set.
13. Select either a Shared Dataset or Use a dataset embedded in my report, for this example we are going to select Use a dataset embedded in my report.
14. Click on Query Designer
15. Inside Query Designer find the right view this is why is important to learn about the views in ConfigMgr, The documentation that was made reference on this blog is a section called SQL Server Views in System Center 2012 Configuration Manager.
16. Ensure to read that and select the view you want on the report, for this example we are going to select, and Application Management View called V_Advertisement.
17. Click OK to go back in the data set properties.
18. If you need to customize the T-SQL Statement, you can do this inside the Query Designer if the current statement is good for you go ahead and click OK.
19. Now we have our first data set created in Report Builder; the next step should be to build a table with the filed we need inside the report.
20. Let’s click on Insert, Table, and Insert Table. This will let you choose the location where the table will be as well as the amount of defaults fields.
21. Click on the table field and select the first field that you want on the report, select AdvertisementID as the first field, the second field I selected AdvertisementName and the last ProgramName.
22. Click Run to test your first results, click on Design to return to the Design view and modify the report.
23. Since is your first report, this will not look pretty. It will just display some raw results based on the fields selected if this is not the view you want modify the dataset again until you have found the view that you need for your report.
24. If the Insert Table does not work for you, try Table Wizard and follow the steps.
25. Once you have completed the report the next step is to save it on the Reporting Point, click Save, and this will save the report on the area you selected before. To change this path, click on Save As.
26. You will also have the opportunity to change the Report Name if needed.
27. After 10 minutes you should see it on the folder:
Hope this help you get started on reporting, if you like this blog post I will write a few more post about reporting. For some of you this is very basic, I know but I had a few request for people that wanted just to know the very basic.
Here is a few more blog post I have done about reporting just in case you had missed them:
Santos Martinez – Sr. PFE – ConfigMgr and Databases