Sales and Commission Splitting

Yahoo Archive
1
Yes, I figured out how to split the rep list a while back and have a RB
report that lists the proper amounts "within" an invoice (example):
Invoice 12345 total sales 1,200.00
Rep 01 % 50 Sales 600.00 Commission 30.00
Rep 02 % 50 Sales 600.00 Commission 30.00

but flipping it over to be:
Rep 01
Invoice 12345 Sales 600.00 Commission 30.00
Rep 02
Invoice 12345 Sales 600.00 Commission 30.00

is the problem. I think I will most likely create a text file in a .dbf
like format from the RB report in the first example and try to read it with
Access or Excel and sort/subtotal from that. If I can get it in a "real"
.dbf format I might try Crystal on it.

Thanks for the ideas,
-Todd C.

-----Original Message-----
From: Winter, Patrick [mailto:pjw@...]
Sent: Wednesday, November 21, 2001 11:51 AM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Sales and Commission Splitting


Just for the record, you can in report builder yank apart the rep string.

Create a variables:
Tilde = CHR(126)
First_Rep = ENTRY(1,SalesRepList,Tilde)
Second_Rep = ENTRY(2,SalesRepList,Tilde)

Now if you can use this to create another variable such as if the rep is in
FIRST_REP or SECOND_REP do something or sort on this or use it with left
outer joins is the next question.

You may need separate reports all single commissions, all first rep splits,
all second rep splits.

Hope this helps. Or at least a small piece to the puzzle.

Patrick Winter
sSc Specialty Screw Corporation
Vantage 5.00.317, Progress 9.1a


-----Original Message-----
From: Todd Anderson [mailto:tanderson@...]
Sent: Wednesday, November 21, 2001 10:21 AM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Sales and Commission Splitting


Todd,

Another way to go at this would be to write a few Progress reports that
export to a file. ( put "+" signs between fields - or some other odd duck
character )

Then import the data into MS-Access as "+" delimited fields.

From there you can pretty much do whatever you want.

Todd Anderson

-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Wednesday, November 21, 2001 9:33 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Sales and Commission Splitting


I need some advice for the best way to approach a really thorny issue for
us. We need to determine "sales by rep" and "sales by the state where the
rep is located" in situations where there are multiple reps. Usually two
but sometimes three. Because of the way rep splits are handled in Vantage,
via an array within the invoice detail, it is not possible to sort the split
sales $ by rep or state. All of the "info" is contained within the one
detail record. I have tried linking every which way and using multiple rep
tables aliases, etc... and it won't work. If there was a mechanism to pass
through all of the data and store it in a temporary file, sort it there and
then report on it it would be pretty easy. In my COBOL days this was very a
piece of cake.

I am thinking of trying a Crystal subreport but have never used subreports.
Before I invest in the time to learn how I wanted to see if this appraoch
would even work for what I am trying to do. The company President has given
me a deadline to get split sales reports working by mid-December so I would
like to not take any more false routes.

The situation is that we might have a customer in Ohio with a plant in
Missouri. Our sales rep for Ohio and the rep for Missouri split the
commissions (and thus credit for the sales $) 50/50. The invoice detail for
a sale has the split info burried within in it so if I try to sort on rep or
state the same invoice detail record can't be in two places in the same
report. What I need is for the report to show 50% of the sales $ under one
state or rep and the other 50% under the other state/rep. I can easily
write a RB report to split the sales and commissions by each invoice and
even show the state and rep code for each split. But taking this and
re-sorting it is the problem.

So Yea or Nea... is Crystal subreports the way to handle hit? Or have RB
create a CSV files and use Crystal or Excel to re-sort it? Are Crystal
subreports hard to learn how to do? Any subreport "gotchas" waiting to
spring up and bite a newbie?

Thanks,
-Todd Caughey
Harvey Vogel Mfg. Co.






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



Yahoo! Groups Sponsor

<
http://rd.yahoo.com/M=212180.1701098.3252983.1269402/D=egroupweb/S=17050071
<http://rd.yahoo.com/M=212180.1701098.3252983.1269402/D=egroupweb/S=17050071
>
83:HM/A=812075/R=0/*
http://www.verisign.com/cgi-bin/go.cgi?a=b17834980005700
<http://www.verisign.com/cgi-bin/go.cgi?a=b17834980005700>
0>

<
http://us.adserver.yahoo.com/l?M=212180.1701098.3252983.1269402/D=egroupmai
<http://us.adserver.yahoo.com/l?M=212180.1701098.3252983.1269402/D=egroupmai
>
l/S=1705007183:HM/A=812075/rand=923152006>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
< http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.> >
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
< http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages> >
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>
< http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links> >

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
< http://docs.yahoo.com/info/terms/ <http://docs.yahoo.com/info/terms/> > .




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



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
<http://docs.yahoo.com/info/terms/>


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]
2
I need some advice for the best way to approach a really thorny issue for
us. We need to determine "sales by rep" and "sales by the state where the
rep is located" in situations where there are multiple reps. Usually two
but sometimes three. Because of the way rep splits are handled in Vantage,
via an array within the invoice detail, it is not possible to sort the split
sales $ by rep or state. All of the "info" is contained within the one
detail record. I have tried linking every which way and using multiple rep
tables aliases, etc... and it won't work. If there was a mechanism to pass
through all of the data and store it in a temporary file, sort it there and
then report on it it would be pretty easy. In my COBOL days this was very a
piece of cake.

I am thinking of trying a Crystal subreport but have never used subreports.
Before I invest in the time to learn how I wanted to see if this appraoch
would even work for what I am trying to do. The company President has given
me a deadline to get split sales reports working by mid-December so I would
like to not take any more false routes.

The situation is that we might have a customer in Ohio with a plant in
Missouri. Our sales rep for Ohio and the rep for Missouri split the
commissions (and thus credit for the sales $) 50/50. The invoice detail for
a sale has the split info burried within in it so if I try to sort on rep or
state the same invoice detail record can't be in two places in the same
report. What I need is for the report to show 50% of the sales $ under one
state or rep and the other 50% under the other state/rep. I can easily
write a RB report to split the sales and commissions by each invoice and
even show the state and rep code for each split. But taking this and
re-sorting it is the problem.

So Yea or Nea... is Crystal subreports the way to handle hit? Or have RB
create a CSV files and use Crystal or Excel to re-sort it? Are Crystal
subreports hard to learn how to do? Any subreport "gotchas" waiting to
spring up and bite a newbie?

Thanks,
-Todd Caughey
Harvey Vogel Mfg. Co.






[Non-text portions of this message have been removed]
3
One thing we have used to write these types of reports was to create a
Crystal SQL Query using ODBC which "splits" the sales rep array apart and
creates a record for each rep. Then it's straightforwrd to write the report
in Crystal against that query and group and sort however you want.

Rick Lane
Intelligent Systems Integration, Inc.
600 Weber Drive
Wadsworth, OH 44281
PH: 330-335-5291
FX: 330-335-7275

"Helping Business Make Intelligent Use of Technology"

-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Wednesday, November 21, 2001 10:33 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Sales and Commission Splitting


I need some advice for the best way to approach a really thorny issue for
us. We need to determine "sales by rep" and "sales by the state where the
rep is located" in situations where there are multiple reps. Usually two
but sometimes three. Because of the way rep splits are handled in Vantage,
via an array within the invoice detail, it is not possible to sort the split
sales $ by rep or state. All of the "info" is contained within the one
detail record. I have tried linking every which way and using multiple rep
tables aliases, etc... and it won't work. If there was a mechanism to pass
through all of the data and store it in a temporary file, sort it there and
then report on it it would be pretty easy. In my COBOL days this was very a
piece of cake.

I am thinking of trying a Crystal subreport but have never used subreports.
Before I invest in the time to learn how I wanted to see if this appraoch
would even work for what I am trying to do. The company President has given
me a deadline to get split sales reports working by mid-December so I would
like to not take any more false routes.

The situation is that we might have a customer in Ohio with a plant in
Missouri. Our sales rep for Ohio and the rep for Missouri split the
commissions (and thus credit for the sales $) 50/50. The invoice detail for
a sale has the split info burried within in it so if I try to sort on rep or
state the same invoice detail record can't be in two places in the same
report. What I need is for the report to show 50% of the sales $ under one
state or rep and the other 50% under the other state/rep. I can easily
write a RB report to split the sales and commissions by each invoice and
even show the state and rep code for each split. But taking this and
re-sorting it is the problem.

So Yea or Nea... is Crystal subreports the way to handle hit? Or have RB
create a CSV files and use Crystal or Excel to re-sort it? Are Crystal
subreports hard to learn how to do? Any subreport "gotchas" waiting to
spring up and bite a newbie?

Thanks,
-Todd Caughey
Harvey Vogel Mfg. Co.






[Non-text portions of this message have been removed]
4
Todd,
One way I have had to handle complicated reports (although never with Sales commissions), is to export tables from Vantage to DBF files and then run Crystal on them. For one, they run tons quicker. For two, I can often make a 'multi-dimensional' situation into a one-dimensional one that Crystal works with much better.

There is probably a way to do your Sales/Commissions report in 4GL, but that's out of my league.

If you have more questions on this, let me know.

Troy Funte

----- Original Message -----
From: Todd Caughey
To: 'vantage@yahoogroups.com'
Sent: Wednesday, November 21, 2001 10:33 AM
Subject: [Vantage] Sales and Commission Splitting


I need some advice for the best way to approach a really thorny issue for
us. We need to determine "sales by rep" and "sales by the state where the
rep is located" in situations where there are multiple reps. Usually two
but sometimes three. Because of the way rep splits are handled in Vantage,
via an array within the invoice detail, it is not possible to sort the split
sales $ by rep or state. All of the "info" is contained within the one
detail record. I have tried linking every which way and using multiple rep
tables aliases, etc... and it won't work. If there was a mechanism to pass
through all of the data and store it in a temporary file, sort it there and
then report on it it would be pretty easy. In my COBOL days this was very a
piece of cake.

I am thinking of trying a Crystal subreport but have never used subreports.
Before I invest in the time to learn how I wanted to see if this appraoch
would even work for what I am trying to do. The company President has given
me a deadline to get split sales reports working by mid-December so I would
like to not take any more false routes.

The situation is that we might have a customer in Ohio with a plant in
Missouri. Our sales rep for Ohio and the rep for Missouri split the
commissions (and thus credit for the sales $) 50/50. The invoice detail for
a sale has the split info burried within in it so if I try to sort on rep or
state the same invoice detail record can't be in two places in the same
report. What I need is for the report to show 50% of the sales $ under one
state or rep and the other 50% under the other state/rep. I can easily
write a RB report to split the sales and commissions by each invoice and
even show the state and rep code for each split. But taking this and
re-sorting it is the problem.

So Yea or Nea... is Crystal subreports the way to handle hit? Or have RB
create a CSV files and use Crystal or Excel to re-sort it? Are Crystal
subreports hard to learn how to do? Any subreport "gotchas" waiting to
spring up and bite a newbie?

Thanks,
-Todd Caughey
Harvey Vogel Mfg. Co.






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


Yahoo! Groups Sponsor



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



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

Another way to go at this would be to write a few Progress reports that
export to a file. ( put "+" signs between fields - or some other odd duck
character )

Then import the data into MS-Access as "+" delimited fields.

From there you can pretty much do whatever you want.

Todd Anderson

-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Wednesday, November 21, 2001 9:33 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Sales and Commission Splitting


I need some advice for the best way to approach a really thorny issue for
us. We need to determine "sales by rep" and "sales by the state where the
rep is located" in situations where there are multiple reps. Usually two
but sometimes three. Because of the way rep splits are handled in Vantage,
via an array within the invoice detail, it is not possible to sort the split
sales $ by rep or state. All of the "info" is contained within the one
detail record. I have tried linking every which way and using multiple rep
tables aliases, etc... and it won't work. If there was a mechanism to pass
through all of the data and store it in a temporary file, sort it there and
then report on it it would be pretty easy. In my COBOL days this was very a
piece of cake.

I am thinking of trying a Crystal subreport but have never used subreports.
Before I invest in the time to learn how I wanted to see if this appraoch
would even work for what I am trying to do. The company President has given
me a deadline to get split sales reports working by mid-December so I would
like to not take any more false routes.

The situation is that we might have a customer in Ohio with a plant in
Missouri. Our sales rep for Ohio and the rep for Missouri split the
commissions (and thus credit for the sales $) 50/50. The invoice detail for
a sale has the split info burried within in it so if I try to sort on rep or
state the same invoice detail record can't be in two places in the same
report. What I need is for the report to show 50% of the sales $ under one
state or rep and the other 50% under the other state/rep. I can easily
write a RB report to split the sales and commissions by each invoice and
even show the state and rep code for each split. But taking this and
re-sorting it is the problem.

So Yea or Nea... is Crystal subreports the way to handle hit? Or have RB
create a CSV files and use Crystal or Excel to re-sort it? Are Crystal
subreports hard to learn how to do? Any subreport "gotchas" waiting to
spring up and bite a newbie?

Thanks,
-Todd Caughey
Harvey Vogel Mfg. Co.






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



Yahoo! Groups Sponsor

<http://rd.yahoo.com/M=212180.1701098.3252983.1269402/D=egroupweb/S=17050071
83:HM/A=812075/R=0/*http://www.verisign.com/cgi-bin/go.cgi?a=b17834980005700
0>

<http://us.adserver.yahoo.com/l?M=212180.1701098.3252983.1269402/D=egroupmai
l/S=1705007183:HM/A=812075/rand=923152006>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]
6
Just for the record, you can in report builder yank apart the rep string.

Create a variables:
Tilde = CHR(126)
First_Rep = ENTRY(1,SalesRepList,Tilde)
Second_Rep = ENTRY(2,SalesRepList,Tilde)

Now if you can use this to create another variable such as if the rep is in
FIRST_REP or SECOND_REP do something or sort on this or use it with left
outer joins is the next question.

You may need separate reports all single commissions, all first rep splits,
all second rep splits.

Hope this helps. Or at least a small piece to the puzzle.

Patrick Winter
sSc Specialty Screw Corporation
Vantage 5.00.317, Progress 9.1a


-----Original Message-----
From: Todd Anderson [mailto:tanderson@...]
Sent: Wednesday, November 21, 2001 10:21 AM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Sales and Commission Splitting


Todd,

Another way to go at this would be to write a few Progress reports that
export to a file. ( put "+" signs between fields - or some other odd duck
character )

Then import the data into MS-Access as "+" delimited fields.

From there you can pretty much do whatever you want.

Todd Anderson

-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Wednesday, November 21, 2001 9:33 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Sales and Commission Splitting


I need some advice for the best way to approach a really thorny issue for
us. We need to determine "sales by rep" and "sales by the state where the
rep is located" in situations where there are multiple reps. Usually two
but sometimes three. Because of the way rep splits are handled in Vantage,
via an array within the invoice detail, it is not possible to sort the split
sales $ by rep or state. All of the "info" is contained within the one
detail record. I have tried linking every which way and using multiple rep
tables aliases, etc... and it won't work. If there was a mechanism to pass
through all of the data and store it in a temporary file, sort it there and
then report on it it would be pretty easy. In my COBOL days this was very a
piece of cake.

I am thinking of trying a Crystal subreport but have never used subreports.
Before I invest in the time to learn how I wanted to see if this appraoch
would even work for what I am trying to do. The company President has given
me a deadline to get split sales reports working by mid-December so I would
like to not take any more false routes.

The situation is that we might have a customer in Ohio with a plant in
Missouri. Our sales rep for Ohio and the rep for Missouri split the
commissions (and thus credit for the sales $) 50/50. The invoice detail for
a sale has the split info burried within in it so if I try to sort on rep or
state the same invoice detail record can't be in two places in the same
report. What I need is for the report to show 50% of the sales $ under one
state or rep and the other 50% under the other state/rep. I can easily
write a RB report to split the sales and commissions by each invoice and
even show the state and rep code for each split. But taking this and
re-sorting it is the problem.

So Yea or Nea... is Crystal subreports the way to handle hit? Or have RB
create a CSV files and use Crystal or Excel to re-sort it? Are Crystal
subreports hard to learn how to do? Any subreport "gotchas" waiting to
spring up and bite a newbie?

Thanks,
-Todd Caughey
Harvey Vogel Mfg. Co.






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



Yahoo! Groups Sponsor

<http://rd.yahoo.com/M=212180.1701098.3252983.1269402/D=egroupweb/S=17050071
83:HM/A=812075/R=0/*http://www.verisign.com/cgi-bin/go.cgi?a=b17834980005700
0>

<http://us.adserver.yahoo.com/l?M=212180.1701098.3252983.1269402/D=egroupmai
l/S=1705007183:HM/A=812075/rand=923152006>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




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



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/