?
Solved

Report with QuickReport using 3 tables

Posted on 2005-03-28
13
Medium Priority
?
581 Views
Last Modified: 2013-11-22
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
Comment
Question by:Fredy992
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
13 Comments
 
LVL 4

Expert Comment

by:bjove
ID: 13697992
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
 

Author Comment

by:Fredy992
ID: 14264634
The solution that bjove gave me didn´t work.
0
 

Author Comment

by:Fredy992
ID: 14275041
The report simply didn´t group by the Disks with the Disks´ partition.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:bjove
ID: 14275291
You must sort records in the query by codcomp , codunit, codpart. I can send you an example project if you like. It works.
0
 

Author Comment

by:Fredy992
ID: 14275320
Would you do that please ??
0
 
LVL 4

Expert Comment

by:bjove
ID: 14282193
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
 

Author Comment

by:Fredy992
ID: 14282865
Send it to <mail removed by AnnieMod>
0
 

Author Comment

by:Fredy992
ID: 14282989
Sorry. What can I do so he can send me the example ??
0
 

Author Comment

by:Fredy992
ID: 14283022
Hey bjove
  Can you upload it somewhere ??
0
 
LVL 4

Accepted Solution

by:
bjove earned 600 total points
ID: 14283115
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question