Issue with calculating a Percentage based fields

Applications Grid / Reports
#1

I have a grid that pulls data from several tables and uses the data to determine the percentage of those in the company and those who have completed the training.

The problem I have is the grid calculates the percentage of the top row and carries that same percentage to all remaining rows.

This is the calculation code I have in a php method which is triggered in the OnRecord event.


$bot_t = {bot_trained};
$str = {strength};
$bot_pr = (($bot_t*100)/$str);
{bot_perc} = $bot_pr;

Am I missing something for it to run for every row in the grid?

The sql queries to pull the "SELECT COUNT(*); runs prior to the execution of this code and it works as intended and is pulling good data. I just can’t get the percentage calculations to work correctly.

What I am getting is the following

Row 1
unit - {strength} - {bot_trained} - {bot_perc}

T1 - 25 - 5 - 25%
T2 - 54 - 3 - 25%
T3 - 108 - 1 - 25%

Thanks.

#2

try to ‘echo’ the values $bot_t and $str before calculating the percentage to check their value.

 
 $bot_t = {bot_trained}; $str = {strength};  echo $bot_t . '--' . $str;  $bot_pr = (($bot_t*100)/$str); {bot_perc} = $bot_pr;
#3

I get the following from the echo

1--4 1--4

I should be getting

1--4 23--69

for the data set I ran

#4

{bot_trained} and {strength} are values from the GRID’s SQL or do you define them using lookup or in the onrecord event?

#5

{bot_trained} and {strength} are added fields to the grid that use a sql lookup for the field. {bot_trained} looks for individuals that have a class record with the "BOT’ in a particular unit. {strength} does they same thing except it is not looking at a specific class, it is only counting the number of people assigned to that unit using a sql lookup.

{bot_trained}


SELECT COUNT(mt.member_id)
FROM member_training mt INNER JOIN member_rank mr ON mt.member_id = mr.member_id
   INNER JOIN member_unit mu ON mt.member_id = mu.member_id
   INNER JOIN units ut ON mu.unit_id = ut.unit_id
   INNER JOIN training_classes tc ON mt.training_class = tc.training_class_id
   INNER JOIN member mb ON mt.member_id = mb.member_id
WHERE ut.unit_active = 1 AND mr.rank_id BETWEEN 1 AND 12 AND tc.class_abbrev = 'BOT' AND ut.parent_bde = '{parent_bde}' AND mb.active = 1

{strength}


SELECT
   COUNT(mb.member_id)
FROM
   member mb INNER JOIN member_unit mu ON mb.member_id = mu.member_id
   INNER JOIN member_rank mr ON mb.member_id = mr.member_id
   INNER JOIN units ut ON mu.unit_id = ut.unit_id
WHERE 
   mb.active = 1  AND ut.parent_bde = '{parent_bde}' AND mr.rank_id BETWEEN 1 AND 12

grid sql


SELECT
   ut.parent_bde
FROM
   units ut
WHERE 
   ut.unit_active = 1 AND display_lvl = 2
ORDER BY
   ut.display_order
#6

Well it started doing something different now…

The first two lines are the same, the third line appears correct, however I don’t have any records for that line.

#7

update: I had ScriptCase remote in this morning to look at the issue. He was not able to figure it out.

All the data in the grid is correct except the calculated fields.

Row 1 and Row 2 are duplicated only in the calculated fields, and all the other data is pushed down a row. so the data that should be in row 2 is now in row 3, and row 3 is in row 4, etc.

anyone have issues like this or have any updates?

#8

Try running the sql selects for both bot_trained and strength in the onRecord event instead of using the added fields.

#9

Thanks this fixed the issue it appears.