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

by

Let’s learn how to Create the SCCM Custom Report Using Report Builder. If you are an SCCM Admin in your organization, you might have been asked to create custom reports according to customer requirements.

Some admins 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 easy.

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 you have some basic hands-on SQL Queries that you use daily.

You don’t have hands-on SQL Queries! That is not a problem; we always start from the beginning. Follow my posts, and soon, I will share many different SQL queries that will help you in your tasks. Then, you will be able to use a report builder for them.

NOTE! Read SCCM CMPivot Architecture Fast Channel Making | ConfigMgr to learn ConfigMgr’s new real-time reporting feature.

Patch My PC
Index
Prerequisites
SQL Server Reporting Service
Reporting Services Point
Gathering Info and Planning
Installing a Report Builder
SQL Query and Creating Report in MECM
Creating a MECM Report in the Console
Using Report Builder
Additional Tips – Create SCCM Custom Report Using Report Builder
Conclusion
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Table 1

Prerequisites

I assume you already have a working environment and use the MECM/SCCM Reporting Feature. But to start with, I will briefly describe the prerequisites. (Right. It’s better to start with some background)

SQL Server Reporting Service

Before you can use the Reporting Feature in Configuration Manager, you must install SQL Server Reporting Services. For detailed information, follow the steps in Install SQL Server Reporting Services.

Reporting Services Point

You must install and configure the Reporting Services Point role on a new or existing site system server. For detailed information, follow the Installing Reporting Service Point.

Gathering Info and Planning

You should go through the link above, gather all the relevant information, and configure accordingly before planning for reporting, such as Hardware requirements, Supported SQL Version, accounts and permissions, security roles, using Report builder registry settings, etc.

Installing a Report Builder

A report builder is a standalone app you can install on your local computer. You can install it from a web portal or the Microsoft endpoint configuration manager.

When you first start Report Builder from the Reporting Service Web portal, it will prompt you to download it as below.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.1
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.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 - Fig.2
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.2
  • 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 it from the link and reading the requirements for downloading Report Builder.
  • For detailed information on installing 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; the 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 - Fig.3
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.3

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

Creating a MECM Report in the Console

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

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.5
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.5

A new Create Report Wizard will open, provide all relevant information, and click on browse.

I prefer to follow a folder structure for everything as an admin, so you can create a folder inside reports, whatever you feel like. And browse that path for this Report. (as in my case, I have created a folder as a custom report)

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.6
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.6

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

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.7
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.7

Once you click on Next and OK, a report will be created in MECM, and the report builder will open.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.8
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.8

Open the Report Builder application to Create the SCCM Custom Report.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.9
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.9

Using Report Builder

Let’s Get started on how we can customize it.

First, we need to create a Dataset for our SQL query; to do that, right-click on Dataset and click on ‘Add dataset.’

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.10
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.10

The Dataset Properties wizard will open, and you can fill in the information according to that. Copy and paste your SQL query as shown in the section below, use the Dataset embedded in my Report, and select your data source.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.11
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.11

To see if the Report is running, click Query Designer, provide your credentials, or use your current Windows credentials.

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

Use query design to RUN and check results.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.13
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.13

Please ensure that the name provided in the query doesn’t contain spaces; otherwise, you will see the error below.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.14
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.14

Once you click OK, you can see that your Dataset has been added; the next step is to click on Table and Matrix Wizard.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.15
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.15

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

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.16
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.16

The next step will show you how to arrange fields; you can choose accordingly. Now, I want to get the value to select both views and drag/drop both views into the Values field.

(When you are trying to create a Pivot, you can use row and column fields with values).

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.17
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.17

Click on the next and next windows and leave the selected values as default (we can edit them afterwards). Then, you will see the screen below.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.18
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.18

You can drag and drop to adjust the size of the matrix page by using your cursor to expand and collapse it. You can also use the above tools to change the colours, font size, and text accordingly.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.19
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.19

Just select the box that you want to edit and make the changes.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.20
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.20

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

Using the report builder to create SCCM custom reports, you can upload the logo.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.22
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.22

You can also select Title, Header, ||Footer, etc., according to your requirements. Click on Run at the top left corner and see how your Report looks:

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.23
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.23

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

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.24
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.24

If you still want to add and edit this Report, click on the icon at the top left corner, save it, and then click on the Report; it will move you to the first screen again.

Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 - Fig.25
Create SCCM Custom Report Using Report Builder | ConfigMgr | Part 1 – Fig.25

SCCM Custom Query Samples

In ConfigMgr (specifically in THIS case), “FUNCTIONS” are used rather than “VIEWS” along with datasets within SSRS. For example, to find out the list of applications, you may need to use the FUNCTION called “fn_ListLatestApplicationCIs” and “fn_ListDeploymentTypeCIs” to find Deployment Types.

SELECT    DISTINCT app.Manufacturer, app.DisplayName, app.SoftwareVersion, dt.DisplayName AS DeploymentTypeName, dt.PriorityInLatestApp, dt.Technology,
v_ContentInfo.ContentSource, v_ContentInfo.SourceSize
FROM         dbo.fn_ListDeploymentTypeCIs(1033) AS dt INNER JOIN
dbo.fn_ListLatestApplicationCIs(1033) AS app ON dt.AppModelName = app.ModelName LEFT OUTER JOIN
v_ContentInfo ON dt.ContentId = v_ContentInfo.Content_UniqueID
WHERE     (dt.IsLatest = 1)

Model Name Query Details are given below:

SELECT app.Manufacturer, app.DisplayName, app.SoftwareVersion, dt.DisplayName AS DeploymentTypeName, dt.PriorityInLatestApp, dt.Technology
FROM dbo.fn_ListDeploymentTypeCIs(1033) AS dt INNER JOIN
dbo.fn_ListLatestApplicationCIs(1033) AS app ON dt.AppModelName = app.ModelName
WHERE (dt.IsLatest = 1)

Check out the Latest Application CI Details custom report:

Select Manufacturer, DisplayName, SoftwareVersion, CI_UniqueID, ModelID, SDMPackageVersion, DateCreated, LastModifiedBy from fn_ListLatestApplicationCIs(1033)

Conclusion

Reporting itself is a vast topic, and several customizations can be made. The next article will cover how to get the Pivot, Pie chart, graphs, parameters, and a few tips for editing.

Happy Learning! Customize your reports and Impress your Customer.

We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.

Author

Ankit Shukla, an IT professional with over seven years of experience, has worked on SCCM/MECM since 2012. During this time, he has extensively dealt with various aspects of the tool, such as migration, infrastructure designing, OSD, custom SQL reporting, and client-side troubleshooting. Ankit’s blog posts reflect his hands-on experience addressing issues and providing practical solutions. The primary aim of his posts is to cater to the audience, comprised of support team members and SCCM admins in 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