Tuesday, January 8, 2013

Read Subject sub-child from tree structure in MS CRM 2011


Always its difficult to get the Subject Tree structure to implement your business logic.
I have written CTE to access the sub-child just by passing the Root level Subject.

Create the function in CRM database.

CREATE FUNCTION [dbo].[GetDownSubjectHierarchy] ( @SubjectId NVARCHAR(100))
RETURNS TABLE
AS
RETURN
(
WITH DirectReports (ParentSubject, ParentSubjectName, SubjectId, Title, Level)
AS
(
-- Anchor member definition
SELECT FSU1.ParentSubject,FSU1.ParentSubjectName, FSU1.SubjectId, FSU1.Title,0 AS Level
FROM dbo.Filteredsubject AS FSU1
WHERE SubjectId = @SubjectId

UNION ALL
-- Recursive member definition
SELECT FSU1.ParentSubject,FSU1.ParentSubjectName, FSU1.SubjectId, FSU1.Title, Level + 1
FROM dbo.Filteredsubject AS FSU1
INNER JOIN DirectReports AS d
ON FSU1.ParentSubject = d.SubjectId
)

    -- Statement that executes the CTE
    SELECT Distinct * FROM DirectReports d
)

Execute the below query to get the Subject sub-child of (down) level 2  by passing the subjectid.

SELECT * FROM [GetDownSubjectHierarchy]('35344367-8535-E211-AC03-000C29AD15D1')
WHERE Level=2

Just execute the Query and store the result in array/list/data set/data table as you want to execute your business logic.

You can get the sub-child of any level, just change the value of level (0 is the root subject).

2 comments:

Omega software said...

Keep up the fantastic piece of work, I read few blog posts on this web site and I believe that your site is real interesting and has lots of great information. ERP Software in Mumbai || System Software || CRM Software in Mumbai || MLM Software

Omega software said...

I really appreciate spending some time to talk about that, I believe firmly regarding this and so really enjoy understanding more about this kind of subject.This is also a very good post which I really enjoyed reading. It is not everyday that I have the possibility to see something like this. CRM Software || MLM Software in Mumbai || ERP Software || System Software in Mumbai