SSRS SUM(IIF()) on a matrix.

Question

SSRS SUM(IIF()) on a matrix.

david_h_edmonds

Default port

Points: 1430
More actions
March 28, 2019 at 7:53 am

#2033903

Hi, apologies if I am asking a question that has been asked many times but I can't find the answer that I need.
I have a matrix in ssrs and I am wanting to add a column into it that shows a value as a perentage of the row total as you can do in excel as follows:

I have tried sum(iff()
I have tried sum/sum
I have tried helper fields and I still can't get it going.
Does anyone have a way to explain this for me please?
Expr for the field is:
=sum(Fields!Booking_Count.Value,"matrix1_Booking_Channel")/sum(Fields!Booking_Count.Value)
My matrix is as follows:
Cheers,
Dave

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

david_h_edmonds - Thursday, March 28, 2019 7:53 AM
Hi, apologies if I am asking a question that has been asked many times but I can't find the answer that I need.
I have a matrix in ssrs and I am wanting to add a column into it that shows a value as a perentage of the row total as you can do in excel as follows:

I have tried sum(iff()
I have tried sum/sum
I have tried helper fields and I still can't get it going.
Does anyone have a way to explain this for me please?
Expr for the field is:
=sum(Fields!Booking_Count.Value,"matrix1_Booking_Channel")/sum(Fields!Booking_Count.Value)
My matrix is as follows:
Cheers,
Dave

Percent of the row total would just be the count/total count in the row * 100. I can't tell where the total count is on your matrix, if there are multiple rows with a totals row, grouped by channel, branch or however it's set up.
In your first example, the expression for the Blue percent of row would be the blue count / total count for all the colors in the row * 100.
=Sum(Fields!BlueCount.Value)/Sum(Fields!BlueCount.Value + Fields!OtherCount.Value + Fields!RedCount.Value + Fields!YellowCount.Value)*100

Sue

david_h_edmonds Default port Points: 1430 More actions · Answer 2

Sue_H - Thursday, March 28, 2019 7:14 PM
david_h_edmonds - Thursday, March 28, 2019 7:53 AM
Hi, apologies if I am asking a question that has been asked many times but I can't find the answer that I need.
I have a matrix in ssrs and I am wanting to add a column into it that shows a value as a perentage of the row total as you can do in excel as follows:

I have tried sum(iff()
I have tried sum/sum
I have tried helper fields and I still can't get it going.
Does anyone have a way to explain this for me please?
Expr for the field is:
=sum(Fields!Booking_Count.Value,"matrix1_Booking_Channel")/sum(Fields!Booking_Count.Value)
My matrix is as follows:
Cheers,
Dave
Percent of the row total would just be the count/total count in the row * 100. I can't tell where the total count is on your matrix, if there are multiple rows with a totals row, grouped by channel, branch or however it's set up.
In your first example, the expression for the Blue percent of row would be the blue count / total count for all the colors in the row * 100.
=Sum(Fields!BlueCount.Value)/Sum(Fields!BlueCount.Value + Fields!OtherCount.Value + Fields!RedCount.Value + Fields!YellowCount.Value)*100
Sue

Hi Sue, thanks for the reply. I am after a dynamic expression something like:

=Sum(current column group member)/Sum(all column group members)*100

Is this even possible?

Cheers

Dave

David Burrows SSC Guru Points: 65138 More actions · Answer 3

Think you want something like this
=(Sum(Fields!Booking_Count.Value)/ReportItems!Textbox1.Value)*100

Replace Textbox1 with the actual name of the Total Count textbox

Far away is close at hand in the images of elsewhere.
Anon.

david_h_edmonds Default port Points: 1430 More actions · Answer 4

David Burrows - Friday, March 29, 2019 7:15 AM
Think you want something like this
=(Fields!Booking_Count.Value/ReportItems!Textbox1.Value)*100
Replace Textbox1 with the actual name of the Total Count textbox

Ok this could work but there is 1 more complication, there is a total for each row of the matrix so I would need a total for each member of the row group and then lookup up the correct total based on the row group member.

Revising what I put earlier, that alters to:

=Sum(current column group member and row group member)/Sum(all column group members for current row group member)*100
How could I achieve this?
Or am I missing the point? I am honestly so confused by this, Excel does it in 2 clicks and this seems so complicated.

Many thanks,
Dave

David Burrows SSC Guru Points: 65138 More actions · Answer 5

Sorry missed the Sum

=(Sum(Fields!Booking_Count.Value)/ReportItems!Textbox1.Value)*100

*Edited my original post as well

Far away is close at hand in the images of elsewhere.
Anon.