SSRS 2005 Matrix repport-finding Avg for one column in matrix report Total

Question

SSRS 2005 Matrix repport-finding Avg for one column in matrix report Total

sandy1234

Say Hey Kid

Points: 673
More actions
April 4, 2012 at 8:23 am

#255882

Hello friends,
I am working the SSRS 2005 Matrix report. My requirement is, I have to display the Avg of one column in the Total Field instead of Sum of all columns.
Please provide me some solution.
Thanks in Advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

sandy1234 Say Hey Kid Points: 673 More actions · Answer 2

Thanks for the reply.

I didn't get you properly.

The problem i am having is, all the columns are populating dynamically in the Matrix report. So to find the Avg for a particular column total Avg and all the remaining columns total as Sum is difficult.

And can you please let me know how to use the Avg function in the Matrix report to find the Totals.

Thank you

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

Ahh I think I see your problem.

I think that you will have to add another Total row in your matrix table and in the expression editor use the average expression:

AVG(Fields!YourField)

This will put an average in each of your columns in the matrix however...

There may be another way not sure..

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

sandy1234 Say Hey Kid Points: 673 More actions · Answer 4

I found the solution using the Switch function

For the desired columns which requires avg of the Column records.

Here is the Function Used:

=Switch(Fields!RevenueType.Value="Column11", Round(Avg(Fields!Amount.Value),2)

,Fields!RevenueType.Value="Column10" ,Round(Avg(Fields!Amount.Value),0)

,sum(Fields!Amount.Value))

Thank you