Query to show Category, Sub category and Item (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
PGSQL & MSSQL:
Excute the below query under Report > New Report > Query Report > Copy, Paste the below and Run the query.
SELECT CategoryDefinition.CATEGORYNAME "Category Name", CategoryDefinition.CATEGORYNAME "Category Name", SubCategoryDefinition.NAME "Sub Category Name" ,ItemDefinition.NAME"Item Name" FROM CategoryDefinition left join SubCategoryDefinition on SubCategoryDefinition.CATEGORYID=CategoryDefinition.CATEGORYID left join ItemDefinition on ItemDefinition.SUBCATEGORYID = SubCategoryDefinition.SUBCATEGORYID group by CategoryDefinition.CATEGORYNAME,SubCategoryDefinition.NAME,ItemDefinition.NAME order by CategoryDefinition.CATEGORYNAME
New to ADSelfService Plus?
Related Articles
Export Category,subcategory, item
This report is used to export the tree view. This report helps to import category,subcategory and item in other applications. Modify and import in the applications. SELECT CategoryDefinition.CATEGORYNAME "Category Name", SubCategoryDefinition.NAME ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to show Account Specific Category, Sub category and Item (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT accountdefinition.org_name "Account Name", CategoryDefinition.CATEGORYNAME "Category Name", SubCategoryDefinition.NAME "Sub Category Name" ,ItemDefinition.NAME"Item Name" FROM CategoryDefinition ...Query to Export solutions (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Run this query under Reports->New Query Report and export it to the desired format SELECT solution.solutionid "Solutionid", KB_Topics.TOPICNAME " Topic",Solution.TITLE "Subject ",Solution.DESCRIPTION ...Query to show ticket aging - PGSQL
Last tested on 14500 Database: PGSQL: select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename "Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item", ...