I have the following values setup in our subject tree

Helpdesk Reqeust
   Application
       Exchange
       Office
       ERP
       CRM
           Workflow
           Custom Entity
   Security
   Hardware
       Desktop
       Laptop
            Dell
            Sony
   Personnel
       New Hire
       Office Cancel
       Office Transfer
Customer Service Request
   EndUser
       Product A
           Unresponsive
           Reset Password
           Drive failure
       Product B
   Partner
       Program A
       Program B
.....etc

   
If I enter a new case and set the subject to "Workflow", the parent subject 
is "CRM".  

Our subject tree goes 4 "levels" deep maximum.  For reporting purposes I 
need to summarize my cases several different ways.  

1.  By subject selected in the case   (ie, "Workflow")
2.  By parent subject (ie, "CRM")
3.  By parent, parent subject  ( ie, "Application")
4.  By parent, parent, parent, subject (ie, "Helpdesk Request")

Has anyone built a SQL function or view where you can take the subject ID in 
the case and return all the "parent" subjects that are above the subject ID 
selected?

Anyone have any ideas on how to do something like this?  Thank you in 
advance for any help.

Chris
did you get a response to your question, or have you found another way 
around the issue. We have a similar requirement and came across your post 
whilst researching it.

cheers

Aneel



"Chris" wrote:

> I have the following values setup in our subject tree
> 
> Helpdesk Reqeust
>    Application
>        Exchange
>        Office
>        ERP
>        CRM
>            Workflow
>            Custom Entity
>    Security
>    Hardware
>        Desktop
>        Laptop
>             Dell
>             Sony
>    Personnel
>        New Hire
>        Office Cancel
>        Office Transfer
> Customer Service Request
>    EndUser
>        Product A
>            Unresponsive
>            Reset Password
>            Drive failure
>        Product B
>    Partner
>        Program A
>        Program B
> ....etc
> 
>    
> If I enter a new case and set the subject to "Workflow", the parent subject 
> is "CRM".  
> 
> Our subject tree goes 4 "levels" deep maximum.  For reporting purposes I 
> need to summarize my cases several different ways.  
> 
> 1.  By subject selected in the case   (ie, "Workflow")
> 2.  By parent subject (ie, "CRM")
> 3.  By parent, parent subject  ( ie, "Application")
> 4.  By parent, parent, parent, subject (ie, "Helpdesk Request")
> 
> Has anyone built a SQL function or view where you can take the subject ID in 
> the case and return all the "parent" subjects that are above the subject ID 
> selected?
> 
> Anyone have any ideas on how to do something like this?  Thank you in 
> advance for any help.
> 

I had to put a clumsy solution together where my query has the inicident base 
table joined up to the subjectbase table mulitple times with left outer joins 
to see if the subject in the case has a parent subject until it get's to the 
top of tree where there are no further parents listed.

We've standardized on only going 5 levels so I know I only have to check 5 
times to see if there are anymore parents.  But the day we go six or more 
levels deep in the subject tree it's going to be a big pain to update this 
query.

The concept they have for "subjects" is nice, but Microsoft needs to come up 
with a better way for reporting the details of how the subject tree is used 
in cases.

"AneelK" wrote:

> Chris
> did you get a response to your question, or have you found another way 
> around the issue. We have a similar requirement and came across your post 
> whilst researching it.
> 
> cheers
> 
> Aneel
> 
> 
> 
> "Chris" wrote:
> 
> > I have the following values setup in our subject tree
> > 
> > Helpdesk Reqeust
> >    Application
> >        Exchange
> >        Office
> >        ERP
> >        CRM
> >            Workflow
> >            Custom Entity
> >    Security
> >    Hardware
> >        Desktop
> >        Laptop
> >             Dell
> >             Sony
> >    Personnel
> >        New Hire
> >        Office Cancel
> >        Office Transfer
> > Customer Service Request
> >    EndUser
> >        Product A
> >            Unresponsive
> >            Reset Password
> >            Drive failure
> >        Product B
> >    Partner
> >        Program A
> >        Program B
> > ....etc
> > 
> >    
> > If I enter a new case and set the subject to "Workflow", the parent subject 
> > is "CRM".  
> > 
> > Our subject tree goes 4 "levels" deep maximum.  For reporting purposes I 
> > need to summarize my cases several different ways.  
> > 
> > 1.  By subject selected in the case   (ie, "Workflow")
> > 2.  By parent subject (ie, "CRM")
> > 3.  By parent, parent subject  ( ie, "Application")
> > 4.  By parent, parent, parent, subject (ie, "Helpdesk Request")
> > 
> > Has anyone built a SQL function or view where you can take the subject ID in 
> > the case and return all the "parent" subjects that are above the subject ID 
> > selected?
> > 
> > Anyone have any ideas on how to do something like this?  Thank you in 
> > advance for any help.
> > 

Chris
thanks - just come across the solution suggested to you in th edeveloper 
newsgroup so will give it a go.

i agree that this needs looking at so that analysis can be made easier.

regards
Aneel

"Chris" wrote:

> I had to put a clumsy solution together where my query has the inicident base 
> table joined up to the subjectbase table mulitple times with left outer joins 
> to see if the subject in the case has a parent subject until it get's to the 
> top of tree where there are no further parents listed.
> 
> We've standardized on only going 5 levels so I know I only have to check 5 
> times to see if there are anymore parents.  But the day we go six or more 
> levels deep in the subject tree it's going to be a big pain to update this 
> query.
> 
> The concept they have for "subjects" is nice, but Microsoft needs to come up 
> with a better way for reporting the details of how the subject tree is used 
> in cases.
> 
> "AneelK" wrote:
> 
> > Chris
> > did you get a response to your question, or have you found another way 
> > around the issue. We have a similar requirement and came across your post 
> > whilst researching it.
> > 
> > cheers
> > 
> > Aneel
> > 
> > 
> > 
> > "Chris" wrote:
> > 
> > > I have the following values setup in our subject tree
> > > 
> > > Helpdesk Reqeust
> > >    Application
> > >        Exchange
> > >        Office
> > >        ERP
> > >        CRM
> > >            Workflow
> > >            Custom Entity
> > >    Security
> > >    Hardware
> > >        Desktop
> > >        Laptop
> > >             Dell
> > >             Sony
> > >    Personnel
> > >        New Hire
> > >        Office Cancel
> > >        Office Transfer
> > > Customer Service Request
> > >    EndUser
> > >        Product A
> > >            Unresponsive
> > >            Reset Password
> > >            Drive failure
> > >        Product B
> > >    Partner
> > >        Program A
> > >        Program B
> > > ....etc
> > > 
> > >    
> > > If I enter a new case and set the subject to "Workflow", the parent subject 
> > > is "CRM".  
> > > 
> > > Our subject tree goes 4 "levels" deep maximum.  For reporting purposes I 
> > > need to summarize my cases several different ways.  
> > > 
> > > 1.  By subject selected in the case   (ie, "Workflow")
> > > 2.  By parent subject (ie, "CRM")
> > > 3.  By parent, parent subject  ( ie, "Application")
> > > 4.  By parent, parent, parent, subject (ie, "Helpdesk Request")
> > > 
> > > Has anyone built a SQL function or view where you can take the subject ID in 
> > > the case and return all the "parent" subjects that are above the subject ID 
> > > selected?
> > > 
> > > Anyone have any ideas on how to do something like this?  Thank you in 
> > > advance for any help.
> > >