May 4, 2016 at 7:58 am
Guys, I am having trouble working out how to get the correct values in my report.
I have a tablix with three rowGroups:
Inner: SorCode
Middle: JobID
Outer: operative.
an operative will carry out a number of jobs and a job will have 1:n SorCodes
To Calculate the value of the job we aggregate the sorCode values UNLESS the total of the SorCodes is < £10 in which case we call the job £10 and give them the percentage of the job they did (some jobs need 2 or three operatives). I have the following expression in both the JobID group total and the operative Group total
=IIF
(
SUM(Fields!OperativeCalcPay.Value)=0,
0,
IIF(
(
Fields!TSK_SORCode.Value="NOACCESS" OR
Fields!TSK_SORCode.Value="CAR181-R" OR
Fields!TSK_SORCode.Value="CAR183-R" OR
Fields!TSK_SORCode.Value="BWK035-R" OR
Fields!TSK_SORCode.Value="PLU131-R" OR
Fields!TSK_SORCode.Value="RFR021-R" OR
Fields!TSK_SORCode.Value="GRD111-R" OR
Fields!TSK_SORCode.Value="ELE153-R"
),
SUM(Fields!OperativeCalcPay.Value),
IIF(
Fields!CurrentJobLabourCost.Value < 10,
10 * (Fields!ROUND_OperativeApportionment.Value / 100),
SUM(Fields!OperativeCalcPay.Value)
)
)
)
This code says: If the CalcPay is zero then Zero ELSE if one of a list of sorcodes then actual value ELSE if Less than 10, round up to 10 and give then a split of the job value (typically 100, 50 or 33.3)
For example.
Joe does two jobs:
Job 1: SorCode 1 = 4.55 (100% of job)
Job 2: SorCode 1 = 7.88 (50 % of job)
Job 2: SorCode 2 = 4.55 (50% of job)
He should get
Job 1 Total = £10 (Call the job £10 and give him 100% of it)
Job 2 Total = £12.43 (sum of 7.88 and 4.55 - even though each SorCode is worth less than 10, the whole job is worth more)
This works great at the Job Level, however because of the scoping, at Operative level it reports
£16.98 (4.55+7.88+4.55) instead of £22.43 (10.00+12.43)
I am using 2008, NOT 2008R2.
I can't put the logic into the dataset as it is based on an aggregate
I have tried moving the logic to a rowGroup variable, but in 2008 I don't seem to be able to access the variable at the outer group level (I have tried as an expression on the report and an expression in a rowGroup variable in the outer group)
Do I need to move the logic to code and if so how would I translate the group aggregate logic because all the examples I have seen are based on the calculation at detail level.
HELP! :w00t:
May 5, 2016 at 2:25 am
Having spent some time scratching my head, and as a result of a number of folks looking at this post but with no replies, I am posting the solution.
=ROUND(SUM(
IIF(
SUM(Fields!OperativeCalcPay.Value,"rgJobNumb")=0,
0,
IIF(
(
Fields!TSK_SORCode.Value="NOACCESS" OR
Fields!TSK_SORCode.Value="CAR181-R" OR
Fields!TSK_SORCode.Value="CAR183-R" OR
Fields!TSK_SORCode.Value="BWK035-R" OR
Fields!TSK_SORCode.Value="PLU131-R" OR
Fields!TSK_SORCode.Value="RFR021-R" OR
Fields!TSK_SORCode.Value="GRD111-R" OR
Fields!TSK_SORCode.Value="ELE153-R"
),
SUM(Fields!OperativeCalcPay.Value,"Details"),
IIF(
Fields!CurrentJobLabourCost.Value < 10,
ROUND(10 * (Fields!ROUND_OperativeApportionment.Value / 100),2),
SUM(Fields!OperativeCalcPay.Value,"Details")
)
)
)
),2)
I had to specify the scope of the first IIF to evaluate at the Job level, this then causes the inner logic to operate only on the detail records within the Job scope (by specifying the detail scope). The original query is then wrapped in a SUM to take the values scoped at Job and give me the grand total. A little bit of tidying up is then needed to ensure that the values are rounded to the nearest penny and there are some divides going on.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply