Benjamin's blog

SQL & Inventory tips, tricks, and random thoughts (generally geared toward the Configuration Manager Admin)

Adding Summaries to ConfigMgr 2012 SSRS Reports

Adding Summaries to ConfigMgr 2012 SSRS Reports

  • Comments 3
  • Likes

The ConfigMgr 2007 reports have a record count at the top of the reports. The reports in ConfigMgr 2012 do not. I'll be walking through the steps of how to add this to your 2012 reports. Additionally, if you're unfamiliar with SSRS or "Report Builder" this will be a good basic tutorial.

First, as an example of what I mean from the 2007 reports I'm going to use the report named "Computers where physical memory has changed" (because this is the screenshot I was given from a 2007 report and for no other reason :) ). As a reference, here is the top of this report in 2007; I've highlighted the portion we'll be adding to the CM12 report:

image

SSRS reports can be edited in Visual Studio and is generally where I create mine. However, this may not be an option and could be a bit overwhelming for the newbie. Luckily, we have a more simple and free option available – Report Builder.

To start the editing of a report you'll need to install Report Builder. Don't worry, this is simple and fast…this can be installed from your server simply by trying to edit a report. To do that:

If you're in the console you can right click on a report and select "Edit".

image

If you've navigated to the SSRS report site you can click on the "Open Menu" arrow on a report and choose "Edit in Report Builder".

image

This will initiate the Report Builder install. Just click "Run" and the report will open up in Report Builder as soon as the install is complete.

image

image

The top portion of the report is going to have the style and design consistent across all the out of box reports. Besides the logo portion at the top of the report there will be a text box for the report title and then a table that contains the "Description" (in 2007 this was the "Comment" portion). We're going to leave all that stuff alone and only alter the table that contains the actual data to be returned.

The easiest way to find the "data table" is to select the first column name you recognize as the output. The column headings will have "<<Expr>>" shown because the titles are locale specific and therefore use an expression to retrieve the correct title based on the locale. So, the first column name you'll recognize will a) not have the expression abbreviation and b) be the name of a column in the database. In our example this is "Name0":

image

Once this field is selected you can see the outline of the entire table – what I'm calling the "data table". Because we don't want to mess with the column headings we're going to insert a new row above them (remember, these are the fields with "<<Expr>>"). To do that, select the first row of the table on the far left so that the entire row is selected. In that same place right click and go to "Insert Row" –> "Above".

image

To match the 2007 report as closely as possible we'll choose to put the summary in the far right column in the newly created row. It is pretty clear that the summary in these reports is nothing more than a record count. This means we could pretty much create a count of any of the fields that we know won't be NULL. For example, in this report we could just take a count of the field "Name0". We can enter this formula/aggregate directly in the cell by typing "[Count(Name0)]". Or we could use the Expression builder to help us build the syntax. To use the expression builder right click on the cell and choose "Expression".

image

The expression builder is a great way to build the expression and get help as well. To build this expression we'll go to "Common Functions" –> "Aggregate" in the "Category" window (bottom left side) and then double click on "Count" in the "Item" window (bottom middle). This entered "Count(" into the expression value window (top). Now, we can go to "Fields (DataSet0)" and double click on "Name0" which is displayed in the "Values" window (bottom right side). This appended "Fields!Name0.Value" to the expression. All we need to do now is close the "Count" function with a parenthesis and choose "Ok".

image

Now, when you run the report you'll get the record count! However, if you're following along you may have noticed another aggregate when you added "Count". It was called "CountRows". This is going to be a good one to use in order to have one simple expression that can be used across all reports regardless of the columns that are returned as well as needing to know which of the columns won't be NULL. So, I am going to update the expression (and suggest that you do the same) to use "=CountRows()". Running this report I show:

image

Pretty cool right? However, if you're like me, you want to format that number and probably add some text to it as well. Formatting is pretty simple. You can simply right click on the field and choose "Text Box Properties…". Then go to the "Number" 'tab' and format the number however you want; I'm going to choose 0 decimal places and to use the comma as the "1000 separator" (I'll let you look into the "Use regional formatting" option if you so desire):

image

image

You say, "That's nice and all but I want to make this look like the 2007 report!" Well, okay, let's do that. We'll make it say "(Number of Records: <row count formatted>)". To do that we'll need to add some text and align the text to the right side. Again, this is all pretty easy…however, if you want to keep the number formatted it does start to make the expression look a little scarier. No fear though, you can copy/paste the expression we're going to use for all your reports so you don't need to sweat it.

The formula will use the ampersand to concatenate the text, the "CStr" function to convert the number to string (to avoid a datatype mismatch), and the "Format" function to format the row count (to use the thousands separator and have 0 decimals). I've tried to highlight them in the screenshot to help. The formula you can use (copy/paste) is:

="(Number of Records: " & CStr(Format(CountRows(), "#,##0")) & ")"

image

And, after selecting "right alignment" in "Paragraph" on the ribbon it should look like the 2007 report. Naturally, you could also set the background color of the field/row to whatever you want as well – which I suppose would make it look more like the 2007 reports. That can be done in the text box properties under the "Fill" tab. I'll let you experiment with that one. :)

And…as a bonus, if you really wanted to make it look like the 2007 reports you could add the timestamp of the report execution time as well. There is a built-in function called "ExecutionTime" that you can use for that (the full formula used in the expression is "=Globals!ExecutionTime"). Here's my final report:

image

One last note…once you hit "Save" in Report Builder the rdl (report file) will be updated and the report will always look this way. However, if you ever re-deploy the reporting point or do an upgrade these custom changes will most likely be overwritten by the default out of box reports you started out with. So, I'd make sure to save a copy to your source control or to "Save As" the report to a custom folder which you can create via SSRS.

Happy Report Building!

Here is the rdl file I updated if you would like a reference.

Comments
  • lovely

  • Nice... good one Benjamin

  • Great topic for something I didn't even know I was missing... now I want to update all our reports. :)

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment