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
GerhardpetAsked:
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.

mlmccCommented:
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

Open in new window


mlmcc
0
GerhardpetAuthor 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
mlmccCommented:
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 

Open in new window


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

Open in new window


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}

Open in new window


mlmcc
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GerhardpetAuthor 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
James0628Commented:
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
GerhardpetAuthor 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
mlmccCommented:
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} 

Open in new window


mlmcc
0
GerhardpetAuthor 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

Open in new window


{@Extended Selling Price} formula is
round({INVENTORY.ONHAND}*{PRICING.BVRTLPRICE01},2)

Open in new window

{@Extended Average Cost} formula is
round({INVENTORY.ONHAND}*{INVENTORY.WEIGHTED},2)

Open in new window

{@Extended Current Cost} formula is
round({INVENTORY.ONHAND}*{INVENTORY.WHOLESALE},2)

Open in new window

0
mlmccCommented:
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}  

Open in new window


mlmcc
0
GerhardpetAuthor 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} 

Open in new window


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}

Open in new window


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

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

mlmcc
0
GerhardpetAuthor 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
James0628Commented:
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

Open in new window

0
GerhardpetAuthor Commented:
James,
Thanks for your input but it still does not give me the correct results
0
mlmccCommented:
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

Open in new window


mlmcc
0
GerhardpetAuthor 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
mlmccCommented:
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
GerhardpetAuthor 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
mlmccCommented:
The report I looked at didn't have any data saved.

mlmcc
0
James0628Commented:
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
GerhardpetAuthor 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

Open in new window


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
mlmccCommented:
Try this report

mlmcc
Stock-Status-SummaryRevised.rpt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
James0628Commented:
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
GerhardpetAuthor 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.