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

by

If you are working as an SCCM Admin in your organization, you might have been asked to create custom reports (Create Custom Report Using Report Builder) according to customer requirements. As an admin, some create WQL queries and some prefer to work on SQL Queries and achieve their desired results.

NOTE! – If you are interested to learn new real-time reporting feature of ConfigMgr, read through 👉SCCM CMPivot Architecture Fast Channel Making | ConfigMgr.

Introduction

This post is intended for the audience who are beginners and just started working on this tool or for those who are having a little bit of experience but haven’t explored this option. With this post, I am assuming that you are having some basic hands-on SQL Queries which you use in your day to day life.

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 able to use report builder for them.


Prerequisites

I am assuming that you already have a working environment and using MECM/SCCM Reporting Feature. But to start with will give a brief about prerequisites. (Right Its better to start with some background)

SQL Server Reporting Service

Before you can use Reporting Feature in Configuration Manager you will need to 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.

1E Nomad

Gathering Info and Planning

You should go through the link provided above and 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 from 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.
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
SCCM Create 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
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
SCCM Create 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 reading the requirements Downloading Report Builder.
  • For detailed information on how to Install a Report builder follow 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
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
SCCM Create 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 Report Builder tab in the web browser.

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

Creating a MECM Report in Console

  • Connect to your configuration Manager Console.
  • Navigate to Monitoring>>Reporting>>Reports and click on Create Report.
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
  • 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 folder as a custom report)
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 2
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 27
  • Once you click on next and Ok, it will create report in MECM and open report builder.
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 3
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 28
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 customise 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 information according to that. Copy and paste your SQL query as shown in below section, use 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.
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 4
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 29
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 5
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 30
  • Make sure the name provided in the query doesn’t contain any spaces otherwise you will see 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 on 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, as of now I want to get the value to so just select both views and drag/drop both views in the Values field.
    • (When you are trying to create a Pivot then 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 next and next windows and leave the selected values as default what they are (we can edit them afterward) and then you will see 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 expand and collapse it, You can also use the above tools to change the colors, font size, texts accordingly.
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 6
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 31
  • Just select the box which you want to edit and do the changes.
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 7
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 32

Additional Tips – SCCM Create 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 report a name, you can also put your company logo.
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 8
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 33
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 9
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 34
  • You can also select Title, Header, ||Footer, etc according to your requirement.
  • Click on Run at the top left corner and see how your Report looks:
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 10
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 35
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 11
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 36
  • 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.
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 12
SCCM Create Custom Report Using Report Builder | ConfigMgr | Part 1 37

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.

References