Creating a basic Config mgr. report with SQL reporting services

Converting the Configuration manager ASP reports to SRS reports (SQL Reporting Services) is a really easy task. Once you want to modify or create new the reports you need to a little more work. I create reports using these applications.

  • SQL Server Report Builder 1.0
  • SQL Server Report Builder 2.0 (requires SQL Server 2008)
  • Business Intelligence Development Studio – BIDS (Visual Studio)

BIDS is the favorit choice as it gives the developer the most choices (creation of datasets etc.). However SQL Server Report Builder 2.0 is the easiest tool learn and doesn’t require a whole lot of SQL knowledge. In this example I will use SQL Server Report Builder 2.0 to create a report that shows a count of my computer models. You can download the product for free at http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en

Configuring SQL Report Builder 2.0 to use the correct SRS

  1. Open SQL report Builder 2.0

    image

  2. Click on the “Office button” and select Options.

    image

  3. Configure the Report server site to match the WEB Service URL setting on the Reporting Services Configuration Manager

    image  

    image

Creating the first SRS report

  1. To establish the connection click on the Table or Matrix icon.

    image

  2. Click Browse

    image

  3. Select the Config mgr. database and click Open.

    image

  4. Select the data source connection string and click Open

    image 

  5. Click Next.

    image 
  6. Select DBO, Views

    image

  7. Select the view V_GS_ComputerSystem and add the fields Manufacturer() and Model(). Click Next when done.

    image 

  8. Drag Manufacturer() to Column report and Model() to Column and to Values. Right click Model() and select Count. Click Next when done.

    image 

  9. Select Stepped subtotals above and click Next.

    image

  10. Choose a style and click Finish.

    image

  11. Customize the report with custom titles etc.

    image

  12. Press F5 or click Run to view the report.

    image
    wauw that’s a nice looking report 🙂

About the Author:

Kent Agerlund
Microsoft Regional Director, Enterprise Mobility MVP. Microsoft Certified Trainer and Principal consultant. I have been working with Enterprise client management since 1992. Co-founder of System Center User Group Denmark in 2009. Certified MCITP: Enterprise Administrator, MCSA+Messaing, and much more. Member of: Microsoft Denmark System Center Partner Expert Team The Danish Technet Influencers program System Center Influencers Program.

One Comment

  1. Michael August 22, 2011 at 8:00 - Reply

    I don’t have this data source connection string into my list of reports. How can I create that or is it by default?

Leave A Comment