SSRS Expression Help

Question

SSRS Expression Help

ajkarora

SSChasing Mays

Points: 601
More actions
August 18, 2017 at 9:32 am

#402429

I have a SSRS table with the following data

I created a bar graph to get the Goal % average for each operator

But if you look at the numbers they don’t total up correctly with the total Goal %. I am using the following expression to get the data for the graph “=SUM(Fields!Goal.Value)/ CountDistinct(Fields!Operator.Value + cstr(Fields!Date.Value) )”
Since I am grouping by Operator its not picking up the correct number to divide by. Can anyone help me out? I need to show the following numbers on the chart
Alicia: (38.50)/10 = 3.85%
Hortenica: (98.50 + 50.50) / 10 = 14.90%
Lourdes: (45.50+72.00)/10 = 11.75%
Noemia: (13.00)/10 = 1.30%
Other: (10.00)/10 = 1.00%
Patti: (24.00+0.50) / 10 = 2.45
I am dividing by 10 before there are 10 operators between the dates and that number can change.

Viewing 4 posts - 1 through 3 (of 3 total)

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

pietlinden SSC Guru Points: 63509 More actions · Answer 1

Maybe you're leaving something out, because your question doesn't make sense. 100% of WHAT? Do they all contribute to some grand total or something? Adding percentages makes sense if it's all one big whole everyone is contributing to. If day is a new "whole", then that's different. You can't add percentages across days, if that's the case. What is Goal, and how is it calculated? Are you comparing Actual to Goal?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

Along those same lines, does each operator have a different goal or is it a common goal as Pietlinden suggested? If each operator has a different goal which is why you're summing the Fields!Goal.Value, then it can make sense that even though Hortencia and Lourdes have high percents, if they have low individual goals then it doesn't contribute much to the sum of all operator's goals.

If you need to compare each operator's contribution to the total goal of all operators, I'm not sure how to handle that in SSRS. In TSQL it would be a situation to use windowed functions to compute the sum of all goals. You can't just divide the sum of all goals by the number of operators.

pietlinden SSC Guru Points: 63509 More actions · Answer 3

This depends on whether each Person has his own Goal value or everyone has one combined Goal value. If they all have a single goal, say "1000 units", you could create a variable to hold that value. Then each person would have SUM([ActualUnits]). Then in the column next to it (in a matrix), you could do something like

=SUM(Fields!Actuals.Value)/Variables!TotalGoal.Value
but that assumes that they're all working on the same thing... otherwise, adding the percentages doesn't make any sense. 1% of a million is more than 10% of 100....