Hi all,
I'm having trouble creating a pretty involved report. It is to list our
different raw material types and sizes. Under each type and size, it's to
list any outstanding purchase orders we have for more of that size. And
under that, it's to list any customer orders we have open for products that
are made with that size. For example:
TYPE A
------
SIZE 1 1,000 pounds in stock
Purchase Order 1: 10,000 pounds from Vendor #1000
Purchase Order 2: 15,000 pounds from Vendor #2520
Order 100: 50 pieces for Customer #950
Order 102: 75 pieces for Customer #188
SIZE 2 1,985 pounds in stock
SIZE 3 44,500 pounds in stock
Order 204: 125 pieces for Customer #1010
TYPE B
------
SIZE 1 0 pounds in stock
Purchase Order 5: 5,500 pounds from Vendor #988
As you can see, some sizes have outstanding PO's and outstanding Orders
(Type A, Size 1), some have one or the other (Type A, Size 3 and Type B,
Size 1) and some have neither (Type A, Size 2).
I've tried creating the report with different grouping, additional detail
line bands, etc., and it's just not happening. I have the Type as Group
Heading 1, and then have tried a couple different ways to handle the rest
with different Grouping or multiple detail lines. But no matter which why I
go, it doesn't pull the correct PO's and Orders for the detail information.
Can anyone suggest a method to attack this?
Thanks very much,
John
John,
First of all, If you're doing a lot of reports
(I assume you're using 9.0)
I'd suggest you get Cathy Pountney's book ...
"The VFP Report Writer: Pushing it to the Limit and Beyond"
from Whil's site. It's worth every penny.
It'll make it a lot easier if you break the data out into
three tables and set the relations accordingly.
Type, Size
PO's
Order's
Then, it you'll need two Groups Bands (Type, Size) and
two Detail Bands (PO's, Order's)
Stick the Type and Size data in the appropriate group header.
Create a second Detail Band.
Set the 'Target Alias Expression' to the appropriate table
for the PO's and Order's.
I think that should handle it with a little fine tuning.
Best,
Francis I. Coppage, Jr.
"Two things are infinite: the universe and human stupidity; and
I'm not sure about the universe."
... Albert Einstein
-----Original Message-----
From: profoxtech-bounces@leafe.com
[mailto:profoxtech-bounces@leafe.com]On Behalf Of John J.
Mihaljevic
Sent: Thursday, June 19, 2008 12:18 PM
To: profoxtech@leafe.com
Subject: Setting up a complex report
Hi all,
I'm having trouble creating a pretty involved report. It is to
list our
different raw material types and sizes. Under each type and
size, it's to
list any outstanding purchase orders we have for more of that
size. And
under that, it's to list any customer orders we have open for
products that
are made with that size. For example:
TYPE A
------
SIZE 1 1,000 pounds in stock
Purchase Order 1: 10,000 pounds from Vendor #1000
Purchase Order 2: 15,000 pounds from Vendor #2520
Order 100: 50 pieces for Customer #950
Order 102: 75 pieces for Customer #188
SIZE 2 1,985 pounds in stock
SIZE 3 44,500 pounds in stock
Order 204: 125 pieces for Customer #1010
TYPE B
------
SIZE 1 0 pounds in stock
Purchase Order 5: 5,500 pounds from Vendor #988
As you can see, some sizes have outstanding PO's and outstanding
Orders
(Type A, Size 1), some have one or the other (Type A, Size 3 and
Type B,
Size 1) and some have neither (Type A, Size 2).
I've tried creating the report with different grouping,
additional detail
line bands, etc., and it's just not happening. I have the Type
as Group
Heading 1, and then have tried a couple different ways to handle
the rest
with different Grouping or multiple detail lines. But no matter
which why I
go, it doesn't pull the correct PO's and Orders for the detail
information.
Can anyone suggest a method to attack this?
Thanks very much,
John
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance:
http://leafe.com/mailman/listinfo/profox
OT-free version of this list:
http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/005701c8d230$68c5b630$3a51
2290$@mihaljevic@union-spring.com
** All postings, unless explicitly stated otherwise, are the
opinions of the author, and do not constitute legal or medical
advice. This statement is added to the messages for those lawyers
who are too stupid to see the obvious.
Report [OT] Abuse:
http://leafe.com/reportAbuse/005701c8d230$68c5b630$3a512290$@miha
ljevic@union-spring.com
If you're using VFP 9, use multiple detail bands to solve this.
Here are the tables you need:
Items: Type, Size, Pounds
Index: Type + Size
Po: Type, Size, Number, Pounds, Vendor
Index: Type + Size + Number
Order: Type, Size, Number, Pieces, Customer
Index: Type + Size + Number
SELECT Items
SET RELATION TO Type+Size INTO PO
SET RELATION TO Type+Size INTO Order ADDITIVE
Create the report:
Data Group #1: Type
Put the "TYPE" line here
Data Group #2: Size
Put the "SIZE" info here with the quantity in stock
Detail Band #1: Target Alias = "PO"
Put the PO Info here
Detail Band #2: Target Alias = "Order"
Put the Order info here
That should give you what you want.
Cathy Pountney
-----Original Message-----
From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On Behalf
Of John J. Mihaljevic
Sent: Thursday, June 19, 2008 1:18 PM
To: profox@leafe.com
Subject: Setting up a complex report
Hi all,
I'm having trouble creating a pretty involved report. It is to list our
different raw material types and sizes. Under each type and size, it's to
list any outstanding purchase orders we have for more of that size. And
under that, it's to list any customer orders we have open for products that
are made with that size. For example:
TYPE A
------
SIZE 1 1,000 pounds in stock
Purchase Order 1: 10,000 pounds from Vendor #1000
Purchase Order 2: 15,000 pounds from Vendor #2520
Order 100: 50 pieces for Customer #950
Order 102: 75 pieces for Customer #188
SIZE 2 1,985 pounds in stock
SIZE 3 44,500 pounds in stock
Order 204: 125 pieces for Customer #1010
TYPE B
------
SIZE 1 0 pounds in stock
Purchase Order 5: 5,500 pounds from Vendor #988
As you can see, some sizes have outstanding PO's and outstanding Orders
(Type A, Size 1), some have one or the other (Type A, Size 3 and Type B,
Size 1) and some have neither (Type A, Size 2).
I've tried creating the report with different grouping, additional detail
line bands, etc., and it's just not happening. I have the Type as Group
Heading 1, and then have tried a couple different ways to handle the rest
with different Grouping or multiple detail lines. But no matter which why I
go, it doesn't pull the correct PO's and Orders for the detail information.
Can anyone suggest a method to attack this?
Thanks very much,
John
[excessive quoting removed by server]
Thanks very much, Cathy. It's got me closer, but I'm still not there. I'm
getting (from my original example):
TYPE A
------
SIZE 1 1,000 pounds in stock (I'm only getting this detail once for each
Type)
(The headline for the Purchase Order info, but no detail)
(The headline for the Order info, and a list of every Order in the
system...not just those for the correct material Size)
Also, although I'm only getting the detail for the first Size within each
Type, it still gives me a "break" for each new Size...just doesn't show the
detail. But it's clearly breaking, because it gives me another headline for
the Purchase Order info, and lists every Order in the system again.
>From all I can see, I set things up exactly as you outlined in your email.
Is it possible that I've got the "Target Alias" entered incorrectly? Should
I filter the Purchase Order and Order tables somehow? Should all this be
set up in the form that calls the report or done through the Data
Environment section of the Report?
Thanks again!
John
> -----Original Message-----
> From: profoxtech-bounces@leafe.com [mailto:profoxtech-
> bounces@leafe.com] On Behalf Of Cathy Pountney
> Sent: Friday, June 20, 2008 8:11 AM
> To: profoxtech@leafe.com
> Subject: RE: Setting up a complex report
>
> If you're using VFP 9, use multiple detail bands to solve this.
>
> Here are the tables you need:
>
> Items: Type, Size, Pounds
> Index: Type + Size
>
> Po: Type, Size, Number, Pounds, Vendor
> Index: Type + Size + Number
>
> Order: Type, Size, Number, Pieces, Customer
> Index: Type + Size + Number
>
> SELECT Items
> SET RELATION TO Type+Size INTO PO
> SET RELATION TO Type+Size INTO Order ADDITIVE
>
> Create the report:
>
> Data Group #1: Type
> Put the "TYPE" line here
>
> Data Group #2: Size
> Put the "SIZE" info here with the quantity in stock
>
> Detail Band #1: Target Alias = "PO"
> Put the PO Info here
>
> Detail Band #2: Target Alias = "Order"
> Put the Order info here
>
>
> That should give you what you want.
>
> Cathy Pountney
>
>
>
> -----Original Message-----
> From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On
> Behalf
> Of John J. Mihaljevic
> Sent: Thursday, June 19, 2008 1:18 PM
> To: profox@leafe.com
> Subject: Setting up a complex report
>
> Hi all,
>
> I'm having trouble creating a pretty involved report. It is to list
> our
> different raw material types and sizes. Under each type and size, it's
> to
> list any outstanding purchase orders we have for more of that size.
> And
> under that, it's to list any customer orders we have open for products
> that
> are made with that size. For example:
>
> TYPE A
> ------
>
> SIZE 1 1,000 pounds in stock
>
> Purchase Order 1: 10,000 pounds from Vendor #1000
> Purchase Order 2: 15,000 pounds from Vendor #2520
>
> Order 100: 50 pieces for Customer #950
> Order 102: 75 pieces for Customer #188
>
> SIZE 2 1,985 pounds in stock
>
>
> SIZE 3 44,500 pounds in stock
>
> Order 204: 125 pieces for Customer #1010
>
> TYPE B
> ------
>
> SIZE 1 0 pounds in stock
>
> Purchase Order 5: 5,500 pounds from Vendor #988
>
> As you can see, some sizes have outstanding PO's and outstanding Orders
> (Type A, Size 1), some have one or the other (Type A, Size 3 and Type
> B,
> Size 1) and some have neither (Type A, Size 2).
>
> I've tried creating the report with different grouping, additional
> detail
> line bands, etc., and it's just not happening. I have the Type as
> Group
> Heading 1, and then have tried a couple different ways to handle the
> rest
> with different Grouping or multiple detail lines. But no matter which
> why I
> go, it doesn't pull the correct PO's and Orders for the detail
> information.
>
> Can anyone suggest a method to attack this?
>
> Thanks very much,
>
> John
>
>
>
>
>
[excessive quoting removed by server]
It sounds to me like the target alias isn't right and the parent and
relations aren't right. Make sure you have the PARENT table (items) selected
when you run report and the parent has relations to the children (POs and
Orders). It's also very important that the target alias of the first detail
band is the name of the PO cursor (using quotes) and the target alias of the
second detail band is the Orders cursor (using quotes).
If you still can't figure it out, post the code used to set this up and a
screen shot of the report definition. If I see it, I might be able to spot
the problem.
Cathy
-----Original Message-----
From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On Behalf
Of John J. Mihaljevic
Sent: Friday, June 20, 2008 10:17 AM
To: profox@leafe.com
Subject: RE: Setting up a complex report
Thanks very much, Cathy. It's got me closer, but I'm still not there. I'm
getting (from my original example):
TYPE A
------
SIZE 1 1,000 pounds in stock (I'm only getting this detail once for each
Type)
(The headline for the Purchase Order info, but no detail)
(The headline for the Order info, and a list of every Order in the
system...not just those for the correct material Size)
Also, although I'm only getting the detail for the first Size within each
Type, it still gives me a "break" for each new Size...just doesn't show the
detail. But it's clearly breaking, because it gives me another headline for
the Purchase Order info, and lists every Order in the system again.
>From all I can see, I set things up exactly as you outlined in your email.
Is it possible that I've got the "Target Alias" entered incorrectly? Should
I filter the Purchase Order and Order tables somehow? Should all this be
set up in the form that calls the report or done through the Data
Environment section of the Report?
Thanks again!
John
> -----Original Message-----
> From: profoxtech-bounces@leafe.com [mailto:profoxtech-
> bounces@leafe.com] On Behalf Of Cathy Pountney
> Sent: Friday, June 20, 2008 8:11 AM
> To: profoxtech@leafe.com
> Subject: RE: Setting up a complex report
>
> If you're using VFP 9, use multiple detail bands to solve this.
>
> Here are the tables you need:
>
> Items: Type, Size, Pounds
> Index: Type + Size
>
> Po: Type, Size, Number, Pounds, Vendor
> Index: Type + Size + Number
>
> Order: Type, Size, Number, Pieces, Customer
> Index: Type + Size + Number
>
> SELECT Items
> SET RELATION TO Type+Size INTO PO
> SET RELATION TO Type+Size INTO Order ADDITIVE
>
> Create the report:
>
> Data Group #1: Type
> Put the "TYPE" line here
>
> Data Group #2: Size
> Put the "SIZE" info here with the quantity in stock
>
> Detail Band #1: Target Alias = "PO"
> Put the PO Info here
>
> Detail Band #2: Target Alias = "Order"
> Put the Order info here
>
>
> That should give you what you want.
>
> Cathy Pountney
>
>
>
> -----Original Message-----
> From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On
> Behalf
> Of John J. Mihaljevic
> Sent: Thursday, June 19, 2008 1:18 PM
> To: profox@leafe.com
> Subject: Setting up a complex report
>
> Hi all,
>
> I'm having trouble creating a pretty involved report. It is to list
> our
> different raw material types and sizes. Under each type and size, it's
> to
> list any outstanding purchase orders we have for more of that size.
> And
> under that, it's to list any customer orders we have open for products
> that
> are made with that size. For example:
>
> TYPE A
> ------
>
> SIZE 1 1,000 pounds in stock
>
> Purchase Order 1: 10,000 pounds from Vendor #1000
> Purchase Order 2: 15,000 pounds from Vendor #2520
>
> Order 100: 50 pieces for Customer #950
> Order 102: 75 pieces for Customer #188
>
> SIZE 2 1,985 pounds in stock
>
>
> SIZE 3 44,500 pounds in stock
>
> Order 204: 125 pieces for Customer #1010
>
> TYPE B
> ------
>
> SIZE 1 0 pounds in stock
>
> Purchase Order 5: 5,500 pounds from Vendor #988
>
> As you can see, some sizes have outstanding PO's and outstanding Orders
> (Type A, Size 1), some have one or the other (Type A, Size 3 and Type
> B,
> Size 1) and some have neither (Type A, Size 2).
>
> I've tried creating the report with different grouping, additional
> detail
> line bands, etc., and it's just not happening. I have the Type as
> Group
> Heading 1, and then have tried a couple different ways to handle the
> rest
> with different Grouping or multiple detail lines. But no matter which
> why I
> go, it doesn't pull the correct PO's and Orders for the detail
> information.
>
> Can anyone suggest a method to attack this?
>
> Thanks very much,
>
> John
>
>
>
>
>
[excessive quoting removed by server]
Hi Cathy,
Here is a screen shot of the report definition:
> -----Original Message-----
> From: profoxtech-bounces@leafe.com [mailto:profoxtech-
> bounces@leafe.com] On Behalf Of Cathy Pountney
>
> It sounds to me like the target alias isn't right and the parent and
> relations aren't right. Make sure you have the PARENT table (items)
> selected when you run report and the parent has relations to the
> children (POs and Orders). It's also very important that the target
> alias of the first detail band is the name of the PO cursor (using
> quotes) and the target alias of the second detail band is the Orders
> cursor (using quotes).
>
> If you still can't figure it out, post the code used to set this up and
> a screen shot of the report definition. If I see it, I might be able to
> spot the problem.
Here are my tables and relations (all of which are in the Data Environment
section of the report definition)
Tables:
RAWMATERIAL order GRADE_DIAM str(grade,2)+str(diameter,6,4)
RAWCODES order RAWCODE (This give me DESCRIPTION in the Group Header 1 band)
PODETAIL order GD_NUMBER str(grade,2)+str(diameter,6,4)+str(po_number,5)
POHEADER orde PO_NUMBER po_number
ORDERS order GD_NUMBER str(grade,2)+str(diameter,6,4)+str(ordernumber,5)
Relations:
RAWMATERIAL to ORDERS gr_diameter
RAWMATERIAL to PODETAIL gr_diameter
RAWMATERIAL to RAWCODES grade
PODETAIL to POHEADER po_number
Here is an example of the output I'm getting:
As you can see, I'm getting no PO Detail but EVERY Order in the system for
every grade_diameter. Each time grade_diameter changes, I get a new list of
nothing for Purchase Orders, and another list of every Order in the system..
If there's anything else you need to see in order to help me figure this
out, let me know and I'll reply right away.
Thanks very much for your time and your help!
John
--- StripMime Report -- processed MIME parts ---
multipart/related
multipart/alternative
text/plain (text body -- kept)
text/html
image/png
image/png
---
Sorry, all. Didn't know embedded images would get removed from the email.
Cathy...I also sent that to you directly. If you didn't get them, let me
know and I'll send them as attachments.
Thanks,
John