Share via

Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 4

  • Article

Creating the Summary Tables

In Part 3 the dataset were created.  We’ll now take the data and place them into the tables and charts.

  1. Click the Insert tab --> click Table --> Insert Table
  2. Draw a table below the title
  1. A default table with 3 columns will appear
  • Hover over the bottom row of the first column
    and click the little notepad icon symbol that appears
  • Select the Dataset --> Select Collection_Details --> Select Name
  • Click Next
  • Add Devices to the next column and the scanned_last_30_days to the last column
  • Right click the top of the last column --> Select Insert Column --> Select Right
  • Add Inventory_last_30_Days to the last column
  • Right click the bottom row in the Devices column
  • Select fx Expression
  • Change the expression from =Fields!Devices.Value to =SUM(Fields!Devices.Value)
  • Repeat this step for the Scanned_last_30_Days
    and Inventory_last_30_Days column
  • Right click the bottom row of any column
  1. Notice the orange bracket in the first column
  • Select Row Group --> Delete Group
  • Select Delete group only--> Click OK
    1. This disables the looping ability of the table and the orange bracket will disappear

The table should look like this:

Press F5 to run the report to see if it is working properly

  1. Create another table and place it under the one just created
  2. Right click the bottom row of any column
  3. Select Row Group --> Delete Group
  4. Select Delete group only --> Click OK
  5. Hover over the bottom row of the first column and click the little notepad icon symbol that appears
  6. Select the Dataset-> Software_Update_Details -->  Title
  7. Hover over the bottom row of the second column and click the little notepad icon symbol that appears
  8. Select Software_Update_Details --> Status
  9. Hover over the bottom row of the third column and click the little notepad icon symbol that appears
  10. Select Software_Update_Details --> Status
  11. Create a new column
  12. Hover over the bottom row of the forth column and click the little notepad icon symbol that appears
  13. Select Software_Update_Details --> Deployed
  14. Hover over the bottom row of the forth column and click the little notepad icon symbol that appears
  15. Create a new column
  16. Select Software_Update_Details --> Deployed
  17. Right click the last row in the first column -->  Select fx Expression
  18. Change the expression to =Count(Fields!Title.Value)
  19. Right click the last row in the second column --> Select fx Expression
  20. Change the Expression to =SUM(IIF(Fields!Status.Value = "Required",1,0))
  21. Right click the last row in the third column --> Select fx Expression
  22. Change the expression to =SUM(IIF(Fields!Status.Value = "Installed",1,0))
  23. Right click the last row in the fourth column --> Select fx Expression
  24. Change the expression to =Sum(IIF(Fields!Deployed.Value = "Yes", 1, 0))
  25. Right click the last row in the fifth column --> Select fx Expression
  26. Change the expression to =Sum(IIF(Fields!Deployed.Value = "No", 1, 0))

The table should look like this:

Press F5 to run the report to see if it is working properly

Creating the Operating System Pie Chart

 

  1. Click the Insert tab --> Click Chart --> Chart Wizard
    1. The New Chart window will appear
  2. Select the All_OperatingSystems dataset
  3. Click Next
  4. Select the Pie Chart
  5. Click Next
  6. Place Operating_System in the Series
  7. Place Number_of_Systems in Values
  8. Click Next
  9. Select the Ocean style
  10. Click Finish
  11. Right click the Pie chart --> Select Show Data Labels
  12. Rename the Chart Title to Operating Systems

Press F5 to run the report to see if it is working properly

Creating the Line Chart

 

  1. Click the Insert tab --> Click Chart --> Chart Wizard
    1. The New Chart window will appear
  2. Select the Software_Update_Details dataset
  3. Click Next
  4. Click the Line Chart
  5. Click Next
  6. Add Count(DatePosted) in Values
  7. Add Status in Series
  8. Add DatePosted in Categories
  9. Click Next
  10. Select the Ocean style
  11. Click Finish
  12. Right click the chart --> Select Show Data Labels
  13. Click off the chart then right click it again
    1. The Chart Data Properties should appear next to it
  14. Under Category groups right click DatePosted
  15. Select Category Group Properties…
  16. Click the Fx button for the Label
  17. Change the Expression to =Format(Fields!DatePosted.Value, "MMM yy")
  18. Click OK
  19. Click the fx button for the group
  20. Change the Expression to =MonthName(Month(Fields!DatePosted.Value))

Press F5 to run the report to see if it is working properly

To make the Required line red follow these steps:

 

  1. Click the View tab

  2. Select Properties

  3. Click off then back on to the chart

  4. In the properties pane under Chart look for CustomPaletteColors

  5. Click the … button for the CustomPaletteColors collection

  6. Change the color in row #1 (it starts at 0) to Red

Press F5 to run the report to see if it is working properly

Creating the Severity Chart

  1. Click the Insert tab --> Click Chart --> Chart Wizard
    1. The New Chart window will appear
  2. Select the Software_Update_Details dataset
  3. Click Next
  4. Click the Column Chart
  5. Click Next
  6. Add Status in Series
  7. Add Severity in Categories
  8. Add Count(Severity) in Values
  9. Click Next
  10. Select the Ocean style
  11. Click Finish
  12. Right click the chart --> Select Show Data Labels

Creating the Category Chart

  1. Click the Insert tab --> Click Chart --> Chart Wizard
    1. The New Chart window will appear
  2. Select the Software_Update_Details dataset
  3. Click Next
  4. Click the Column Chart
  5. Click Next
  6. Add Status in Series
  7. Add Count(CategoryInstanceName) in Values
  8. Add CategoryInstanceName in Categories
  9. Click Next
  10. Select the Ocean style
  11. Click Finish
  12. Right click the chart -->Select Show Data Labels

Creating the Software Update Table

Adding in the list of all applicable updates doesn’t fit in with the concept of a dashboard but it is good information to see.

  1. Create another table and place it under the charts
  2. Hover over the bottom row of the first column and click the little notepad icon symbol that appears
  3. Select the Dataset --> Software_Update_Details --> Count
  4. Continue to add in the columns for each field returned from the dataset

To add sorting to the columns follow these steps:

  1. Right click a column header --> Select Text Box Properties…
  1. The Text Box Properties window will appear
  • Select Interactive Sorting
  • Check Enable interactive sorting on this text box
  • Select the column to be sorted in the Sort by selection
  • Repeat this for each column you want to allow sorting on

Press F5 to run the report to see if it is working properly

You should now have a dashboard to give you a quick view of overall software update status.  Although you may not use this dashboard in its entirety, you can create your own custom queries to satisfy your own requirements and gain more out of one the most powerful but underused  features of ConfigMgr 2012.

 

I purposely didn't update the top right text box.  Based on the steps within this demonstration, see if you can have it display the start month, start year, end month and end year.

Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 1
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 2
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 3
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 4
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 5

Comments

  • Anonymous
    January 01, 2003
    Check out Part 5 for a new query that will only show the status of updates for the members of the selected collection.
  • Anonymous
    January 01, 2003
    I believe you (Paul and Moe) are explaining the same thing.

    In this example the collection should just limit the what updates are being queried on however, the queried view contains the update status for all systems regarding that update.

    To get the results you're looking for the query would need to get the status information from a different view(s), possibly the v_Update_ComplianceStatus and count each status. I'll look into it.
  • Anonymous
    April 03, 2014
    Hi Gary - hope you're still out there! Excellent blog and really useful dashboard. Thank you.
    I have one issue that i can't seem to resolve. In the first table, the count for the Scanned Last 30 Days and Inventoried Last 30 Days is always the same no matter what collection i run the report against. So the total doesn't match the number if devices for the collection. The sums seem to always take 'all systems' as the collection to count against. Did you see this? I've double-checked exactly what I've done and i can't see anything wrong...
  • Anonymous
    April 11, 2014
    The comment has been removed
  • Anonymous
    April 06, 2015
    Not even close on creating the summary table on the second table. When I test the report I get a big fat 0 under the Title column and the other fields are totally blank.