Extract part of string 1

[Polarican0115]

Crystal Reports 2011

Creating a Sales by SalesRep report. It must include any Sales that are associated with the Master Sales Rep. Many Master Sales ID's are also part of Shared Groups of Sales Reps (Shared SalesRep ID)

Highlighted in Red is the type of data I need to capture.
Please note: the Position of the Master Sales Rep ID in the "Contains Reps" field is the exact position of the corresponding calculation of the Commission Split%

[/URL]]

All Master Sales Rep ID's are at 100%
The Commission % field is set up to hold up to 5 splits.

I need to extract the correct Split% for each Master Sales Rep ID to multiply it against the total sales for that particular Group.

Thank you for any help you could provide

 

[pmax9999]

Based on how the data appears in your post, it seems that the data in the "Contains Reps" column is separated by a space. Therefore to extract its component values you could use the Split function. For example, for the last line of your data;

Split({Table.ContainsReps},' ')[1] = "AAA"
Split({Table.ContainsReps},' ')[2] = "BBB"
Split({Table.ContainsReps},' ')[3] = "CCC"
Split({Table.ContainsReps},' ')[4] = "DDD"
Split({Table.ContainsReps},' ')[5] = "EEE"

However it is not clear what separates the data in the "Commission Split %" column. On the face of it, it looks like the percentages are in groups of 5 characters (2 numeric + decimal point + 2 numeric) but it is not clear how that would work for percentages less than 10 or equal to 100.

Please confirm:
1. my assumption regarding the "Contains Reps" column;
2. exactly how the component data in the "Commission Split %" would be determined (logically/programmatically); and
3. whether there would only ever be 5 discrete pieces of data in each of the columns.

It should then be relatively straight forward to variables and a loop structure to extract the information you are looking for.

Cheers
Pete

 

[pmax9999]

And if I was right about the structure of the "Commission Split %" column, the following applies:

Val(Split(Picture(Replace({Table.ContainsReps},".", ""), "xx.xx,xx.xx,xx.xx,xx.xx,xx.xx"), ",")[1]) = 30.00
Val(Split(Picture(Replace({Table.ContainsReps},".", ""), "xx.xx,xx.xx,xx.xx,xx.xx,xx.xx"), ",")[2]) = 25.00
Val(Split(Picture(Replace({Table.ContainsReps},".", ""), "xx.xx,xx.xx,xx.xx,xx.xx,xx.xx"), ",")[3]) = 25.00
Val(Split(Picture(Replace({Table.ContainsReps},".", ""), "xx.xx,xx.xx,xx.xx,xx.xx,xx.xx"), ",")[4]) = 10.00
Val(Split(Picture(Replace({Table.ContainsReps},".", ""), "xx.xx,xx.xx,xx.xx,xx.xx,xx.xx"), ",")[5]) = 10.00

Does this help

Pete

 

[Polarican0115]

Thank you very much Pete. This was very helpful. I am still working through this because I have some issues trying to Group by what I pull out of "Contains Rep", but this was great to get me started.

 

[Polarican0115]

Pete,

Your assumptions were correct.
1. The data in the Contain Reps column are always separated by a space.
2. Your formula worked great for extracting the data from the Commission Split % column
3. There will only ever be 5 discrete pieces of data in each column.

I am now able to get the data I need out of those columns and calculate the Commission amounts.

** What I am having an issue with is getting all the Reps in the Contains Reps column to completely "Group" together.

For whatever reason, I can only get the Master Rep account and any Shared Sales Rep ID that has the Master rep Acct in Position 1 of the Contains Reps Column to Group together?

These Rep accounts Group together:

EEE (Master Rep Acct)
124 (Position 1 of Contain Reps)
224 (Position 1 of Contain Reps)

It excludes the following reps:

335 (Position 2 of Contain Reps)
446 (Position 5 of Contain Reps)


What I need to show is the following:

[/URL]]

I tried Grouping by the Master Sales Rep ID and also tried Grouping by Contains Reps with the same results. What would be the best way to Group this information to get the results illustrated above?

 

[pmax9999]

Without understanding the structure of your database, it is difficult to give anything other than very general suggestions.

The following formula will loop through each record, find the position of 'EEE' and then find the matching percentage based on that position:

[Code {@EEE_Percentage}]
WhilePrintingRecords;

Local StringVar x := {Table.Contains Rep};
Local NumberVar p := 0;
Local NumberVar i;
Global NumberVar PCNT;

For i := 1 to UBound(Split(x, ' ')) do
If Split(x, ' ') = 'EEE'
Then p := i
Else p := p;

PCNT := Val(Split(Picture(Replace({Table.Commission Split},".", ""), "xx.xx,xx.xx,xx.xx,xx.xx,xx.xx"), ",")[p]);

PCNT
[/Code]

Does that help?

Pete

 

[Polarican0115]

I appreciate that Pete, but what you provided earlier did a great job helping me to match percentage based on position.

Now that I have the split data, What I need to do is Group all MasterSalesRep accts with any SharedSalesRep acct they are part of. (Regardless of the position in the user3 column)

I am utilizing Crystal 2011 connecting to SQL server via an OLEDB connection.


This is the actual table I am utilizing to get the data:

[/URL]]

For whatever reason, I can only get the Master rep acct to Group with any Shared Rep accounts from Position 1 of the User3 column. If the Master Rep account exists in Position 2 to 5 of particular Shared rep acct, it will not Group with the Master rep acct? Any ideas?

 

[Polarican0115]

The image is kind of small. This is the detail I added to the screen shot:

This is the actual table (Databasename.slsrep_ext) that I am utilizing to get the commission splits from, then group by Master rep Acct.

All Master Rep accts will have a single value in the "user3" column.

The "slrep" column will contain the Rep ID for Master Rep accounts as well as the "Shared" rep accts.

The "user3" column will contain Master Rep ID's only. Displaying which Master Rep ID's will be credited for that specific "Shared" rep Acct.

The "user4" column contains the split % for commissions.

 

[pmax9999]

The more I think about this, the more I would be inclined to use SQL to do 5 passes of the data and use Union to build a dataset with a column of Sales Reps and a column of percentages.

However I think it could still be done in Crystal. I haven't tested this but I think you could group on the first sales rep in the column, then use a sub-report to get other results where the sales rep is in the 2^nd to 5^th positions.

I'd be happy enough to test this approach further if you made an excel spreadsheet of the data sample above available via dropbox or similar.

Cheers
Pete

 

[Polarican0115]

Thank you Pete, I would appreciate that very much. I created a spreadsheet with all the data from that table.

 

[Polarican0115]

Pete, please disregard the first attachment. The drop box link below should work fine.

Thank you again.

Eric

 

[pmax9999]

OK, I think we can make this work.

Follow the following steps:

1. Create the following Formula {@BaseSalesRep}:

[Code {@BaseSalesRep}]
If {Table.slsrep} = {Table.user3}
Then {Table.slsrep}
Else Split({Table.user3}, ' ')[1]
[/Code]

2. Create a group on this new formula - details section can be suppressed.

3. Insert a Sub-Report into the Group Footer that uses the same table, and create a Parameter (String) called {?SalesRep}.

4. Add the following Record Selection formula to the Sub-Report:

[Code RecordSelectionFormula]
{?SalesRep} in Split({Table.user3}, ' ')
[/Code]

5. Link the Sub-Report from {@BaseSalesRep} in the Main Report to {?SalesRep} in the Sub-Report.

6. Create the following formula in the sub-report to extract the Commission % for each of the Sales Reps:

[Code {@Sales%}]
WhilePrintingRecords;

Local StringVar x := {Table.user3};
Local NumberVar p := 0;
Local NumberVar i;
Global NumberVar PCNT;

For i := 1 to UBound(Split(x, ' ')) do
If Split(x, ' ') = {?SalesRep}
Then p := i
Else p := p;

PCNT := If {Table.slsrep} = {Table.user3}
Then 100
Else Val(Split(Picture(Replace({Table.user4},".", ""), "xx.xx,xx.xx,xx.xx,xx.xx,xx.xx"), ",")[p]);

PCNT

[/Code]

7. Use the PCNT Variable value to calculate the Commission value based on the sales.


This report will be slow and inefficient as it needs to make many database passes so it can retrieve records all sales records for each sales rep, one sales rep at a time. I did not get to the point of calculating actual Commission values because the data did not appear to include sales values. Because of the use of variables in extracting the Commission percentage, you will need to also use variables for accumulating the total commission for each Sales Rep. I also suggest you review the data fairly closely to see which rows can be excluded completely (for example where user3 is null) from both the main report and the sub-report, so as to improve efficiency as much as possible.

Hope this helps.

Cheer
Pete.

 

[Polarican0115]

Thank you again Pete, I truly appreciate all your help!

I followed your steps, but I received the following message for @BaseSalesRep after trying to run it the first time:

[/URL]]

 

[pmax9999]

If you exclude records from the main report where {Table.slsrep} is null the problem should disappear.

Add the following to your record selection:

[Code RecordSelection]
Not(Isnull({Table.slsrep}))
[/Code]

If that doesn't fix it, amend the formula as follows:

[Code {@BaseSalesRep}]
If {Table.slsrep} = {Table.user3}
Then {Table.slsrep}
Else
If UBound(Split({Table.user3}, ' ')) > 0
Then Split({Table.user3}, ' ')[1]
[/Code]

Cheers
Pete

 

[Polarican0115]

That worked Pete, thank you!! I am also working on excluding rows to increase efficiency to help speed it up a bit, it does take a while to run. But without your help I would not be anywhere near this close.

The only issue I am still having is the last part, trying to get the accumulated total commission for each Sales Rep.

Thank you again! This forum is amazing!

 

[Polarican0115]

I'm having trouble trying to get the accumulated total commission for each Sales Rep. How do I properly use variables for accumulating the total commission for each Sales Rep? Should I open a new Thread since this is off the original topic? I really appreciate all the help!

 

[pmax9999]

This should probably be in a new thread, but in broad terms:

1. Create the following formula and place it in the RH of the sub-report:
[Code {@Reset}]
WhilePrintingRecords;

Global NumberVar PCNT := 0;
Global NumberVar TotSales := 0;
[/Code]

2. Create the following formula and place it in the Details section of the sub-report

[Code {@SalesSplit}]
WhilePrintingRecords;

Global NumberVar PCNT;
Local Numbervar Sales;
Global NumberVar TotSales;

Sales := {Table.Sales} * PCNT/100;

TotSales := TotSales + Sales;

Sales
[/Code]

3. Create the following formula and place it in the RF of the sub-report:

[Code {@TotalSales}]
WhilePrintingRecords;

Global NumberVar TotSales
[/Code]

Hope this helps.

Cheers
Pete