{Disarmed} Commission Report in Crystal

Yahoo Archive
1
Would you be willing to share this. I am doing the same thing with
the customer and sales rep rate and it would be nice not to build
from scratch.

--- In vantage@yahoogroups.com, "Jeff Stockard" <jeff.stockard@...>
wrote:
>
> We created our own Commission Report. We use a BAQ and our Table
relations
> go InvcDtl to Customer, linked by Custnum. The InvcHead to
InvcDtl linked
> by InvoiceNum. Use the InvcDtl.OrderNum, InvcDtl.OrderLine, and
> InvcDtl.OrderRelNum to get the information from each Sales Order.
Each of
> our Customers has a potential to have a different Commission rate,
so I
> added the Number20 field to the Customer Setup screen. We put in
the
> Commission rate there. Then I used the BAQ report designer to
design a
> Crystal report, that multiples the line by the Commission rate.
Each
> section is grouped by SalesRepCode and totaled at the bottom of
each
> section.
>
> Hope this helps
>
> Jeff
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf Of
> Thomas Rose
> Sent: Tuesday, November 25, 2008 3:00 PM
> To: vantage@yahoogroups.com
> Subject: RE: {Disarmed} [Vantage] Commission Report in Crystal
>
>
>
> Part of the reason there is no BAQ already is because of the way
Vantage
> stores the list of reps. Rather than have a separate table with
the sales
> rep and commission data (the proper way to do it in a relational
DB), Epicor
> chose to put all that info in the order or invoice table. There
are fields
> for each of the five available commission percents, and fields for
each of
> the five available commission splits. You might expect there to be
five
> fields for each of the available reps. (After all, as long as you
are not
> normalizing your DB, why not take it this one step further?) If
you expected
> that, you would be wrong. They put each sales rep code in one
field, where
> each sales rep code is separated by a tilde (~). You can, and will
need to
> parse this field in your BAQ. Unfortunately, you cannot
simultaneously link
> the sales rep code to the sales rep table, both because the data
is not
> normalized, and because you can't link the parsed field to the
sales rep
> table.
>
> You are lucky, though, if you do not have our commission
structure. Epicor
> lets you select between paying when the invoice is paid or when
the invoice
> is created. We pay commission after the invoice is paid and the
product is
> shipped. Because we typically bill in advance, that is not as
> straightforward a proposition as you might expect.
>
> Have fun with this, and do try to keep the cursing to a minimum as
you
> extract the data :).
>
> Thom Rose
> Controller
> Electric Mirror LLC
> HOTEL LUXURY
>
> "The World Leader in Back-lit Mirrors & Mirror TV Technology"
>
> T 425 776-4946
> F 425 491-8200
> A 11831 Beverly Park Rd, Bldg D, Everett, WA 98204 USA
> www.electricmirror.com<http://www.electricmirror.com>
>
> Note: The information contained in the e-mail, including any
attachments, is
> legally privileged and confidential. If you are not the intended
recipient
> you are hereby notified that any reading, use or dissemination of
this
> message is strictly prohibited. If you have received this message
in error,
> please immediately notify us by telephone at 425-776-4946 and
delete this
> message from your system. Even though this e-mail and any
attachments are
> believed to be free of any virus or other defect that might affect
any
> computer system into which it is received and opened, it is the
> responsibility of the recipient to ensure that it is virus free,
and no
> responsibility is accepted by Electric Mirror LLC for any loss or
damage
> arising in any way from its use
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%
40yahoogroups.com> ] On
> Behalf Of Dave Eaton
> Sent: Tuesday, November 25, 2008 11:46 AM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: {Disarmed} [Vantage] Commission Report in Crystal
>
> I need to create a Commission report similar to the standard
Commission
> report that comes from Progress but it needs to be a Crystal
report and
> include the SO number. I am trying to do this using the BAQ Report
> Designer but I don't see any BAQ's with this information.
>
> Does anyone know whether there is an existing BAQ with commission
> information? If not is it possible to create a BAQ that contains
> commission information?
>
> I'm on Vantage V 803.405A
>
> I'm a newbie at this so thanks in advance for your help.
>
> Dave
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
2
I need to create a Commission report similar to the standard Commission
report that comes from Progress but it needs to be a Crystal report and
include the SO number. I am trying to do this using the BAQ Report
Designer but I don't see any BAQ's with this information.

Does anyone know whether there is an existing BAQ with commission
information? If not is it possible to create a BAQ that contains
commission information?

I'm on Vantage V 803.405A

I'm a newbie at this so thanks in advance for your help.

Dave
3
Commission report is still hard coded report.



Steve



-----Original Message-----
From: Dave Eaton [mailto:deaton@...]
Sent: November 25, 2008 1:46 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Commission Report in Crystal



I need to create a Commission report similar to the standard Commission
report that comes from Progress but it needs to be a Crystal report and
include the SO number. I am trying to do this using the BAQ Report
Designer but I don't see any BAQ's with this information.

Does anyone know whether there is an existing BAQ with commission
information? If not is it possible to create a BAQ that contains
commission information?

I'm on Vantage V 803.405A

I'm a newbie at this so thanks in advance for your help.

Dave





[Non-text portions of this message have been removed]
4
Part of the reason there is no BAQ already is because of the way Vantage stores the list of reps. Rather than have a separate table with the sales rep and commission data (the proper way to do it in a relational DB), Epicor chose to put all that info in the order or invoice table. There are fields for each of the five available commission percents, and fields for each of the five available commission splits. You might expect there to be five fields for each of the available reps. (After all, as long as you are not normalizing your DB, why not take it this one step further?) If you expected that, you would be wrong. They put each sales rep code in one field, where each sales rep code is separated by a tilde (~). You can, and will need to parse this field in your BAQ. Unfortunately, you cannot simultaneously link the sales rep code to the sales rep table, both because the data is not normalized, and because you can't link the parsed field to the sales rep table.

You are lucky, though, if you do not have our commission structure. Epicor lets you select between paying when the invoice is paid or when the invoice is created. We pay commission after the invoice is paid and the product is shipped. Because we typically bill in advance, that is not as straightforward a proposition as you might expect.

Have fun with this, and do try to keep the cursing to a minimum as you extract the data :).

Thom Rose
Controller
Electric Mirror LLC
HOTEL LUXURY

"The World Leader in Back-lit Mirrors & Mirror TV Technology"

T 425 776-4946
F 425 491-8200
A 11831 Beverly Park Rd, Bldg D, Everett, WA 98204 USA
www.electricmirror.com<http://www.electricmirror.com>

Note: The information contained in the e-mail, including any attachments, is legally privileged and confidential. If you are not the intended recipient you are hereby notified that any reading, use or dissemination of this message is strictly prohibited. If you have received this message in error, please immediately notify us by telephone at 425-776-4946 and delete this message from your system. Even though this e-mail and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free, and no responsibility is accepted by Electric Mirror LLC for any loss or damage arising in any way from its use


From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Dave Eaton
Sent: Tuesday, November 25, 2008 11:46 AM
To: vantage@yahoogroups.com
Subject: {Disarmed} [Vantage] Commission Report in Crystal


I need to create a Commission report similar to the standard Commission
report that comes from Progress but it needs to be a Crystal report and
include the SO number. I am trying to do this using the BAQ Report
Designer but I don't see any BAQ's with this information.

Does anyone know whether there is an existing BAQ with commission
information? If not is it possible to create a BAQ that contains
commission information?

I'm on Vantage V 803.405A

I'm a newbie at this so thanks in advance for your help.

Dave



[Non-text portions of this message have been removed]
5
We created our own Commission Report. We use a BAQ and our Table relations
go InvcDtl to Customer, linked by Custnum. The InvcHead to InvcDtl linked
by InvoiceNum. Use the InvcDtl.OrderNum, InvcDtl.OrderLine, and
InvcDtl.OrderRelNum to get the information from each Sales Order. Each of
our Customers has a potential to have a different Commission rate, so I
added the Number20 field to the Customer Setup screen. We put in the
Commission rate there. Then I used the BAQ report designer to design a
Crystal report, that multiples the line by the Commission rate. Each
section is grouped by SalesRepCode and totaled at the bottom of each
section.

Hope this helps

Jeff



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Thomas Rose
Sent: Tuesday, November 25, 2008 3:00 PM
To: vantage@yahoogroups.com
Subject: RE: {Disarmed} [Vantage] Commission Report in Crystal



Part of the reason there is no BAQ already is because of the way Vantage
stores the list of reps. Rather than have a separate table with the sales
rep and commission data (the proper way to do it in a relational DB), Epicor
chose to put all that info in the order or invoice table. There are fields
for each of the five available commission percents, and fields for each of
the five available commission splits. You might expect there to be five
fields for each of the available reps. (After all, as long as you are not
normalizing your DB, why not take it this one step further?) If you expected
that, you would be wrong. They put each sales rep code in one field, where
each sales rep code is separated by a tilde (~). You can, and will need to
parse this field in your BAQ. Unfortunately, you cannot simultaneously link
the sales rep code to the sales rep table, both because the data is not
normalized, and because you can't link the parsed field to the sales rep
table.

You are lucky, though, if you do not have our commission structure. Epicor
lets you select between paying when the invoice is paid or when the invoice
is created. We pay commission after the invoice is paid and the product is
shipped. Because we typically bill in advance, that is not as
straightforward a proposition as you might expect.

Have fun with this, and do try to keep the cursing to a minimum as you
extract the data :).

Thom Rose
Controller
Electric Mirror LLC
HOTEL LUXURY

"The World Leader in Back-lit Mirrors & Mirror TV Technology"

T 425 776-4946
F 425 491-8200
A 11831 Beverly Park Rd, Bldg D, Everett, WA 98204 USA
www.electricmirror.com<http://www.electricmirror.com>

Note: The information contained in the e-mail, including any attachments, is
legally privileged and confidential. If you are not the intended recipient
you are hereby notified that any reading, use or dissemination of this
message is strictly prohibited. If you have received this message in error,
please immediately notify us by telephone at 425-776-4946 and delete this
message from your system. Even though this e-mail and any attachments are
believed to be free of any virus or other defect that might affect any
computer system into which it is received and opened, it is the
responsibility of the recipient to ensure that it is virus free, and no
responsibility is accepted by Electric Mirror LLC for any loss or damage
arising in any way from its use

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of Dave Eaton
Sent: Tuesday, November 25, 2008 11:46 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: {Disarmed} [Vantage] Commission Report in Crystal

I need to create a Commission report similar to the standard Commission
report that comes from Progress but it needs to be a Crystal report and
include the SO number. I am trying to do this using the BAQ Report
Designer but I don't see any BAQ's with this information.

Does anyone know whether there is an existing BAQ with commission
information? If not is it possible to create a BAQ that contains
commission information?

I'm on Vantage V 803.405A

I'm a newbie at this so thanks in advance for your help.

Dave

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]