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

  • Article

In Part 1 it was mentioned that the dashboard will accept
certain parameters. By default, SRS will list 2 parameters per line.

For the Collection parameter, the dashboard will display the collections name but will pass its Unique ID to the datasets in a variable called ColID. 

To create this parameter, a dataset needs to be created. To create the dataset, a data source is required.

Creating the Data Source

  1. In the Report Data pane right click Data Sources--> Click Add Data Source
    1. The Data Source Properties window will appear
  2. Change the Name to CM
  3. Select Use a connection embedded in my report
  4. Select Microsoft SQL Server as the connection type
  5. Click the Build… button
  6. Enter the Server name to the SQL server
  7. Select the ConfigMgr database
  8. Click Test Connection to ensure that a connection could be established
  9. Click OK
  10. Click OK

Creating the All_Collections Data Set

  1. In the Report Data pane right click Datasets --> Click Add Dataset
    1. The Dataset Properties window will appear
  2. Change the Name to All_Collections
  3. Select Use a dataset embedded in my report
  4. Select the CM Data source
  5. In the Query box enter the following:
    select distinct c.CollectionID, c.Name from v_Collection c order by c.Name
  6. Click OK

Creating the ColID Parameter 

  1. In the Report Data pane right click the Parameters folder --> Click Add Parameter.
    1. The Report Parameter Properties window will appear
  2. Change the Name to ColID (this is spelled colid)
  3. Change the prompt to Select a collection
  4. Click Available Values
  5. Select Get values from a query from the dataset
  6. Select CollectionID for the Value field
  7. Select Name for the Label field
  8. Click Default Values
  9. Select Specify values
  10. Click the Add button
  11. Enter SMS00001 for the Value
    1. This is the Collection ID for the All Systems collection.
  12. Click OK

Your Report data pane should look like this:

Creating the other parameters

Month Parameters

Although it is possible to create datasets for the other parameters, this demonstration will use static entries.

  1. In the Report Data pane right click the
    Parameters folder --> Click Add Parameter.
  1. The Report Parameter Properties window will appear
  • Change the Name to StartMonth
  • Change the prompt to Select a month that the report will start from
  • Change the Data type to Integer
  • Click Available Values
  • Select Specify values
  • Click the Add button 12 times
  • Enter January for the Label and 1 for the Value
  • Repeat this until you have December for the Label and 12 for the Value
  • Click Default Values
  • Select Specify values
  • Click the Add button
  • Click the fx button
  1. The Expression window will appear
  • Enter =Month(today()) for the expression value
  1. This will get the number value of the month for day the report is run
  • Click OK
  • Click OK 

Repeat these steps for the EndMonth parameter with the prompt of Select a month the report will end with

Year Parameters

Similar to the Month parameter the dashboard will create static year parameters with the starting year having a slight twist to its default value.

  1. In the Report Data pane right click the Parameters folder --> Click Add Parameter.
  1. The Report Parameter Properties window will appear
  • Change the Name to StartYear
  • Change the prompt to Select a year that the report will start from
  • Change the Data type to Integer
  • Click Available Values
  • Select Specify values
  • Click the Add button 10 times
  • Enter 2004 for both the Label and Value
  • Repeat this until you have 2014for both the Label and Value
  1. You may add or remove in more entries if you want to report update older or newer than 2004
  • Click Default Values
  • Select Specify values
  • Click the Add button
  • Click the fx button
  1. The Expression window will appear
  • Enter =Year(today()) -1 to default the report to the year previous to when it is run
  • Click OK
  • Click OK

Repeat these steps for the EndYear parameter with the prompt of Select a year that the report will end with and a default value of =Year(today())

Your Report Data pane should now look like this:

Creating Other Datasets

  1. Similar to how the All_Collections dataset was created, create the All_OperatingSystems Dataset with the query of:
    select distinct c.Name [Collection Name], gsos.Caption0 [Operating System], count(gsos.Caption0) [Number of Systems]
    from v_R_System rs
    JOIN v_FullCollectionMembership fcm on rs.ResourceID = fcm.ResourceID
    JOIN v_Collection c on c.CollectionID = fcm.CollectionID
    JOIN v_GS_OPERATING_SYSTEM gsos on gsos.ResourceID = fcm.ResourceID
    WHERE c.CollectionID = @ColID
    group by c.Name, fcm.collectionID, gsos.Caption0
  2. Create another Dataset called Collection_Details with the query of:
    select distinct c.Name, count(rs.ResourceID) [Devices], (Select Count(LastScanTime) from v_UpdateScanStatus uss where datediff("d",LastScanTime, getdate()) <= 30) [Scanned last 30 Days],(Select Count(LastHWScan) from v_GS_WORKSTATION_STATUS gsws where datediff("d",LastHWScan, getdate()) <= 30) [Inventory last 30 Days]
    from v_FullCollectionMembership fcm
    JOIN v_Collection c on c.CollectionID = fcm.CollectionID
    JOIN v_R_System rs on rs.ResourceID = fcm.ResourceID
    LEFT JOIN v_GS_WORKSTATION_STATUS gsws on gsws.ResourceID = fcm.ResourceID
    where fcm.CollectionID = @ColID
    group by c.Name
  3. Create the final dataset called Software_Update_Details with the query of:
    DECLARE @StartDate datetime, @EndDate datetime
    Set @StartDate = DATEFROMPARTS(@StartYear,@StartMonth,1)
    Set @EndDate = DATEFROMPARTS(@EndYear,@EndMonth,1)
    Select ucs.CI_ID, count(ucs.CI_ID) [Count],
    CASE((status))
    When 3 Then 'Installed' When 2 Then 'Required' Else 'Not Applicable' End as 'Status', BulletinID, ArticleID,
    CASE((IsDeployed))
    When 0 Then 'No' Else 'Yes' End as 'Deployed',
    CASE((ui.IsSuperseded))
    When 0 Then 'No' Else 'Yes' End as 'Superseded',
    CASE((ui.IsExpired))
    When 0 Then 'No' Else 'Yes' End as 'Expired',
    ui.Title, ui.DatePosted, cica.CategoryInstanceName,
    CASE((ui.Severity))
    When 2 Then 'Low'
    When 6 Then 'Moderate'
    When 8 Then 'Important'
    When 10 Then 'Critical'
    Else 'NA' End as 'Severity'
    From v_Update_ComplianceStatusReported ucs
    JOIN v_UpdateInfo ui on ucs.CI_ID = ui.CI_ID
    JOIN v_CICategoryInfo_All cica JOIN v_CategoryInfo ci on cica.CategoryInstanceID = ci.CategoryInstanceID AND cica.CategoryTypeName = 'UpdateClassification' on ucs.CI_ID = cica.CI_ID
    Where resourceid IN(Select ResourceID from v_FullCollectionMembership where CollectionID = @ColID) AND ui.DatePosted BETWEEN @StartDate AND @EndDate AND ucs.status <> 1
    GROUP BY ucs.CI_ID, ucs.status, BulletinID, ArticleID, IsDeployed, ui.Title, DatePosted, cica.CategoryInstanceName, cica.CategoryInstanceID,ui.IsSuperseded, ui.Severity, ui.IsExpired
    ORDER BY ucs.status, BulletinID, ArticleID

Your Report Data pane should now look like this:

 

 

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