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.