web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Dilip's blog on MICROSOFT DYNAMICS 365 / SQL Server CTEs and Recursi...

SQL Server CTEs and Recursive Parent Grouping in Advanced Mode Group Properties in SSRS

Views (2175)
Dilip Profile Picture Dilip 1,164
Hello!

Few weeks ago, I was developing an interesting report in SSRS which pulls out data from AX - Projects module.

It was interesting for 2 reasons:

1. I learned to use more effectively SQL Server CTE's (Common table expressions) while developing this report.

From a layman's perspective, CTEs were introduced since SQL Server 2005 and it’s more useful to do a recursive search (display hierarchical data e.g. employee-manager, BOM etc). In my scenario, I had to do a recursive search for Project-Parent Id in my Stored proc)

Below sharing some links, which will explain further more in detail about CTE's and how to implement them.

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

http://msdn.microsoft.com/en-us/magazine/cc794278.aspx

2.  The second concept was about recursive parent Grouping (in SSRS)

In SSRS, we can group the child field based on the recursive parent key (There’s an option in the Edit Group > Advanced Section to key in the Recursive parent field) – explained here in this blog - http://sql-bi-dev.blogspot.com.au/2010/08/recursive-hierarchy-group-in-ssrs-2008.html

This was originally posted here.

Comments

*This post is locked for comments