change cell background based on value compared to average

(9 posts) (2 voices)
  • Started by
  • Latest reply from


No tags yet.

  1., Member

    I have calculated the average value for a column using dbr.avg. How do I test if the the individual row entries in that column are greater than the average and change cell background by applying a CSS class if true. Was thinking I could use dbr.calc but could not see how that would work.

    item cost
    box 4
    sock 6
    toy 10
    Average 6.6

    Want to highlight cell with 10 as it is greater than average but leave 4 and 6 alone.


  2. myDBR Team, Key Master

    You can use the columnreference aggregate (columnref.avg) in the calculation. With it you can use the average for the column as variable in the dbr.calc.

    select 'dbr.avg', 'Value';
    select 'dbr.calc', 'cellstyle', '[Value] > [Value.avg] ? "background:cyan" : ""';
    select 'dbr.cellstyle', 'Value', 'cellstyle'; select 'Box' as Item, 4 as Value, null as cellstyle
    select 'Sock', 6, null
    select 'Toy', 10, null;

    myDBR Team

  3., Member

    Almost there. The problem is that value.avg is for the entire column. I have a crosstab which means the average is worked out per location.

    I would send you a screen shot of the report which would explain it better but I can't seem to attach a file so here is an similar example:

    Item cost
    box 2
    train 5
    bear 6
    Avg 4.3
    Sock 7
    Shirt 8
    hat 4
    Avg 6.3

    Overall avg for column is 5.3

    The problem is that the train row is not highlighted as although it is above the average for the toys section it is not above the overall average for the column i.e columnref.avg is comparing the entire column average not against the section average.

  4. myDBR Team, Key Master

    Not quite clear how your crosstab is organized.

    You can use any image upload service (like to share your image. You can then attach the image to the forum post via the IMG-button.

    Alternative, just share an SQL export of yourt report.
    myDBR Team

  5., Member

    Example of avg report


    I have created a link to a screenshot of the report.

    In column 4 under count date month in the first location(Goods in entrance) there is listed 5.6, 4.7, 2.8, 4.4. The average for list location(Goods in entrance) is 4.4.

    Therefore 5.6 and 4.7 should be highlighted in cyan as they are over 4.4.

    They are not because the columnref.avg takes all the column 4 totals from all the locations (Goods in entrance, main factory entrance, packing room etc) and this average is higher at 5.2 which is why only 5.6 is highlighted in cyan.

    I want to be comparing with the average against each location (i.e 4.4 for Goods in entrance, 4.3 for main factory entrance).

    Hope that makes sense

  6. myDBR Team, Key Master

    What is the database system and the database version you are using?

    myDBR Team

  7., Member

    MYSQL v5.7.23

  8. myDBR Team, Key Master

    As MySQL 5.7 does not have window functions, you can use a temporary table to calculate the averages per Location/Month. Then join the temporary table with the actual query.

    See a demo.

    myDBR Team

  9., Member

    Thank you that worked very well. Appreciated the demo as that really showed me the way. Great work


You must log in to post.