Need help with suppress formula in Crystal

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

Select allOpen in new window

mlmcc

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

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 

Select allOpen in new window

Another way

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

Select allOpen 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}

Select allOpen in new window

mlmcc

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?

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

James,
That is what I currently have. That does not work because it adds up all records and not based on the records displayed.

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} 

Select allOpen in new window

mlmcc

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

Select allOpen in new window

{@Extended Selling Price} formula is

round({INVENTORY.ONHAND}*{PRICING.BVRTLPRICE01},2)

Select allOpen in new window

{@Extended Average Cost} formula is

round({INVENTORY.ONHAND}*{INVENTORY.WEIGHTED},2)

Select allOpen in new window

{@Extended Current Cost} formula is

round({INVENTORY.ONHAND}*{INVENTORY.WHOLESALE},2)

Select allOpen in new window

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}  

Select allOpen in new window

mlmcc

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} 

Select allOpen 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}

Select allOpen in new window

When using this formula I get 0.00 as a result

How are you determining the totalis not correct?

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

mlmcc

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

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

Select allOpen in new window

James,
Thanks for your input but it still does not give me the correct results

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

Select allOpen in new window

mlmcc

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!

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

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?

The report I looked at didn't have any data saved.

mlmcc

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

Select allOpen 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?

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

Yes it works now! Thank you very much for you help!