Steps to include Service Catalog Additional Fields in a Query Report
Hi Users,
To get Service Request reports based on Service Catalog additional fields, follow the below guide.
Tables involved:
Required information to construct the query:
Query:
SELECT wo.WORKORDERID "Request ID", scat_global.GUDF_CHAR1 "Global Character field1",scat_global.GUDF_CHAR2 "Global Character field2",scat_global.GUDF_CHAR3 "Global Character field3",scat_global.GUDF_CHAR4 "Global Character field4",scat_global.GUDF_CHAR5 "Global Character field5",scat_global.GUDF_CHAR6 "Global Character field6",scat_global.GUDF_CHAR7 "Global Character field7",scat_global.GUDF_CHAR8 "Global Character field8",scat_global.GUDF_CHAR9 "Global Character field9",scat_global.GUDF_CHAR10 "Global Character field10",scat_global.GUDF_CHAR11 "Global Character field11",scat_global.GUDF_CHAR12 "Global Character field12",scat_global.GUDF_CHAR13 "Global Character field13",scat_global.GUDF_CHAR14 "Global Character field14",scat_global.GUDF_CHAR15 "Global Character field15",scat_global.GUDF_CHAR16 "Global Character field16",scat_global.GUDF_CHAR17 "Global Character field17",scat_global.GUDF_CHAR18 "Global Character field18",scat_global.GUDF_CHAR19 "Global Character field19",scat_global.GUDF_CHAR20 "Global Character field20",scat_global.GUDF_CHAR21 "Global Character field21",scat_global.GUDF_CHAR22 "Global Character field22",scat_global.GUDF_CHAR23 "Global Character field23",scat_global.GUDF_CHAR24 "Global Character field24",scat_global.GUDF_LONG1 "Global Numeric field1",scat_global.GUDF_LONG2 "Global Numeric field2",scat_global.GUDF_LONG3 "Global Numeric field3",scat_global.GUDF_LONG4 "Global Numeric field4",scat_global.GUDF_LONG5 "Global Numeric field5",scat_global.GUDF_LONG6 "Global Numeric field6",scat_global.GUDF_LONG7 "Global Numeric field7",scat_global.GUDF_LONG8 "Global Numeric field8",scat_global.GUDF_DATE1 "Global Date field1",scat_global.GUDF_DATE2 "Global Date field2",scat_global.GUDF_DATE3 "Global Date field3",scat_global.GUDF_DATE4 "Global Date field4",scat_global.GUDF_DATE5 "Global Date field5",scat_global.GUDF_DATE6 "Global Date field6",scat_global.GUDF_DATE7 "Global Date field7",scat_global.GUDF_DATE8 "Global Date field8",scat_local.UDF_CHAR1 "Local Character field1",scat_local.UDF_CHAR2 "Local Character field2",scat_local.UDF_CHAR3 "Local Character field3",scat_local.UDF_CHAR4 "Local Character field4",scat_local.UDF_CHAR5 "Local Character field5",scat_local.UDF_CHAR6 "Local Character field6",scat_local.UDF_CHAR7 "Local Character field7",scat_local.UDF_CHAR8 "Local Character field8",scat_local.UDF_CHAR9 "Local Character field9",scat_local.UDF_CHAR10 "Local Character field10",scat_local.UDF_CHAR11 "Local Character field11",scat_local.UDF_CHAR12 "Local Character field12",scat_local.UDF_CHAR13 "Local Character field13",scat_local.UDF_CHAR14 "Local Character field14",scat_local.UDF_CHAR15 "Local Character field15",scat_local.UDF_CHAR16 "Local Character field16",scat_local.UDF_CHAR17 "Local Character field17",scat_local.UDF_CHAR18 "Local Character field18",scat_local.UDF_CHAR19 "Local Character field19",scat_local.UDF_CHAR20 "Local Character field20",scat_local.UDF_CHAR21 "Local Character field21",scat_local.UDF_CHAR22 "Local Character field22",scat_local.UDF_CHAR23 "Local Character field23",scat_local.UDF_CHAR24 "Local Character field24",scat_local.UDF_LONG1 "Local Numeric field1",scat_local.UDF_LONG2 "Local Numeric field2",scat_local.UDF_LONG3 "Local Numeric field3",scat_local.UDF_LONG4 "Local Numeric field4",scat_local.UDF_LONG5 "Local Numeric field5",scat_local.UDF_LONG6 "Local Numeric field6",scat_local.UDF_LONG7 "Local Numeric field7",scat_local.UDF_LONG8 "Local Numeric field8",scat_local.UDF_DATE1 "Local Date field1",scat_local.UDF_DATE2 "Local Date field2",scat_local.UDF_DATE3 "Local Date field3",scat_local.UDF_DATE4 "Local Date field4",scat_local.UDF_DATE5 "Local Date field5",scat_local.UDF_DATE6 "Local Date field6",scat_local.UDF_DATE7 "Local Date field7",scat_local.UDF_DATE8 "Local Date field8" FROM WorkOrder wo LEFT JOIN ServiceCatalog_Fields scat_global ON wo.WORKORDERID=scat_global.WORKORDERID LEFT JOIN ServiceReq_App scat_local ON wo.WORKORDERID=scat_local.WORKORDERID WHERE (wo.ISPARENT=1)
Regards,
Karups
To get Service Request reports based on Service Catalog additional fields, follow the below guide.
Tables involved:
- ServiceCatalog_Fields
- Values of common additional fields for a workorder are stored in this table
- This table is common for all service categories
- Columns in this table starts with GUDF_ (examples: GUDF_CHAR1, GUDF_LONG1 GUDF_DATE1)
- ServiceDefinition
- Details about service categories are stored into this table
- DynamicTables
- Details about the created dynamic tables for service categories are stored into this table
- ServiceReq_<Service Category Id/Service Category Name>
- Values of service category specific additional fields for a workorder are stored in this table
- This table is specific to the service category
- Columns in this table starts with UDF_ (examples: UDF_CHAR1, UDF_LONG1 UDF_DATE1)
Required information to construct the query:
- Get the name of the dynamic table for the service category by looking into ServiceDefinition table and DynamicTables table (Get the SERVICEID from ServiceDefinition table and get the dynamic table name from DynamicTables table using the SERVICEID)
- Get the list of alias names for columns and replace the alias given in the below query with actual alias names
- List of alias names can be taken from Service Catalog Additional fields configuration section
- Go to Admin -> Service Catalog -> Manage -> Additional Fields
- Choose Common from "Service Category" select box
- Here you will find 24 Char fields, 8 Numeric fields and 8 Date fields
- The label of the first char field corresponds to GUDF_CHAR1 and the second char field corresponds to GUDF_CHAR2 etc.
- The label of the first numeric field corresponds to GUDF_LONG1 and the second numeric field corresponds to GUDF_LONG2
- The label of the first date field corresponds to GUDF_DATE1 and the second date field corresponds to GUDF_DATE2
- Using this convention modify the query with actual column aliases
- With this, you are done with common additional fields
- Now choose the service category from "Service Category" select box
- Here you will find 24 Char fields, 8 Numeric fields and 8 Date fields
- The label of the first char field corresponds to UDF_CHAR1 and the second char field corresponds to UDF_CHAR2 etc.
- The label of the first numeric field corresponds to UDF_LONG1 and the second numeric field corresponds to UDF_LONG2
- The label of the first date field corresponds to UDF_DATE1 and the second date field corresponds to UDF_DATE2
- Using this convention modify the query with actual column aliases
- With this, you are done with service specific additional fields
- Remove the unwanted columns from the below query
Query:
SELECT wo.WORKORDERID "Request ID", scat_global.GUDF_CHAR1 "Global Character field1",scat_global.GUDF_CHAR2 "Global Character field2",scat_global.GUDF_CHAR3 "Global Character field3",scat_global.GUDF_CHAR4 "Global Character field4",scat_global.GUDF_CHAR5 "Global Character field5",scat_global.GUDF_CHAR6 "Global Character field6",scat_global.GUDF_CHAR7 "Global Character field7",scat_global.GUDF_CHAR8 "Global Character field8",scat_global.GUDF_CHAR9 "Global Character field9",scat_global.GUDF_CHAR10 "Global Character field10",scat_global.GUDF_CHAR11 "Global Character field11",scat_global.GUDF_CHAR12 "Global Character field12",scat_global.GUDF_CHAR13 "Global Character field13",scat_global.GUDF_CHAR14 "Global Character field14",scat_global.GUDF_CHAR15 "Global Character field15",scat_global.GUDF_CHAR16 "Global Character field16",scat_global.GUDF_CHAR17 "Global Character field17",scat_global.GUDF_CHAR18 "Global Character field18",scat_global.GUDF_CHAR19 "Global Character field19",scat_global.GUDF_CHAR20 "Global Character field20",scat_global.GUDF_CHAR21 "Global Character field21",scat_global.GUDF_CHAR22 "Global Character field22",scat_global.GUDF_CHAR23 "Global Character field23",scat_global.GUDF_CHAR24 "Global Character field24",scat_global.GUDF_LONG1 "Global Numeric field1",scat_global.GUDF_LONG2 "Global Numeric field2",scat_global.GUDF_LONG3 "Global Numeric field3",scat_global.GUDF_LONG4 "Global Numeric field4",scat_global.GUDF_LONG5 "Global Numeric field5",scat_global.GUDF_LONG6 "Global Numeric field6",scat_global.GUDF_LONG7 "Global Numeric field7",scat_global.GUDF_LONG8 "Global Numeric field8",scat_global.GUDF_DATE1 "Global Date field1",scat_global.GUDF_DATE2 "Global Date field2",scat_global.GUDF_DATE3 "Global Date field3",scat_global.GUDF_DATE4 "Global Date field4",scat_global.GUDF_DATE5 "Global Date field5",scat_global.GUDF_DATE6 "Global Date field6",scat_global.GUDF_DATE7 "Global Date field7",scat_global.GUDF_DATE8 "Global Date field8",scat_local.UDF_CHAR1 "Local Character field1",scat_local.UDF_CHAR2 "Local Character field2",scat_local.UDF_CHAR3 "Local Character field3",scat_local.UDF_CHAR4 "Local Character field4",scat_local.UDF_CHAR5 "Local Character field5",scat_local.UDF_CHAR6 "Local Character field6",scat_local.UDF_CHAR7 "Local Character field7",scat_local.UDF_CHAR8 "Local Character field8",scat_local.UDF_CHAR9 "Local Character field9",scat_local.UDF_CHAR10 "Local Character field10",scat_local.UDF_CHAR11 "Local Character field11",scat_local.UDF_CHAR12 "Local Character field12",scat_local.UDF_CHAR13 "Local Character field13",scat_local.UDF_CHAR14 "Local Character field14",scat_local.UDF_CHAR15 "Local Character field15",scat_local.UDF_CHAR16 "Local Character field16",scat_local.UDF_CHAR17 "Local Character field17",scat_local.UDF_CHAR18 "Local Character field18",scat_local.UDF_CHAR19 "Local Character field19",scat_local.UDF_CHAR20 "Local Character field20",scat_local.UDF_CHAR21 "Local Character field21",scat_local.UDF_CHAR22 "Local Character field22",scat_local.UDF_CHAR23 "Local Character field23",scat_local.UDF_CHAR24 "Local Character field24",scat_local.UDF_LONG1 "Local Numeric field1",scat_local.UDF_LONG2 "Local Numeric field2",scat_local.UDF_LONG3 "Local Numeric field3",scat_local.UDF_LONG4 "Local Numeric field4",scat_local.UDF_LONG5 "Local Numeric field5",scat_local.UDF_LONG6 "Local Numeric field6",scat_local.UDF_LONG7 "Local Numeric field7",scat_local.UDF_LONG8 "Local Numeric field8",scat_local.UDF_DATE1 "Local Date field1",scat_local.UDF_DATE2 "Local Date field2",scat_local.UDF_DATE3 "Local Date field3",scat_local.UDF_DATE4 "Local Date field4",scat_local.UDF_DATE5 "Local Date field5",scat_local.UDF_DATE6 "Local Date field6",scat_local.UDF_DATE7 "Local Date field7",scat_local.UDF_DATE8 "Local Date field8" FROM WorkOrder wo LEFT JOIN ServiceCatalog_Fields scat_global ON wo.WORKORDERID=scat_global.WORKORDERID LEFT JOIN ServiceReq_App scat_local ON wo.WORKORDERID=scat_local.WORKORDERID WHERE (wo.ISPARENT=1)
Regards,
Karups