Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1

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.

Patch My PC

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.

Adaptiva

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.
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1

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.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1
  • 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
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1

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.

Create SCCM Custom Report Using Report Builder
Create SCCM Custom Report Using Report Builder

Creating a MECM Report in Console

Connect to your configuration Manager Console. Navigate to Monitoring>>Reporting>>Reports and click on Create Report.

Create SCCM Custom Report Using Report Builder
Create SCCM Custom Report Using Report Builder

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)

SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
Create SCCM Custom Report Using Report Builder

Select the folder where the new report should be created using the list below.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 1

Once you click on next and Ok, it will create a report in MECM and open report builder.

Create SCCM Custom Report Using Report Builder
Create SCCM Custom Report Using Report Builder

Open the Report Builder application to Create SCCM Custom Report.

SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1

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.’

Add DataSet SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
Add DataSet SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1

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.

Choose Data Source - SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
Choose Data Source – SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1

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.

Create SCCM Custom Report Using Report Builder
Create SCCM Custom Report Using Report Builder

Use query design to RUN and check results.

Create SCCM Custom Report Using Report Builder
Create SCCM Custom Report Using Report Builder

Make sure the name provided in the query doesn’t contain any spaces; otherwise, you will see the below error.

Choose Data Source - SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
Choose Data Source – SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1

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.

Choose Data Source - SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
Choose Data Source – SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1

Once you click on Table or Matrix wizard, choose your dataset and click next.

Choose Data Source - SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
Choose Data Source – SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1

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).

Arrange Fields - Choose Data Source - SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
Arrange Fields – Choose Data Source – SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1

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.

Default Options - Choose Data Source - SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
Default Options – Choose Data Source – SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1

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.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 2
Create SCCM Custom Report Using Report Builder

Just select the box which you want to edit and do the changes.

Create SCCM Custom Report Using Report Builder
Create SCCM Custom Report Using Report Builder

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.

Create SCCM Custom Report Using Report Builder
Create SCCM Custom Report Using Report Builder

You can upload the logo using the report builder to create SCCM custom reports.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 3

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:

Create SCCM Custom Report Using Report Builder
Create SCCM Custom Report Using Report Builder

Check the results of the SCCM custom report with the logo.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 4

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.

Create SCCM Custom Report Using Report Builder
Create SCCM Custom Report Using Report Builder

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.

4 thoughts on “Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1”

  1. 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.

    Reply
  2. Are there additional parts to this tutorial? (SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1)

    Thank you for well-written tutorial.

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.