How to do totals by column and conditional coloring of cells

Attaching a screenshot.

Sorry for the delay.

I would make a few changes:

1. Instead of using a special time header row, use a frozen row.

2. Mark this row with cellsAutoUpdated: true, that will re-render the content on every change (events loaded, event moved or created…).

3. Use onBeforeCellRender to calculate and display the column total (there is no need to pre-calculate it).

The key elements could look like this:

A. When loading resources, prepend the frozen row with the totals

async loadResources() {
  const {data} = await DayPilot.Http.get("/resources/");
  const resources = [
    {name: "Totals", id: "totals", frozen: "top", cellsAutoUpdated: true},
    ...data
  ];
  dp.update({resources});
}

B. Calculate the totals:

const scheduler = new DayPilot.Scheduler("scheduler", {
  onBeforeCellRender: args => {
    if (args.cell.resource === "totals") {
      const events = scheduler.events.forRange(args.cell.start, args.cell.end);

      const total = events.reduce((sum, e) => {
        const weight = e.data?.tags?.weight ?? 0;
        return sum + (Number.isFinite(n) ? n : 0);
      }, 0);

      args.cell.properties.text = total;
    }
  },
  // ..
});
// ...

You can also take a look at the following tutorial which implements a simplified version of this logic (it just counts the events):

• JavaScript Scheduler: Column Summary and Availability Chart

Let me know if it doesn’t work as expected.

And to color the cells, you can update the cache object (totals) when rendering the frozen row and reuse the value for the standard cells:

const totals = {};

const scheduler = new DayPilot.Scheduler("scheduler", {
  onBeforeCellRender: args => {
    if (args.cell.resource === "totals") {
      const events = scheduler.events.forRange(args.cell.start, args.cell.end);

      const total = events.reduce((sum, e) => {
        const weight = e.data?.tags?.weight ?? 0;
        return sum + (Number.isFinite(n) ? n : 0);
      }, 0);

      args.cell.properties.text = total;
      
      if (total > 100) {
        args.cell.properties.backColor = "#cc0000";
      }
      
      totals[args.cell.start.toString()] = total;
    }
    else {
      if (totals[args.cell.start.toString()] > 100) {
        args.cell.properties.backColor = "#ee0000";
      }
    }
  },
  // ..
});

Cells are always rendered from top to bottom. The frozen row will be updated on every change (cellsAutoUpdated). That ensures the cached values will be up-to-date when rendering standard cells.

Thanks, I’ll definitely try this. Since my totals are effectively a column property, I was trying too hard to compute it per column, but obviously per-cell will be fast enough, which removes a chunk of my convolutions.

OK, that went smoothly and it’s a lot cleaner. A few notes and issues:

• Because moving an event into a new column alters the totals, that can affect the backColor of the standard cells, so those need to get repainted. Therefore, I made all the resources cellsAutoUpdated: true. (So that’s solved)

• It turns out that a resource with frozen: “top” is rendered last, not first, so caching the totals during that resource’s onBeforeCellRender is too late. In my case I don’t have a lot of resources, so I just removed frozen, and then top-to-bottom rendering fixes things. (So that’s solved)

• I had wanted to recolor groupBy:Day timeheader when the total was > 100%, but that doesn’t get repainted (since I removed my update(timeHeaders) hack). I can do without that, but let me know if there’s an easy solution or if you plan to add something like cellsAutoUpdated for header rows.

Thank you!

Thanks for the update.

At this moment, the time headers are only updated if you call update().

If you call it without parameters, it refreshes everything (including time headers and cells). It is designed to be fast (only the viewport is rendered) so for smaller grids it might be the easiest solution.

However, you would have to call it manually from all places where events can be changed (like you did before).