# Need help with suppress formula in Crystal

I need help with a suppress formula in Crystal where I have 3 conditions

if {?Onhand QTY} = All then show all records

if {?Onhand QTY} <= 0 then show where ONHAND <= 0

if {?Onhand QTY} >= 0 then show where ONHAND >= 0

How would I create a formula that I can use in the Section Expert Suppress (no Drill-Down)

I'm on Crystal 10 and Pervasive 10
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I think I understand

You have a parameter that you want to use to control what to show.  Remember the suppression is really the negative of what you want to show.

Do you need all records for the report or could you use a selection formula to produce just the records you need to show?

Try this formula

``````if {?Onhand QTY} = 'All' then
FALSE
ELSE if {?Onhand QTY} = '<= 0' then
ONHAND > 0
ELSE if {?Onhand QTY} = '>= 0' then
ONHAND < 0
ELSE
TRUE
``````

mlmcc
0
Author Commented:
I think I could use a selection formula because I don't need all records based on the parameter. But your formula works for what I want except....

One thing that is not working now with the formula is the total in the footer that is totaling the value of all inventory. It takes the total of all records instead of just for the returned records from the parameter

I have attached the report here if that helps
Stock-Status-Summary.rpt
0
Commented:
That is why I asked if you needed all the records.  Summaries work on all the records.

Two ways to get it to work
1.  Use selection to get the records you want
2. Create a formula to control which values are summarized.

Selection formula
``````if {?Onhand QTY} = 'All' then
TRUE
ELSE if {?Onhand QTY} = '<= 0' then
{OnHandField} <= 0
ELSE if {?Onhand QTY} = '>= 0' then
{OnHandField} >= 0
ELSE
TRUE
``````

Another way
``````{?Onhand QTY} = 'All'
OR
({?Onhand QTY} = '<= 0' AND     {OnHandField} <= 0)
OR
({?Onhand QTY} = '>= 0' AND     {OnHandField}>= 0)
``````

Formula to select the values for use in the summary
``````if {?Onhand QTY} = 'All' then
{OnHandField}
ELSE if {?Onhand QTY} = '<= 0' AND   {ONHANDFIeld} <= 0 THEN
{OnHandField}
ELSE if {?Onhand QTY} = '>= 0' AND    {ONHANDField} >= 0 THEN
{OnHandField}
ELSE
{OnHandField}
``````

mlmcc
0
Author Commented:
Can't quite figure out it how to do it.

This is the formula that I want to summarize

{@Extended Value}

how would I do this?
0
Commented:
If you want a grand total, you could create a formula like this to do the summary and then put the formula on the report:
Sum ({@Extended Value})

Or, you could put {@Extended Value} in the detail section, right-click and select Insert > Summary and add your summary on that formula.  You can remove {@Extended Value} from the detail section after that.  It was just there so you could use Insert > Summary.

James
0
Author Commented:
James,
That is what I currently have. That does not work because it adds up all records and not based on the records displayed.
0
Commented:
WHat is your extended value formula?

It should be something like the formula I posted above

``````if {?Onhand QTY} = 'All' then
{OnHandField}
ELSE if {?Onhand QTY} = '<= 0' AND   {ONHANDFIeld} <= 0 THEN
{OnHandField}
ELSE if {?Onhand QTY} = '>= 0' AND    {ONHANDField} >= 0 THEN
{OnHandField}
ELSE
{OnHandField}
``````

mlmcc
0
Author Commented:
The extended formula is

``````If {?BV99LS05ExtensionsBasedOn} = "Average Cost Price" then
{@Extended Average Cost}
else if {?BV99LS05ExtensionsBasedOn} = "Selling Price" then
{@Extended Selling Price}
else if {?BV99LS05ExtensionsBasedOn} = "Current Cost Price"
then {@Extended Current Cost}
else 0
``````

{@Extended Selling Price} formula is
``````round({INVENTORY.ONHAND}*{PRICING.BVRTLPRICE01},2)
``````
{@Extended Average Cost} formula is
``````round({INVENTORY.ONHAND}*{INVENTORY.WEIGHTED},2)
``````
{@Extended Current Cost} formula is
``````round({INVENTORY.ONHAND}*{INVENTORY.WHOLESALE},2)
``````
0
Commented:
If you are also trying to include the suppression, you need to add the suppression checks.

Try it this way

``````if {?Onhand QTY} = 'All' then
{@Extended Value}
ELSE if {?Onhand QTY} = '<= 0' AND   {ONHANDFIeld} <= 0 THEN
{@Extended Value}
ELSE if {?Onhand QTY} = '>= 0' AND    {ONHANDField} >= 0 THEN
{@Extended Value}
ELSE
{@Extended Value}
``````

mlmcc
0
Author Commented:
mlmcc,
Your formula works but it does not give me the correct results. The sum total is not correct.

This is the formula with the correct fields that I'm using now

``````if {?Onhand QTY} = 'All' then
{@Extended Value}
ELSE if {?Onhand QTY} = '<= 0' AND   {INVENTORY.ONHAND} <= 0 THEN
{@Extended Value}
ELSE if {?Onhand QTY} = '>= 0' AND    {INVENTORY.ONHAND} >= 0 THEN
{@Extended Value}
ELSE
{@Extended Value}
``````

Someone else helped me as well using this formula and it does not work either

``````if (NOT({?Onhand QTY} = 'All') or ({?Onhand QTY} = '<= 0' and {INVENTORY.ONHAND} >= 0) or ({?Onhand QTY} = '>= 0' and {INVENTORY.ONHAND} <= 0)) then
0
else
{@Extended Value}
``````

When using this formula I get 0.00 as a result
0
Commented:
How are you determining the totalis not correct?

Can you upload the report with saved data to show the issue?

mlmcc
0
Author Commented:
Sure have a look.. With the data now my 1 row total is -11,498.64 and the summarized total is 8,461.65
Stock-Status-Summary.rpt
0
Commented:
You should have 0 after the last Else in the first formula in your previous post.  The way you have it, the formula _always_ produces {@Extended Value} as the result (which makes the formula kind of pointless :-).

I haven't looked at your report, so I don't know if that's the only problem, but it's definitely a problem.

James

``````if {?Onhand QTY} = 'All' then
{@Extended Value}
ELSE if {?Onhand QTY} = '<= 0' AND   {INVENTORY.ONHAND} <= 0 THEN
{@Extended Value}
ELSE if {?Onhand QTY} = '>= 0' AND    {INVENTORY.ONHAND} >= 0 THEN
{@Extended Value}
ELSE
0
``````
0
Author Commented:
James,
Thanks for your input but it still does not give me the correct results
0
Commented:
Try this

Either use a summary on the extended value formula to get the total or change your summary total to

``````if {?Onhand QTY} = 'All' then
Sum({@Extended Value})
ELSE if {?Onhand QTY} = '<= 0' AND   {INVENTORY.ONHAND} <= 0 THEN
Sum({@Extended Value})
ELSE if {?Onhand QTY} = '>= 0' AND    {INVENTORY.ONHAND} >= 0 THEN
Sum({@Extended Value})
ELSE
0
``````

mlmcc
0
Author Commented:
mlmcc,
I have already tried a summary on my extended formula and it does not work. It totals up all records not just the records displayed base on the formula that you helped me with.

When I use the formula above the total is \$0.00 which is not correct either.

Thanks for trying to help!
0
Commented:
I assume if you use ALL then the report is correct?

If you right click the Extended Value formula and click INSERT --> SUMMARY
Does that show the correct value?
If not what does it show?

Do you get the correct value for the records that show in the details for extended value?

mlmcc
0
Author Commented:
Yes when I use ALL then the report is correct.

If you right click the Extended Value formula and click INSERT --> SUMMARY
Does that show the correct value?
Yes, only if I select ALL

Do you get the correct value for the records that show in the details for extended value?
Yes

Did you take a look at the report with the saved data?
0
Commented:
The report I looked at didn't have any data saved.

mlmcc
0
Commented:
The {@Extended Value} formula in the last report that you u/l'ed is not the same as the formula in your post.

The {@Extended Value} formula that you posted here is trying to include different records, based on the {?Onhand QTY} parameter and the INVENTORY.ONHAND field.  But the {@Extended Value} formula in the report is not checking any fields.  It's just doing different calculations, based on a different parameter, and it's going to do that same calculation for every record.

So, what are you really trying to do in that formula?

James
0
Author Commented:
The extended value formula is not what I have created. It comes with the report out of the box and that is not the problem.

Tthis is the suppression formula for the detail rows which works fine

``````if {?Onhand QTY} = 'All' then
FALSE
ELSE if {?Onhand QTY} = '<= 0' then
{INVENTORY.ONHAND} >= 0
ELSE if {?Onhand QTY} = '>= 0' then
{INVENTORY.ONHAND} <= 0
ELSE
TRUE
``````

But now the problem is with summarizing the extended formula based on how may records are returned for the details.

Does that make sense?
0
Commented:
Try this report

mlmcc
Stock-Status-SummaryRevised.rpt
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Sorry.  I got my wires crossed somehow.  It seems like the formula that I posted earlier (with 0 at the end) should work, but, if not, hopefully mlmcc's version of the report has your solution.

James
0
Author Commented:
Yes it works now! Thank you very much for you help!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.