Several months ago, I recommended the use of Sql Server Reporting Services instead of the default Sccm reports. Back at the time I said I would give more information about exactly how to do it, and now I finally got the opportunity to reinstall Reporting services and make a step by step document so that those not using the tool for the moment would see that it’s not that complex at the end (at least if you just want basic Sccm reports with a little more style).
One of the added features when you move from the default Sccm 2007 report system to SRSS is that you can customize how your reports look without having to use other tools. While you can ‘duplicate’ reports from Sccm 2007 to SRSS or create reports directly by pointing to your database, Sccm 2012 should make use of SRSS by default for all reports, so knowing your way around the tools I show here will clearly benefit you in the future.
I decided to make a step by step description.
For creating our reports, we will use SQL Server Business Intelligence Development Studio . Don’t be fooled by the name, you won’t have to install some subset of Visual Studio, it is just part of SQL server and you can just add it afterwards if needed.
(Note that in this post, I will use the term Bids to refer to the tool)
The installation of Reporting Services, IIS and SQL server are out of scope but just note that you can distribute the roles on different computers (you might install a virtual server just to test the reports without impacting your SQL environment).
Now that we have all software ready, let’s start.
Launch Bids and you will be have a default project opened (Report Project1)
The first thing to do is to create a Data source that point to your Sccm server db (this is quite simple)
Second point is to create a new report.
Click on Report, Right Click and Choose Add New Report. Select the RDS (data source) that you just created as Shared data source, then click next and you come to the Query Builder.
Now you have the choice of using the builder or copy paste an existing query. I must admit that i prefer to use Sql Management Studio to see how the queries react and how much time they take to process directly on the server, but if you don’t have access to the server, you might as well use the query builder. In this scenario, since we already know what we need, I will just copy-paste the query I posted previously.
!!! Just make sure that the ‘ sign is being used inside of the query, somehow, the post has seen ‘ changed to `. You would get the following error:
Once you have copied the query to the Query window, Select the Report Type as Tabular, and in the Design Table, set the different levels as in the followin screenshot
Choose your report style and select the check box to preview the report.
You should get a result like this
Ok That’s a good start (we already have the information we need, and the Versions can be opened as needed if more detail is needed
Now while still in Design view, you can change the Report Title, suppress the Last Scan subtotals (this is the number of days since the HW inventory has run, to avoid ‘ghost’ machines)
Now still in Design Mode, click on the intersection of Computer and [Arpdisplayname0] rightclick, select Expression and enter CountRows(), this will take care of the subtotals based on ARPDisplayName0 grouping.
(that’s where you can now see the <<expr>> label
Let’s have a look at what we get