Using SSRS - Matrix Control to Generate Columns Dynamically

Scenario

In a support center there are agents who assist customers for solving their issues with company products. Company needs to track, amount of time spent by agents on various activities. Report is required in following format:

Number and Name of Activities are not fixed in this report because:

• New activities can be added
• Existing activities can be modified

So, the columns “Time spent on Activity 1” to “Time spent on Activity N” need to be generated dynamically.

Solution

Table structure

Agent Table:
Activity Table:
Agent Activity Mapping Table:

Query that gives me all Agent, Activity data:

SELECT A.AgentName, ACT.ActivityName, ASM.TimeInMinutes
 FROM   dbo.Agent_ActivityMapping ASM INNER JOIN dbo.Agent A  ON
  A.AgentID = ASM.AgentRef INNER JOIN dbo.Activity ACT  ON 
  ACT.ActivityID = ASM.ActivityRef

Step 2: Right click on “Shared Data Source” folder in solution explorer and click on “Add New Data Source”:

Step 3: Now right click on “Reports” folder in solution explorer and click on Add new item and select Report. I have named my report as “ActivityMatrix”

Step 4: Double click on report to open it. In Report Data pane, Right click on Dataset folder and Add Dataset. In this dataset, I have used the same query that we had created to fetch all the Agent-Activity data.

Step 5: Now insert a Matrix onto the report.

Step 6: Now drag Agent name into data section of first column. Drag ActivityName into header of second column. That is because result of Activity element will give us the column names. It will look something like this:

Now, drag “TimeInMinutes” element from dataset to data of second column. This column will give us sum of time spent on an activity by an agent.

Step 7: In order to add a column at the end of the report, right-click and select following:

Notice that there are 4 options of inserting a column. First 2 options are for adding column inside the group i.e., the ActivityName group. Last two options are for adding column outside the group. So we will select last option in our case. This will be a computed column. This needs to be the sum of time spent on various activities by each agent i.e., [Sum(TimeInMinutes)]. So, our report looks like this now:

Final preview of report is as follows:

Target achieved!