tablegrid with real-time column inclusion

jrga · 2023-09-02 00:37:22

jrga
Member
Offline

From: Brasil
Registered: 2015-10-26
Posts: 127

Topic: tablegrid with real-time column inclusion

I would like to create an MVD application, which allows the user to include fields from a table in a tablegrid from a list of fields. My idea is to create a view containing several database tables, totaling numerical fields and, as the user changes the grid fields, this would affect these sums of values. For example: grid header: Categories, sales value. The user could exchange category for customers. Thus, the total sales values per category would be presented by customers. I hope I managed to explain myself. Thanks for any help.

Roberto Alencar

derek · 2023-09-02 12:17:39

derek
Moderator
Offline

Registered: 2015-02-16
Posts: 2,678

Re: tablegrid with real-time column inclusion

Hi
Just a few ideas.
1. You could enable column moving (see screenshot in the attachment) which allows you to drag
columns around in real time.
2. You could use multiple tablegrids to show different representations of your data ('customer summary' button and 'category summary' button in the attachment)
3. You could use a table ('customgrid' in the attachment) to store settings for your main tablegrid; in the attachment, you can set whether a column is visible or hidden, change the column heading etc).
Maybe trying combinations of these might help.
Derek.

Post's attachments

alternate grids.zip 524.3 kb, 92 downloads since 2023-09-02

jrga · 2023-09-02 17:02:24

jrga
Member
Offline

From: Brasil
Registered: 2015-10-26
Posts: 127

Re: tablegrid with real-time column inclusion

Hi Derek, thank you so much for your prompt reply! I'm studying the project you sent to adapt it to my needs. Thank you one more time! Roberto

Roberto Alencar

jrga · 2023-09-03 21:31:36

jrga
Member
Offline

From: Brasil
Registered: 2015-10-26
Posts: 127

Re: tablegrid with real-time column inclusion

Hi Derek, thanks again for your generosity. I set up another solution, which follows below, because I have 32,000 records in a table and 12,000 in another. The solution you suggested is also quite interesting and I intend to use it in other applications. I looked in the forum if there is the condition "case and end case", which exists in other languages, such as visual foxpro. In the script below I used "if" a lot, when I could perhaps use "case" if it existed in the MVD. Hugs.

var
   limit_grid: integer = 5000; //limita linhas da consulta
   Chart: TChart;  //usado no overview

procedure frmOverview_TableGrid1_OnCellDoubleClick (Sender: TObject; ACol, ARow: Integer);
//quando uma das combobox (cbDim1 ou cbDim2) contiver nome cliente (opção 8), o registro do
//cliente poderá ser acessado via frm_clientes se houver um doubleclick no grid
var
    posicao, id_cliente : integer;
    campo_txt: string;
begin
    campo_txt := '';
    if frmOverview.cbDim1.ItemIndex = 8 then
       campo_txt := frmOverview.TableGrid1.Cells[0,Arow]
    else if frmOverview.cbDim2.ItemIndex = 8 then
       campo_txt := frmOverview.TableGrid1.Cells[1,Arow];

    posicao := pos('ID:',campo_txt);
    if posicao <> 0 then
    begin
       id_cliente := strtoint(rigth_left(campo_txt,posicao+3,10));
       frm_clientes.ShowRecord('tbl_clientes',id_cliente);
    end;
end;

procedure frmOverview_btn_mais_linhas_OnClick (Sender: TObject; var Cancel: boolean);
begin
    limit_grid := 10000000;  //retira limita de linhas da consulta
    atualiza_overview;
    limit_grid := 5000; //retoma limite
end;

function rigth_left(word:string;pos_ini:integer;num_char:integer): string;
//*** extrai substring partir da posição e número de caracteres informados na chamada
begin
    result := copy(word,pos_ini,num_char);
end;


procedure frmOverview_TableGrid1_OnAfterSort (Sender: TObject; ACol: Integer);
begin
//   frmOverview.TableGrid1.SelectedRow := 0;
end;

procedure frmOverview_btn_limpa_OnClick (Sender: TObject; var Cancel: boolean);
begin
    //limpa compos usados no filtro da cláusula Where  (clear compounds used in Where clause filter)
    frmOverview.cbo_ano.dbItemID := -1;
    frmOverview.cbo_aparelho.dbItemID := -1;
    frmOverview.cbo_marca.dbItemID := -1;
    frmOverview.cbo_mes.dbItemID := -1;
    frmOverview.Edit_bairro.Text := '';
    frmOverview.Edit_cliente.Text := '';
    frmOverview.Edit_ID.text := '';
    frmOverview.Edit_modelo.text := '';
    frmOverview.Edit_pagto.text := '';
    frmOverview.DateTimePicker_ate.Checked := false;
    frmOverview.DateTimePicker_de.Checked := false;
end;

procedure frmOverview_btn_update_OnClick (Sender: TObject; var Cancel: boolean);
begin
    atualiza_overview;
end;

procedure frmOverview_cbDim2_OnChange (Sender: TObject);
var c: integer;
begin
    atualiza_overview;
end;

procedure frmOverview_cbDim1_OnChange (Sender: TObject);
begin
          atualiza_overview;
end;

procedure Form1_OnShow (Sender: TObject; Action: string);
begin
    frmoverview.BorderIcons := 5;

    //CBDIM1
    frmOverview.cbDim1.Items.Clear;
    frmOverview.cbDim1.Items.Add ('0 Bairro');
    frmOverview.cbDim1.Items.Add ('1 Aparelho');
    frmOverview.cbDim1.Items.Add ('2 Marca');
    frmOverview.cbDim1.Items.Add ('3 Modelo');
    frmOverview.cbDim1.Items.Add ('4 Ano Entrada');
    frmOverview.cbDim1.Items.Add ('5 Mês Entrada');
    frmOverview.cbDim1.Items.Add ('6 Ano-Mês Entrada');
    frmOverview.cbDim1.Items.Add ('7 Forma Pagto');
    frmOverview.cbDim1.Items.Add ('8 Cliente');
    //CBDIM2
    frmOverview.cbDim2.Items.Clear;
    frmOverview.cbDim2.Items.Add ('0 Bairro');
    frmOverview.cbDim2.Items.Add ('1 Aparelho');
    frmOverview.cbDim2.Items.Add ('2 Marca');
    frmOverview.cbDim2.Items.Add ('3 Modelo');
    frmOverview.cbDim2.Items.Add ('4 Ano Entrada');
    frmOverview.cbDim2.Items.Add ('5 Mês Entrada');
    frmOverview.cbDim2.Items.Add ('6 Ano-Mês Entrada');
    frmOverview.cbDim2.Items.Add ('7 Forma Pagto');
    frmOverview.cbDim2.Items.Add ('8 Cliente');

    //fixa primeiro item na abertura do formulário (fixed first item on form opening)
    frmOverview.cbDim1.ItemIndex := 4;
    frmOverview.cbDim2.ItemIndex := 4;

    //carrega dados nas combobox utilizadas na busca cláusula Where  (// load data in the combobox used in the Where clause search)
    frmOverview.cbo_ano.dbSQLExecute('select strftime("%Y",tbl_orderm_servico.entrada) as ano from tbl_orderm_servico group by ano order by 1');
    frmOverview.cbo_mes.dbSQLExecute('select strftime("%m",tbl_orderm_servico.entrada) as mes from tbl_orderm_servico group by mes order by 1');
    frmOverview.cbo_aparelho.dbSQLExecute('select aparelho, id from tbl_aparelhos order by 1');
    frmOverview.cbo_marca.dbSQLExecute('select marca, id from tbl_marcas order by 1');

{== BAR CHART ====}    //parâmetros para o gráfico de barras
    Chart := TChart.Create(frmOverview);
    Chart.Parent := frmOverview.panelbar1;
    Chart.Align := alClient;
    Chart.AddSeries(ThorizBarSeries.Create(Chart));
    Chart.View3D := true;
    //frmOverview.bLineUpdate.Click;
{== BAR CHART ====}

    atualiza_overview;
end;

procedure atualiza_overview;
//**** aciona geração da consulta e do gráfico de barras
var
    txt_sel, txt_sel1, txt_where: string;
    c,i: integer;
    total_grid: real = 0;
    Results: TDataSet;
    x_value: string;
    cf_y: string;
    y: integer;
begin

    //Usuário define que campos da query serão exibidos na primeira e segunda coluna da consulta
    //User defines which query fields will be displayed in the first and second column of the query

  txt_Sel := '';
  txt_Sel1 := '';
  //contribuição do Sparrow - campos que serão utilizados na consulta
  CASE frmOverview.cbDim1.itemindex of
    0:  txt_Sel1 := 'tbl_clientes.bairro AS Bairro';
    1:    txt_Sel1 := 'tbl_aparelhos.aparelho AS Aparelho';
    2:    txt_Sel1 := 'tbl_marcas.marca AS Marca';
    3:    txt_Sel1 := 'tbl_orderm_servico.modelo AS Modelo';
    4:    txt_Sel1 := 'strftime("%Y",tbl_orderm_servico.entrada) AS Ano';
    5:    txt_Sel1 := 'strftime("%m",tbl_orderm_servico.entrada) AS Mês';
    6:    txt_Sel1 := 'strftime("%Y-%m",tbl_orderm_servico.entrada) AS Ano_Mês';
    7:    txt_Sel1 := 'tbl_orderm_servico.pagamento AS "Forma Pagto"';
    8:    txt_Sel1 := 'tbl_clientes.nome||" - ID:"||tbl_clientes.id AS Cliente';
  end;

  CASE frmOverview.cbDim2.itemindex of
    0:  txt_Sel := txt_Sel1 + ', tbl_clientes.bairro AS Bairro';
    1:    txt_Sel := txt_Sel1 + ', tbl_aparelhos.aparelho AS Aparelho';
    2:    txt_Sel := txt_Sel1 + ', tbl_marcas.marca AS Marca';
    3:    txt_Sel := txt_Sel1 + ', tbl_orderm_servico.modelo AS Modelo';
    4:    txt_Sel := txt_Sel1 + ', strftime("%Y",tbl_orderm_servico.entrada) AS Ano';
    5:    txt_Sel := txt_Sel1 + ', strftime("%m",tbl_orderm_servico.entrada) AS Mês';
    6:    txt_Sel := txt_Sel1 + ', strftime("%Y-%m",tbl_orderm_servico.entrada) AS Ano_Mês';
    7:    txt_Sel := txt_Sel1 + ', tbl_orderm_servico.pagamento AS "Forma Pagto"';
    8:    txt_Sel := txt_Sel1 + ', tbl_clientes.nome||" - ID:"||tbl_clientes.id AS Cliente';
  end;

    txt_where :=
    '(CASE WHEN '+inttostr(frmOverview.cbo_ano.ItemIndex)+'     = -1 THEN 1=1 ELSE strftime("%Y",tbl_orderm_servico.entrada) = "'+frmOverview.cbo_ano.Text+'" END) AND '+
    '(CASE WHEN '+inttostr(frmOverview.cbo_mes.ItemIndex)+'     = -1 THEN 1=1 ELSE strftime("%m",tbl_orderm_servico.entrada) = "'+frmOverview.cbo_mes.Text+'" END) AND '+
    '(CASE WHEN '+inttostr(frmOverview.cbo_aparelho.dbitemid)+' = -1 THEN 1=1 ELSE tbl_orderm_servico.id_tbl_aparelhos = '+inttostr(frmOverview.cbo_aparelho.dbitemid)+' END) AND '+
    '(CASE WHEN '+inttostr(frmOverview.cbo_marca.dbitemid)+'    = -1 THEN 1=1 ELSE tbl_orderm_servico.id_tbl_marcas = '+inttostr(frmOverview.cbo_marca.dbitemid)+' END) AND '+
    '(CASE WHEN LENGTH("'+frmOverview.Edit_cliente.Text+'")     =  0 THEN 1=1 ELSE tbl_clientes.nome LIKE "%'+frmOverview.Edit_cliente.Text+'%" END) AND '+
    '(CASE WHEN LENGTH("'+frmOverview.Edit_ID.Text+'")          =  0 THEN 1=1 ELSE tbl_clientes.id = "'+frmOverview.Edit_ID.Text+'" END) AND '+
    '(CASE WHEN LENGTH("'+frmOverview.Edit_bairro.Text+'")      =  0 THEN 1=1 ELSE tbl_clientes.bairro LIKE "%'+frmOverview.Edit_bairro.Text+'%" END) AND '+
    '(CASE WHEN LENGTH("'+frmOverview.Edit_modelo.Text+'")      =  0 THEN 1=1 ELSE tbl_orderm_servico.modelo LIKE "%'+frmOverview.Edit_modelo.Text+'%" END) AND '+
    '(CASE WHEN LENGTH("'+frmOverview.Edit_pagto.Text+'")       =  0 THEN 1=1 ELSE tbl_orderm_servico.pagamento LIKE "%'+frmOverview.Edit_pagto.Text+'%" END) AND '+
    '(CASE WHEN '+frmOverview.DateTimePicker_de.sqlDateTime+' IS NULL THEN 1=1 ELSE strftime("%Y-%m-%d %H:%M:%f",tbl_orderm_servico.entrada) >= '+frmOverview.DateTimePicker_de.sqlDatetime+' END) AND '+
    '(CASE WHEN '+frmOverview.DateTimePicker_ate.sqlDateTime+' IS NULL THEN 1=1 ELSE strftime("%Y-%m-%d %H:%M:%f",tbl_orderm_servico.entrada) <= '+frmOverview.DateTimePicker_ate.sqlDatetime+' END)';


    //Query que preencherá o tablegrid1   (Query that will populate the tablegrid1)
    frmoverview.TableGrid1.dbSQL :=
    ' SELECT '+
              txt_sel+' , '+
    '         SUM( "tbl_orderm_servico"."valor_servicos" + "tbl_orderm_servico"."valor_pecas") as "Vlr Total" , '+
    '         0 as "Perc%", '+
    '         COUNT( "tbl_orderm_servico"."id" )  AS "Qtde os" '+
    ' FROM     "tbl_clientes" '+
    ' INNER JOIN "tbl_orderm_servico"  ON "tbl_clientes"."id" = "tbl_orderm_servico"."id_tbl_clientes" '+
    ' LEFT OUTER JOIN "tbl_aparelhos"  ON "tbl_aparelhos"."id" = "tbl_orderm_servico"."id_tbl_aparelhos" '+
    ' LEFT OUTER JOIN "tbl_marcas"  ON "tbl_marcas"."id" = "tbl_orderm_servico"."id_tbl_marcas" '+
    ' WHERE '+txt_where+
    ' GROUP BY 1, 2 ORDER by 1, 2 LIMIT '+inttostr(limit_grid);
    frmoverview.TableGrid1.dbSQLExecute; //executa o query

    c := frmOverview.TableGrid1.RowCount - 1;
    for i := 0 to c do  //soma e obtém total da coluna de valores
    begin
        if strtofloat(frmOverview.TableGrid1.Cells[2, i]) <0 then i := i + 1 else
        total_grid := total_grid + strtofloat(frmOverview.TableGrid1.Cells[2, i]);
    end;

    if total_grid <> 0 then  //evita dividir por zero
    begin
        c := frmOverview.TableGrid1.RowCount - 1;
        for i := 0 to c do  //calcula partipação de cada valor sobre o total acima
        begin
            if strtofloat(frmOverview.TableGrid1.Cells[2, i]) <0 then i := i + 1 else
            frmOverview.TableGrid1.Cells[3, i] := floattostr((strtofloat(frmOverview.TableGrid1.Cells[2, i]) / total_grid)*100);
        end;
    end;

    frmOverview.TableGrid1.CalculateFooter;

    frmOverview.TableGrid1.EndUpdate;

    //frmOverview.TableGrid1.BestFitColumns(bfBoth);
    //*** deve ser colocado no begin-end do final    frmOverview.TableGrid1.Options := frmOverview.TableGrid1.Options + goFooter;

    TNxNumberColumn(frmOverview.TableGrid1.Columns[3]).FormatMask := '##0.00%';
    TNxNumberColumn(frmOverview.TableGrid1.Columns[3]).Footer.FormatMask := '##0.00';

    //formata colunas numéricas (format numeric columns)
    TNxNumberColumn(frmoverview.TableGrid1.Columns[2]).FormatMask := '###,###,##0.00';
    TNxNumberColumn(frmoverview.TableGrid1.Columns[3]).FormatMask := '##0.00';
    frmoverview.TableGrid1.Columns[2].Alignment := taRightJustify;

    //preenche rodapé com totais para determinadas colunas
    //fills footer with totals for certain columns
    frmOverview.TableGrid1.Columns[0].Footer.Caption := 'Total Linhas: ';
    frmOverview.TableGrid1.Columns[1].Footer.FormulaKind := fkCount;
    frmOverview.TableGrid1.Columns[2].Footer.FormulaKind := fkSum;
    TNxNumberColumn(frmOverview.TableGrid1.Columns[2]).Footer.FormatMask := '###,###,##0.00';
    TNxNumberColumn(frmOverview.TableGrid1.Columns[3]).Footer.FormatMask := '##0.0';
    frmOverview.TableGrid1.Columns[3].Footer.FormulaKind := fkSum; //soma qtde
    frmOverview.TableGrid1.Columns[4].Footer.FormulaKind := fkSum; //soma qtde

    frmOverview.TableGrid1.CalculateFooter; //gera totais no rodapé (generate totals in footer)

    //frmOverview.TableGrid1.BestFitColumns(bfboth); // ajusta largura colunas automátciamente

    Chart.Series[0].Clear;
    Chart.Legend.Visible := false;


   //grava dados na tabela que alimenta gráfico
    y:= 1;
    SQLexecute('DELETE FROM bar_data');
    SQLExecute('INSERT INTO bar_data (eixo_x, eixo_y) '+
    ' SELECT '+
              txt_sel1+' , '+
    '         SUM( "tbl_orderm_servico"."valor_servicos" + "tbl_orderm_servico"."valor_pecas") as "Vlr Total" '+
    ' FROM    "tbl_clientes" '+
    ' INNER JOIN "tbl_orderm_servico"  ON "tbl_clientes"."id" = "tbl_orderm_servico"."id_tbl_clientes" '+
    ' LEFT OUTER JOIN "tbl_aparelhos"  ON "tbl_aparelhos"."id" = "tbl_orderm_servico"."id_tbl_aparelhos" '+
    ' LEFT OUTER JOIN "tbl_marcas"  ON "tbl_marcas"."id" = "tbl_orderm_servico"."id_tbl_marcas" '+
    ' WHERE '+txt_where+
    ' GROUP BY 1 ORDER by 1 LIMIT '+inttostr(limit_grid));

    SQLQuery('SELECT eixo_x, sum(round(eixo_y,0)) As cf_eixo_y FROM bar_data group by eixo_x order by eixo_x desc' , Results);

    while not Results.Eof do
    begin
        x_value := Results.FieldByName('eixo_x').asString;
        cf_y := Results.FieldByName('cf_eixo_y').asstring;
        if ValidInt(cf_y) then Chart.Series[0].AddXY(strtoint(cf_y),y,x_value);
        Results.Next;
        inc(y);
    end;
    Results.Free;
   //grava dados na tabela que alimenta gráfico

end;


begin

end.

Post's attachments

tela1.jpg 109.38 kb, 19 downloads since 2023-09-06

Roberto Alencar

k245 · 2023-09-04 09:17:06

k245
Member
Offline

From: Deep
Registered: 2017-10-12
Posts: 1,545

Re: tablegrid with real-time column inclusion

You are now trying to create a "constructor within a constructor". This process is interesting, but its long-term prospects are vague.

It seems to me that the costs of creating such a system would be unpredictably high. During the same time (and even faster), you can create a final list of data that the user really needs to solve specific business problems. At first, it seems that universal systems can save development time. And in some cases it is. But then, in the process of operation and improvements, versatility can become a bottleneck and dramatically increase the cost and complexity of adding functionality.

Визуальное программирование: блог и телеграм-канал.

sparrow · 2023-09-04 11:52:03

sparrow
Member
Offline

From: Рідна МАТИ, UKRAINE
Registered: 2018-06-02
Posts: 895

Re: tablegrid with real-time column inclusion

Looks like I wasn't wrong.
Something like this.

var
      campo1, campo2, as1, as2, txt_where, txt_Sel: string;
begin
  txt_Sel := '';
  CASE frmOverview.cbDim1.itemindex of
    0:  txt_Sel := 'tbl_clientes.bairro AS Ano';
    1:    txt_Sel := 'tbl_aparelhos.aparelho AS Aparelho';
    2:    txt_Sel := 'tbl_marcas.marca AS Marca';
    3:    txt_Sel := 'tbl_orderm_servico.modelo AS Modelo';
    4:    txt_Sel := 'strftime("%Y",tbl_orderm_servico.entrada) AS Ano';
    5:    txt_Sel := 'strftime("%m",tbl_orderm_servico.entrada) AS Mês';
    6:    txt_Sel := 'strftime("%Y-%m",tbl_orderm_servico.entrada) AS Ano_Mês';
    7:    txt_Sel := 'tbl_orderm_servico.pagamento AS Pagamento';
    8:    txt_Sel := 'tbl_clientes.nome||" - "||tbl_clientes.id AS Cliente';
  end;

  CASE frmOverview.cbDim2.itemindex of
    0:  txt_Sel := txt_Sel + ', tbl_clientes.bairro AS Ano';
    1:    txt_Sel := txt_Sel + ', tbl_aparelhos.aparelho AS Aparelho';
    2:    txt_Sel := txt_Sel + ', tbl_marcas.marca AS Marca';
    3:    txt_Sel := txt_Sel + ', tbl_orderm_servico.modelo AS Modelo';
    4:    txt_Sel := txt_Sel + ', strftime("%Y",tbl_orderm_servico.entrada) AS Ano';
    5:    txt_Sel := txt_Sel + ', strftime("%m",tbl_orderm_servico.entrada) AS Mês';
    6:    txt_Sel := txt_Sel + ', strftime("%Y-%m",tbl_orderm_servico.entrada) AS Ano_Mês';
    7:    txt_Sel := txt_Sel + ', tbl_orderm_servico.pagamento AS Pagamento';
    8:    txt_Sel := txt_Sel + ', tbl_clientes.nome||" - "||tbl_clientes.id AS Cliente';
  end;

    txt_where :=
    '(CASE WHEN '+inttostr(frmOverview.cbo_ano.ItemIndex)+'     = -1 THEN 1=1 ELSE strftime("%Y",tbl_orderm_servico.entrada) = "'+frmOverview.cbo_ano.Text+'" END) AND '+
    '(CASE WHEN '+inttostr(frmOverview.cbo_mes.ItemIndex)+'     = -1 THEN 1=1 ELSE strftime("%m",tbl_orderm_servico.entrada) = "'+frmOverview.cbo_mes.Text+'" END) AND '+
    '(CASE WHEN '+inttostr(frmOverview.cbo_aparelho.dbitemid)+' = -1 THEN 1=1 ELSE tbl_orderm_servico.id_tbl_aparelhos = '+inttostr(frmOverview.cbo_aparelho.dbitemid)+' END) AND '+
    '(CASE WHEN '+inttostr(frmOverview.cbo_marca.dbitemid)+'    = -1 THEN 1=1 ELSE tbl_orderm_servico.id_tbl_marcas = '+inttostr(frmOverview.cbo_marca.dbitemid)+' END) AND '+
    '(CASE WHEN LENGTH("'+frmOverview.Edit_cliente.Text+'")     =  0 THEN 1=1 ELSE tbl_clientes.nome LIKE "%'+frmOverview.Edit_cliente.Text+'%" END) AND '+
    '(CASE WHEN LENGTH("'+frmOverview.Edit_ID.Text+'")          =  0 THEN 1=1 ELSE tbl_clientes.id = "'+frmOverview.Edit_ID.Text+'" END) AND '+
    '(CASE WHEN LENGTH("'+frmOverview.Edit_bairro.Text+'")      =  0 THEN 1=1 ELSE tbl_clientes.bairro LIKE "%'+frmOverview.Edit_bairro.Text+'%" END) AND '+
    '(CASE WHEN LENGTH("'+frmOverview.Edit_modelo.Text+'")      =  0 THEN 1=1 ELSE tbl_orderm_servico.modelo LIKE "%'+frmOverview.Edit_modelo.Text+'%" END) AND '+
    '(CASE WHEN LENGTH("'+frmOverview.Edit_pagto.Text+'")       =  0 THEN 1=1 ELSE tbl_orderm_servico.pagamento LIKE "%'+frmOverview.Edit_pagto.Text+'%" END)';

    //Query que preencherá o tablegrid1   (Query that will populate the tablegrid1)
    frmoverview.TableGrid1.dbSQL :=
    ' SELECT '+ 
    '     '+  txt_Sel +' , '+
    '         SUM( "tbl_orderm_servico"."valor_servicos" + "tbl_orderm_servico"."valor_pecas") as "Vlr Total" , '+
    ' ROUND(100.0 * sum(valor_pecas+valor_servicos) / (SELECT sum(valor_pecas+valor_servicos) FROM tbl_orderm_servico where id_tbl_marcas <> 0), 2) as "Perc%", '+
    '         COUNT( "tbl_orderm_servico"."id" )  AS "Qtde os" '+
    ' FROM     "tbl_clientes" '+
    ' INNER JOIN "tbl_orderm_servico"  ON "tbl_clientes"."id" = "tbl_orderm_servico"."id_tbl_clientes" '+
    ' LEFT OUTER JOIN "tbl_aparelhos"  ON "tbl_aparelhos"."id" = "tbl_orderm_servico"."id_tbl_aparelhos" '+
    ' LEFT OUTER JOIN "tbl_marcas"  ON "tbl_marcas"."id" = "tbl_orderm_servico"."id_tbl_marcas" '+
    ' WHERE '+txt_where+
    ' GROUP BY 1, 2 ORDER by 1, 2';
    
    frmoverview.TableGrid1.dbSQLExecute; //executa o query

...

jrga · 2023-09-04 14:42:00

jrga
Member
Offline

From: Brasil
Registered: 2015-10-26
Posts: 127

Re: tablegrid with real-time column inclusion

Hi Sparrow, thanks with the syntax of the "case". I will use your code to adjust mine. Hugs.

Roberto Alencar

tablegrid with real-time column inclusion

Posts: 7

1 Topic by jrga 2023-09-02 00:37:22 (edited by jrga 2023-09-02 10:51:17)

Topic: tablegrid with real-time column inclusion

2 Reply by derek 2023-09-02 12:17:39

Re: tablegrid with real-time column inclusion

3 Reply by jrga 2023-09-02 17:02:24

Re: tablegrid with real-time column inclusion

4 Reply by jrga 2023-09-03 21:31:36 (edited by jrga 2023-09-05 23:34:21)

Re: tablegrid with real-time column inclusion

5 Reply by k245 2023-09-04 09:17:06

Re: tablegrid with real-time column inclusion

6 Reply by sparrow 2023-09-04 11:52:03 (edited by sparrow 2023-09-04 12:59:39)

Re: tablegrid with real-time column inclusion

7 Reply by jrga 2023-09-04 14:42:00 (edited by jrga 2023-09-04 14:43:37)

Re: tablegrid with real-time column inclusion

Posts: 7