# Reporting Services - sum columns and sum of resolved value • ### Question

• Hello,

I have two simple questions.

The first questions is:   How to sum columns in matrix, I have 5 columns and I want  column sum 1 and 2.

The  second question is:  How to sum rows. I create query =avg(iif(filds!urgency.value=1 and filds!_time<=14,1,iif(filds!urgency.value=2 and filds!_time<=130,1,0)), everything is ok, but I must sum average of average .

Tuesday, June 19, 2012 9:52 AM

• Question 1) Create a Calculated Field e.g. "=Field1 + Field2"

Report Data --> Right Click DataSet --> Add Calculated Field

Question 2) Add a Total

Right click field in question --> Add Total

Bonediggler

• Marked as answer by Wednesday, June 20, 2012 5:47 AM
Tuesday, June 19, 2012 3:37 PM
• Hello
I found solution in my problem, may be useful to someone
It is two queries.
The first query:
AVG of AVG  vertical

=( sum(   iif(Month(Fields!Date.Value)<>1 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1,1,0) )   )/sum(iif(Month(Fields!Date.Value)=1 and Fields!nrtickets.Value,1,0) ) + sum(   iif(Month(Fields!Date.Value)<>2 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1,1,0) )   )/sum(iif(Month(Fields!Date.Value)=2 and Fields!nrtickets.Value,1,0) ) + sum(   iif(Month(Fields!Date.Value)<>3 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1,1,0) )   )/sum(iif(Month(Fields!Date.Value)=3 and Fields!nrtickets.Value,1,0) ) ) /3

the second query:
AVG of AVG horizontal

=( avg(   iif(Fields!urgency.Value<>1 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1 and Fields!urgency.Value=1,1,0) )   ) + avg(   iif(Fields!urgency.Value<>2 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1 and Fields!urgency.Value=2,1,0) )   ) +avg(   iif(Fields!urgency.Value<>3 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1 and Fields!urgency.Value=3,1,0) )   )+avg(   iif(Fields!urgency.Value<>4 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1 and Fields!urgency.Value=4,1,0) )   )  )/countdistinct(Fields!urgency.Value)

• Edited by Saturday, July 7, 2012 4:19 PM
• Marked as answer by Monday, July 9, 2012 2:14 AM
Saturday, July 7, 2012 4:14 PM

### All replies

• Question 1) Create a Calculated Field e.g. "=Field1 + Field2"

Report Data --> Right Click DataSet --> Add Calculated Field

Question 2) Add a Total

Right click field in question --> Add Total

Bonediggler

• Marked as answer by Wednesday, June 20, 2012 5:47 AM
Tuesday, June 19, 2012 3:37 PM
• This tutorial will help you understand how column and row totals work.

http://msdn.microsoft.com/en-us/library/bb630415(v=sql.100).aspx

Thanks

Francis, MCTS: BI 2008, MCITP: BI 2008 Stay Thirsty My Friends

Tuesday, June 19, 2012 5:22 PM
• I'm sorry,
The first questions: Is OK, I have problem with value from sql, because the value was varchar not int. It's my mistake.

Question 2->  Is not the simple, when I use "add total", gets the results sum of avg, but I want avg of avg.  correctly results is 97,50%

MostInterestingMan I'll check this link. thansk

• Edited by Wednesday, June 20, 2012 7:00 AM
Wednesday, June 20, 2012 5:47 AM
• Hello

Maybe someone knows how to solve this problem

Monday, July 2, 2012 8:15 AM
• Hello
I found solution in my problem, may be useful to someone
It is two queries.
The first query:
AVG of AVG  vertical

=( sum(   iif(Month(Fields!Date.Value)<>1 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1,1,0) )   )/sum(iif(Month(Fields!Date.Value)=1 and Fields!nrtickets.Value,1,0) ) + sum(   iif(Month(Fields!Date.Value)<>2 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1,1,0) )   )/sum(iif(Month(Fields!Date.Value)=2 and Fields!nrtickets.Value,1,0) ) + sum(   iif(Month(Fields!Date.Value)<>3 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1,1,0) )   )/sum(iif(Month(Fields!Date.Value)=3 and Fields!nrtickets.Value,1,0) ) ) /3

the second query:
AVG of AVG horizontal

=( avg(   iif(Fields!urgency.Value<>1 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1 and Fields!urgency.Value=1,1,0) )   ) + avg(   iif(Fields!urgency.Value<>2 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1 and Fields!urgency.Value=2,1,0) )   ) +avg(   iif(Fields!urgency.Value<>3 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1 and Fields!urgency.Value=3,1,0) )   )+avg(   iif(Fields!urgency.Value<>4 and Fields!nrtickets.Value,nothing,iif(  Fields!scre.Value=1 and Fields!urgency.Value=4,1,0) )   )  )/countdistinct(Fields!urgency.Value)

• Edited by Saturday, July 7, 2012 4:19 PM
• Marked as answer by Monday, July 9, 2012 2:14 AM
Saturday, July 7, 2012 4:14 PM