Adding columns or tables to a custom SQL report

Products

• IT Management Suite
• Asset Management Solution

Issue/Introduction

 How to add columns or tables to a custom SQL report

Resolution

The following instructions describe how to add new columns or tables into a custom SQL report's SQL script. Note: Out of box reports cannot be edited. These can be cloned, and if they are SQL-based, this article's instructions can then be applied to the clone's SQL script.
 
Initial setup

1. In a Symantec Management Platform Console, click on the Reports button.
2. Select the custom report to edit.
3. Click on the Edit button.
4. Click on the Parameterised Query tab. New columns and tables can be added here into the SQL script. Note: The remaining instructions reference a cloned copy of the Service and Asset Management > Assets > General Asset Report for use as an example.

Moving and adding existing columns

1. In the Parameterised Query tab, a SELECT statement lists the columns that will appear on the report. For example, this looks like the following from the General Asset Report:
   
   
   
   Note: The SQL script in this article's instructions and in the screenshots of the cloned report have been cleaned up to remove margins to make the code easier to read.
   
   This shows that the columns will be the following in the listed order as they appear in sequence in the SELECT statement: Asset Type, Name, Asset Status, Manufacturer, Model, Serial Number, System Number, Barcode and Last Barcode Scan.
   
   
   
   Note: The GUID column does not appear as reports are programmed to normally not show GUID values. GUIDs can be displayed by adding an alias that does not reference the GUID. For example:
   
   i.Guid as 'GUID',
   
   The GUID would need to be added twice, as normally reports require a GUID column referenced as either _ItemGuid or _ResourceGuid, to enable report parameters to work. For example:
   
   i.Guid as _ItemGuid,
   i.Guid as 'GUID',
   
   Where the first meets the report parameter's requirements and the second displays the GUID column.
    
2. Column order can be changed by moving the column names in the SELECT statement to a different position. For example:
   
   
   
   In this, Serial Number, System Number and Barcode were moved before Manufacturer, etc. The report columns then appear this way too: Asset Type, Name, Asset Status, Serial Number, System Number, Barcode, Manufacturer, Model  and Last Barcode Scan.
   
   
    
3. Additional columns can be added from any current table or view that is in the SELECT or JOIN statements. For example, the vAsset Asset Tag data class can be added by inserting it into the column names:
   
   
   
   Which the columns then appear as: Asset Type, Name, Asset Status, Serial Number, System Number, Barcode, Manufacturer, Model  Last Barcode Scan and Asset Tag.

Adding new columns (from different tables) 

1. If new columns are needed from different tables or views that are not part of the SELECT or JOIN statements, a new JOIN must be made to the appropriate table or view. The new JOIN can be inserted into the existing JOINS such as as the first, or the last JOIN. For example, the Location may be desired to be used. Information on the Location is referenced by several JOIN statements to the ResourceAssociation and Item tables. Note: If the JOIN needed is going to a resource association, i.e., Location, Department, Asset Owner, etc., the JOIN should be a LEFT JOIN, and the Resource Type value substituted for the specific type, such as found in the below example. The Item table would then normally be needed to be referenced as a second JOIN to obtain the resource associaton's Name value.
   
   
    
2. Once the correct JOIN has been made to the different table or view, columns from this can be added to the SELECT statement. In the above example, i.Name is added for the name of the Location.