Friday, March 9, 2012

How to add columns dynamically based on number of groups used.

Hi,

I am pretty new to the Microsoft Reporting services. I wanted to know if this is possible to design in the reporting server.

I have a requirement to take the number of groups in my report as input. So if there are two groups I will have two columns and if I have three groups I will have three colums. Is there any way I can dynamically set the number of columns based on the input.

Currently I am having to design different report for different number of groups. This causes more maintenance in case of frequent report design changes.
Thanks in advance,
Ragz.To Elaborate further on this question.

If I have a table like

Employee(EmployeeName varchar(100),EmployeeManagerName varchar(100),team varchar(100),experience int)

I take the following inputs:
The number of groups and what to group on.

For example one input might be:
No of groups:1
group-on:EmployeeName.
The resulting report will have
Employee-Name experience
xxxx yyyy
But If I have Number of Groups as 2 and
group-on as
EmployeName and EmployeemanagerName
then i will have three columns Employee-name Employee-manager-name experience

The questions is how can I generate these columns dynamically ?|||Yes, you can do this. The way to do this is:
1) Create a report parameter that specifies the number of groups
2) to add a calculated field to your data set (right click on the Fields window and choose add).
2.1) the expression for the field would select which of the three groups you'd have (e.g. 1, 2, or 3... or what ever you need)
3) Create a matrix report
3.1) group the columns on your constructed parameter

That should probably work.

-Lukasz

No comments:

Post a Comment