Advertisement

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.

RDL file creation for SCCM SSRS Reports

Launch Report Builder 3.0

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

Create RDL File

Select Table or Matrix Wizard

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

SSRS RDL report

Create new DataSet

4.  I’m going to 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 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 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

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

 

Styles feature different fonts and colors schemes

Styles feature different fonts and colors schemes

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.

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 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 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 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 report is working from SSRS website.

CUSTOM Report Predefined Maintenance Task

CUSTOM Report Predefined Maintenance Task

About Author 

Anoop is Microsoft MVP and Veeam Vanguard ! He is a Solution Architect on enterprise client management with more than 13 years of experience (calculation done on the year 2014) in IT. He is Blogger, Speaker and Local User Group Community leader. His main focus is on Device Management technologies like SCCM 2012,Current Branch, Intune. He writes about the technologies like SCCM, SCOM, Windows 10, Azure AD, Microsoft Intune, RMS, Hyper-V etc...

    Find more about me on:
  • googleplus
  • twitter
  • facebook
  • linkedin
  • youtube
Posted in: ConfigMgr (SCCM), SCCM, SCCM 2012, SQL

7 Comments

  1. Bhasker says:

    You Rock Dude

  2. Vino Jose says:

    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.

  3. Sana Kosanam says:

    Hi Anoop,

    WHere are these reports stored? How can we back them up?

    Thanks
    Sana

Leave a Comment and Contact Anoop