• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

Report with QuickReport using 3 tables

I´m working with Delphi 5 and using QuickReport to do my reports. I need to make a report that uses three table. I made a report with a Detail band to show the information of my master table that will show all the computers in the Database wich is:

create table computer (
   codcomp varchar2(65) not null primary key,
   computer_name varchar2(65),
   computer_user varchar2(50)
);

Than I put in a "GroupHeader1" and a "SubDetail1". This SubDetail1 has it´s property "HearderBand" ponting to the "GroupHeader1". This SubDetail will show all of the Hard Disks from the computer. Table:

create table disk (
   codcomp varchar2(65) not null references computer (codcomp) on delete cascade,
   codunit integer not null,
   unit_id varchar2(100),
   capacity double precision,
   primary key(codcomp, codunit)
);


Now I need to show all of the Partitions from this Hard Disk from the computer showing in the same report using this table: And this table will also have more than one Partition.

create table disk_part (
   codcomp varchar2(65) not null references computer (codcomp) on delete cascade,
   codunit integer not null references disk (codunit) on delete cascade,
   codpart integer not null,
   type varchar2(15),
   primary key(codcomp, codunit,codpar)
);

I tried to put a SubDetail but it won´t work. It repeats the table Disk and I don´t want it to be repeated. I want it to show the Computer than show all of it´s Disk below and right below it show all of it´s partition. How can I do this ?
0
Fredy992
Asked:
Fredy992
  • 6
  • 4
1 Solution
 
bjoveCommented:
Put one QRGroup band ex. QRGroup1.
Put one QRGroup band ex. QRGroup2.

Put one QRband band ex. QRBand1.
Make
  QRBand1.BandType = rbDetail

Sort the query by codcomp , codunit, codpart

Put one QRDBText on QRGroup1 for codcomp
Put one QRDBText on QRGroup2 for codunit
Put one QRDBText on QRBand for codpart

Set
  QRGroup1.Expression = codcomp  
  QRGroup2.Expression = codunit
 
0
 
Fredy992Author Commented:
The solution that bjove gave me didn´t work.
0
 
Fredy992Author Commented:
The report simply didn´t group by the Disks with the Disks´ partition.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
bjoveCommented:
You must sort records in the query by codcomp , codunit, codpart. I can send you an example project if you like. It works.
0
 
Fredy992Author Commented:
Would you do that please ??
0
 
bjoveCommented:
Can you give me your email address, so i can send you the project.
About the solution... Make sure that you select correct components. QRGroup is a different component on the palete. You have two QRGroup components (bands) (one with property Expresion set to codcomp and one with Expresion set to codunit) and one QRBand component (band) with property BandType set to rbDetail. Ordering of the query is important. You must order records by codcomp , codunit, codpart.
0
 
Fredy992Author Commented:
Send it to <mail removed by AnnieMod>
0
 
Fredy992Author Commented:
Sorry. What can I do so he can send me the example ??
0
 
Fredy992Author Commented:
Hey bjove
  Can you upload it somewhere ??
0
 
bjoveCommented:
Project is made in delphi 6 and Access database.

This is unit1.dfm:
-------------------------------------------------------------------------------------
object Form1: TForm1
  Left = 486
  Top = 284
  Width = 423
  Height = 368
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnShow = FormShow
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 96
    Top = 56
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 0
    OnClick = Button1Click
  end
  object ADOConnection1: TADOConnection
    ConnectionString =
      'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\te' +
      'st.mdb;Mode=Share Deny None;Extended Properties="";Persist Secur' +
      'ity Info=False;Jet OLEDB:System database="";Jet OLEDB:Registry P' +
      'ath="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Je' +
      't OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Op' +
      's=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database ' +
      'Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Enc' +
      'rypt Database=False;Jet OLEDB:Don'#39't Copy Locale on Compact=False' +
      ';Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=Fa' +
      'lse'
    LoginPrompt = False
    Provider = 'Microsoft.Jet.OLEDB.4.0'
    Left = 24
    Top = 32
  end
  object ADOQuery1: TADOQuery
    Connection = ADOConnection1
    CursorType = ctStatic
    Parameters = <>
    SQL.Strings = (
     
        'SELECT computer.codcomp, computer.computer_name, computer.comput' +
        'er_user, disk.codunit, disk.unit_id, disk.capacity, disk_part.co' +
        'dpart, disk_part.type'
     
        'FROM (computer INNER JOIN disk ON computer.codcomp = disk.codcom' +
        'p) INNER JOIN disk_part ON (disk_part.codcomp = disk.codcomp) AN' +
        'D (disk.codunit = disk_part.codunit) AND (computer.codcomp = dis' +
        'k_part.codcomp)'
      'ORDER BY computer.codcomp, disk.codunit, disk_part.codpart')
    Left = 40
    Top = 136
  end
end
---------------------------------------------------------------------------------------------


This is unit1.pas:
----------------------------------------------------------------------------------------------

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB;

type
  TForm1 = class(TForm)
    Button1: TButton;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    procedure FormShow(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

uses Unit2;

{$R *.dfm}

procedure TForm1.FormShow(Sender: TObject);
begin
      ADOConnection1.Open;
  ADOQuery1.Open;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
      Form2.QuickRep1.Preview;
end;

end.
-------------------------------------------------------------------------------

Unit2.dfm
-------------------------------------------------------------------------------
object Form2: TForm2
  Left = 192
  Top = 114
  Width = 870
  Height = 640
  Caption = 'Form2'
  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 QuickRep1: TQuickRep
    Left = 24
    Top = 16
    Width = 794
    Height = 1123
    Frame.Color = clBlack
    Frame.DrawTop = False
    Frame.DrawBottom = False
    Frame.DrawLeft = False
    Frame.DrawRight = False
    DataSet = Form1.ADOQuery1
    Font.Charset = DEFAULT_CHARSET
    Font.Color = clWindowText
    Font.Height = -13
    Font.Name = 'Arial'
    Font.Style = []
    Functions.Strings = (
      'PAGENUMBER'
      'COLUMNNUMBER'
      'REPORTTITLE')
    Functions.DATA = (
      '0'
      '0'
      #39#39)
    Options = [FirstPageHeader, LastPageFooter]
    Page.Columns = 1
    Page.Orientation = poPortrait
    Page.PaperSize = A4
    Page.Values = (
      100
      2970
      100
      2100
      100
      100
      0)
    PrinterSettings.Copies = 1
    PrinterSettings.Duplex = False
    PrinterSettings.FirstPage = 0
    PrinterSettings.LastPage = 0
    PrinterSettings.OutputBin = Auto
    PrintIfEmpty = True
    SnapToGrid = True
    Units = MM
    Zoom = 100
    object QRGroup1: TQRGroup
      Left = 38
      Top = 38
      Width = 718
      Height = 40
      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 = (
        105.833333333333
        1899.70833333333)
      Expression = 'codcomp'
      Master = QuickRep1
      ReprintOnNewPage = False
      object QRDBText1: TQRDBText
        Left = 8
        Top = 8
        Width = 94
        Height = 17
        Frame.Color = clBlack
        Frame.DrawTop = False
        Frame.DrawBottom = False
        Frame.DrawLeft = False
        Frame.DrawRight = False
        Size.Values = (
          44.9791666666667
          21.1666666666667
          21.1666666666667
          248.708333333333)
        Alignment = taLeftJustify
        AlignToBand = False
        AutoSize = True
        AutoStretch = False
        Color = clWhite
        DataSet = Form1.ADOQuery1
        DataField = 'computer_name'
        Transparent = False
        WordWrap = True
        FontSize = 10
      end
    end
    object QRGroup2: TQRGroup
      Left = 38
      Top = 78
      Width = 718
      Height = 40
      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 = (
        105.833333333333
        1899.70833333333)
      Expression = 'codunit'
      Master = QuickRep1
      ReprintOnNewPage = False
      object QRDBText2: TQRDBText
        Left = 32
        Top = 8
        Width = 39
        Height = 17
        Frame.Color = clBlack
        Frame.DrawTop = False
        Frame.DrawBottom = False
        Frame.DrawLeft = False
        Frame.DrawRight = False
        Size.Values = (
          44.9791666666667
          84.6666666666667
          21.1666666666667
          103.1875)
        Alignment = taLeftJustify
        AlignToBand = False
        AutoSize = True
        AutoStretch = False
        Color = clWhite
        DataSet = Form1.ADOQuery1
        DataField = 'unit_id'
        Transparent = False
        WordWrap = True
        FontSize = 10
      end
      object QRDBText3: TQRDBText
        Left = 96
        Top = 8
        Width = 50
        Height = 17
        Frame.Color = clBlack
        Frame.DrawTop = False
        Frame.DrawBottom = False
        Frame.DrawLeft = False
        Frame.DrawRight = False
        Size.Values = (
          44.9791666666667
          254
          21.1666666666667
          132.291666666667)
        Alignment = taLeftJustify
        AlignToBand = False
        AutoSize = True
        AutoStretch = False
        Color = clWhite
        DataSet = Form1.ADOQuery1
        DataField = 'capacity'
        Transparent = False
        WordWrap = True
        FontSize = 10
      end
    end
    object QRBand1: TQRBand
      Left = 38
      Top = 118
      Width = 718
      Height = 40
      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 = (
        105.833333333333
        1899.70833333333)
      BandType = rbDetail
      object QRDBText4: TQRDBText
        Left = 176
        Top = 16
        Width = 44
        Height = 17
        Frame.Color = clBlack
        Frame.DrawTop = False
        Frame.DrawBottom = False
        Frame.DrawLeft = False
        Frame.DrawRight = False
        Size.Values = (
          44.9791666666667
          465.666666666667
          42.3333333333333
          116.416666666667)
        Alignment = taLeftJustify
        AlignToBand = False
        AutoSize = True
        AutoStretch = False
        Color = clWhite
        DataSet = Form1.ADOQuery1
        DataField = 'codpart'
        Transparent = False
        WordWrap = True
        FontSize = 10
      end
      object QRDBText5: TQRDBText
        Left = 240
        Top = 16
        Width = 26
        Height = 17
        Frame.Color = clBlack
        Frame.DrawTop = False
        Frame.DrawBottom = False
        Frame.DrawLeft = False
        Frame.DrawRight = False
        Size.Values = (
          44.9791666666667
          635
          42.3333333333333
          68.7916666666667)
        Alignment = taLeftJustify
        AlignToBand = False
        AutoSize = True
        AutoStretch = False
        Color = clWhite
        DataSet = Form1.ADOQuery1
        DataField = 'type'
        Transparent = False
        WordWrap = True
        FontSize = 10
      end
    end
  end
end
---------------------------------------------------------------------------------------

unit2.pas
----------------------------------------------------------------------------------------

unit Unit2;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, QuickRpt, QRCtrls, ExtCtrls;

type
  TForm2 = class(TForm)
    QuickRep1: TQuickRep;
    QRGroup1: TQRGroup;
    QRGroup2: TQRGroup;
    QRBand1: TQRBand;
    QRDBText1: TQRDBText;
    QRDBText2: TQRDBText;
    QRDBText3: TQRDBText;
    QRDBText4: TQRDBText;
    QRDBText5: TQRDBText;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form2: TForm2;

implementation

uses Unit1;

{$R *.dfm}

end.
---------------------------------------------------------------------------


0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now