Formatpercent SSRS Matrix logic?

Question

Formatpercent SSRS Matrix logic?

mariadziewulska

Old Hand

Points: 331
More actions
April 14, 2011 at 3:18 am

#249442

Hello,
Am trying to work out a percentage in an SSRS Matrix.
This calculation involves the hours per activity against total amount of hours per WeekCommencing and per Team.
The columns in the Matrix are: WeekCommencing Team
The rows in the Matrix: Activity (Hours)
The expression I have used: =FormatPercent(Fields!Hours.Value/Sum(Fields!Hours.Value,"DataSet1"),0)
DataSet1 includes:
WeekCommencing
Activity
Team
Hours
The results are incorrect. I am getting a lot of zeros eg:
TEAM WeekCommencing Activity (project) % Total
IMS 07/03/11 4 0 4
The % in this example should be 100%
Any suggestions greatfully appreciated.
Many thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

mariadziewulska (4/14/2011)
Hello,
Am trying to work out a percentage in an SSRS Matrix.
This calculation involves the hours per activity against total amount of hours per WeekCommencing and per Team.
The columns in the Matrix are: WeekCommencing Team
The rows in the Matrix: Activity (Hours)
The expression I have used: =FormatPercent(Fields!Hours.Value/Sum(Fields!Hours.Value,"DataSet1"),0)
DataSet1 includes:
WeekCommencing
Activity
Team
Hours
The results are incorrect. I am getting a lot of zeros eg:
TEAM WeekCommencing Activity (project) % Total
IMS 07/03/11 4 0 4
The % in this example should be 100%
Any suggestions greatfully appreciated.
Many thanks

I am not sure where exactly you are using that expression. I would keep your format and value expressions separate.

For the value of the cell use

=Fields!Hours.Value/Sum(Fields!Hours.Value,"DataSet1")

In the format property for the cell put in P0 (that is a P and a zero) which is shorthand for percentage format with zero decimal places

mariadziewulska Old Hand Points: 331 More actions · Answer 2

Hello,

Thank you for your reply however I am still getting the incorrect data.

Assume X = FormatPercent Expression

The Matrix looks like this:

TEAM WEEKCOMMENCING ACTIVITY (Absence, Maintenance...) X TOTAL

IPS 7/03/2011 4 (hours) 0% 4

X should equal 100%

Any suggestions appreciated

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

If you remove all formatting expressions what do you get for X?

mariadziewulska Old Hand Points: 331 More actions · Answer 4

Hi,

Thanks for your reply. With no formatting the Expression gives a value of zero.

many thanks

quan23 SSCommitted Points: 1506 More actions · Answer 5

TEAM WeekCommencing Activity (project) % Total
IMS 07/03/11 4 0 4
The % in this example should be 100%

Do you only have a single row in your matrix output?

If you have more than one output row then the % in the example above will never be 100% as you are comparing the total for that week/team/activity against the overall total for the whole matrix

quan23 SSCommitted Points: 1506 More actions · Answer 6

Perhaps more helpfully, shouldn't this formula:

=Fields!Hours.Value/Sum(Fields!Hours.Value,"DataSet1")

be changed to this:

=sum(Fields!Hours.Value)/Sum(Fields!Hours.Value,"DataSet1")

As you want to sum up all the hours for that activity/week/team?

mariadziewulska Old Hand Points: 331 More actions · Answer 7

Hello,

Thank you for your suggestions. Have more than one row showing the percentages.

Also have tried your formula to no avail.

Many thanks

quan23 SSCommitted Points: 1506 More actions · Answer 8

If you limit your output to a single row does it show the % as 100%?

mariadziewulska Old Hand Points: 331 More actions · Answer 9

Hello,

Cannot limit to single row since this is a Matrix. The filed Activity has the values: Absence, Training, Maintenance,Management, Support as column names and amount of hours as the body of the Matrix.

I am giving up and writing this in SQL.

Many thanks for all your help

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 10

mariadziewulska (4/15/2011)
Hi,
Thanks for your reply. With no formatting the Expression gives a value of zero.
many thanks

If the result with all formatting removed is zero, then formatting it as a Percent will not make it 100%. There is something wrong with your data or formula.