0

I want to alternate the colours of my grid by groups. My first try is doing this adding a GroupNumber to the ClientDataset (using the DENSE_RANK() function of SQL Server).

select dense_rank() over (order by Viatge) as GroupNumber, 
       Transports_v.* 
from Transports_v
where IdTransportista = :Id
order by 1

Now I can alternate colors on the grid using this code:

procedure TFFacturesTransportista.AEDBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
  if Odd(QAlbaransPEndentsGroupNumber.Value) then AEDBGrid1.Canvas.Brush.Color := clInfoBk;

  AEDBGrid1.DefaultDrawColumnCell(Rect,DataCol,Column,state);
end;

It works well, but if I manually delete a row then I can have two consecutive even or odd groups, and they will be drawn with the same colour.

Is there a better way to check if the current record starts a new group ?.

Thank you.

Marc Guillot
  • 6,090
  • 1
  • 15
  • 42
  • Did you try JediVCL's UltimateDBGrid ? `DBGrid1.AlternateRowColor := cl3DLight;` - and you have it. – Arioch 'The Feb 25 '21 at 12:41
  • You can just refresh the grid (i.e. reexecute the SELECT query) after you delete a row. – Olivier Feb 25 '21 at 12:43
  • Alternatively you may **hope** to use `TDataSet.RecNo` by adding a *Calculated Field* that would proxy that value to your grid, then analyze it. Read about `AutoCalcFields` and friends and about http://docwiki.embarcadero.com/Libraries/Sydney/en/Data.DB.TDataSet.RecNo Granted, not every dataset would bother to implement .RecNo, but many do – Arioch 'The Feb 25 '21 at 12:51
  • 1
    @Arioch'The Groups are not defined by the record number. – Olivier Feb 25 '21 at 13:01
  • @Arioch'The Thanks, but I don't want to alternate colours at every record, but only at every group of records defined by the field Viatge. – Marc Guillot Feb 25 '21 at 13:09
  • @Olivier Thanks, but it won't work for me, because I don't apply the changes until I leave the form, so re-executing the query would lose all those changes. – Marc Guillot Feb 25 '21 at 13:11
  • 2
    Then you should compute the group number on the client side, and recompute it after you remove a row from the `TClientDataset`. – Olivier Feb 25 '21 at 13:21
  • @Olivier, yes, thanks, I will start doing this, although I hope someone knows a cleaner way than having to loop the entire dataset after every insertion/deletion. – Marc Guillot Feb 25 '21 at 13:34
  • Why isn't it "clean"? One way or another, you will need to traverse the dataset anyway to inspect the values of Viatge. The only optimization you can do is start from the record just before the one you deleted. – Olivier Feb 25 '21 at 13:40
  • @MarcGuillot when you *invalidate* the DBGrid it will iterate through the `TDataSet` anyway, so don't be afraid to iterate, just think WHERE and HOW MUCH you do it. And yes, it is up to you HOW you compute the group or any other indicator attribute, and you can even compute the color itself (that would be bad old-skul approach), that is what **Calculated Fields** exist for – Arioch 'The Feb 25 '21 at 13:40
  • I would also consider using TVirtualTreeView in Report Mode :-) – Arioch 'The Feb 25 '21 at 13:41
  • @Arioch'The that's an interesting idea. :-) I have done the group numbering on the client side, but I will give a try to a TreeView. – Marc Guillot Feb 25 '21 at 14:21
  • Just do not mistake stock Windows/VCL TreeVIew and VTV, those are VERY different if not totally. In particular, depending on your task, there may or may not be reason to load the whole datatset - it might be better to only load top-level and next-level nodes and load (N+1)th levels after user opens some Nth level node, if ever – Arioch 'The Feb 25 '21 at 14:46
  • 1
    Hi @Marc. I just thought it was worth mentioning that the JEDI TJvDBGrid we discussed in another Q&A the other day supports an alternate row color: wiki.delphi-jedi.org/wiki/JVCL_Help:TJvDBGrid.AlternateRowColor. – MartynA Feb 25 '21 at 17:23
  • @MartynA even for groups he can use https://wiki.delphi-jedi.org/wiki/JVCL_Help:TJvDBGrid.OnGetCellParams much simpler, than mess with VCL standard custom-default drawing. OTOH in one my project i used TJvUltimDBGrid and Unified-Interbase DB lib, and had to implement custom sorting, and ion the end i had to patch BOTH libs, or they created AV's on sorting, in between half-open dataset and half-initialized grid... – Arioch 'The Feb 25 '21 at 21:09

1 Answers1

1

There is no ‘magic’ function for what you want: use alternate color based on some field value (aka group) when the list is sorted on that field value/group.

But you could make your own list of groups (based on the dataset content) and then use the even/odd specification of the index of the group in that list to get the color.

When you delete rows, check to see if its the last row for that group and then delete the group from the list. Other way around, when a record is added with a new group, add the new group to the group list.

Small note: the order of the groups in the list must be the same as the order in which the groups appear in the dataset!

R. Hoek
  • 916
  • 8
  • 27