How to use drop-down lists in a custom report

Products

• IT Management Suite
• Asset Management Solution

Issue/Introduction

How to use drop-down lists in a custom report

Resolution

The following instructions describe how to use drop-down lists in a custom SQL report in the Symantec Management Platform 7.x Console. This includes how to populate the drop-down lists manually and automatically by providing example instructions for an example custom SQL report. For additional information on working with custom reports, refer to the Symantec Management Platform User Guide.

Part 1: Add a Report Parameter 

For this part, two report parameters will be added, "AssetStatus" and "CostCenter". 

Manually Add/Edit Drop-down Values: AssetStatus

Manually adding values for the drop-down list is useful for when there is a small amount of possible values to be used or the values are desired to be enforced for what can be selected by the user.

1. In a Symantec Management Platform Console, click on the Reports button > Reports.
2. Right click on the Reports top-most folder and choose New > Report > SQL Report. Note: If an existing custom SQL report or cloned out of box SQL report is to instead be used, click on its Edit button instead of creating a new SQL report and then skip to step 4.
3. Type a report name for the report in its Name field. For example, "Assets by Status and Cost Center".
4. Click on the Report Parameters tab.
5. Click on the Add button > New Parameter.
6. In the Parameter section, in the Name field, type a name. For example, "AssetStatus".
7. In the Description field, type a description. For example, "Asset Status".
8. If the parameter is to be required, click to enable the Parameter Required check box.
9. In the Type field, choose Basic GUID Parameter. Note: The Default Value and Test Value fields are then populated with a GUID with all zeroes.
10. In the Default Value field, enter the desired starting GUID (optional). For example, "0A0203A5-D2B6-49F1-A53B-5EC31A89437C" for the Active status. Then, when the report is first opened, all Active assets are displayed by default until the user changes the Status drop-down list value and clicks on the Refresh button.
11. In the Value Provider section, in the Name field, choose Drop-down List Value Edit Control.
12. In the Tooltip and Label Text fields, type a tooltip and label text names. For example, both can be "Asset Status".
13. In the Manually Add/Edit Drop-down Values section, click on the "v" expand button to open the section (it is closed by default).
14. In the Display field, type a name. For example, "Active".
15. In the Value field, type a value. For example, "0A0203A5-D2B6-49F1-A53B-5EC31A89437C".
    
    Tip: The value for these examples is required to be a GUID. The user must investigate which GUID to use for which name type. For example, the Active status GUID is "0A0203A5-D2B6-49F1-A53B-5EC31A89437C". This can be found by using the following SQL script, such as to look through the Item (or any other) table:
    
    USE Symantec_CMDB
    SELECT Guid, Name, Description
    FROM Item
    WHERE Name = 'Active'
    
    This SQL query can return more than one result. The user would have to determine which GUID therefore is the correct one to use. Because of this, some experimentation by the user may be necessary to obtain the correct GUID for each value that is added.
     
16. Click on the Add button.
17. Continue adding desired values to the Display and Value fields. For example, add the following Display and Values to add all out of box Status types:
    
    Disposed                      485C2F89-2FAF-46F3-9E98-D80116D1022D
    In Stock                       1C139F6C-F210-4002-90D0-4DFAF98D5FA4
    Missing                        631344E4-5712-4B98-A3DD-F2058FBB67E2
    On Order                      FBF4A5D6-C649-406E-B919-E3C83AFA039B
    Retired                         492C463B-AFA2-4DD6-AE73-6FD2C7B0E489
    Returned to Lessor        588224BA-16C7-4F84-B808-EF895CE5F759
    RMA                             48ABACB3-CE35-40E4-A1F8-32932135931D
    
    And then add in a custom catch-all choice:
    
    Any                              00000000-0000-0000-0000-000000000000
    
    Note: Manually added items cannot be resorted after being added. If a specific order is desired, these must be added as desired in the order they are to appear in. If these are already added out of order, the only way to change this is to delete the items and re-add them correctly.
    
    Upon completion of all of the above steps, using the Manually Add/Edit Drop-down Values method, the Parameter Editing\Creation window will look like Figure 1, below:
    
    Figure 1: Example Report Parameter window using manually added values
    
    
     
18. Click on the OK button.

Add Drop-down Values From A Report: CostCenter

Using a report to automatically populate values is useful if there is a large amount of or changing data. For example, Cost Centers, Departments or Locations are good candidates to use a report for with this method.
 

1. Repeat steps 1 through 5 from the Manually Add/Edit Drop-down Values: AssetStatus section to create a second new report parameter.
2. In the Parameter section, in the Name field, type a name. For example, "CostCenter".
3. In the Description field, type a description. For example, "Cost Center".
4. Repeat steps 8 through 11 from the Manually Add/Edit Drop-down Values: AssetStatus section. Leave, however, step 10's default GUID as the zero GUID that was added by default, not replace it with a specific Cost Center GUID.
5. In the Tooltip and Label Text fields, type a tooltip and label t names. For example, both can be "Cost Center". 
6. In the Add Drop-down Values From A Report section, click on the "v" expand button to open the section (it is closed by default).
7. Click on the Select a Value link in the Select Report field.
8. Select a report that will provide the expected values for the parameter. For example, select All Cost Centers. Note: If a report does not exist that does this, or if one does but after selecting it, the Report Field to Display does not populate, the user will need to create their own custom report for this express purpose.
9. Click on the OK button.
10. In the Report Field to Display field, choose a value.For example, Name.
11. In the Report Field to Use as Value field, choose a value. For example, _ItemGuid. Upon completion of all of the above steps, using the Add Drop-down Values for A Report method, the Parameter Editing\Creation window will look like Figure 2, below: 
    
    Figure 2: Example Report Parameter window using report (automatic) added values
    
    
     
12. Click on the OK button.
     

Part 2: Add a Query Parameter
 

1. Click on the Data Sources tab.
2. Click on the Query Parameters tab.
3. Click on the Add button and then pick the name of the report parameter in Part 1, step 7 from the Manually Add/Edit Drop-down Values: AssetStatus section. Note: If the user later changes the report parameter values, it is recommended to then go to the Query Parameters tab, delete the prior reference to the report parameter and then re-add it, as report parameter changes do not carry over to already created Query Parameters based off of them.
4. Repeat steps 1 through 3, but pick the name of the report parameter in Part 1, step 2 from the Add Drop-down Values From A Report: CostCenter section.
5. Two query parameters are now added, one for each of the report parameters from Part 1.

Part 3:  Add the parameter to the SQL code

1. Click on the Parameterised Query tab.
2. Type in a SQL script to enable the report to show the expected values. This will then reference the parameters via variables. Please Note: Symantec Technical Support is unable to write SQL queries for the customer. Please refer to your DBA for SQL script writing assistance.
   
   For example,
   
   DEClARE @AssetStatus UNIQUEIDENTIFIER
   SET @AssetStatus = '%AssetStatus%'
   SELECT va._ResourceGuid '_ItemGuid', vi.Name 'Asset', va.[Asset Type], vc.Name 'Cost Center', va.[Serial Number], va.[System Number], va.Barcode, va.Status
   FROM vAsset va
   JOIN vItem vi
   ON vi.Guid = va._ResourceGuid
   JOIN ResourceAssociation ra
   ON ra.ParentResourceGuid= va._ResourceGuid
   JOIN vCostCenter vc
   ON vc._ResourceGuid = ra.ChildResourceGuid
   WHERE  (@AssetStatus = '00000000-0000-0000-0000-000000000000' OR @AssetStatus = va._AssetStatusGuid)
   AND ra.ChildResourceGuid = '%CostCenter%'
   ORDER BY vi.Name
   
   Note: The name any parameter must be spelled exactly as it was entered on the Report Parameter window. This includes capitalization. For example, "Assetstatus" and "assetstatus" would not be recognized, and a Data Source error will occur when trying to save the report.
    
3. Click on the Save Changes button. The report is then processed and results are displayed. From the above examples, this will then display all assets with a status of Active after the user selects a cost center and clicks on the Refresh button. Note: When changing report parameter values in the drop-down lists, after changing the value, click on the report's Refresh button to refresh the results list.
    

Troubleshooting

• If the above examples do not work, double check that everything as shown was entered exactly, or try importing the included sample custom reports, which includes the above examples in them.
• Review the Altiris Server logs to troubleshoot any errors that occur, such as Data Source errors (invalid SQL script).