How to use a collection filter in a report?

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
How to use a collection filter in a report?

Answer

There is more than one method and we are only presenting one of them here.

1. Create a parameter of an item picker type and then select the collections class.
2. Add a correlative sub-query with the corresponding collection filter in it to the report SQL.
3. Then export the report and add a default collection to the XML file.
4. And last import the report back into the Notification Server.

Step 1: Create a parameter of an item picker type and select a collection class.

Once you have completed initial report without the collection filter.

• Open the report to edit again and move to the bottom of the page.
• Select New Parameter.
• In the top box called Name, you will enter the parameter name you wish to pass to the report query (Collection).
• In the dropdown menu called Parameter type, you will select item picker.
• Under Settings, you will enter the text in the Prompt box that will display in the report. Example: Collection.
• Select the Collection class in the Class filter box.
• Click OK.

  

Step 2: Add a correlative sub-query with the corresponding collection filter in it to the report SQL.

Once you have added the collection filter parameter.

• Add in the correlative sub-query to the SQL query.
• You will use the table CollectionMembership to determine the item membership.
• The examples use a predicate in under the clause where of the SQL query to determine membership. This can be computers or users.
• The part you need to add is:

  where [%Guid%] in (
  select ResourceGuid
    from CollectionMembership
   where CollectionGuid = '%Collection%'
  )

Remember that the parameter name will corresponds to the SQL query variable name (Collection).

Simple example 1: This SQL query narrows all computer to a given computer collection.

select [Domain], [Name], [IP Address]
 from vComputer
 where [Guid] in (
        select ResourceGuid
          from CollectionMembership
         where CollectionGuid = '%Collection%'
          )


Simple example 2: This SQL query narrows all of users to a given users collection.

select [Display Name], [Office Location], [Office Telephone], [Email]
  from vUser
where [Guid] in (
        select ResourceGuid
          from CollectionMembership
         where CollectionGuid = '%Collection%'
          )


Step 3: Export the report and add a default collection to the XML file.

Once you have added the needed changes the SQL query, you will need to:

• Export the report by right-clicking the report in the left pane and select Export.
• Open this report which is an XML type file in a text editor.
• You will need to add a line to the file in the item picker section for that parameter. The GUID used for the examples is for all platforms. You can change the GUID to what ever GUID you will need.

  <default><![CDATA[{a39168f4-59f9-423d-a47b-9f0e2a5d3682}]]></default>

• Once you have added the line in the file, save the file.

Some possible collection GUIDs:

eb3a1a12-e1c7-4431-b060-f0333e4e488c: All Computers

a39168f4-59f9-423d-a47b-9f0e2a5d3682: All Platforms

4c31e964-d085-42d8-bfb0-d439b4c61e47: All Package Servers

e3a71b08-1612-44a6-9f71-7d359d5475b4: All Windows Computers

Example 1: This is the section without the needed line.

<parameter type="custom" assemblyName="Altiris.NS.StandardItems, Version=6.0.6074.0, Culture=neutral, PublicKeyToken=d516cb311cfb6e4f" typeName="Altiris.NS.StandardItems.Query.ItemPickerParameter" filterClass="a725fb57-09e1-4e9f-bb13-b4600094cf61" excludeDescendents="False" prompt="True" name="Collection" substituted="true">     <prompt><![CDATA[Collection: ]]></prompt>
 </parameter>


Example 2: This is the section with the needed line.

<parameter type="custom" assemblyName="Altiris.NS.StandardItems, Version=6.0.6074.0, Culture=neutral, PublicKeyToken=d516cb311cfb6e4f" typeName="Altiris.NS.StandardItems.Query.ItemPickerParameter" filterClass="a725fb57-09e1-4e9f-bb13-b4600094cf61" excludeDescendents="False" prompt="True" name="Collection" substituted="true">
      <default><![CDATA[{a39168f4-59f9-423d-a47b-9f0e2a5d3682}]]></default>
      <prompt><![CDATA[Collection: ]]></prompt>
  </parameter>


Note: At this time there is no users interface to add a default collection using this method.

Step 4: The last step is to import the report back into the Notification Server.

When you import a report into Notification Server with an item picker, it has a reference to an assembly in it (assemblyName="Altiris.NS.StandardItems, Version=6.0.6074.0, Culture=neutral, PublicKeyToken=d516cb311cfb6e4f" typeName="Altiris.NS.StandardItems.Query.ItemPickerParameter").

If you import a report into a Notification Server with a different assembly build than the report was built on, it will not import. To import a report with a different assembly build, you will need to change the assemblyName section of the file to reflect the change.