Using 'IF THEN' when declaring a shared variable

[former_member620944]

I have a report that I'm trying to move from Access to Crystal (per my boss' request). We have 35 product price categories. I need to declare average prices for those which are being used in any given report. Any or all of the categories could have information, depending on which products are sold for the chosen time period.

Then, when I run the main report, any product that doesn't have a stored price uses the average price.

I have a calculation in the subreport above the main report that should generate that average. But it wasn't giving me any data from the declarations. Then I changed the syntax, and now I get an error saying "The remaining text (everything after the first declaration) doesn't seem to be part of the formula".

Here's the structure of the equation:

If Not(IsNull({'ALL_ITEMS_'_1.PREVIOUS PRICE}))]

Then

{'ALL_ITEMS_'_1.PREVIOUS PRICE}

Else

WhilePrintingRecords;
Shared Currencyvar ABRS;
Shared Currencyvar ASST;
Shared Currencyvar AUTO; [there are more after this, but it's kind of redundant]

If {tbProductClass.Description} = "Abrasives"
Then ABRS := AVERAGE({'ALL_ITEMS_'_1.PREVIOUS PRICE},{tbProductClass.Description});
ABRS; <----- Here's is where the compiler sees the last good part of the equation

else if {tbProductClass.Description} = "Assortment"
Then ASST := AVERAGE({'ALL_ITEMS_'_1.PREVIOUS PRICE},{tbProductClass.Description});
ASST;

else if {tbProductClass.Description} = "Automotive"
Then AUTO := AVERAGE({'ALL_ITEMS_'_1.PREVIOUS PRICE},{tbProductClass.Description});
AUTO;

[Again, there are more of these, but redundancy]

As far as I can tell, there's nothing wrong with the syntax structurally -- it seems to be a logic issue.

If this can be fixed (please ...), please let me know. If not, is there another way to accomplish what I'm trying to do?

[DellSC · Accepted solution]

Try changing the formula to something like this:

WhilePrintingRecords;
If Not(IsNull({'ALL_ITEMS_'_1.PREVIOUS PRICE}))] Then
  {'ALL_ITEMS_'_1.PREVIOUS PRICE}
Else (
  Shared Currencyvar ABRS;
  Shared Currencyvar ASST;
  Shared Currencyvar AUTO; [there are more after this, but it's kind of redundant]
  If {tbProductClass.Description} = "Abrasives" Then (
    ABRS := AVERAGE({'ALL_ITEMS_'_1.PREVIOUS PRICE},{tbProductClass.Description});
    ABRS;
  )
  else if {tbProductClass.Description} = "Assortment" Then (
    ASST := AVERAGE({'ALL_ITEMS_'_1.PREVIOUS PRICE},{tbProductClass.Description});
    ASST;
  )
  else if {tbProductClass.Description} = "Automotive" Then (
    AUTO := AVERAGE({'ALL_ITEMS_'_1.PREVIOUS PRICE},{tbProductClass.Description});
    AUTO;
  )
)

When you have multiple lines in an Else or between a Then and an Else, you need to use parentheses to group the lines together.

You might also need to remove the semi-colon before each closing parenthesis, but I would try it with them first.

-Dell