Report Builder 3 - Calculating totals for a conditional expression
Hi. I am trying to get a total of available time per month for both branch and an overall total: The available time per month per person is calculated using a conditional expression: =iif(Fields!personOccupation.Value = "CON", Sum(Fields!AvailableTimeCON.Value), Fields!AvailableTimeEMP.Value) where if the person is a contractor (CON), their available time is their actual worked hours (AvailableTimeCON is a calculated field that adds all actual worked hours), but if a person is an employee (EMP), their available time is calculated based on their yearly budgeted hours. AvailableTimeEMP is a calculated field with the following expression: =iif(Fields!personOccupation.Value = "EMP", iif(Trim(Fields!feePin.Value) = "DUB", (Fields!feeBudRec.Value / 261) * Fields!SwitchMonthsDUB.Value, (Fields!feeBudRec.Value / 261) * Fields!SwitchMonthsUK.Value), Nothing) This calculates that if the person is employee and in the DUB branch, then it takes their yearly budgeted hours, divides by working days per year, and multiplies by how many days are in the working month (from calculated field SwitchMonthDUB), and if a person is an employee in any other branch, then their yearly budgeted hours are divided by working days per year, and multiplied by how many days are in the working month (from calculated field SwitchMonthUK). The number of yearly budgeted hours differs from employee to employee. I have been unable to work out what expression I need to use to calculate both the total available time per branch, and an overall total for all branches, and really hope somebody can point me in the right direction with how to do this!
July 13th, 2012 6:59am

Hi There Thanks for your posting. Can you please try this expression =sum(iif(Fields!personOccupation.Value = "CON", Sum(Fields!AvailableTimeCON.Value), Fields!AvailableTimeEMP.Value),"yourgroupname") Please put your branch group name above =sum(iif(Fields!personOccupation.Value = "CON", Sum(Fields!AvailableTimeCON.Value), Fields!AvailableTimeEMP.Value),"yourDataset") =sum(iif(Fields!personOccupation.Value = "CON", Sum(Fields!AvailableTimeCON.Value), Fields!AvailableTimeEMP.Value),"yourgroupname") Please put your branch group name above =sum(iif(Fields!personOccupation.Value = "CON", Sum(Fields!AvailableTimeCON.Value), Fields!AvailableTimeEMP.Value),"yourDataset") For overall toal Please put your dataset name above and see what would be the outcome of that Many thanks Syed Qazafi Anjum
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2012 7:39am

Thanks for your response, Syed. Unfortunately, neither of those expressions worked for me. Using =sum(iif(Fields!personOccupation.Value = "CON", Sum(Fields!AvailableTimeCON.Value), Fields!AvailableTimeEMP.Value),"feePin") to calculate the branch totals ended up with this as a result: which is the same result I get if I use =sum(iif(Fields!personOccupation.Value = "CON", Sum(Fields!AvailableTimeCON.Value), Fields!AvailableTimeEMP.Value)) without putting in the feePin group name. Using =sum(iif(Fields!personOccupation.Value = "CON", Sum(Fields!AvailableTimeCON.Value), Fields!AvailableTimeEMP.Value),"DataSet1") to calculate the overall total results in this error: I tried taking out the Sum part of Sum(Fields!AvailableTimeCON.Value), and the result was a ridiculously large number, similar to the result of the above branch total expression.
July 13th, 2012 8:01am

Hi There I dont know why you are using sum in this expression =iif(Fields!personOccupation.Value = "CON", Sum(Fields!AvailableTimeCON.Value), Fields!AvailableTimeEMP.Value) I think you need to use this =sum(iif(Fields!personOccupation.Value = "CON", (Fields!AvailableTimeCON.Value), Fields!AvailableTimeEMP.Value),"feePin") Please put your branch group name above =sum(iif(Fields!personOccupation.Value = "CON", (Fields!AvailableTimeCON.Value), Fields!AvailableTimeEMP.Value),"DataSet1") Many thanks Syed
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2012 8:08am

Thank you for your further reply! I need to use Sum(Fields!AvailableTimeCON.Value) to calculate the individual available time as the contractors available time for the month is made of multiple time entries. You are correct though, I do not need to use it in the Branch and overall total expressions. Using =sum(iif(Fields!personOccupation.Value = "CON", Fields!AvailableTimeCON.Value, Fields!AvailableTimeEMP.Value),"feePin") for the Branch total gives the correct result when there is only a contractor in a branch. If there are employees in the branch as well, the branch total is definitely not correct - for example, the DUB branch total should be 970.0, whereas the expression is returning 30,799.2. As you can see, using =sum(iif(Fields!personOccupation.Value = "CON", Fields!AvailableTimeCON.Value, Fields!AvailableTimeEMP.Value),"DataSet1") for the overall total also does not work.
July 13th, 2012 9:29am

Hi There Thanks for your posting again. I think I understand What is happening actually. Do you need the sum of both Employee as well as CON on the Branch level and on total level or just CON please give some more deatils. If you just need the sum of COn then use this expression otherwise please give some more deatils =sum(iif(Fields!personOccupation.Value = "CON", Fields!AvailableTimeCON.Value, 0),"feePin") =sum(iif(Fields!personOccupation.Value = "CON", Fields!AvailableTimeCON.Value, 0),"DataSet1") =sum(iif(Fields!personOccupation.Value = "CON", Fields!AvailableTimeCON.Value, 0),"feePin") =sum(iif(Fields!personOccupation.Value = "CON", Fields!AvailableTimeCON.Value, 0),"DataSet1") many thanks Syed Qazafi Anjum
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2012 10:07am

Thank you for the time and effort you're putting in to help me out with this problem! Unfortunately, I do need to get the sum of both the employees available hours and the contractors available hours, for each branch, and for the overall total. So, for example, with the screenshot in my previous post, the LIV branch total should be 739 (28.6 for the first contractor + 80.4 for the second contractor + 157.5 x 4 for each of the four employees). The expressions you've given definitely result in the correct totals for contractors only, so the problem seems to be with getting the sums of the employee available time for each branch/overall total.
July 16th, 2012 4:03am

Hi There Thanks for your posting. Can you please let me know that how you are calculating Fields!AvailableTimeEMP.Value. Are you calculating using expression inside SSRS or inside your dataset? So in the above example you have mentioned how you are calculating 157.5 It seems that available time contain both time for CON as well as EMP. Please send more detail or alternative send the RDL file to sqazafi@hotmail.com Many thanks Syed Qazafi Anjum
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 4:19am

Hi Syed, I have sent you an email with the rdl file and some more information about how the monthly employee available hours are calculated. Thanks again for your help!
July 16th, 2012 5:03am

Hello tefkae, we have not heard any progress on this issue. Is it solved? If you solved this issue, please share us the solution of this issue. Regards, EdwardEdward Zhu TechNet Community Support
Free Windows Admin Tool Kit Click here and download it now
August 16th, 2012 4:56am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics