In this post I would like to Show, how to create a Basic Report with System Center 2012 Configuration Manger SP1. Besides your SCCM 2012 Installation you will need Access to the Report Service and have installed the SQL Report builder. Let’s say you will a simple Report, that Shows the Resource ID, NetBios Name and the Last Hardware Scan of your machines.
Chose “Create Report” and the “Create Report Wizard” appears, where you have to add the Information about your specific report. In my case, I created a new Folder to store the report, I didn’t want to mix up custom and built-in reports.
Complete this wizard, and with clicking on Close, it will ask you to install the SQL Report Builder if it is not already installed. Once the Report builder is started, click on “Table or Matrix:
In the “New Table or Matrix” wizard, Chose to “Create a dataset” and click on next. A similar Screen should appear:
You will be asked for credentials to connect to the report Service. I just used the same Account as I use when I set up the report role:
And if you enter the Username and Password correct, it is possible, that you receive the following error:
The certificate chain was issued by an authority that is not trusted
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.)
Picture: This relies on the used certificate in the SQL Configuration, usually it is a Self-Signed Certificate, which your Client does not trust. I just change the certificate to one of my PKI:
After this, just restart the SQL Server Service:
After this small hurdle, you can start with your report, but let us start with our easy report, add Name and Last Hardware Scan Date to the report. Open the “Views” Tree and navigate to “v_GS_SYSTEM”, select “ResourceID” and “Name0”:
To get the Information about the Hardware Scan navigate to “v_GS_WORKSTATION_STATUS” and add “LastHWScan”:
Now we have added Information from two different tables, and we have to create a Realtionship between the tables. You might already recognized that this could be done easily by using the ResourceID from both tables, click on “Autodetect First” and then on “Add Relationship”:
Add both tables on one side of the join type and double-click on the right section:
In the “Edit Related Fields” wizard click on “Add Field” and chose the “ResourceID:
Click on next, and arrange the fields for getting the report:
Choose your Settings for the report:
The printscreen for choosing a style i didn’t share with you, because it’s secret 😉
Click on Finish, and save the report. Now it is time to open your Report within a Configuration Manager Console:
If you have edited the Design just a Little bit (deleted a row and sized the rows), it could look like this:
Hope this helps.
One thought on “SCCM 2012 – Create Report with SQL Report Builder”
To figure out the certificate issue you can also do following steps:
On the machine with the Report Services installed:
Start>run>mmc>file>add/remove snapins>certificate>Computer Account>Local Computer>Certificates>Personal>Certificates>Issued to: Name of server
Look for a Certificate with a friendly Name “ConfigMgr SQL Server Identification Certificate”. Export that cert.
Then on the machine you running the report builder, import the certificate to the local computer trusted root.
Reconnect to the Report Builder.
Hope that helps.