How to Create SCCM Custom Report RDL file

This is another long pending post. In this post, I’m going to provide step by step guide to create a custom SSRS reports. To create custom SSRS report, we need to create RDL file. To create 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 SSRS website. Go through the following 19 steps to create a custom report (.RDL file).

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 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. data set is used for creating a query to columns and rows.

4.  I’m going to 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 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 either use windows authentication to connect to 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 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 besic 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 SSRS report. As you can see in the below screen shot Click to add title to change the report heading.

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

15.  Save created SSRS report as 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 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 report is working from SSRS website.