Click here to Skip to main content
Click here to Skip to main content

Using SSRS - Matrix Control to Generate Columns Dynamically

, 9 Apr 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
This will help you generate columns dynamically in SSRS reports using Matrix control.


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.


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!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
Australia Australia
I started software development about 5 years ago. As part of my education I did Bachelor's in Computer applications and Masters in Computer applications. My skillset comprises of - MVC, jQuery, AJAX, Lightswitch, SSIS, SSAS, SSRS.
These days I am working on migration to Dynamics CRM using KingswaySoft which is a SSIS Integration toolkit for Microsoft Dynamics CRM.

Comments and Discussions

GeneralThanks PinmemberJuan Manuel Ramírez Francisco14-Apr-15 9:23 
QuestionGet values for Coaching value PinmemberMember 1124638419-Nov-14 1:09 
QuestionDynamic Column Generation in RDLC Report PinmemberMember 110582204-Sep-14 0:28 
AnswerRe: Dynamic Column Generation in RDLC Report PinmemberS.Kaur10-Nov-14 18:31 
QuestionThanx PinmemberMember 1049237628-Dec-13 11:47 
AnswerRe: Thanx PinmemberS.Kaur10-Nov-14 18:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150414.1 | Last Updated 9 Apr 2013
Article Copyright 2013 by S.Kaur
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid