Create custom SQL report using Report Builder 3.0

I am writing this post to explain step by step process how we can create a custom report in SCCM (System Center Configuration Manager)  using Report Builder 3.0.

The minimum requirement  to create the custom report using Report Builder we need to install the Reporting Service Point Role in SCCM site.

In this blog, I will create a custom report to fetch application installed and its version.

Let’s get started 🙂

1) Open the SCCM Console and navigate to monitoring . GoTo-> Reports->Create Report.

Report.png

2) Provide the name of the report which you want to have it  for this scenario I will name it as “Mayukh – Report to List Installed Applications” . Select the path where the report will reside in SCCM under “Reporting“. You can customise and have a custom folder under Reporting in SCCM .

Report-1.png

3) Click “Next”  , “Next”  , “Finish” . After you close the wizard Internet-Explorer will automatically launch , security warning dialog box will be prompted click “Run” to proceed. If you have installed Report Builder 3.0 it will directly launch the Report Builder where you can directly customize and create a report.

report-2report-3

4) Once the report builder will be launched GoTo-> DataSource -> Click on Properties. We will create a “Datasource” and in case if you have limited admin rights to connect to SQL Database you need to provide your credentials to proceed.

report-4report-5

To make sure your credential provide is correct and you can able to connect to Database instance click on “Test Connection“. You can see the successful message box .

Report-6.png

 

5) Now create a dataset and select the Data Source and click “Test Connection“. Click on “Edit Text ” and paste the SQL query . Click “!” to execute the query and see query works as expected and click “Next”.

report-8report-9report-11

6) Arrange the different fields as shown below and click “Next”. Click next , next and keep all the settings as default .

report-12report-13

7) If you would like to customise and have better look and feel you can design it . Click “Run” to execute the report and check if it works as expected.

Report-14.png

8) If everything looks fine , remember to save the report before you exit.

You can download and run the report directly from below link  :

https://www.dropbox.com/s/i8t1g5uwaqsd255/Mayukh%20-%20Report%20to%20List%20installed%20application.rdl?dl=0

 

 

 

 

 

 

 

 

 

Advertisements

12 thoughts on “Create custom SQL report using Report Builder 3.0

  1. Michael Farber

    I am running a VM lab, with server 2016, SQL 2016 and SCCM 1610.

    I have added reporting role, but when i try to create a report i am getting the following error:
    before you can create or edit report, your computer must run a version of SQL Server Report Builder that matches the version of SQL Server that you use for your report server.

    What can i do to fix it?

    Thank you,

    Like

    1. Hi Michael ,

      I believe you have installed latest version of Report Builder and rightly configured. As “Report Builder isn’t installed when you install SQL Server 2016; you need to download and install it separately.”

      Follow the steps to install report builder :https://msdn.microsoft.com/en-us/library/ms159221.aspx

      The information provided by you seems to be version support issues. Please provide more info in detail like SQL server version and report builder version ?

      Thanks
      Mayukh

      Like

  2. Michael Farber

    SQL Server File version 2015.130.1601.5
    Version 13.0.1601.15

    Not sure where to check the report builder version, but got this from the registry:
    ReportBuilder_2_0_0_0.application

    Like

  3. Michael Farber

    for some reason i am not able to connect to data source using test connection.

    I am %100 sure i have the correct credentials.

    When trying to open your .rdl file, it is trying to connect to http://dc2cortsccmpri/reportserver and is not able to open?

    And i see you are using a SQL query for the report, but where is the query?

    Please help follow this step by step ?

    Thank you,

    Like

    1. Hi Michael ,

      1) I hope you fixed the report builder issue , you should have Report Builder 3.0 latest version.

      2) If you cannot able to connect to datasource , please GoTo-> Datasource-> Properties-> Credentials -> Provide credentials which you are using for your SQL server to connect.

      I have a similar screenshot in step 4. please have a look.

      Thanks
      Mayukh

      Like

  4. Michael Farber

    I am able to connect now. How do you get to step 5?
    Now create a dataset and select the Data Source? Where is that option?
    Thank you

    Like

  5. Michael Farber

    Could you please paste a query that should be used here?
    I am trying to use this:
    select distinct

    r.Name0 ‘Computer Name’, User_Name0 ‘User Name’,v_GS_OPERATING_SYSTEM.Caption0 as OS,
    p.DisplayName0 ‘Microsoft Office Version’,
    p.Version0 as ‘Version’
    from V_R_System r
    join v_GS_OPERATING_SYSTEM ON r.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
    inner join v_ADD_REMOVE_PROGRAMS p
    on r.ResourceID = p.ResourceID
    order by r.Name0
    But getting an error.
    Invalid object name ‘V_R_System’

    Like

  6. Michael Farber

    Dude….i though this is “post to explain step by step process how we can create a custom report in SCCM ”
    I am sorry, but a step by step process is for guys new to this and have never done it before. Maybe its just me, but this is not possible to follow for someone who is doing it for the first time…Maybe if you used a query to create this step by step you could paste it as well?
    Thank you.

    Like

    1. Hi Michael ,

      Really sorry to hear that , Sir all steps are mentioned to create a simple custom report . If the query is not working it depends on SCCM database which will get installed by default when you install it.

      V_R_System – is a view of the table.
      Try to replace V_R_System to SMS_R_System .

      Tip : Replacing V(View) with SMS will fix your issue? That’s the reason I asked you to look your DB tables.

      Thanks
      Mayukh

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s