Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right". What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.Is this possible???Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window. Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

How would I place this column to the RIGHT of the Subtotal column???

Add an invisiblecolumn group which will be grouped bythe percentage expressionand add the subtotal to this colum group have the which will give youthe sum of all individual percentages. Shyam

Here is the error message I am receiving:"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.Thank you both greatly for your help. I feel about 75% towards getting this to work.Jason

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be: Fields!InnerGroupField/Fields!OuterGroupField Then using the subtoal would automatically mean a sum which is what you want I guess. Shyam

What if the percentage I want to calculate is the ratio between two columns? Is this possible, or willI have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

hi, I have same Problem.I want to display Total Present and Total Absent at the End of the Rows. I am using Matrix(VS2005). I used Aggergate function for displaying Total Present and Total Absent. But it shows A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions Error. Please answer. its urgent. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 Mahesh P P P P P P P P P P P P P P P P P P P P P P P P P P P P P P 2 Alok A P P P P P P P P P A P P P P P P P P P P P P P P P P P P P

Hi.I am facing same issue with my report. I have a matrix in my report. I need to add a percentage column to the right of subtoal ( total) column. I tried to do mentioned in the forumn, but it didn;t work out for me. Can any body help me in this in detail? I have 1 column of Female ( In which Number of female value)and column of Male (In which Number of male value ) and 3rd is subtotal ( toal) ..addition of male and female...and now I need 4th column which is Percentage ..!! In this percentage column, I will have addition of male and female / total number of male and female...that is % of male/female population to the total..thanks..in adavcne..

Were you able to find a solution to this?

Sorry for late reply..I am still searching for solution for this.? I need to add % column...on the right of "Total" coulmn in my matrix in SQL Report 2005.any idea

I was trying to look for this as well. but can't find anything that is normal out there.Well this is an actual project of mine so i need to provide a solution or else i will be FIRED! :D...What i did is include the Total and % dimension in my sql.its like--build the normal datainsert into @temp..whatever..--build the total dimensioninsert into @temp..select..group by ...--build the % dimensioninsert into..select...group by ...(in report, i just verify if label has "%" and i change the format of matrix cell to #%)I hate the solution to be honest because you need to play a lot in grouping and sorting to come out to the solution, and there's unnecessary overhead from sql server (like creation of total dimension and %). but it's good than nothing.. :p...

I know last post is almost one year aged but ... I cannot find a solution to the same problem! Just to recall: - one dataset with many rows (qty counts of different types of stuff ex.: LEV1 LEV2 TYPE RECNUM TYPE1 GREEN SOUTH 2 TYPE1 YELLOW SOUTH 4 TYPE1 GREEN SOUTH 2 TYPE1 RED NORTH 3 TYPE2 SOFT SOUTH 2 TYPE2 HARD NORTH 5 TYPE2 HARD SOUTH 3 TYPE2 SOFTER NORTH 2 - the report is matrix-style (i.e.: pivot) with a drill-down on LEV1 ... something like this SOUTH % NORTH % TOTAL + TYPE1 GREEN 4 100,00% 0 0,00% 4 YELLOW 4 100,00% 0 0,00% 4 RED 0 0,00% 3 100,00% 3 + TYPE2 SOFT 2 100,00% 0 0,00% 2 HARD 3 37,50% 5 62,50% 8 SOFTER 0 0,00% 2 100,00% 2 The column I'd like to insert is the one with the percentage (SOUTH-NORTH over TOTAL in the example). I can't figure out how to solve the problem and from a search all over the net it seems that is not only a problem of mine! By the way ... merry Christmas Giampaolo