ServiceDesk

Any one have an idea on how to write the SQL query properly in order to pull the classfications out of the ensemble database?  I am trying to do a self join but the first column is still displaying everysingle classification like it is the top level classififcation.  Currently we go 5 levels deeps with the classifications.

In case you're interested, 7.1 SP2 will have import/export functionality for hierarchy classifications.

I plan on going that route eventually but that is a ways away.  Thanks for the reply.  

Just an FYI... I know it doesn't help you now.

i have came this issue before, and i exported the data using SQL

If you could provide that query that would be awesome.  The self joins were not working for me.  It would list all of the selections in the first column then start to tree out.  Couldnt figure it out.

you will need to do a self join on the same HierarchyCategory table

select i.name as Parent, o.name as Item

from [ProcessManager].[dbo].[HierarchyItem] o

inner join [ProcessManager].[dbo].[HierarchyItem] i on i.HierarchyItemID = o.ParentHierarchyItemID

Took Mohammed's script a little further to include the parent category name and to deal with childless parents.

SELECT p.Name AS Category, i.Name AS Parent, o.Name AS Item

FROM [ProcessManager].[dbo].[ HierarchyCategory] AS p INNER JOIN

[ProcessManager].[dbo].[ HierarchyItem] AS i ON p.HierarchyCategoryID = i.HierarchyCategoryID LEFT OUTER JOIN

[ProcessManager].[dbo].[ HierarchyItem] AS o ON i.HierarchyItemID = o.ParentHierarchyItemID

ORDER BY Category, Parent