Click here to Skip to main content
11,642,381 members (68,291 online)
Click here to Skip to main content

Using SSRS - Matrix Control to Generate Columns Dynamically

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

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!

License

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

Share

About the Author

S.Kaur
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 - ASP.net 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.

You may also be interested in...

Comments and Discussions

 
Questionplease help on this by providing query Pin
Member 1184894320-Jul-15 0:02
memberMember 1184894320-Jul-15 0:02 
QuestionThank you for your article Pin
phuythanh9-Jun-15 21:15
memberphuythanh9-Jun-15 21:15 
GeneralThanks it solved my problem too. Also if we add row at below it will give us total of column values. Pin
sachin shelake4-Jun-15 1:12
membersachin shelake4-Jun-15 1:12 
GeneralThanks Pin
Juan Manuel Ramírez Francisco14-Apr-15 8:23
memberJuan Manuel Ramírez Francisco14-Apr-15 8:23 
QuestionGet values for Coaching value Pin
Member 1124638419-Nov-14 0:09
memberMember 1124638419-Nov-14 0:09 
QuestionDynamic Column Generation in RDLC Report Pin
Member 110582203-Sep-14 23:28
memberMember 110582203-Sep-14 23:28 
AnswerRe: Dynamic Column Generation in RDLC Report Pin
S.Kaur10-Nov-14 17:31
memberS.Kaur10-Nov-14 17:31 
QuestionThanx Pin
Member 1049237628-Dec-13 10:47
memberMember 1049237628-Dec-13 10:47 
AnswerRe: Thanx Pin
S.Kaur10-Nov-14 17:31
memberS.Kaur10-Nov-14 17: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
Web04 | 2.8.150731.1 | Last Updated 9 Apr 2013
Article Copyright 2013 by S.Kaur
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid