November 3, 2010 at 6:00 am
Hi
I have a Tablix with a row grouping by Location and a row field Totalling the current employee count for that state
I have three adjacent columns grouping by Month, based on what month is selected in the multivalue-parameter, one column returns back number of employees joined for those months. second column how many left for those month. The third column the total headcount for those months
FOR Example if the parameter selected was Jan, Feb, March, the below would be returned
JOIN LEFT HEADCOUNT
CURRENTJan Feb MarchJan Feb March Jan Feb March
WA 15 2 3 4 3 1 2 14 16 18
VIC 23 1 4 5 3 4 1 21 21 28
In the headcount I would like the total headcount to be a running value
ie for the WA Jan Headcount column it should be Current + Join - Left = 15 + 2 - 3 = 14
In february I would like to use the Value which was calculated in the month of January
i.e 14 + Join - Left = 14 + 3 - 1 = 16
In March it would use the Value which was calculated in the month of Feb
16 + join - Left = 16 + 4 - 2 = 18
The value I have under the Column month grouping is
Sum(field.currentemployee.value,"Location") + sum(field.join.value) - sum(field.left.value)
This is the correct number for the Jan column headcount but for Feb and MARCH The starting value is incorrect. Because the Months are dynamic Im not sure how to do this
The output above is what I would like to achieve...can anyone please help on this problem, can this be done at all ?
thanks in advance
November 3, 2010 at 8:30 am
You should look into using runningvalue instead of sum in you calculation, corrected for the value in the current month (because you want the standing at the beginning of the month). Something like:
=RunningValue(field!join.value, sum, "YouDataSet")-field!join.value
- RunningValue(field!left.value, sum, "YouDataSet") + field!left.value
Since I can not properly what your data look like, I'm not sure this cracks your case. Please check (for instance): http://www.sqlservercentral.com/articles/Best+Practices/61537/
Peter Rijs
BI Consultant, The Netherlands
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply