Search code examples
reporting-servicesdynamics-crmdynamics-crm-2011ssrs-2012

CRM 2011, SSRS Report showing user list, with teams in columns

Rookie T-SQL and SSRS user here with CRM 2011. I'm quite convinced there's a relatively simple way of doing what I'm looking for, but I haven't quite found the right terms to search for and find a solution.

I want to run a list of users using SSRS that will show their team membership, but with each team having a dedicated column to put an "O" or an "X," for example.

I only have about a dozen teams I use in my CRM, so having columns for them wouldn't make the report too big horizontally. Here's essentially how I run it now:

SELECT
     u.Fullname   AS 'Name',
     t.Name       AS 'Team'
FROM TeamMembership tm

INNER JOIN SystemUser u
ON tm.SystemUserID = u.SystemUserId

INNER JOIN Team t
ON tm.TeamId = t.TeamId

...and then I perform grouping and stuff afterward in Visual Studio to make it look at least a little better, but it still looks basically like this: Team Memberships as separate rows

Ideally, it would look a little more like this:

                         <--------------------- (Teams) ------------------------->
    +--------------------+--------+--------+--------+----------------+-----------+
    | Name               |Student |Teacher |Potions |Transfiguration |Divination |
    |--------------------+--------+--------+--------+----------------+-----------+
    | Harry Potter       |   O    |        |   O    |       O        |     O     |
    | Ron Weasley        |   O    |        |   O    |       O        |     O     |
    | Hermione Granger   |   O    |        |   O    |       O        |           |
    | Minerva McGonagall |        |   O    |        |       O        |           |
    | Severus Snape      |        |   O    |   O    |                |           |
    | Sybill Trelawney   |        |   O    |        |                |     O     |
    +--------------------+--------+--------+--------+----------------+-----------+

The User Summary out-of-the-box report does something similar, but with Security Roles. I was hoping to reverse engineer that one a bit, but there's a Microsoft assembly referring to an RdlHelper in some of the expressions... and at that point I'm quite out of my element.

Solution

  • Your select statement is fine, set out your report design as matrix, in row groups you need Name, in column groups you need Team, in the aggregate field you need the expression: IIF(Count(Fields!Name.Value)> 0,0,"")

    See attached example:

    enter image description here