Agent Desktop

Get Involved. Join the Conversation.

Topic

    Nadeem Ali
    Sum of Multiple columns in one row.Answered
    Topic posted November 23, 2018 by Nadeem AliBlue Ribbon: 750+ Points 
    81 Views, 14 Comments
    Title:
    Sum of Multiple columns in one row.
    Content:

    I want to create a report, having multiple columns and get the sum of each column and add all of columns sum and get a final result.

    see image

     

    Image:

    Best Comment

    Sebastiaan Draaisma

    You need to convert it to a float in PHP
    For example:

    // Convert string to float
    $float_03 = floatval($rows[0][3]->val);
    $float_04 = floatval($rows[0][4]->val);
    $float_05 = floatval($rows[0][5]->val);
    $float_06 = floatval($rows[0][6]->val);
    $float_07 = floatval($rows[0][7]->val);
    $float_08 = floatval($rows[0][8]->val);

    // Perform calculation
    $rows[0][9]->val = $float_03 + $float_04 + $float_05 + $float_06 + $float_07 + $float_08;

     

    Column 9 needs to be of decimal type

    Comment

     

    • Nadeem Ali

      Somehow, like this image 

    • Sebastiaan Draaisma

      This can be done with a custom script :-)
      Based on your picture you would need to use the following script in the Process tab for custom scripts

      $rows[0][5]->val = $rows[0][1]->val + $rows[0][2]->val + $rows[0][3]->val + $rows[0][4]->val;

      See attached example

    • Sebastiaan Draaisma

      You may have your column counting wrong. The first column in your report has ID 0

    • Rajan Davis

      Nadeem,

      Can you post a picture of the column values in the report before they are added? You may need to make further modifications so that they are in the correct format prior to being added.

      Another thing you may want to try is if you are using a custom script, wrap the values of the various columns with floatval as some of the values appear to be percentages and some appear to be integers. This will make the values the same type, I think PHP may be casting the values to integers.

      Kind Regards,

      Rajan

    • Nadeem Ali

      Here is the report with columns. Need to know ( The last column for the total of all column should be inserted or it will automatically create that column  ). Here I have not inserted the last column for total.

      • Nadeem Ali

        Another imported thing is, these all fields are a text field. because all of these contains decimal point values, and we don't have any field type for floating numbers, in object designer.

        • Sebastiaan Draaisma

          In that case you need to do it in PHP (the custom script)

          $string = '122.34343';
          $float  = floatval($string);
          

          (this has to be done before the code that will add thing up for you)

    • Sebastiaan Draaisma

      3-8 are no integers. Youwould need to convert these data types to integers. Either in your expression or in the custom script through PHP

      to_number(str)

      This function converts the character string str to a numeric. If the str is not numeric, this function returns zero. If str is a combination of numbers and other characters and begins with a number, this function returns only the initial numeric portion. For example, to_number('123ABC') returns 123.

      See: Functions

      • Nadeem Ali

        Yes. And this creates a problem. because to_number convert (45.278 => 45), the rest of part after the decimal point will not more.

        • Sebastiaan Draaisma

          You need to convert it to a float in PHP
          For example:

          // Convert string to float
          $float_03 = floatval($rows[0][3]->val);
          $float_04 = floatval($rows[0][4]->val);
          $float_05 = floatval($rows[0][5]->val);
          $float_06 = floatval($rows[0][6]->val);
          $float_07 = floatval($rows[0][7]->val);
          $float_08 = floatval($rows[0][8]->val);

          // Perform calculation
          $rows[0][9]->val = $float_03 + $float_04 + $float_05 + $float_06 + $float_07 + $float_08;

           

          Column 9 needs to be of decimal type

    • Rajan Davis

      Hi Nadeem,

      I apologize; can you post a picture of the output of the report?

      If there are items such as percentage signs (%) included with the numbers, it might make things a bit more difficult.

      Also, please try what Sebastiaan has posted above. I was hoping to see what the output of the report was before recommending the solution he provided, but it might be what is needed to solve your problem.

      Kind Regards,

      Rajan

    • Nadeem Ali

      Thanks, buddies. It's pretty simple I was totally confused. It's done!