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

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.

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.

• 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 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

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.

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 folder as a custom report)

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

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

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

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

• Make sure the name provided in the query doesn’t contain any spaces otherwise you will see 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 on next.

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

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

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

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

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.

• 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:

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

References

• https://docs.microsoft.com/en-us/sql/reporting-services/report-builder-tutorials?view=sql-server-ver15
• https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-report-builder?view=sql-server-ver15
• https://docs.microsoft.com/en-us/sql/reporting-services/report-builder/start-report-builder?view=sql-server-ver15
• https://docs.microsoft.com/en-us/mem/configmgr/core/servers/manage/configuring-reporting