weighted average in crosstab

[princessinthetower]

I'm using Crystal 9. When I include a weighted average summary in a crosstab, if the result is zero I get a blank that cannot be formatted as zero or '-'.

I've accounted for nulls in my formulas of the fields needed for the average 'if isnull({table.field}) then 0' and I've set the report options to 'Convert Null values to default'. Outside the crosstab, the weighted average shows correctly as zero.

Is this a bug in the crosstab or can I do something about this? It's annoying to have to explain why some zeroes are 0 and some are blank. The only solution I can come up with is to format all zeroes as blank and in this case it makes the report difficult to read.

 

[lbass]

You could try the following. Create a SQL expression {%cellcount} that counts the records defined by each cell in the crosstab, as in:

(select count(AKA.`ID`) from Table AKA where
AKA.`columnfield` = Table.`columnfield` and
AKA.`rowfield` = Table.`rowfield`)

Replace "Table" with your table name, "columnfield" with your column field name, and "rowfield" with your row field name. "ID" should be the name of some field that is never null. Leave "AKA" as is, since it is an alias field name.

Then create a formula {@wgtave}:

({@yourfield} * {table.wgtfield})/{%cellcount}

//where {@yourfield} takes the form of:

if isnull({table.yourfield}) then 0 else {table.yourfield}

If your weight field can have nulls, then that field needs to be replaced by the same type of formula.

Then use {@wgtave} as your summary field in the crosstab. Sum should be the summary.

-LB

 

[princessinthetower]

Thanks for the response. This is the first time I tried SQL Expressions and found that you can't pass a parameter to a SQL Expression. UNfortunately I need to do that.

 

[lbass]

Is the parameter used in the column or row field? It is not necessarily a problem, depending on how you are using the parameter. For example, let's say your column field is a parameter for {table.ID}. Since the SQL expressions are calculated at the database level, you can use the {table.ID} as the column field in the SQL expression, instead of the parameter. The report will simply not select the results for those IDs not used in the report.

Another possibility: I don't have 9.0, but from what I understand, you can use a SQL command as a basis for your report instead of developing a record selection formula, and I believe you can use parameters in these. If you haven't used this feature before, you might want to experiment by starting a new report.

A SQL expression like the one I suggested appears in the SELECT part of the SQL query exactly as I have shown above, inside parentheses, along with any other fields you are using on your report. You could potentially design the command to include the contents of the SQL expression in the SELECT part of the statement.

It might help if you explained your use of the parameter and how it relates to your crosstab fields (which you have not yet identified).

-LB

 

[princessinthetower]

The parameter is FiscalYear. The summaries I need are total gifts and average gift, among others. There is a left outer join from the Name ID table to the gift summary table. Some IDs have multiple gift summaries for different fiscal years. Some have no gift summary. I can't do an overall record select due to the null gift situation.

I have formulas for donor count and gift count. The weighted average formula in the crosstab calculates correctly except when in a particular geographical category there are no gifts. Then 0 gifts divided by 0 donors produces a blank.

I hope this is clear but I think the effort is quickly outweighing a possible positive result for what is essentially a formatting issue.

 

[lbass]

The solution might not be a lot of work, and I recreated your original problem, so I understand the issue, and also was able to return zeros instead of blanks in a crosstab by using the SQL expression solution.

What is your row field (name?), column field (fiscal year?), and what are the fields/formulas involved in the weighted average? Let's say your column field is fiscal year, that you are using a multiple value number parameter for fiscal year, and that your fiscal year is July 1 to June 30. You are probably using a formula like the following for your column field:

numbervar i := ubound({?fiscal year});
numbervar counter;
stringvar x;

for counter := 1 to i do(
if (year({Table.Date}) = {?fiscal year}[counter] and
month({Table.Date}) <= 6) or
(year({Table.Date}) = {?fiscal year}[counter]-1 and
month({Table.Date}) >= 7) then
x := "FY "+totext({?fiscal year}[counter],0,"") else "Other");
x

The SQL expression you would use could be something like the following example:

(select count(AKA.`ID`) from Table AKA where
AKA.`rowfield` = Table.`rowfield` and
{fn YEAR(AKA.`Date`)} = {fn YEAR(Table.`Date`)} and
{fn MONTH(AKA.`Date`)} = {fn MONTH(Table.`Date`)})

-LB