In this article I’m going to explain how to convert .mrt reports from the Sitecore Online Marketing Suite (Sitecore 6.4) to the Sitecore Digital Marketing System (Sitecore 6.5). Report related changes between OMS and DMS Both the OMS and the DMS use .mrt reports for Analitics reporting. There are two mayor changes between the OMS and DMS reporting; a full redesign of the datamodel and the location of the SQL query for the report data. Mapping the datamodel of the OMS with More Info »

For this example I will use the default asp.net Membership tables. Let say: I need to return all users with their roles (comma separated) in one query. After a long time Googling I found the following solution. select   UserName,   (select roles.RoleName + ', '     FROM aspnet_Roles roles     join aspnet_UsersInRoles usersInRole on roles.RoleId = usersInRole.RoleId     WHERE usersInRole.UserId = aspUser.UserId     for xml path('')) as roles from     aspnet_Users aspUser Don’t know if this is the best way, but it works.

With common table expressions you can save the results to a temporary result set and use this results set for other queries. WITH temporaryNamedResultSet AS (   select UserName from aspnet_Users ) select * from temporaryNamedResultSet