CR9 - need to filter groups but not records 1

[synbios1978]

I created a statement report for work where it shows a range of our customers based on typing in a starting and stopping customer number and displays a statment for each customer (showing what they owe us or any deposits they may have). However, our accounting dept. has asked me to add the option to have a selection for "Over 30", "Over 60", "Over 90", and "Over 120" meaning showing only the customers who are over that many days delinquent. At first I thought no big deal, so I added in the parameters as boolean and put them in the same group. Starts off great. However, I need to show all the data for that customer, both current and everything past due. I've tried adding the criteria:

if {?Over30} = true then
CurrentDate-{ARItem.Date-Reference}>30
else if {?Over60} = true then
CurrentDate-{ARItem.Date-Reference}>60
else if {?Over90} = true then
CurrentDate-{ARItem.Date-Reference}>90
else if {?Over120} = true then
CurrentDate-{ARItem.Date-Reference}>120

inside the record selection area, and I get only the customers I want, but I lose anything that they would have as current on file. And if I put it in the group selection, I still get the right customers and totals, but I'm still missing line items. Can anyone help me out on this??

Thanks in advance!!

 

[Madawc]

I'm not clear what you'r after, but take a look at crosstabs. They do show particular combinations as zero, provided that there is something for a particular row or column.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[synbios1978]

Madawc,

Thanks for the response. I'll try to clear up what I'm looking for. I have a report which shows our customers (1 customer per page) along with a line item list of everything that's outstanding for them. Underneath that, I have totals based on how outstanding each line item is. For instance, if they owe us money for an invoice that's 100 days old, it falls into a group that called "Over 90 days". If they owe us money for an invoice that's 45 days old, it falls into "Over 30 days". Anything under 30 days is considered current.

When our accounting department sends out these statements, they only want to send them out to people who have items that fall into the "Over 30 days" or "Over 60 days" categories for instance.

I can use Record Select to filter the report to only contain entries for "Over 30 days" or "Over 90 days", and when I do that, only those customers which match that criteria shows up, which is great. However, for the customers who do match the criteria, it will not show what's classified as current as the records are being filtered.

Is there a way to only show the customers (setup as a group), who match the criteria of "Over 30 days", but still show the line items for all entries under those customers?

Thanks again!!

 

[lbass]

I would create one number parameter {?DaysOver} with these options: 30, 60, 90, 120.

Then create a formula {@daysover}:
datediff("d",{ARItem.Date-Reference},currentdate)

Then go to report->selection formula->GROUP and enter:

if {?DaysOver} = 120 then
maximum({@daysover},{table.customer}) > 120 else
if {?DaysOver} = 90 then
maximum({@daysover},{table.customer}) > 90 else
if {?DaysOver} = 60 then
maximum({@daysover},{table.customer}) > 60 else
if {?DaysOver} = 30 then
maximum({@daysover},{table.customer}) > 30

-LB

 

[synbios1978]

lbass,

Thanks for the help. That worked perfectly. I had tried using the maximum command in group, but I wasn't using the ,{table.customer} portion of it, and it was giving me a really high number. Thank you very much for the help though!!!