Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-75359

Numeric aggregation in report builder does not work on 'checkbox' user profile fields

    XMLWordPrintable

Details

    • MOODLE_400_STABLE, MOODLE_401_STABLE
    • MOODLE_401_STABLE, MOODLE_402_STABLE
    • MDL-75359-401
    • MDL-75359-402
    • Hide

      Pre-requisites

      1. Ensure you are testing on Postgres

      Manual test

      1. Log in as admin
      2. Navigate to User > Accounts > User profile fields in site administration
      3. Create new Checkbox field
        • Short name: cfield
        • Name: CField
      4. Navigate to Profile from user menu
      5. Press Edit profile
      6. Tick Other fields > CField and press Update profile
      7. Create a new user
        • First name: Test
        • Last name: User
        • Ensure Other fields > CField is unticked
      8. Navigate to Reports from user menu
      9. Create new report from Users report source
        • Include default setup = No
      10. Add the following columns in the report editor:
        • User > Last name
        • User > CField
      11. Set aggregation for CField to Average
      12. Confirm the report updates without exceptions
      13. Confirm the CField column shows "0.5"
      14. Set aggregation for CField to Sum
      15. Confirm the report updates without exceptions
      16. Confirm the CField column shows "1"
      17. Set aggregation for CField to Percentage
      18. Confirm the report updates without exceptions
      19. Confirm the CField column shows "50.0%"

      Automated tests

      1. All changes are also covered by automated tests (CI will pick up any problems)
      Show
      Pre-requisites Ensure you are testing on Postgres Manual test Log in as admin Navigate to User > Accounts > User profile fields in site administration Create new Checkbox field Short name: cfield Name: CField Navigate to Profile from user menu Press Edit profile Tick Other fields > CField and press Update profile Create a new user First name: Test Last name: User Ensure Other fields > CField is unticked Navigate to Reports from user menu Create new report from Users report source Include default setup = No Add the following columns in the report editor: User > Last name User > CField Set aggregation for CField to Average Confirm the report updates without exceptions Confirm the CField column shows "0.5" Set aggregation for CField to Sum Confirm the report updates without exceptions Confirm the CField column shows "1" Set aggregation for CField to Percentage Confirm the report updates without exceptions Confirm the CField column shows "50.0%" Automated tests All changes are also covered by automated tests (CI will pick up any problems)

    Description

      1. Create a user profile field of type checkbox
      2. Create at least one user (or edit existing) so that the value is set for them
      3. Create a custom report with 'users' datatype, add the user name and this profile field
      4. Set "Average" aggregation on this user profile field (Edit or other numeric aggregation types, e.g. "Sum" & "Percent" as per MDL-75770 - PH)

      ERROR:  operator does not exist: numeric * text
      LINE 3: ...u.lastname AS c0_lastname, u.id AS c0_id, AVG(1.0 * rbalias9...
                                                                   ^
      HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
       
                  SELECT COUNT(1)
                    FROM (SELECT u.firstname AS c0_firstname, u.lastname AS c0_lastname, u.id AS c0_id, AVG(1.0 * rbalias9.data) AS c1_data
                            FROM mdl_user u LEFT JOIN mdl_user_info_data rbalias9 ON rbalias9.userid = u.id AND rbalias9.fieldid = 14
                           WHERE u.id != $1 AND u.deleted = 0
                                 GROUP BY c0_firstname, c0_lastname, c0_id
                         ) rbalias30
      [array (
        0 => '1',
      )]
      Error code: dmlreadexception
      * line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
      * line 291 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
      * line 338 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end()
      * line 1017 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
      * line 1671 of /lib/dml/moodle_database.php: call to pgsql_native_moodle_database->get_records_sql()
      * line 1744 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
      * line 1954 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
      * line 170 of /reportbuilder/classes/table/base_report_table.php: call to moodle_database->count_records_sql()
      * line 2132 of /lib/tablelib.php: call to core_reportbuilder\table\base_report_table->query_db()
      * line 262 of /lib/table/classes/external/dynamic/get.php: call to table_sql->out()
      * line 261 of /lib/externallib.php: call to core_table\external\dynamic\get::execute()
      * line 81 of /lib/ajax/service.php: call to external_api::call_external_function()
      

      Attachments

        1. avg_checkbox.png
          avg_checkbox.png
          121 kB
        2. MDL-75359.png
          MDL-75359.png
          330 kB
        3. Screenshot_2022-09-20 Users.png
          Screenshot_2022-09-20 Users.png
          31 kB

        Issue Links

          Activity

            People

              pholden Paul Holden
              marina Marina Glancy
              Carlos Castillo Carlos Castillo
              David Carrillo David Carrillo
              Ron Carl Alfon Yu Ron Carl Alfon Yu
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 7 hours, 6 minutes
                  7h 6m

                  Clockify

                    Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.