furnemont: How-to: Use SQL CTE with custom reports in CRM

[Blog bot]

Источник: http://www.furnemont.eu/2011/07/how-...eports-in-crm/
==============

One of my client asked me the other day to produce a report containing all the cases (incidents) in the system… OK, this is pretty easy and it already exists somewhere

But the hardest part was the filtering of this report: he wanted to be able to put a parent subject in the filter and the report should take care of getting all the child under this subject!


I said “hum…sure it can be done!” because I remembered about the CTE, or Common Table Expressions (more information here: http://en.wikipedia.org/wiki/Common_table_expressions)

Before going on with this post and dealing with code, I should back up a little bit and talk about the CTE…

As you may know, the CTE is kind of a temporary ‘table’, defined in the execution scope of a SELECT statement (for example). The beauty of this temporary ‘table’ is that it can be self-referencing so it’s easy to create recursive queries using them.

From the MSDN:

A CTE can be used to:

• Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
  
• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  
• Reference the resulting table multiple times in the same statement.
  

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

Wow…I could’nt have done better And now for the fun part…

Retrieve the complete list of subject

You may think that this is simple but wait to see the query and you’ll get it:

This is how it looks in Microsoft CRM:

1: WITH SubjectTree

2: AS (

3: -- anchor

4: SELECT s.subjectid, s.title, s.parentsubjectname, 0 as Level

5: FROM dbo.FilteredSubject s (nolock)

6: UNION ALL

7: -- recursive member

8: SELECT s.subjectid, s.title, s.parentsubjectname, Level+1

9: FROM dbo.FilteredSubject s (nolock)

10: INNER JOIN SubjectTree AS t

11: ON s.parentsubject = t.subjectid

12: )

13: SELECT

14: t.*

15: FROM SubjectTree AS t

16: ORDER BY t.Level

This query’s result:



Filtering the CTE query:

Let’s put a query filter in the anchor part of the CTE query:

1: -- anchor

2: SELECT s.subjectid, s.title, s.parentsubjectname, 0 as Level

3: FROM dbo.FilteredSubject s (nolock)

4: WHERE s.title = 'Root level'

The result:



It’s pretty obvious that this kind of query can be very helpful in custom search engines or in reports, where the subject is used.

So how about integrating this kind of query in a custom report?

Integrating the CTE in a custom report:

I won’t explain how to create a custom report in this post since it has been covered previously (see part 1, part 2, part 3, part 4 or part 5).

Instead, I will put the query I’m using to show how to filter the cases based on a subject:

1: DECLARE @sql NVARCHAR(MAX)

2:

3: SET @sql = '

4: WITH SubjectTree

5: AS (

6: -- anchor

7: SELECT s.subjectid, s.title, 0 as Level

8: FROM FilteredSubject s (nolock)

9: WHERE s.subjectid IN (SELECT i.subjectid FROM (' + @CRM_FilteredIncident + ') AS i)

10: UNION ALL

11: --recursive member

12: SELECT s.subjectid, s.title, Level+1

13: FROM dbo.FilteredSubject s (nolock)

14: INNER JOIN SubjectTree AS t

15: ON s.parentsubject = t.subjectid

16: )

17:

18: SELECT

19: i.contactidname AS contact,

20: i.ticketnumber AS reference,

21: i.title,

22: i.subjectidname AS domain,

23: i.createdon,

24: i.owneridname AS owner,

25: i.statecodename AS state

26: FROM FilteredIncident AS i

27: INNER JOIN SubjectTree AS s

28: ON i.subjectid = s.subjectid

29: WHERE i.casetypecodename = ''Urgent Problem''

30: AND i.statecode = 0

31: ORDER BY i.createdon DESC'

32:

33: EXEC(@sql)

Don’t forget to specify a default filter in your report when you upload it so your clients won’t have to define it afterwards, they will only have to set the correct subject and voilà, your report with recursive subject tree will spit out the results they are looking for

Useful links:

• Using CTE (MSDN): here
• Recursive queries using CTEs (MSDN): here
• Definition of CTE (Wikipedia): here
• Comparison of relational DB systems (Wikipedia): here

Источник: http://www.furnemont.eu/2011/07/how-...eports-in-crm/