Click here to Skip to main content
Click here to Skip to main content
Go to top

Using SSRS - Matrix Control to Generate Columns Dynamically

, 9 Apr 2013
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)
India India
I started software development about 4 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, Knockout.js, Lightswitch, SSIS, SSAS, SSRS.

Comments and Discussions

 
QuestionDynamic Column Generation in RDLC Report PinmemberMember 110582203-Sep-14 23:28 
QuestionThanx PinmemberMember 1049237628-Dec-13 10:47 

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 | Mobile
Web04 | 2.8.140905.1 | Last Updated 9 Apr 2013
Article Copyright 2013 by S.Kaur
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid