how to get a percentage corresponding to groups on my report?

[Former Member]

Hi All,

I have a group on my report by USER_ID and then by Activity_Name and I am displaying the detailed data for LOG_DATE, LOG_VALUE, STD_TIME and PRODUCTIVE_HOURS as seen below:

USER_ID                    LOG_DATE          LOG_VALUE          STD_TIME          PRODUCTIVE_HOURS

abcdef                        

Chart Maintenance     

                                   08/15/2016               10                         2.5                         7.5

                                   08/16/2016               20                         3.5                         7.5

Check In/Out              

                                   08/15/2016               20                         3.5                         7.5

                                   08/16/2016               25                         4                            7.5

I need to create a formula on this report that will calculate the Productivity of the Employee by doing a Sum of STD_TIME for a particular LOG_DATE and divide by the PRODUCTIVE_HOURS for that date.

As an example, for the USER abcdef, his/her productivity for 08/15/2016 is equal to ((2.5+3.5)/7.5) * 100 = 80% and for the LOG_DATE of 08/16/2016, the users Productivity is ((3.5+4)/7.5)*100 = 100%

I have created this formula (Sum ({@StdPerHour_num}, {USERS.USER_NAME},GroupName ({PRODUCTIVITY_LOG.LOG_DATE}, "daily"))/{@Max_Prod_Hours}) * 100

but this gives an error stating "A Group condition must be a string" by highlighting the GroupName ({PRODUCTIVITY_LOG.LOG_DATE}, "daily" field in the formula.

Any advise on how to create this formula to achieve the desired result is much appreciated.

Thanks,

Nimish

[former_member205840 · Accepted solution]

Hi Nimish,

If you are not very much keen on 'Activity_Name' Group, then remove it and insert 2nd group on LOG_DATE.  Then insert summary for each LOG_DATE and arrive percentage.

or

Insert the same report as sub report in Group 2 footer, in the sub report follow the above process i.e. remove 'Activity_Name' group and arrive percentage.

Then link your sub report with main report using USER_ID & Activity_name.

Thanks,

Sastry

[Former Member · Accepted solution]

Hi Sastry,

I do need the data to be grouped by "Activity Name" as well. I will try your second method later today and let you know how it goes.

Thanks.

[Former Member · Accepted solution]

Hi All,

I was able to fix this by swapping my Groups Log_Date and Activity_Name with each other (i.e. # 2 to # 3 and vice-versa) and then create this formula to be displayed in the Log_Date footer and Productivity column.

(Sum ({@STANDARD_TIME}, {PRODUCTIVITY_LOG.LOG_DATE}, "daily"))%({Productive_Hours})

Thanks for all your help.

[former_member280812]

Hi Nimish,

Try to use Running Total and play around on it..

Regards,

Eric

[Former Member]

Hi Eric,

I will try to play with Running Totals on my report and let you know if this works.

[abhilash_kumar]

Hi Nimish,

As the dates are in separate groups, there isn't an easy way to do this.

I have something in mind, however, could you tell us where would you like to display the percentage?

If you display the percentage on the same rows that displays the date, then it would repeat needlessly.

Are you OK with the percentage displaying on the footer with each date value showing up against it - Much like a summarized view?

-Abhilash

[Former Member]

Hi Abhilash,

I would like to display the percentage in a Productivity column of the report as seen in the red box highlighted below in the screenshot. It is fine to display the percentage in the footer in a summarized manner.

Right now, the formula is not calculating correctly and I have hidden the activity and user names in this screenshot.

Thank you.

Message was edited by: Nimish P