Formula Not Getting Correct Sum with Parameters and Formulas

[brxmas]

SQL 2000
Crystal 9.0

If someone could please tell me what I need to do in order to get the correct data, I would appreciate it.

Report Filter:
{mc_period_dates.period} <= {?Period} and
{mc_period_dates.period_year} >= {?Year} -1

I'm trying to get the sum of CY Inches when using the following parameters:

Year (enter Year) 2005
Period (enter Period) 9

Choose one of the following parameters as well:
lines (enter range if wanting to see all lineage)
6col (enter range for 6col ads)
10col (enter range for 10col ads)

The following is a forumla to get CY Inches:

IF {?periodorscheduled} = "Period" AND
{mc_period_dates.period} = {?Period} AND
{mc_period_dates.period_year} = {?Year} and
{commission_summary.lines_sum} = {?lines}
THEN
{commission_summary.lines_sum}

ELSE

IF {?periodorscheduled} = "Period" AND
{mc_period_dates.period} = {?Period} AND
{mc_period_dates.period_year} = {?Year} and
{commission_summary.ten_col_inches_sum} = {?10col}
THEN
{commission_summary._ten_col_inches_sum}
ELSE

IF {?periodorscheduled} = "Period" and
{mc_period_dates.period} = {?Period} AND
{mc_period_dates.period_year} = {?Year} and
{commission_summary.6col_inches_sum} = {?6col}
THEN
{commission_summary.6col_inches_sum}

The formula for (?10col) is as follows, same concept for
(?6col) and (?lines):

IF {?periodorscheduled} = "Period" and
{mc_period_dates.period_year} = {?Year} and
{mc_period_dates.period} = {?Period} and
(commission_summary.ten_col_inches_sum} = {?10col}
THEN
{commission_summary.ten_col_inches_sum}

else

IF {?periodorscheduled} = "Period" and
{mc_period_dates.period_year} = {?Year} -1 and
{mc_period_dates.period} = {?Period} and
{commission_summary.ten_col_inches_sum} = {?10col}
THEN
{commission_summary.ten_col_inches_sum}

But, I'm not getting correct data when using these formulas:
IF {?periodorscheduled} = "Period" and
{mc_period_dates.period} = {?Period} AND
{mc_period_dates.period_year} = {?Year} and
{commission_summary.6col_inches_sum} = {?6col}
THEN
{commission_summary.6col_inches_sum}

If I use the formula below I get the correct data. But, users want to run this report using various parameters - lines, 6col, or 10col

IF {?periodorscheduled} = "Period" and
{mc_period_dates.period} = {?Period} AND
{mc_period_dates.period_year} = {?Year}
THEN
{commission_summary.6col_inches_sum}

Correct Output: Wrong Output:
CY Inches CY Inches
103,452 101,246

I've tried using the following as well

IF {?periodorscheduled} = "Period" THEN
(
IF {mc_period_dates.period} = {?Period} AND
{mc_period_dates.period_year} = {?Year} and
{commission_summary.6col_inches_sum} = {?10col}
THEN
{commission_summary.10col_inches_sum}
)

Thanks in advance for your help.

bc

 

[lbass]

The problem is that your commission summary fields (lines, 10 col ads, 6 col ads) can exist in the same record. When you use the if-then, if a record meets the first set of criteria, it doesn't evaluate the later ones. I think you should set it up like this instead. First create a discrete string parameter {?lineno} with options: "all lines", "6-col ads", "10-col ads", but without the quotes. Then use the following formula:

IF {?periodorscheduled} = "Period" AND
{mc_period_dates.period} = {?Period} AND
{mc_period_dates.period_year} = {?Year} then
(
if {?lineno} = "all lines" then
{commission_summary.lines_sum} else
if {?lineno} = "6-col ads" then
{commission_summary.6col_inches_sum} else
if {?lineno} = "10-col ads" then
{commission_summary.ten_col_inches_sum}
)

Then you can insert summaries on this.

It looks like you are using a formula like this to create a manual crosstab, and that you will probably have other formulas that define different periods, right? You would just change the first part of the formula for different periods.

-LB

 

[brxmas]

--LB

Your formula worked perfectly, I appreicate your help. You always have the perfect solution, it never fails. Thanks for your quick response.