How to Create SCCM Custom Report RDL file ConfigMgr Endpoint Manager

How to Create SCCM Custom Report RDL file ConfigMgr Endpoint Manager. This is another long pending post. In this post, I will provide a step-by-step guide to creating custom SSRS reports. We need to create an RDL file to create a custom SSRS report.

To create an RDL file, we can use Report Builder 3.0 (if you’re using SQL Server 2008 R2). You can start the Report Builder 3.0 from the SSRS website. Go through the following 19 steps to create a custom report (.RDL file).

How to Create SCCM Custom Report RDL file ConfigMgr Endpoint Manager

There is another post about Creating Custom RBA Enabled Reports in SCCM ConfigMgr 2012 R2, published on 14 th July; more details – http://blogs.technet.com/b/configmgrdogs/archive/2014/07/14/creating-custom-rbac-enabled-reports-in-configmgr-2012-r2.aspx?

  1. Launch Report builder from SSRS website.
RDL file creation for SCCM SSRS Reports How to Create SCCM Custom Report RDL file ConfigMgr Endpoint Manager
Launch Report Builder 3.0 How to Create SCCM Custom Report RDL file ConfigMgr Endpoint Manager

2. To create a new report – Select Table or Matrix Wizard. This will help you to create simple table format reports.

Patch My PC
Create RDL File
Select Table or Matrix Wizard

3. You’ve to an existing Dataset or create a new one. The data set is used for creating a query to columns and rows.

SSRS RDL report
Create new DataSet

4. I will create a new data source for SQL DB connectivity.

Create a New Data Source
Create a New Data Source

5. Connect to existing SQL DB using Data Source connection setting. Create a new connection setting for DATA SOURCE. Click on BUILD to create a new Connection String.

Create a New Data Source using New Connection settings
Create a New Data Source using New Connection settings BUILD New Connection String

6.  In connection properties window. Enter the SQL server\Instance Name. Select the Database from the drop-down menu below. You can use windows authentication to connect to an SQL server and DB. You must have access to the DB. You can test the access using TEST CONNECTION. Or you can directly type in a connection string at STEP 5.

Connection String ==> Data Source=SQLSERVER\INSTANCE;Initial Catalog=CM_CAS

BUILD Connection with SQL Server and DB
BUILD Connection with SQL Server and DB

7. Choose a connection to a data source -> Select DataSource1 and click NEXT

Choose a connection to a data source
Choose a connection to a data source

8. Build a Query to Specify the data you want from the data source. Click on Edit as text to test and design your query.

Build a Query to Specifiy the data you want from the data source
Build a Query to Specify the data you want from the data source

9. Design query from the following window. Run “!” to test your query. Results you can see in the 2nd pane of the window.

Design your Query
Design your Query

10. Arrange fields to group data in rows, columns, or both and choose values to display. Data expands across the page in column groups and down the page in row groups. Use functions such as sum, Avg, and count on the fields in the values box.

Drag and Drop available fields to Values columns

Arrange the fields SSRS report.

Arrange the fields SSRS report
Arrange the fields SSRS report

11. Choose the layout

If you choose to show subtotals and grand totals, you can place them above or below the group. Stepped reports show a hierarchical structure with indented groups in the same column.

Choose the layout for SSRS report
Choose the layout for SSRS report

12. Choose a style 

Styles feature different fonts and colors schemes but do not affect the basic layout. You can customize the style after you finish the wizard.

Styles available by default in Report Builder 3.0:-  1. Corporate, 2. Forest, 3. Generic 4. Mahogany, 5. Ocean, 6. Slate

Styles feature different fonts and colors schemes
Styles feature different fonts and colors schemes.

13. Change or Customize the Title of the SSRS report as you can see below screenshot Click to add a title to change the report heading.

Change or Customize the Title of SSRS report
Change or Customize the Title of SSRS report.

14. Test Run and Preview the created SSRS report before uploading the RDL file to the server.

Test Run and Preview the SSRS report
Test Run and Preview the SSRS report

15. Save created SSRS report as an RDL file.

Save Just created SSRS report as RDL file
Save Just created SSRS report as RDL file

16. Upload the RDL file to SSRS website

Upload the RDL file To SSRS website
Upload the RDL file To SSRS website

17. Upload a report(.rdl), model (.smdl), Shared dataset (.rsd), Report Part (.rsc), or other resource into custom.

Select the RDL File to Upload using the browse button. Also, there is an option to Overwrite the existing report.

Upload RDL file
Upload RDL file

18. SSRS Custom Reports Uploaded Successfully.

SSRS Custom Reports Uploaded
SSRS Custom Reports Uploaded

19. Click on the report and check whether the report is working from the SSRS website.

CUSTOM Report Predefined Maintenance Task
CUSTOM Report Predefined Maintenance Task

Author

Anoop is Microsoft MVP! He is a Solution Architect in enterprise client management with more than 20 years of experience (calculation done in 2021) in IT. He is a blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. E writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc…

9 thoughts on “How to Create SCCM Custom Report RDL file ConfigMgr Endpoint Manager”

  1. Could you please help in understanding the linked reports in SSRS. I need to create a report in which I can click on each value and it drills down and runs another report which gives more details on that.

    Reply
  2. I’ve been creating custom reports for months now using this method, but have just discovered they are not stored in the same place as the default reports on the RSP server. Where are they? I’ve tried searching for .rdl files on the whole server and they cannot be found??

    Reply
  3. Can we add filter.

    Suppose we have 20+ Ad Groups and I want to look compliance for 1 selected AD group then how we will create report like this

    Reply

Leave a Comment

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