You need this to be an outer join because otherwise you are only going
to get orders with a misc charges. Misc charges can also be from the
header and not always at the line level. The trick is going to be
getting the total misc charge at the line level when there are multiple
charges. Try a calculated misc charge to determine what line it is for
and then an aggregate on the calculated field by line. Something like
CALCMIS IIF(OrderMsc.OrderLine = OrderDtl.OrderLine, MiscAmt,0) this
will give you the misc charge amount that you can then do an aggregate
calculation on at the line level to add to the extprice of the line to
give you your total line amount.
Jim
________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Moore, Jim (Anniston)
Sent: Monday, August 18, 2008 10:38 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Vantage 6.1 - OrderMsc
I have:
Orderhed to Customer
OrderHed to OrderDtl
OrderDtl to OrderMsc
For the last one orderdtl to ordermsc I have;
Company to company
Ordernum to ordernum
Orderline to orderline
Inner join type.
Thanks for the reply.
Jim
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Jim Frice
Sent: Friday, August 15, 2008 3:51 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Vantage 6.1 - OrderMsc
How is your join set up for the OrderMsc table?
________________________________
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Moore, Jim (Anniston)
Sent: Friday, August 15, 2008 1:56 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Vantage 6.1 - OrderMsc
I am trying to complete a Sales Order report that uses the fields
OrderHed, OrderDtl and Customer
All is fine to this point and as long as there are no misc charges for
that Sales Order line it calculates correctly.
After I link OrderMsc the problems begin. I have tried linking to both
OrderHed and OrderDtl.
When I try to total up a Sales Order Line with a misc charge, it either
drops the line entirely or pulls up the other charges.
If that Sales order only has misc charges on it, it does not show the
line at all.
I am trying to do this in Report Builder but am stuck.
I noticed back on 7/16/2003 there was a post with a similar question,
but no answer.
Any help or advice is appreciated.
Jim Moore
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
to get orders with a misc charges. Misc charges can also be from the
header and not always at the line level. The trick is going to be
getting the total misc charge at the line level when there are multiple
charges. Try a calculated misc charge to determine what line it is for
and then an aggregate on the calculated field by line. Something like
CALCMIS IIF(OrderMsc.OrderLine = OrderDtl.OrderLine, MiscAmt,0) this
will give you the misc charge amount that you can then do an aggregate
calculation on at the line level to add to the extprice of the line to
give you your total line amount.
Jim
________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Moore, Jim (Anniston)
Sent: Monday, August 18, 2008 10:38 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Vantage 6.1 - OrderMsc
I have:
Orderhed to Customer
OrderHed to OrderDtl
OrderDtl to OrderMsc
For the last one orderdtl to ordermsc I have;
Company to company
Ordernum to ordernum
Orderline to orderline
Inner join type.
Thanks for the reply.
Jim
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Jim Frice
Sent: Friday, August 15, 2008 3:51 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Vantage 6.1 - OrderMsc
How is your join set up for the OrderMsc table?
________________________________
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Moore, Jim (Anniston)
Sent: Friday, August 15, 2008 1:56 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Vantage 6.1 - OrderMsc
I am trying to complete a Sales Order report that uses the fields
OrderHed, OrderDtl and Customer
All is fine to this point and as long as there are no misc charges for
that Sales Order line it calculates correctly.
After I link OrderMsc the problems begin. I have tried linking to both
OrderHed and OrderDtl.
When I try to total up a Sales Order Line with a misc charge, it either
drops the line entirely or pulls up the other charges.
If that Sales order only has misc charges on it, it does not show the
line at all.
I am trying to do this in Report Builder but am stuck.
I noticed back on 7/16/2003 there was a post with a similar question,
but no answer.
Any help or advice is appreciated.
Jim Moore
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]