Como agrupar datas no QuickReport
19/12/2005
0
Amigos,
tenho uma tabela no firebird:
preciso fazer um relatorio q agrupe as datas no QuickReport
a SQL seria assim:
o resultado final seria +/- assim:
19/12/2005
------------------------------
09:00 Ligar para o fornecedor X
09:20 Ligar para o fornecedor Y
12:30 Almoço com o cliente Z
20/12/2005
-------------------------------
10:00 Levar documentos ao banco
11:00 Reunião com funcionários
21/12/2005
--------------------------------
08:00 Ir ao banco para pagar as contas de telefone
13:00 Almoço com grupo de fornecedores
tenho uma tabela no firebird:
Compromissos codcomp integer (PK), data date, hora time, descricao varchar(100) realizado char(1)
preciso fazer um relatorio q agrupe as datas no QuickReport
a SQL seria assim:
select * from compromissos where data >= :d1 and data <= :d2 order by data
o resultado final seria +/- assim:
19/12/2005
------------------------------
09:00 Ligar para o fornecedor X
09:20 Ligar para o fornecedor Y
12:30 Almoço com o cliente Z
20/12/2005
-------------------------------
10:00 Levar documentos ao banco
11:00 Reunião com funcionários
21/12/2005
--------------------------------
08:00 Ir ao banco para pagar as contas de telefone
13:00 Almoço com grupo de fornecedores
Orlando Frade
Curtir tópico
+ 0
Responder
Posts
20/12/2005
Marcio.theis
Acredito que você pode fazer algo do tipo:
.PAS
.DFM
O SQL utilizado foi:
order by compromissos.data
O que ele ira fazer é agrupar pela data colocando logo abaixo cada movimento do dia.
.PAS
unit Unit2; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, DB, ADODB, QRCtrls, QuickRpt, ExtCtrls; type TrelCompromissos = class(TForm) qrpPeriodo: TQuickRep; DetailBand1: TQRBand; TQRExpr2: TQRExpr; QRExpr3: TQRExpr; qtlbTitulo: TQRBand; TQRLabel1: TQRLabel; qrlNomeEmpresa: TQRLabel; qrlNomeEndereco: TQRLabel; qrlNomeCidadeUF: TQRLabel; QRBand1: TQRBand; TQRLabel3: TQRLabel; QRLabel3: TQRLabel; qrgProduto: TQRGroup; lblProduto: TQRLabel; qreProduto: TQRExpr; qrgProdutoSub: TQRGroup; QRBand2: TQRBand; lblTotal: TQRLabel; QRExpr10: TQRExpr; QRShape3: TQRShape; QRShape4: TQRShape; PageFooter: TQRBand; QRSysData1: TQRSysData; QRSysData4: TQRSysData; QRLabel10: TQRLabel; qrlUsuario: TQRLabel; SummaryBand1: TQRBand; QRLabel2: TQRLabel; QRExpr2: TQRExpr; QRShape1: TQRShape; QRShape2: TQRShape; qryCompromissos: TADOQuery; qryCompromissoscodcomp: TIntegerField; qryCompromissosdata: TDateTimeField; qryCompromissoshora: TStringField; qryCompromissosdescricao: TStringField; qryCompromissosrealizado: TStringField; private { Private declarations } public { Public declarations } end; var relCompromissos: TrelCompromissos; implementation uses Unit1; {$R *.dfm} end.
.DFM
object relCompromissos: TrelCompromissos Left = 189 Top = 106 Width = 696 Height = 480 Caption = ´Relatório de Compromissos´ Color = clBtnFace Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -11 Font.Name = ´MS Sans Serif´ Font.Style = [] OldCreateOrder = False Scaled = False PixelsPerInch = 96 TextHeight = 13 object qrpPeriodo: TQuickRep Left = 0 Top = 0 Width = 794 Height = 1123 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False DataSet = qryCompromissos Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -11 Font.Name = ´Arial´ Font.Style = [] Functions.Strings = ( ´PAGENUMBER´ ´COLUMNNUMBER´ ´REPORTTITLE´) Functions.DATA = ( ´0´ ´0´ ´´´´´´) Options = [LastPageFooter] Page.Columns = 1 Page.Orientation = poPortrait Page.PaperSize = Custom Page.Values = ( 120 2970 120 2100 100 100 0) PrinterSettings.Copies = 1 PrinterSettings.Duplex = False PrinterSettings.FirstPage = 0 PrinterSettings.LastPage = 0 PrinterSettings.OutputBin = First PrintIfEmpty = True ReportTitle = ´Relatório de Compromissos´ SnapToGrid = True Units = MM Zoom = 100 object DetailBand1: TQRBand Left = 38 Top = 188 Width = 718 Height = 11 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False AlignToBottom = False Color = clWhite ForceNewColumn = False ForceNewPage = False Size.Values = ( 29.1041666666667 1899.70833333333) BandType = rbDetail object TQRExpr2: TQRExpr Left = 170 Top = 0 Width = 81 Height = 13 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 34.3958333333333 449.791666666667 0 214.3125) Alignment = taCenter AlignToBand = False AutoSize = False AutoStretch = True Font.Charset = ANSI_CHARSET Font.Color = clBlack Font.Height = -9 Font.Name = ´Arial´ Font.Style = [] Color = clWhite ParentFont = False ResetAfterPrint = False Transparent = False WordWrap = True Expression = ´qryCompromissos.hora´ FontSize = 7 end object QRExpr3: TQRExpr Left = 258 Top = 0 Width = 289 Height = 13 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 34.3958333333333 682.625 0 764.645833333333) Alignment = taLeftJustify AlignToBand = False AutoSize = False AutoStretch = False Font.Charset = ANSI_CHARSET Font.Color = clBlack Font.Height = -9 Font.Name = ´Arial´ Font.Style = [] Color = clWhite ParentFont = False ResetAfterPrint = False Transparent = False WordWrap = True Expression = ´qryCompromissos.descricao´ FontSize = 7 end end object qtlbTitulo: TQRBand Left = 38 Top = 45 Width = 718 Height = 94 Frame.Color = clBlack Frame.DrawTop = True Frame.DrawBottom = True Frame.DrawLeft = True Frame.DrawRight = True Frame.Style = psInsideFrame Frame.Width = 2 AlignToBottom = False Color = clWhite ForceNewColumn = False ForceNewPage = False Size.Values = ( 248.708333333333 1899.70833333333) BandType = rbTitle object TQRLabel1: TQRLabel Left = 532 Top = 7 Width = 175 Height = 17 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 44.9791666666667 1407.58333333333 18.5208333333333 463.020833333333) Alignment = taRightJustify AlignToBand = False AutoSize = True AutoStretch = True Caption = ´Relatório de Compromissos´ Color = clWhite Font.Charset = ANSI_CHARSET Font.Color = clBlack Font.Height = -13 Font.Name = ´Arial´ Font.Style = [fsBold] ParentFont = False Transparent = False WordWrap = True FontSize = 10 end object qrlNomeEmpresa: TQRLabel Left = 8 Top = 8 Width = 162 Height = 23 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 60.8541666666667 21.1666666666667 21.1666666666667 428.625) Alignment = taLeftJustify AlignToBand = False AutoSize = True AutoStretch = False Caption = ´qrlNomeEmpresa´ Color = clWhite Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -19 Font.Name = ´Arial´ Font.Style = [fsBold] ParentFont = False Transparent = False WordWrap = True FontSize = 14 end object qrlNomeEndereco: TQRLabel Left = 8 Top = 50 Width = 104 Height = 17 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 44.9791666666667 21.1666666666667 132.291666666667 275.166666666667) Alignment = taLeftJustify AlignToBand = False AutoSize = True AutoStretch = False Caption = ´qrlNomeEndereco´ Color = clWhite Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -13 Font.Name = ´Arial´ Font.Style = [] ParentFont = False Transparent = False WordWrap = True FontSize = 10 end object qrlNomeCidadeUF: TQRLabel Left = 8 Top = 69 Width = 106 Height = 17 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 44.9791666666667 21.1666666666667 182.5625 280.458333333333) Alignment = taLeftJustify AlignToBand = False AutoSize = True AutoStretch = False Caption = ´qrlNomeCidadeUF´ Color = clWhite Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -13 Font.Name = ´Arial´ Font.Style = [] ParentFont = False Transparent = False WordWrap = True FontSize = 10 end end object QRBand1: TQRBand Left = 38 Top = 139 Width = 718 Height = 26 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False AlignToBottom = False Color = clWhite ForceNewColumn = False ForceNewPage = False Size.Values = ( 68.7916666666667 1899.70833333333) BandType = rbColumnHeader object TQRLabel3: TQRLabel Left = 170 Top = 8 Width = 81 Height = 15 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = True Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 39.6875 449.791666666667 21.1666666666667 214.3125) Alignment = taCenter AlignToBand = False AutoSize = False AutoStretch = False Caption = ´Hora´ Color = clWhite Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -9 Font.Name = ´Arial´ Font.Style = [fsBold] ParentFont = False Transparent = False WordWrap = True FontSize = 7 end object QRLabel3: TQRLabel Left = 258 Top = 8 Width = 289 Height = 15 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = True Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 39.6875 682.625 21.1666666666667 764.645833333333) Alignment = taLeftJustify AlignToBand = False AutoSize = False AutoStretch = False Caption = ´Descrição´ Color = clWhite Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -9 Font.Name = ´Arial´ Font.Style = [fsBold] ParentFont = False Transparent = False WordWrap = True FontSize = 7 end end object qrgProduto: TQRGroup Left = 38 Top = 165 Width = 718 Height = 18 Frame.Color = clBlack Frame.DrawTop = True Frame.DrawBottom = True Frame.DrawLeft = True Frame.DrawRight = True AlignToBottom = False Color = clWhite ForceNewColumn = False ForceNewPage = False Size.Values = ( 47.625 1899.70833333333) Expression = ´qryCompromissos.data´ FooterBand = QRBand2 Master = qrpPeriodo ReprintOnNewPage = False object lblProduto: TQRLabel Left = 20 Top = 1 Width = 54 Height = 20 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 52.9166666666667 52.9166666666667 2.64583333333333 142.875) Alignment = taLeftJustify AlignToBand = False AutoSize = True AutoStretch = False Caption = ´>> Data:´ Color = clWhite Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -13 Font.Name = ´Arial´ Font.Style = [fsBold] ParentFont = False Transparent = False WordWrap = True FontSize = 10 end object qreProduto: TQRExpr Left = 83 Top = 1 Width = 145 Height = 17 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 44.9791666666667 219.604166666667 2.64583333333333 383.645833333333) Alignment = taLeftJustify AlignToBand = False AutoSize = True AutoStretch = False Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -13 Font.Name = ´Arial´ Font.Style = [fsBold] Color = clWhite ParentFont = False ResetAfterPrint = False Transparent = False WordWrap = True Expression = ´qryCompromissos.data´ FontSize = 10 end end object qrgProdutoSub: TQRGroup Left = 38 Top = 183 Width = 718 Height = 5 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False AlignToBottom = False Color = clWhite ForceNewColumn = False ForceNewPage = False Size.Values = ( 13.2291666666667 1899.70833333333) Expression = ´qryCompromissos.data´ Master = qrpPeriodo ReprintOnNewPage = False end object QRBand2: TQRBand Left = 38 Top = 199 Width = 718 Height = 26 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False AlignToBottom = False Color = clWhite ForceNewColumn = False ForceNewPage = False Size.Values = ( 68.7916666666667 1899.70833333333) BandType = rbGroupFooter object lblTotal: TQRLabel Left = 519 Top = 6 Width = 82 Height = 17 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 44.9791666666667 1373.1875 15.875 216.958333333333) Alignment = taLeftJustify AlignToBand = False AutoSize = True AutoStretch = False Caption = ´Total do Dia:´ Color = clWhite Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -13 Font.Name = ´Arial´ Font.Style = [fsBold] ParentFont = False Transparent = False WordWrap = True FontSize = 10 end object QRExpr10: TQRExpr Left = 608 Top = 6 Width = 110 Height = 17 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 44.9791666666667 1608.66666666667 15.875 291.041666666667) Alignment = taRightJustify AlignToBand = False AutoSize = False AutoStretch = False Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -13 Font.Name = ´Arial´ Font.Style = [fsBold] Color = clWhite ParentFont = False ResetAfterPrint = True Transparent = False WordWrap = True Expression = ´COUNT´ Mask = ´,,´ FontSize = 10 end object QRShape3: TQRShape Left = 514 Top = 5 Width = 203 Height = 1 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 2.64583333333333 1359.95833333333 13.2291666666667 537.104166666667) Shape = qrsHorLine end object QRShape4: TQRShape Left = 514 Top = 23 Width = 203 Height = 1 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 2.64583333333333 1359.95833333333 60.8541666666667 537.104166666667) Shape = qrsHorLine end end object PageFooter: TQRBand Left = 38 Top = 251 Width = 718 Height = 19 Frame.Color = clBlack Frame.DrawTop = True Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Frame.Width = 2 AlignToBottom = False Color = clWhite ForceNewColumn = False ForceNewPage = False Size.Values = ( 50.2708333333333 1899.70833333333) BandType = rbPageFooter object QRSysData1: TQRSysData Left = 641 Top = 3 Width = 74 Height = 15 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 39.6875 1695.97916666667 7.9375 195.791666666667) Alignment = taRightJustify AlignToBand = False AutoSize = True Color = clWhite Data = qrsPageNumber Text = ´Página ´ Transparent = False FontSize = 8 end object QRSysData4: TQRSysData Left = 556 Top = 3 Width = 59 Height = 15 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 39.6875 1471.08333333333 7.9375 156.104166666667) Alignment = taLeftJustify AlignToBand = False AutoSize = True Color = clWhite Data = qrsDate Text = ´Data: ´ Transparent = False FontSize = 8 end object QRLabel10: TQRLabel Left = 3 Top = 3 Width = 132 Height = 15 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 39.6875 7.9375 7.9375 349.25) Alignment = taLeftJustify AlignToBand = False AutoSize = True AutoStretch = False Caption = ´Relatório de Compromissos´ Color = clWhite Transparent = False WordWrap = True FontSize = 8 end object qrlUsuario: TQRLabel Left = 250 Top = 3 Width = 250 Height = 15 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 39.6875 661.458333333333 7.9375 661.458333333333) Alignment = taCenter AlignToBand = False AutoSize = False AutoStretch = False Caption = ´USUARIO´ Color = clWhite Transparent = False WordWrap = True FontSize = 8 end end object SummaryBand1: TQRBand Left = 38 Top = 225 Width = 718 Height = 26 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False AlignToBottom = False Color = clWhite ForceNewColumn = False ForceNewPage = False Size.Values = ( 68.7916666666667 1899.70833333333) BandType = rbSummary object QRLabel2: TQRLabel Left = 595 Top = 6 Width = 45 Height = 20 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 52.9166666666667 1574.27083333333 15.875 119.0625) Alignment = taLeftJustify AlignToBand = False AutoSize = True AutoStretch = False Caption = ´Total:´ Color = clWhite Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -16 Font.Name = ´Arial´ Font.Style = [fsBold] ParentFont = False Transparent = False WordWrap = True FontSize = 12 end object QRExpr2: TQRExpr Left = 650 Top = 5 Width = 65 Height = 20 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 52.9166666666667 1719.79166666667 13.2291666666667 171.979166666667) Alignment = taRightJustify AlignToBand = False AutoSize = False AutoStretch = False Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -16 Font.Name = ´Arial´ Font.Style = [fsBold] Color = clWhite ParentFont = False ResetAfterPrint = False Transparent = False WordWrap = True Expression = ´COUNT´ FontSize = 12 end object QRShape1: TQRShape Left = 590 Top = 23 Width = 127 Height = 3 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 7.9375 1561.04166666667 60.8541666666667 336.020833333333) Shape = qrsHorLine end object QRShape2: TQRShape Left = 590 Top = 4 Width = 127 Height = 3 Frame.Color = clBlack Frame.DrawTop = False Frame.DrawBottom = False Frame.DrawLeft = False Frame.DrawRight = False Size.Values = ( 7.9375 1561.04166666667 10.5833333333333 336.020833333333) Shape = qrsHorLine end end end object qryCompromissos: TADOQuery Connection = Form1.ADOConnection Parameters = <> SQL.Strings = ( ´select *´ ´from compromissos´ ´order by compromissos.data´) Left = 8 Top = 16 object qryCompromissoscodcomp: TIntegerField FieldName = ´codcomp´ end object qryCompromissosdata: TDateTimeField FieldName = ´data´ end object qryCompromissoshora: TStringField FieldName = ´hora´ Size = 5 end object qryCompromissosdescricao: TStringField FieldName = ´descricao´ Size = 100 end object qryCompromissosrealizado: TStringField FieldName = ´realizado´ FixedChar = True Size = 1 end end end
O SQL utilizado foi:
select * from compromissos where compromissos.data between :DataIni and :DataFim order by compromissos.data
order by compromissos.data
O que ele ira fazer é agrupar pela data colocando logo abaixo cada movimento do dia.
Responder
Clique aqui para fazer login e interagir na Comunidade :)