CODEWITHSUNDEEP
reporting-services
KellyLynch
KellyLynch

Reputation: 251

Create Report with dynamic columns

I have SQL Server 2005 Reporting Services. I need to create a report with number of columns that is defined during runtime.

I have a DB table with content like the following:

Person           Date             Val
----------------------------------------------
Person1          2012-01-03       3
Person2          2012-02-11       5
Person1          2012-02-17       7
Person2          2012-01-19       2
Person2          2012-01-15       4

I have to create a Report like this:

Person           Jan 2012         Feb 2012
----------------------------------------------
Person1          3                7
Person2          6                5

In other words, for every Person I have to make sum of all his Value fields for a given month, and put the sum into column corresponding to the month. In the example above I have 6 for Person2 during month Jan 2012 - this is sum of the values 2 (on 2012-01-19) and 4 (on 2012-01-15).

Thus, in design time I do not know the date range covered by that my table. I should detect it, to build array of months covered by the date range (the Jan 2012 and Feb 2012 in the sampel above) and add a column for each of the months.

How should I implement this using SSRS?

Upvotes: 2

Views: 10531

Answers (2)

Irawan Soetomo
Irawan Soetomo

Reputation: 1325

If you're stuck with Tablix, here's a similar situation that I have solved, How do i represent an unknown number of columns in SSRS?.

Upvotes: 1

Jamie F
Jamie F

Reputation: 23789

In SQL 2008 and later the approach to this problem changed a little bit, so make sure that you are looking at reference materials for 2005 if that is what you are using.

On this page, look for the Matrix section. Matrices in SSRS are designed to solve exactly the problem you describe with minimal effort. In 2008 Matrices and Tables are both variants of the "Tablix:" a control that can have both row and column groups.

Upvotes: 2

Related Questions