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.

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

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.

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

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.

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

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

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.

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

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.

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.

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

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.

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

15. Save created SSRS report as an RDL file.

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

18. SSRS Custom Reports Uploaded Successfully.

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

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…