Config Manager 2012/R2 Build and Customize SQL Reports

See also:

I want to share with you my experience in building a professional SQL report in Configuration Manager 2012/R2. I suppose you have installed the Reporting Point role and you have everything configured for you. I will be creating a report to generate a full hardware inventory for all machines in a nice and organize way.

Now, open the Configuration Manager console> Monitor> Reporting>Reports> Right click> Create Report.

Choose SQL-based Report, and type a name for the report, Click Browse and choose any category for your report. Click Next.

Config Manager 2012 Build and Customize SQL Reports 1

A browser Window will open automatically and hopefully the SQL Server 2012 Report Builder 3.0 will be downloaded and open for you. I am assuming you are using SQL 2012 in this case, but it really does not matter what version you are using. In all cases, you will have the SQL Report Builder open for you. This tool is nothing but a a way for you to choose your SQL Query and customize how the report will look like.

Config Manager 2012 Build and Customize SQL Reports 2

Now right click on the Datasets and click Add Dataset.  You can think of datasets as a temporary SQL database table that contain the result of your SQL query that you are about to enter.

Config Manager 2012 Build and Customize SQL Reports 26

On the Dataset properties page, choose Use a dataset embedded in my report, and in the data source, choose the one created automatically for you with a long name that looks like a GUID, and then past your SQL query. In this example, I am using the SQL query from my previous blog post here.

Config Manager 2012 Build and Customize SQL Reports 4

Now click on the Query Designer and click the execute icon to make sure the query runs without any issues. If all is okay Click Ok twice.

Config Manager 2012 Build and Customize SQL Reports 5

You should have in the left side of the screen new columns in your Dataset.

Config Manager 2012 Build and Customize SQL Reports 6

Now Go to the Insert tab, and click Table> Table Wizard.

Config Manager 2012 Build and Customize SQL Reports 7

Choose the default Dataset (Dataset1) and Next.

Config Manager 2012 Build and Customize SQL Reports 8Now drag all Available Fields  in to the values section. Leave other sections empty.

Config Manager 2012 Build and Customize SQL Reports 9

on the next screen Choose the layout, just click Next.

Config Manager 2012 Build and Customize SQL Reports 10

Choose any style you like form the Choose a style page, click Finish.

Config Manager 2012 Build and Customize SQL Reports 11

Now you should have the table on your report. Feel free to adjust its location to the center of the page.

Config Manager 2012 Build and Customize SQL Reports 27

You can also insert a text box to type the report title.

Config Manager 2012 Build and Customize SQL Reports 13

Now click Run and you will have your report ready to view.

Extra Customization

Add Date header

You can add a Date value below the title of the report to indicate the date in which the report executed on. To do that, go to the Title text box, and right click > Create Placeholder..

Config Manager 2012 Build and Customize SQL Reports 14

in the Placeholder Properties box, in the Value field, enter =Today().

This will automatically generate the date and time value. If you want only to have the date value and not the time, then instead of =today(), you can type =format(cdate(today()),”dd-MM-yyy”)

Config Manager 2012 Build and Customize SQL Reports 15

Your report will look like this now:

Config Manager 2012 Build and Customize SQL Reports 16

Format Date on Column Values

If you have for example a column that produces a date, you can do the same trick by using the format function we have just used. Let us take the BIOSDate field in the report. just right click the value of the [BIOSDate], right click>Placeholder Properties..

Config Manager 2012 Build and Customize SQL Reports 17

On the Placeholder Properties, under the Value field, press the fx icon.

Config Manager 2012 Build and Customize SQL Reports 18The Expression window should look like this:

Config Manager 2012 Build and Customize SQL Reports 19

Now delete the existing expression value and type the following =format(cdate(Fields!BIOSDate.Value),”dd-MM-yyy”).

Now when you run the report, the BIOSData value will show only the date part and not the time part.

Config Manager 2012 Build and Customize SQL Reports 22

Add Index

You can also add an index to your report table. Just go to the designer again, click on the table border> Insert Column >Left

Config Manager 2012 Build and Customize SQL Reports 23

 Now right click on the value cell and click Expression..

Config Manager 2012 Build and Customize SQL Reports 24

 On the expression value, just type  =rownumber(nothing)

Config Manager 2012 Build and Customize SQL Reports 25

 Click Ok and you are done. You now have an index to your report table. Check this YouTube video that describes how to add an index number also.

Add Count of Rows

 Finally, you can add a row count to your report, the same way you added the date of the report field. Just right click anyway in the report title text box, click Create Placeholder.  Click the Fx icon in the Value field, and then type the following expression =count(Fields!Machine.Value, “DataSet1”)

Config Manager 2012 Build and Customize SQL Reports 28

We used the Count() function, and we gave it the name of one column (Machine in this case), and the name of the dataSet that we had created DataSet1

Now when you run the report, you will have the count of machines in the report:

Config Manager 2012 Build and Customize SQL Reports 29

3 comments on “Config Manager 2012/R2 Build and Customize SQL Reports

  1. Pingback: Config Manager 2012 (SCCM) Most Amazing Hardware Inventory SQL Report | Ammar Hasayen - Blog

  2. Pingback: Configuration Manager 2012 and WSUS/SUP – How Windows Updates Works | Ammar Hasayen - Blog

  3. Hi thanks for this amazing report.its so useful for me so i want to add IP address for each machine to this report,can you help me?

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