Record Selection Parameter for Detail Section

[donnarenia]

Is there a way to use a record selection prameters to select a detail section.

For example, I am working on a commission report, that shows the following details:
Invoice Date
Client Name
Invoice #
Invoice Amt
Commission Rate {CLNTCUS.~Custom20}
Commission Amount (this is a formula @commission)
In Details B section, I want to show the exact same details but for a Second Salesperson on the same Invoice.
Invoice Date
Client Name
Invoice #
Invoice Amt
Commission Rate (will be different {CLNTCUS.~Custom22}
Commission Amount (this is a formula @commission2)
I have set up a record selection formula based on the Salesperson
if{?Salesperson}= "Salesperson 1" then
{CLNTCUS.~Custom3} = {?Employee} and
not ({CLNTCUS.~Custom3} in ["House Account", "JRK"])and
{@DatesRemaining} < 13.00 and
{AR.InvoiceDate} = {?Invoice Date} and
{AR.Type} = "I" else
if {?Salesperson} = "Salesperson 2" then
{CLNTCUS.~Custom21} = {?Employee} and
{@DatesRemaining} < 13.00 and
{AR.InvoiceDate} = {?Invoice Date} and
{AR.Type} = "I"

I am trying to figure out how can I set up a way to connect the details for the Salesperson? Is this possible to do in one report?

 

[Madawc]

You can use Drill Down to have the overall details shown and extra details for an account you choose. Crystal's HELP should give you details

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

This seems like the same topic as your other recent post where suppression appeared to work. What was the problem with that approach? I don't think you should have started a new thread.

-LB

 

[donnarenia]

It worked but when it came to totatling my amounts the group header was giving me problems so I scraped that and used various if then formulas based on the selection criteria. If you feel I should not have started another post, how can I delete threads to eliminate the problem?

 

[donnarenia]

How do i use Group Selection to get a combination of two fields,

CLNTCUS.~Custom3 - which represents Salesperson 1 and
CLNTCUS.~Custom21 -which represents Salesperson 2

each sales person can appear on one invoice but have to split the commission on the total invoice amount. Right now my forumalas are based on my Group Header which I use CLNTCUS.~Custom3 (GH1) and CLNTCUS.~Custom21 (GH2)to differtiate the commission rate to use. I need the report to pull the same employee regardless if they appear as salesperson 1 or 2, is this possible?

 

[lbass]

If you really want the fields combined, you could try using a command that merges the two fields into one, with a second text field that can disttinguish whether they are the Salesperson 1 or 2, like this:

select 'SalesPerson1' as type, clntcus.~custom3 as salesperson, ar.invoicedate
from clntcus
inner join ar on
clntcus.key = ar.key
where <add criteria>

union all

select 'SalesPerson2' as type, clntcus.~custom21 as salesperson, ar.invoicedate
from clntcus
inner join ar on
clntcus.key = ar.key
where <add criteria>

You could group on {command.salesperson} and then use the {command.type} field to distinguish each (1 or 2). You should use the command as the sole datasource for your report.

-LB

 

[donnarenia]

That sounds like exactly what I need. Where do I write the formula in the Formula Expert, Section Expert....not sure about that

 

[donnarenia]

Or can I write a Group selection formula that will allow me to select based on {CLNTCUS.~Custom3} or {CLNTCUS.~Custom21}

 

[lbass]

This is a command, so start a new rpeort and select your datasource->add command (above the table list) and enter it there.

You can start by copying the SQL query from your current report (database->show SQL query) and pasting it into the command area of the new report and then adjusting it to remove the current second salesperson field.

Then copy the query, add a union all and paste it below the original query, substituting the salesperson2 field. Add the text field to both sides of the query, remembering that fields to be merged must be in the same ordinal position on each side of the union all, and there must be the same number of fields on each side.

The command should be used as your sole datasource. If you are using parameters, create them inside the command (on the right hand side).

-LB

 

[donnarenia]

Sorry...that is way above my level of Crystal. I am using version 8.5 and it is connecting to a CCH (Advantage)database that is pulling this data. At this point, I feel it may be something I have to outsource or use two reports to get my results. Thanks for all your help over time, and for your patience.

I thought it may have been simple enough since to use group selection so I will try that angle for now.

 

[lbass]

I think there is a way of getting the correct commissions by adding the table twice and using running totals to get the commissions per salesperson in a salesperson group footer. Let me know if this method might be acceptable to you.

-LB

 

[donnarenia]

I have the correct commissions, my problem is the way i have my formulas calculated they feed off of a Group to separate the Salesperson {CLNTCUS.~Custom3}) (for salesperson 1) and {CLNTCUS.~Custom21}) (for salesperson 2)

--formula used to calculate the correct commission per salesperson 1
WhilePrintingRecords;
Shared NumberVar adjust ;
Sum ({@commission}, {CLNTCUS.~Custom3}) - adjust
--formula used to calculate the correct commission per salesperson 2
WhilePrintingRecords;
Shared NumberVar adjust ;
Sum ({@commission}, {CLNTCUS.~Custom21}) - adjust

What I am having a problem with is one salespersons 1 can sometimes be listed as salesperson 2 and vice versa. IF that happens, I have inserted if-then-formulas to make the calculation work.

My Report for DJM (Salesperson 1)looks like this:

Date Inv Date Aged Client Inv.# Inv. Amt Comm Rate Comm Amt.
2/21/11 1/15 1 abc co 1111 $150 .20 $30
3/21/10 1/15 10 abc co 1112 $150 .35 $52.50
6/21/10 1/15 7 abc co 1113 $150 .23 $30

**On the invoice where the Comm Rate is .20 it is shared with someone else and DJM is Salesperson 1 THAT WORKS

But what doesn't work is an invoice where DJM is Salesperson 2 with a comm. rate of .15

I can get the formulas to work but I can't seem to pull in DJM when he is listed as Sales person 2 {CLNTCUS.~Custom21}

 

[lbass]

Well, that has been the problem all along. You have added in another complication, and that is that you are adjusting the value by some a shared variable from a subreport I guess, and you haven't explained why or where the subreport is located. But, anyway, you also didn't explain whether you want to try my method with results in the group footer only.

Another simpler method might be to insert a subreport in the custom3 group section that is linked from the main report custom3 field to the subreport's custom21 field. This would show only values for the salesperson2 (custom21) field. You would then share the results back to the main report to add to the custom3 results.

-LB