Pulling back groups for use in a report drop-down list (group picker)

There are a couple different ways to include a group picker list in your custom report.  One way is to use the built-in report controls included in the SCOM reporting framework.  Personally, I have found these controls to be tricky to implement and not quite as flexible to use as a universal implementation for any type of object or monitoring data.  This may just be due to my lack of experience with the built-in reporting controls, but I’ve found another way to create a group picker control that allows for more flexibility.

Basically, I create a new “helper” dataset to populate a drop-down list, containing all current and valid groups in the data warehouse.  From there, we can match managed entities with monitoring data, depending on the types of objects and data the report will offer.

Here is my example TSQL to return this dataset.

 /*
 Report Dataset
 ParamHelper_GroupList
 Jonathan Almquist (jonalm@microsoft.com)
 12/17/2010
 */
  
 SELECT DISTINCT vManagedEntity.DisplayName AS 'GroupName'
 FROM  vManagedEntity INNER JOIN
                vManagedEntityManagementGroup ON vManagedEntity.ManagedEntityRowId = vManagedEntityManagementGroup.ManagedEntityRowId
 WHERE (vManagedEntity.ManagedEntityTypeRowId IN
                    (SELECT ManagedEntityTypeRowId
                     FROM   dbo.ManagedEntityDerivedTypeHierarchy
                                        ((SELECT ManagedEntityTypeRowId
                                          FROM   vManagedEntityType
                                          WHERE (ManagedEntityTypeSystemName = 'system.group')), 0) AS ManagedEntityDerivedTypeHierarchy_1)) AND 
                (vManagedEntityManagementGroup.ToDateTime IS NULL)
 ORDER BY 'GroupName'

I hope this helps others get started down the right path when authoring reports…

 

Go to main page to download formatted TSQL scripts for all report dataset samples on my blog.