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.
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.
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.
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.
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.
You should have in the left side of the screen new columns in your Dataset.
Now Go to the Insert tab, and click Table> Table Wizard.
Choose the default Dataset (Dataset1) and Next.
on the next screen Choose the layout, just click Next.
Choose any style you like form the Choose a style page, click Finish.
Now you should have the table on your report. Feel free to adjust its location to the center of the page.
You can also insert a text box to type the report title.
Now click Run and you will have your report ready to view.
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..
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”)
Your report will look like this now:
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..
On the Placeholder Properties, under the Value field, press the fx icon.
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.
You can also add an index to your report table. Just go to the designer again, click on the table border> Insert Column >Left
Now right click on the value cell and click Expression..
On the expression value, just type =rownumber(nothing)
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”)
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: