1. 

   ken@pesttrend.com, 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.

   i.e
   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.

   Thanks
   Ken
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
   
   union
   
   select 'Sock', 6, null
   
   union
   
   select 'Toy', 10, null;
   
   

   --
   myDBR Team
3. 

   ken@pesttrend.com, 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
   Toys
   box 2
   train 5
   bear 6
   Avg 4.3
   Cloths
   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 pasteboard.co) 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. 

   ken@pesttrend.com, Member

   URL
   Example of avg report

   IMG
   

   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. 

   ken@pesttrend.com, 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. 

   ken@pesttrend.com, Member

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

Reply

You must log in to post.