none
Best way to combine data within a group

    Question

  • 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
    Friday, December 06, 2013 1:26 AM

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
    Friday, December 06, 2013 12:19 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

    Monday, December 09, 2013 2:34 AM

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
    Friday, December 06, 2013 12:19 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

    Friday, December 06, 2013 6:30 PM
  • 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?

    Friday, December 06, 2013 7:55 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

    Monday, December 09, 2013 2:34 AM
  • Thank you, Katherine.  Your instructions worked perfectly.
    • Marked as answer by blumm1 Wednesday, December 11, 2013 4:31 PM
    • Unmarked as answer by blumm1 Wednesday, December 11, 2013 4:32 PM
    Wednesday, December 11, 2013 4:31 PM