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?
- 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…
You Rock Dude
Thank you Dear 😉
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.
Hi Vino ! – I think, if you want to go in deep in SSRS custom reports for SCCM 2012 and SCCM 2007 then please start from some of the Training videos youtube creating custom reports SSRS
Regards
Anoop
Regards
Anoop
Hi Anoop,
WHere are these reports stored? How can we back them up?
Thanks
Sana
I don’t remember out of my head now. Can you try searching *.RDL files?
Anoop
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??
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
Well, feel free to develop a solution to include a filter. I didn’t get time to research that part yet. And share it with the community here to help each other!