Вернуться   AXForum > Microsoft Dynamics CRM > Dynamics CRM: Blogs
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

Опции темы Поиск в этой теме Опции просмотра
Старый 18.07.2011, 18:11   #1  
Blog bot is offline
Blog bot
21,339 / 725 (67) +++++++
Регистрация: 28.10.2006
furnemont: How-to: Use SQL CTE with custom reports 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:

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)


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: )


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:



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)


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: )



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'


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

Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
CRM DE LA CREME! CRM 4.0 Disaster Recovery Blog bot Dynamics CRM: Blogs 2 26.02.2016 08:23
furnemont: How-to series: Send SMS messages from CRM 2011 (part 3) Blog bot Dynamics CRM: Blogs 0 13.06.2011 12:11
CRM DE LA CREME! Configuring Microsoft Dynamics CRM 4.0 for Internet-facing deployment Blog bot Dynamics CRM: Blogs 0 18.08.2009 11:05
Microsoft Dynamics CRM Team Blog: Reports for CRM 4.0 using SQL Server 2008 and Report Builder 2.0 Blog bot Dynamics CRM: Blogs 0 11.11.2008 08:05
Microsoft Dynamics CRM Team Blog: How it Works: SQL Server Reporting Services and Dynamics CRM Blog bot Dynamics CRM: Blogs 0 19.07.2008 04:42
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Часовой пояс GMT +3, время: 04:49.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.