Let’s learn how to Create SCCM Custom Report Using Report Builder. If you are working as an SCCM Admin in your organization, you might have been asked to create custom reports according to customer requirements.
As an admin, some create WQL queries, and some prefer to work on SQL Queries and achieve their desired results. Creating SCCM Custom Reports using Report Builder is not an easy task.
NOTE! – If you are interested to learn the new real-time reporting feature of ConfigMgr, read through 👉SCCM CMPivot Architecture Fast Channel Making | ConfigMgr.
Introduction – Create SCCM Custom Report Using Report Builder
This post is intended for the audience who are beginners and have just started working on this tool or for those who have a little bit of experience but haven’t explored this option.
With this post, I assume that you have some basic hands-on SQL Queries that you use in your day-to-day life.
You are not having hands-on SQL Queries! That is not a problem; we always start from the beginning, follow my posts, and soon I will be sharing a lot of different SQL queries which will help you in your tasks, and then you will be able to use report builder for them.
Prerequisites
I assume that you already have a working environment and use MECM/SCCM Reporting Feature. But to start with will give a brief about prerequisites. (Right. It’s better to start with some background)
SQL Server Reporting Service
Before you can use Reporting Feature in Configuration Manager, you must install SQL Server Reporting Services. For detailed Information, follow Install SQL Server Reporting Services.
Reporting Services Point
You need to install and configure the Reporting Services Point role on a new or existing site system server. For detailed Information, follow Installing Reporting Service Point.
Gathering Info and Planning
You should go through the link provided above, gather all the relevant information, and configure accordingly before planning for reporting like Hardware requirements, Supported SQL Version, accounts and permissions, security roles, using Report builder registry settings, etc.
Installing a Report Builder
Report builder is a standalone app that you can install on your local computer. You can install a Report builder either from a web portal or the Microsoft endpoint configuration manager.
- The first time you start Report builder from Reporting Service Web portal it will prompt you as below to get Report Builder.
To start a report builder from the web portal, type the URL of your report server in your web browser; by default, the URL is https://<servername>/reports.
- Once you have the Prompt to Install Report builder when using it the first time, install it.
- You can also Install Report builder by downloading from the link and reading the requirements Downloading Report Builder.
- For detailed information on how to Install a Report builder follow the link Install a Report Builder.
SQL Query and Creating Report in MECM
I am taking the below query as an example to show how we can use it in Report Builder, below query results in providing two columns ‘Hostname’ and their ‘operating systems’ in your environment.
Select v_R_System.Name0 as 'Hostname', v_R_System.Operating_System_Name_and0 as 'Operating System' from v_R_System
Either you can create a Report in MECM and right-click ‘edit,’ which will open a Report Builder, or you can directly click on the Report Builder tab in the web browser.
Creating a MECM Report in Console
Connect to your configuration Manager Console. Navigate to Monitoring>>Reporting>>Reports and click on Create Report.
A new Create Report wizard will open, provide all relevant information, and click on browse.
I prefer to follow a folder structure of everything as an Admin, so you can create a folder inside reports, whatever you feel like. And browse to that path for this report. (as in my case, I have created a folder as a custom report)
Select the folder where the new report should be created using the list below.
Once you click on next and Ok, it will create a report in MECM and open report builder.
Open the Report Builder application to Create SCCM Custom Report.
Using Report Builder
Let’s Get started on how we can customize it.
First, we need to create a Dataset for our SQL query; for that right, click on Dataset and click on ‘Add dataset.’
Dataset Properties wizard will open, and you can fill in information according to that. Copy and paste your SQL query as shown in the below section, use the dataset embedded in my report and select your data source.
You can click on Query Designer and provide your credential or use current windows credentials to see if the report is running or not.
In this case, we have already tested our query on SQL and know it is running fine, so we can click OK at the bottom to proceed.
If you have good hands-on SQL queries, you can directly write a query as a text in the report builder and check it with Query Designer.
Use query design to RUN and check results.
Make sure the name provided in the query doesn’t contain any spaces; otherwise, you will see the below error.
Once you click on OK, you can see your Dataset has been added; the next step would be to click on Table and Matrix Wizard.
Once you click on Table or Matrix wizard, choose your dataset and click next.
The next step will show you to arrange fields; you can choose accordingly; now, I want to get the value to select both views and drag/drop both views in the Values field.
(When you are trying to create a Pivot, you can use row and column fields with values).
Click on the next and next windows and leave the selected values as default (we can edit them afterward), and then you will see the below screen.
You can drag and drop adjust the size of the matrix page by using your cursor to expand and collapse it, and You can also use the above tools to change the colors, font size, texts accordingly.
Just select the box which you want to edit and do the changes.
Additional Tips – Create SCCM Custom Report Using Report Builder
Right-click on the report builder page and click on Insert>Text box, adjust that text box accordingly and provide the report a name; you can also put your company logo.
You can upload the logo using the report builder to create SCCM custom reports.
According to your requirement, you can also select Title, Header, ||Footer, etc. Click on Run at the top left corner and see how your Report looks:
Check the results of the SCCM custom report with the logo.
If you still feel you want to add and edit this report, click on the icon at the top left corner, save this report and then click on the report; it will again move you to the first screen.
Conclusion
Reporting itself is a vast topic, and there is n number of customizations that can be done. The next article will cover how to get the Pivot, Pie chart, graphs, parameters, few tips for editing.
Happy Learning! Customize your reports and Impress your Customer.
Author
Hi, My Name is Ankit Shukla, and I have been working in IT since 2012. For the last 7 years, I have been working on SCCM/MECM. In these years have worked on different features of this tool, including Migration/designing the infrastructure/OSD/Custom SQL Reporting/Client-Side troubleshooting, and others. With my Blog Posts, you will find my experience on the issues which I have faced and solutions for them. The major objective of these posts is to reach out to the audience who are working as Support team members as SCCM Admins in their organizations.
This is a good tutorial. I have a question. I have region based collections. I would like to add them to a basic report I am building. What is the best way to go about this? I keep looking for a way to tie location based collections to my reports.
You can check below post to understand how you can declare a certain collection for a SQL query.
https://forum.howtomanagedevices.com/endpointmanager/configuration-manager/simple-query-like-this-select-from-win32reg_addremoveprograms/
Are there additional parts to this tutorial? (SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1)
Thank you for well-written tutorial.
Hello Sam,
Thanks for appreciating the article, yes i am currently drafting the part 2, will publish it soon.