|
|
 | | From: | Heinz Kesting | | Subject: | Report with Master/Detail Rowset | | Date: | Fri, 31 Dec 2004 16:40:38 +0100 |
|
|
 | Hello everybody,
I'm at my wits end after countless trials to get a rather simple thing to
work.
There are two rowsets, linked through a master/detail connection. The report
should display from the master rowset only one field (kind of a subtitle),
below listed 4 fields from the detail rowset in a grid-like way. Business as
usual. The corresponding detail rows are never more than 15 or max. 20, so
they always will fit on just one page.
The next master row (with its corresponding detail rows) should be printed
then on a new page, and here I seem to stuck.
Can anybody please deliver a rough layout for this kind of report?
Thanks for helping in adavnce,
Heinz
P.S. A happy, peaceful and prosperous New Year to everyone around here!
|
|
| | From: | Michael Nuwer [dBVIPS] | | Subject: | Re: Report with Master/Detail Rowset | | Date: | Fri, 31 Dec 2004 11:16:23 -0500 |
|
|
| Heinz Kesting wrote:
> Hello everybody,
> I'm at my wits end after countless trials to get a rather simple thing to
> work.
> There are two rowsets, linked through a master/detail connection. The report
> should display from the master rowset only one field (kind of a subtitle),
> below listed 4 fields from the detail rowset in a grid-like way. Business as
> usual. The corresponding detail rows are never more than 15 or max. 20, so
> they always will fit on just one page.
> The next master row (with its corresponding detail rows) should be printed
> then on a new page, and here I seem to stuck.
> Can anybody please deliver a rough layout for this kind of report?
Does your report have a group band? If yes, the headerBand object has a
"beginNewFrame" property which will do what you need.
with (this.STREAMSOURCE1.GROUP1.headerBand)
beginNewFrame = true
endwith
>
> P.S. A happy, peaceful and prosperous New Year to everyone around here!
Yes, I will "me too" that sentiment.
** END HEADER -- do not remove this line
//
// Generated on 12/31/04
//
local r
r = new SampleReport()
r.render()
class SampleReport of REPORT
with (this)
autoSort = false
endwith
this.DBASESAMPLES1 = new DATABASE()
this.DBASESAMPLES1.parent = this
with (this.DBASESAMPLES1)
left = 585.0
top = 465.0
databaseName = "DBASESAMPLES"
active = true
endwith
this.MASTER1 = new QUERY()
this.MASTER1.parent = this
with (this.MASTER1)
left = 585.0
top = 465.0
database = form.dbasesamples1
sql = "select * from fish.dbf"
requestLive = false
active = true
endwith
this.CHILD2 = new QUERY()
this.CHILD2.parent = this
with (this.CHILD2)
left = 555.0
top = 2010.0
database = form.dbasesamples1
sql = "select * from fish.dbf"
requestLive = false
active = true
endwith
with (this.CHILD2.rowset)
indexName = "ID"
masterRowset = form.master1.rowset
masterFields = "ID"
endwith
with (this.printer)
duplex = 1 // None
orientation = 1 // Portrait
paperSource = 15
paperSize = 1
resolution = 4 // High
color = 1 // Monochrome
trueTypeFonts = 2 // Download
endwith
this.STREAMSOURCE1 = new STREAMSOURCE(this)
this.STREAMSOURCE1.GROUP1 = new GROUP(this.STREAMSOURCE1)
with (this.STREAMSOURCE1.GROUP1)
groupBy = "ID"
endwith
with (this.STREAMSOURCE1.GROUP1.headerBand)
height = 250.0
beginNewFrame = true
endwith
this.STREAMSOURCE1.GROUP1.headerBand.TEXT1 = new
TEXT(this.STREAMSOURCE1.GROUP1.headerBand)
with (this.STREAMSOURCE1.GROUP1.headerBand.TEXT1)
height = 300.0
left = 0.0
top = 0.0
width = 3000.0
prefixEnable = false
text = "Header Text for Group1"
endwith
this.STREAMSOURCE1.GROUP1.headerBand.TEXTNAME1 = new
TEXT(this.STREAMSOURCE1.GROUP1.headerBand)
with (this.STREAMSOURCE1.GROUP1.headerBand.TEXTNAME1)
height = 300.0
left = 3300.0
top = 60.0
width = 2880.0
variableHeight = true
prefixEnable = false
text = {||this.form.master1.rowset.fields["name"].value}
endwith
with (this.STREAMSOURCE1.GROUP1.footerBand)
height = 250.0
endwith
with (this.STREAMSOURCE1.detailBand)
height = 500.0
endwith
this.STREAMSOURCE1.detailBand.TITLETEXTSPECIES1 = new
TEXT(this.STREAMSOURCE1.detailBand)
with (this.STREAMSOURCE1.detailBand.TITLETEXTSPECIES1)
canRender = {||this.parent.firstOnFrame}
height = 300.0
left = 5370.0
top = 105.0
width = 3780.0
prefixEnable = false
suppressIfBlank = true
text = "Species"
endwith
this.STREAMSOURCE1.detailBand.TEXTSPECIES1 = new
TEXT(this.STREAMSOURCE1.detailBand)
with (this.STREAMSOURCE1.detailBand.TEXTSPECIES1)
height = 293.0
left = 5370.0
top = 398.0
width = 3780.0
variableHeight = true
prefixEnable = false
text = {||this.form.child2.rowset.fields["species"].value}
endwith
this.PAGETEMPLATE1 = new PAGETEMPLATE(this)
with (this.PAGETEMPLATE1)
height = 15840.0
width = 12240.0
marginTop = 1080.0
marginLeft = 1080.0
marginBottom = 1080.0
marginRight = 1080.0
gridLineWidth = 0
endwith
this.PAGETEMPLATE1.STREAMFRAME1 = new STREAMFRAME(this.PAGETEMPLATE1)
with (this.PAGETEMPLATE1.STREAMFRAME1)
height = 11592.0
left = 360.0
top = 1365.0
width = 9360.0
form.STREAMFRAME1 = form.pagetemplate1.streamframe1
endwith
this.firstPageTemplate = this.form.pagetemplate1
this.form.pagetemplate1.nextPageTemplate = this.form.pagetemplate1
this.form.pagetemplate1.streamframe1.streamSource =
this.form.streamsource1
this.form.streamsource1.rowset = this.form.master1.rowset
endclass
--
Michael Nuwer
http://www.ChelseaData.ca/dLearn/
http://www.nuwermj.potsdam.edu/dSamples/
|
|
| | From: | Heinz Kesting | | Subject: | Re: Report with Master/Detail Rowset | | Date: | Sat, 1 Jan 2005 22:07:47 +0100 |
|
|
| Hi Michael,
Thanks a lot - that was exactly what I was trying to do.
After going into some detail I finally tried to group the detail rowset. The
criteria for this is stored in a character field of the detail rowset,
containing just one letter, an "A" or an "M". So I thought I could do the
trick with building up a second group, called GROUP2 in the code below, but
it didn't work. Obviously the field to group by must be part of the
form.rowset, which - in this case - is the master rowset, and not the detail
rowset, where this field (called ART) is included.
The footerband I created is displayed after the very last detail row, but
not after the last detail row of the group I wanted.
Is it possible to have the detail rows grouped this way at all?
Alternatively I sorted the detail rows per index (MASTERFIELD + ART) and
added the field ART to the columns of the report. Additionally I tried to
replace the field values ("A" respectively "M") with the corresponding
German word for the abbreviated field value to make it easier to understand.
I tried the following code lines for the text property in the object
constructor, but all of these three failed ...
text = {||iif({this.form.q_adet.rowset.fields["art"].value} = "A", "Arbeit",
"Material")}
text = {||iif({this.form.q_adet.rowset.fields["art"].value} == "A",
"Arbeit", "Material")}
text = {||iif("A" $ {this.form.q_adet.rowset.fields["art"].value}, "Arbeit",
"Material")}
While the last one produces an error, saying "incompatible data type,
required: Char",
the first and second ones work without error, but - ignoring the field value
and - showing always the same result: the first line always returns
"Arbeit", so the statement in the IIF here is found TRUE, while the second
line always returns "Material", here the IIF statement must be found FALSE.
I have no explanation for this, as the field ART can hold only ONE character
by table definition.
** END HEADER -- Diese Zeile nicht entfernen
//
// Erstellt am 01.01.2005
//
local r
r = new SAMPLEREPORT()
r.render()
class SAMPLEREPORT of REPORT
with (this)
metric = 4 // Zentimeter
autoSort = false
endwith
this.DBASE = new DATABASE()
this.DBASE.parent = this
with (this.DBASE)
left = -0.0212
top = -0.0212
databaseName = "CALCULAB"
active = true
endwith
this.Q_PAK = new QUERY()
this.Q_PAK.parent = this
with (this.Q_PAK)
left = -0.0212
top = -0.0212
database = form.dbase
sql = "select * from packages.dbf"
requestLive = false
active = true
endwith
with (this.Q_PAK.rowset)
indexName = "PAKET_ID"
endwith
this.Q_ADET = new QUERY()
this.Q_ADET.parent = this
with (this.Q_ADET)
left = -0.0212
top = -0.0212
database = form.dbase
sql = "select * from content.dbf"
requestLive = false
active = true
endwith
with (this.Q_ADET.rowset)
indexName = "TEST"
masterRowset = form.q_pak.rowset
masterFields = "PAKET_ID"
endwith
this.Q_MDET = new QUERY()
this.Q_MDET.parent = this
with (this.Q_MDET)
left = -0.0212
top = -0.0212
database = form.dbase
sql = 'select * from content.dbf where ART = "M"'
requestLive = false
active = true
endwith
with (this.Q_MDET.rowset)
indexName = "PAKET_ID"
masterRowset = form.q_pak.rowset
masterFields = "PAKET_ID"
endwith
this.PAGETEMPLATE1 = new PAGETEMPLATE(this)
with (this.PAGETEMPLATE1)
height = 29.6986
width = 20.9991
marginTop = 1.905
marginLeft = 1.905
marginBottom = 1.905
marginRight = 1.905
gridLineWidth = 0
endwith
this.PAGETEMPLATE1.STREAMFRAME1 = new STREAMFRAME(this.PAGETEMPLATE1)
with (this.PAGETEMPLATE1.STREAMFRAME1)
height = 19.8967
left = 0.635
top = 2.9845
width = 16.51
form.STREAMFRAME1 = form.pagetemplate1.streamframe1
endwith
this.PAGETEMPLATE1.TXT_HEAD = new TEXT(this.PAGETEMPLATE1)
with (this.PAGETEMPLATE1.TXT_HEAD)
height = 1.27
left = 0.7832
top = 0.2963
width = 16.51
prefixEnable = false
alignVertical = 1 // Mitte
alignHorizontal = 1 // Zentriert
fontSize = 18.0
fontBold = true
fontItalic = true
text = "vordefinierte Rechnungspakete"
borderStyle = 6 // Schattiert
form.TXT_HEAD = form.pagetemplate1.txt_head
endwith
this.PAGETEMPLATE1.TXT_ANZ = new TEXT(this.PAGETEMPLATE1)
with (this.PAGETEMPLATE1.TXT_ANZ)
height = 0.4868
left = 13.0883
top = 5.2408
width = 1.651
prefixEnable = false
alignVertical = 1 // Mitte
fontBold = true
fontItalic = true
text = " Anzahl"
borderStyle = 4 // Einfach
form.TXT_ANZ = form.pagetemplate1.txt_anz
endwith
this.PAGETEMPLATE1.TXT_POS = new TEXT(this.PAGETEMPLATE1)
with (this.PAGETEMPLATE1.TXT_POS)
height = 0.4869
left = 0.6635
top = 5.2408
width = 1.4358
prefixEnable = false
alignVertical = 1 // Mitte
alignHorizontal = 1 // Zentriert
fontBold = true
fontItalic = true
text = "Pos.Nr"
borderStyle = 4 // Einfach
form.TXT_POS = form.pagetemplate1.txt_pos
endwith
this.PAGETEMPLATE1.TXT_EDV = new TEXT(this.PAGETEMPLATE1)
with (this.PAGETEMPLATE1.TXT_EDV)
height = 0.4868
left = 2.1028
top = 5.2408
width = 2.032
prefixEnable = false
alignVertical = 1 // Mitte
alignHorizontal = 1 // Zentriert
fontBold = true
fontItalic = true
text = "EDV-Nr"
borderStyle = 4 // Einfach
form.TXT_EDV = form.pagetemplate1.txt_edv
endwith
this.PAGETEMPLATE1.TXT_BEZ = new TEXT(this.PAGETEMPLATE1)
with (this.PAGETEMPLATE1.TXT_BEZ)
height = 0.4868
left = 4.1348
top = 5.2408
width = 8.9535
prefixEnable = false
alignVertical = 1 // Mitte
fontBold = true
fontItalic = true
text = " Bezeichnung"
borderStyle = 4 // Einfach
form.TXT_BEZ = form.pagetemplate1.txt_bez
endwith
this.PAGETEMPLATE1.TXT_ART = new TEXT(this.PAGETEMPLATE1)
with (this.PAGETEMPLATE1.TXT_ART)
height = 0.4868
left = 14.7394
top = 5.2408
width = 2.3495
prefixEnable = false
alignVertical = 1 // Mitte
alignHorizontal = 1 // Zentriert
fontBold = true
fontItalic = true
text = "Leistungsart"
borderStyle = 4 // Einfach
form.TXT_ART = form.pagetemplate1.txt_art
endwith
this.PAGETEMPLATE1.TXT_FOOT = new TEXT(this.PAGETEMPLATE1)
with (this.PAGETEMPLATE1.TXT_FOOT)
height = 0.5
left = 0.0
top = 25.0
width = 17.0
prefixEnable = false
alignVertical = 1 // Mitte
alignHorizontal = 1 // Zentriert
fontSize = 8.0
text = {||"CalcuLab: vordefinierte Rechnungspakete"}
borderStyle = 4 // Einfach
form.TXT_FOOT = form.pagetemplate1.txt_foot
endwith
this.STREAMSOURCE1 = new STREAMSOURCE(this)
this.STREAMSOURCE1.GROUP1 = new GROUP(this.STREAMSOURCE1)
with (this.STREAMSOURCE1.GROUP1)
groupBy = "PAKET_ID"
endwith
with (this.STREAMSOURCE1.GROUP1.headerBand)
height = 3.0869
beginNewFrame = true
endwith
this.STREAMSOURCE1.GROUP1.headerBand.REC_HEAD = new
RECTANGLE(this.STREAMSOURCE1.GROUP1.headerBand)
with (this.STREAMSOURCE1.GROUP1.headerBand.REC_HEAD)
left = 0.0
top = 0.0212
width = 16.4676
height = 1.3758
text = " Paketname "
colorNormal = "BtnText/white"
fontSize = 9.0
fontBold = true
fontItalic = true
endwith
this.STREAMSOURCE1.GROUP1.headerBand.TXT_PAKETNAME = new
TEXT(this.STREAMSOURCE1.GROUP1.headerBand)
with (this.STREAMSOURCE1.GROUP1.headerBand.TXT_PAKETNAME)
height = 0.5292
left = 1.5451
top = 0.5715
width = 14.732
variableHeight = true
prefixEnable = false
fontSize = 13.0
fontBold = true
fontItalic = true
text = {||this.form.q_pak.rowset.fields["paket_bez"].value}
endwith
with (this.STREAMSOURCE1.GROUP1.footerBand)
height = 0.441
endwith
this.STREAMSOURCE1.GROUP2 = new GROUP(this.STREAMSOURCE1)
with (this.STREAMSOURCE1.GROUP2)
groupBy = "ART"
endwith
with (this.STREAMSOURCE1.GROUP2.footerBand)
height = 0.6739
endwith
this.STREAMSOURCE1.GROUP2.footerBand.TXT_PAKETNAME = new
TEXT(this.STREAMSOURCE1.GROUP2.footerBand)
with (this.STREAMSOURCE1.GROUP2.footerBand.TXT_PAKETNAME)
height = 0.5292
left = 0.9313
top = 0.1482
width = 14.732
variableHeight = true
prefixEnable = false
fontSize = 10.0
fontBold = true
fontItalic = true
text = {||"GROUP2.footerBand.TXT_PAKETNAME"}
endwith
with (this.STREAMSOURCE1.detailBand)
height = 0.3951
endwith
this.STREAMSOURCE1.detailBand.TXT_VAL_POS = new
TEXT(this.STREAMSOURCE1.detailBand)
with (this.STREAMSOURCE1.detailBand.TXT_VAL_POS)
height = 0.4868
left = 0.0847
top = 0.0
width = 1.3935
variableHeight = true
prefixEnable = false
alignVertical = 1 // Mitte
alignHorizontal = 1 // Zentriert
text = {||this.form.q_adet.rowset.fields["lf_nr"].value}
endwith
this.STREAMSOURCE1.detailBand.TXT_VAL_EDV = new
TEXT(this.STREAMSOURCE1.detailBand)
with (this.STREAMSOURCE1.detailBand.TXT_VAL_EDV)
height = 0.4868
left = 1.6086
top = 0.0
width = 1.8838
variableHeight = true
prefixEnable = false
alignVertical = 1 // Mitte
alignHorizontal = 1 // Zentriert
text = {||this.form.q_adet.rowset.fields["edv_nr"].value}
endwith
this.STREAMSOURCE1.detailBand.TXT_VAL_BEZ = new
TEXT(this.STREAMSOURCE1.detailBand)
with (this.STREAMSOURCE1.detailBand.TXT_VAL_BEZ)
height = 0.0
left = 3.81
top = 0.0
width = 8.509
variableHeight = true
prefixEnable = false
alignVertical = 1 // Mitte
text = {||this.form.q_adet.rowset.fields["bezeichnung"].value}
endwith
this.STREAMSOURCE1.detailBand.TXT_VAL_ANZ = new
TEXT(this.STREAMSOURCE1.detailBand)
with (this.STREAMSOURCE1.detailBand.TXT_VAL_ANZ)
height = 0.4868
left = 12.3613
top = 0.0
width = 1.3123
variableHeight = true
prefixEnable = false
alignVertical = 1 // Mitte
alignHorizontal = 2 // Rechts
text = {||this.form.q_adet.rowset.fields["anzahl"].value}
endwith
this.STREAMSOURCE1.detailBand.TXT_VAL_ART = new
TEXT(this.STREAMSOURCE1.detailBand)
with (this.STREAMSOURCE1.detailBand.TXT_VAL_ART)
height = 0.4868
left = 14.3086
top = 0.0
width = 2.032
prefixEnable = false
alignVertical = 1 // Mitte
alignHorizontal = 1 // Zentriert
// text = {||this.form.q_adet.rowset.fields["art"].value}
text = {||iif({this.form.q_adet.rowset.fields["art"].value} = "A",
"Arbeit", "Material")}
// text = {||iif("A" $ {this.form.q_adet.rowset.fields["art"].value},
"Arbeit", "Material")}
endwith
with (this.printer)
duplex = 1 // Ohne
orientation = 1 // Hochformat
paperSource = 15
paperSize = 1
resolution = 4 // Hoch
color = 1 // Monochrom
trueTypeFonts = 2 // Download
endwith
this.firstPageTemplate = this.form.pagetemplate1
this.form.pagetemplate1.nextPageTemplate = this.form.pagetemplate1
this.form.pagetemplate1.streamframe1.streamSource =
this.form.streamsource1
this.form.streamsource1.rowset = this.form.q_pak.rowset
endclass
Thanks for helping,
Best regards, Heinz
|
|
| | From: | Michael Nuwer [dBVIPS] | | Subject: | Re: Report with Master/Detail Rowset | | Date: | Sat, 01 Jan 2005 17:00:20 -0500 |
|
|
| Heinz Kesting wrote:
> Hi Michael,
> Thanks a lot - that was exactly what I was trying to do.
> After going into some detail I finally tried to group the detail rowset. The
> criteria for this is stored in a character field of the detail rowset,
> containing just one letter, an "A" or an "M". So I thought I could do the
> trick with building up a second group, called GROUP2 in the code below, but
> it didn't work. Obviously the field to group by must be part of the
> form.rowset, which - in this case - is the master rowset, and not the detail
> rowset, where this field (called ART) is included.
> The footerband I created is displayed after the very last detail row, but
> not after the last detail row of the group I wanted.
> Is it possible to have the detail rows grouped this way at all?
Yes, in some cases it is possible. There is an explanation of how to do
this in a rather old, but still useful, dBulletin article:
http://www.jpmartel.com/bu04_b.htm
I have found that the method discussed in the article tends to fail when
a filter is needed for data in the child table. But, if such a filter
is not necessary, the method works fine.
>
> text = {||iif({this.form.q_adet.rowset.fields["art"].value} = "A", "Arbeit",
> "Material")}
For the above line, try this part:
iif({this.form.q_adet.rowset.fields["art"].value},...
like this:
iif(this.form.q_adet.rowset.fields["art"].value,...
The full expression is this:
text = {||iif(this.form.q_adet.rowset.fields["art"].value="A","Arbeit",
"Material")}
--
Michael Nuwer
http://www.ChelseaData.ca/dLearn/
http://www.nuwermj.potsdam.edu/dSamples/
|
|
| | From: | Heinz Kesting | | Subject: | Re: Report with Master/Detail Rowset | | Date: | Sun, 2 Jan 2005 21:52:56 +0100 |
|
|
| Hi Michael
> Yes, in some cases it is possible. There is an explanation of how to do
> this in a rather old, but still useful, dBulletin article:
>
> http://www.jpmartel.com/bu04_b.htm
>
> I have found that the method discussed in the article tends to fail when
> a filter is needed for data in the child table. But, if such a filter
> is not necessary, the method works fine.
I've read it, but to be honest, didn't really catch the point where I could
come in. So I left this alone and decided for my alternative way ...
> The full expression is this:
> text = {||iif(this.form.q_adet.rowset.fields["art"].value="A","Arbeit",
> "Material")}
.... which is, actually, more than sufficient for this special purpose. And
although I was quite close to success I failed at some tiny brackets. This
syntax is still a bit weird to me, if it comes to begin with ... = {|| ...
Thanks a lot for your support!
Best regards, Heinz
|
|
| | From: | Michael Nuwer [dBVIPS] | | Subject: | Re: Report with Master/Detail Rowset | | Date: | Sun, 02 Jan 2005 20:16:37 -0500 |
|
|
| Heinz Kesting wrote:
> ... which is, actually, more than sufficient for this special purpose. And
> although I was quite close to success I failed at some tiny brackets. This
> syntax is still a bit weird to me, if it comes to begin with ... = {|| ...
Hi Heinz,
Yes, it took me some time to understand the idea of a code block. At
dBCon2004, Ken Chan described a code block as a function with no name,
or an "unnamed function." I found that description useful.
--
Michael Nuwer
http://www.nuwermj.potsdam.edu/dLearn/
http://www.nuwermj.potsdam.edu/dSamples/
"I would rather be vaguely right, than precisely wrong."
-- John Maynard Keynes
|
|
|
