Answered by:
Best way to combine data within a group

How can I alter my expression to show a merged cell for the locations located in group A and B (see snapshot below)
Basically the "Limit" and RIIR is based on the sites combined, Unlike the sites located in group C which are measured individually.
What is the best way to do this? Here is the expression that I am currently using to Calculate the RIIR based on the subgroup which is Sites, it's Parent Group is called Location_2.
RIIR Field =
=Fields!YTD_OSHA_RECORDABLES.Value*200000/Fields!YTD_EMPLOYEE_HOURS.Value
Basically I want to keep everything but the Limit and RIIR individual for each site...I just want to combine the RIIR and LIMIT for the groups a and b.
Thanks,
M
 Edited by blumm1 Friday, December 06, 2013 1:30 AM
Question
Answers

Hi,
I think this is possible you will need to use conditional expressions.
The Limit the expression will be as follows:
=iif(fields!group.value like "A" or fields!group.value like "B", sum(fields!Limit.value,"GroupName"), fields.Limit.value)
The RIIR Field expression will be as follows:
=iif(fields!group.value like "A" or fields!group.value like "B", sum(Fields!YTD_OSHA_RECORDABLES.Value*200000/Fields!YTD_EMPLOYEE_HOURS.Value,"GroupName"),
Fields!YTD_OSHA_RECORDABLES.Value*200000/Fields!YTD_EMPLOYEE_HOURS.Value)
You will need to conditionally hide the Limit and RIIR values for textboxes A1, A2 & B1 & B2.
Thanks
 Marked as answer by blumm1 Wednesday, December 11, 2013 4:32 PM

Hello,
Based on my research, it seems that you want to add the sum value of YTD_RESTRICTED_DUTY_CASES field and the sum value of YTD_LOST_TIME_CASES field together firstly, and multiply by 20000, then divided by the sum value of YTD_EMPLOYEE_HOURS field to calculate the Dart Rate value. If in this case, please see the following expression:
=iif(Fields!Location_Level_2.Value like "A" or Fields!Location_Level_2.Value like "B", (sum(Fields!YTD_RESTRICTED_DUTY_CASES.Value)+sum(Fields!YTD_LOST_TIME_CASES.Value))*200000/sum(Fields!YTD_EMPLOYEE_HOURS.Value), "")
If there are any misunderstanding, please elaborate the issue for further investigation.
Regards,
Katherine Xiong
Katherine Xiong
TechNet Community Support Edited by Katherine XiongMicrosoft contingent staff, Moderator Monday, December 09, 2013 2:35 AM
 Marked as answer by blumm1 Wednesday, December 11, 2013 4:32 PM
All replies

Hi,
I think this is possible you will need to use conditional expressions.
The Limit the expression will be as follows:
=iif(fields!group.value like "A" or fields!group.value like "B", sum(fields!Limit.value,"GroupName"), fields.Limit.value)
The RIIR Field expression will be as follows:
=iif(fields!group.value like "A" or fields!group.value like "B", sum(Fields!YTD_OSHA_RECORDABLES.Value*200000/Fields!YTD_EMPLOYEE_HOURS.Value,"GroupName"),
Fields!YTD_OSHA_RECORDABLES.Value*200000/Fields!YTD_EMPLOYEE_HOURS.Value)
You will need to conditionally hide the Limit and RIIR values for textboxes A1, A2 & B1 & B2.
Thanks
 Marked as answer by blumm1 Wednesday, December 11, 2013 4:32 PM

Im not sure if I am doing something wrong, but it is not calculating correctly.
This the expression I used for the RIIR for example:
=iif(Fields!Location_Level_2.Value like "A" or Fields!Location_Level_2.Value like "B", sum(Fields!YTD_OSHA_RECORDABLES.Value*200000/Fields!YTD_EMPLOYEE_HOURS.Value,"Location_Level_21"), Fields!YTD_OSHA_RECORDABLES.Value*200000/Fields!YTD_EMPLOYEE_HOURS.Value)
The total column should be 10.20 using the forumla Sum of Recordables*200000/Sum of Employee Hours 15*200000/294156.51

My followup question has been fixed
I used the following expression for RIIR grouping total:
=iif(Fields!Location_Level_2.Value like "A" or Fields!Location_Level_2.Value like "B", sum(Fields!YTD_OSHA_RECORDABLES.Value)*200000/sum(Fields!YTD_EMPLOYEE_HOURS.Value), Fields!YTD_OSHA_RECORDABLES.Value*200000/Fields!YTD_EMPLOYEE_HOURS.Value)
However, I have another field that is not calculating correctly called Dart Rate
=iif(Fields!Location_Level_2.Value like "A" or Fields!Location_Level_2.Value like "B", sum(Fields!YTD_RESTRICTED_DUTY_CASES.Value)+sum(Fields!YTD_LOST_TIME_CASES.Value)*200000/sum(Fields!YTD_EMPLOYEE_HOURS.Value), "")
I think it has something to do with how I have to add two fields to get a value before completing the calculation because the same expression calculated correctly for Group B, and group B was a 0+1 while group A was a 8+8 . Is there a special way I should add two values before multiplying that then dividing?

Hello,
Based on my research, it seems that you want to add the sum value of YTD_RESTRICTED_DUTY_CASES field and the sum value of YTD_LOST_TIME_CASES field together firstly, and multiply by 20000, then divided by the sum value of YTD_EMPLOYEE_HOURS field to calculate the Dart Rate value. If in this case, please see the following expression:
=iif(Fields!Location_Level_2.Value like "A" or Fields!Location_Level_2.Value like "B", (sum(Fields!YTD_RESTRICTED_DUTY_CASES.Value)+sum(Fields!YTD_LOST_TIME_CASES.Value))*200000/sum(Fields!YTD_EMPLOYEE_HOURS.Value), "")
If there are any misunderstanding, please elaborate the issue for further investigation.
Regards,
Katherine Xiong
Katherine Xiong
TechNet Community Support Edited by Katherine XiongMicrosoft contingent staff, Moderator Monday, December 09, 2013 2:35 AM
 Marked as answer by blumm1 Wednesday, December 11, 2013 4:32 PM