locked
Reporting Services - sum columns and sum of resolved value RRS feed

  • 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

Answers

  • 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 lukas317 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 lukas317 Saturday, July 7, 2012 4:19 PM
    • Marked as answer by Edward Zhu 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 lukas317 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.
    thanks for answer

    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 lukas317 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 lukas317 Saturday, July 7, 2012 4:19 PM
    • Marked as answer by Edward Zhu Monday, July 9, 2012 2:14 AM
    Saturday, July 7, 2012 4:14 PM