Reporting Services 2005: add ±, %, average in a matrix table

• tsoulfa

  SSC Rookie

  Points: 25

  More actions

  November 11, 2008 at 10:29 am

  #202612

  Hello, I´m a beginner in reporting services 2005. I don´t know to write code and i try create the below.

  I have created the matrix table:

  units

  =Fields!Reporting_Year.Value

  =Fields!Periods.Value =Fields!units.Value

  and the result is:

  units

  2007 2008

  P_1 35.557 34.842

  P_2 35.601 35.049

  P_3 35.957 35.319

  P_4 35.819 35.661

  P_5 37.351 35.738

  P_6 37.549 36.301

  1) How could I create a column with ± and % ?

  units

  2007 2008 ± %

  P_1 35.557 34.842 -715 -2,01%

  2) At the end of the table i want average of years and no total. how could i do this?

  Thank you in advance for your instant reply....its very urgent!

• h.yasmine82

  SSC Enthusiast

  Points: 182

  More actions

  December 2, 2008 at 8:22 am

  #906258

  hello,

  i have the same problem, i don't know what you do

  please help

• sparky-407434

  Right there with Babe

  Points: 731

  More actions

  December 22, 2008 at 3:23 am

  #916028

  I also want to show averages.

  If this isn't possible can some-one explain how to make a table next to Matrix which has averages of the matrix?

• vicky-954852

  SSC Enthusiast

  Points: 192

  More actions

  December 23, 2008 at 3:16 pm

  #917137

  Hi!!

  I hope this help you, this is a trick, I can't do it in the matrix, but it can be in a second table...

  1. In the dataset that fill the matrix you going to add a new calculus, this going be:

  CASE WHEN Fields!Reporting_Year.Value = 2007 then -Fields!units.Value else Fields!units.Value end AS Dif,

  CASE WHEN Fields!Reporting_Year.Value = 2007 then -Fields!units.Value else 0 end AS FirstYear

  2. Insert a table, the table should have 3 columns

  3. In the properties of the table define the dataset (should be the same that the matrix)

  4. Add the group Fields!Periods.Value

  5. Delete the next rows from the table: group header, table detail and table footer (the table only should have table header and group footer)

  6. -- difference between years

  In the second line and first column write =sum(Fields!Dif.Value)

  7. -- %

  In the second line and second column write =(sum(Fields!Dif.Value)*100)/sum(Fields!FirstYear.Value)

  8. -- average between years

  In the second line and third column write =Sum(Fields!units.Value)/CountDistinct(Fields!Reporting_Year.Value)

  the table should look of this way:

  Difference%Average

  =sum(Fields!Dif.Value)=(sum(Fields!Dif.Value)*100)/sum(Fields!FirstYear.Value)=Sum(Fields!units.Value)/CountDistinct(Fields!Reporting_Year.Value)