Suppressing Records

[Dee38]

Hi

I am trying to create a very simple report that details live customer accounts.
Each account has a stop_flag status: 0=Off Stop, 1=Soft Stop, 2=Hard Stop, 3=Closed
Some customer accounts are Delivery accounts only, so sales are invoiced on a related Invoicing (customer) account, not on the Delivery account.
Customer A/c Invoice A/c Stop Flag status
ABE004 ABE004 3 Closed
ABE005 ABE004 0 Off stop
FIR001 FIR001 0
FIR002 FIR001 0
FIR003 FIR001 0

The problem I have is that when a customer account that is also an invoicing account for one or more depots is closed on the sales ledger, the stop flag status becomes 3 e.g. ABE004 but all delivery accounts which feed into it, e.g. ABE005, despite having the closed flag displayed on the SL account, remain at Stop Flag status 0. Therefore, if I use record selection customer.stop_flag<>3 this will remove ABE004 from my report but will not remove ABE005, and I want both gone as both are closed.
I can’t simply do a suppress if customer.account <> invoice_ac because this will suppress FIR002 and FIR003 and they are live, active (delivery) accounts that I want on my report together with their invoicing account FIR001.
So how do I code for the scenario, suppress if the stop_flag for the invoice_ac of the customer.account is 3.
Any help would be much appreciated.
Many thanks

 

[padytom]

I'm not sure I understand you well but since you know your formula to use, the condition you want to use in the record section should be used in the formula section and use the formula instead of the field.

 

[kenhamady]

You are trying to use record selection and you need GROUP selection that refers to a subtotal. Try this:
1) Make sure there is a group by Invoice Account # so that the first two records in the example above are in the same account group
2) Write a formula that says:

if {StopFlagStatus} = '3'
then 1
else 0​

3) Create a subtotal that sums this formula for your group by Invoice Account #
4) Highlight this subtotal and go into the Select Expert (Group)
5) Add a rule that says the Sum of the formula field is equal to 0

This should get rid of both records. Note that these records will still be in the report data (shown in the group tree, the record count and in any grand totals) but they will not show in the details and will not be counted in any RUNNING totals you create.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[Dee38]

Hi
Many thanks for the replies.
kenhamady, I went down the road of grouping before your reply came through so, so far so good!
In Group Header #1 I have my groups by invoice account:
Group #1 Name: customer.invoice_ac (string)
And in Details I now have @StopFlagStatus (Number) which is the formula field you have just suggested I create:
If {customer.stopflag} = 3 then 1 else 0
My report now looks like this:
ABE004
ABE004 3 ABE004 1.00
ABE005 0 ABE004 0.00

#RTotal0: Sum of @StopFlagStatus 1.00 (in GroupFooter #1)

But where I am struggling is, if I right click Sum of @StopFlagStatus (i.e. my subtotal) and go to Select Expert Group, there is no option to choose #RTotal0: in order to suppress it with a rule. I'm in Crystal 2008 I should have said!
Many thanks

 

[Dee38]

Hi

Seems to have worked if I do a Section Expert Suppress in the Group Header, Details and Group Footer and put {#RTotal0}>0 by clicking on each of the X-2 formulae buttons.
Many thanks for your help.
Dee

 

[kenhamady]

Your welcome. The reason you couldn't do group selection is because you used a running total rather than a regular summary field (insert summary) to create your subtotal.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[Dee38]

So I did!
I will try the summary field also.
Thanks - you're fab!

 

[Dee38]

Report was even easier to do with the Insert Summary!
I now have a fabulous report of all live customer accounts, which can be searched using any combination of 6 dynamic parameters.
The tip about assigning a different value to any group containing a specific record (in this case Stop Flag = 3) is priceless!
Thank you again.