Complex parameter issue

[jlr123]

I have a report that has normal selection parameters for person and invoice date. However, I need to add additional parameters for selecting records for the person dependent upon an effective date of changes made to records at customer level. We do not change the record at the invoice level, however we do have effective dates for the changes at customer level. This is my "normal" parameters for selecting records:
{AR_InvoiceHistoryDetail.ItemType} <> "4" and
{AR_InvoiceHistoryDetail.ItemCode} <> "/ADVANCE PAYMENT" and
{AR_InvoiceHistoryHeader.InvoiceDate} = {?Invoice Date Range} and
{AR_Customer.SalespersonNo} = {?Salesperson}

And this is what I need to get but don't know how to phrase or where to place formulas.
if isnull({AR_InvoiceHistoryHeader.UDF_EFFECTIVE_DATE})then {AR_InvoiceHistoryHeader.InvoiceDate}

the second formula is based on the one above.
if isnull ({AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP})then GroupName ({AR_Customer.SalespersonNo})else {AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP}

Thanks for your assistance.

The caveat to this issue is that I need to retrieve records for ar_customer.salespersonNo, prior to the 15th of the month and InvoiceHistoryHeader.SalespersonNo after the 15th only if is not null{AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP}

 

[CoSpringsGuy]

Try this in record selection (untested)

datevar invoicedate;
if isnull({AR_InvoiceHistoryHeader.UDF_EFFECTIVE_DATE})
then invoicedate := {AR_InvoiceHistoryHeader.InvoiceDate}
else invoicedate := {AR_InvoiceHistoryHeader.UDF_EFFECTIVE_DATE};

{AR_InvoiceHistoryDetail.ItemType} <> "4" and
{AR_InvoiceHistoryDetail.ItemCode} <> "/ADVANCE PAYMENT" and
invoicedate  = {?Invoice Date Range} and
{AR_Customer.SalespersonNo} = {?Salesperson}

If that doesnt work create a formula called invoicedate

if isnull({AR_InvoiceHistoryHeader.UDF_EFFECTIVE_DATE})
then {AR_InvoiceHistoryHeader.InvoiceDate}
else {AR_InvoiceHistoryHeader.UDF_EFFECTIVE_DATE}

then change records selection to read

AR_InvoiceHistoryDetail.ItemType} <> "4" and
{AR_InvoiceHistoryDetail.ItemCode} <> "/ADVANCE PAYMENT" and
{@invoicedate}= {?Invoice Date Range} and
{AR_Customer.SalespersonNo} = {?Salesperson}

You said the second formula is based on the first but Im not understanding that comment. If you are just looking to group on sales rep if it exists and if not then group on sales number you could group on this formula

if isnull ({AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP}) {AR_Customer.SalespersonNo})else {AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP}

If you group on {AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP} and there are multiple instances that value could be null with many different {AR_Customer.SalespersonNo} values then just changing the group name will cause issues.

Hope I understood your problem enough

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection