Summary field using another summary field

[Bell1991]

I am not sure if this is possible or not.

But i have a summary report goruped by county name and for each county name i have:

County Name
Number of Records for County

HomeType - Primary (field homeType = 1)
HomeType - Secondary (field homeType = 2)
TotalAssessment (field (totalAssessment)

I need to get the count by county for homeType - which i did (since the field name is the same just different values)

Now, i would like to add a column for the average for each specific row

So, for example HomeTypePrimary - count(HomeType = 1) Total Records for that county

Does this make sense?

I am attempting to figure this out in sql and just pass in the values and i am not having much luck - so i though maybe crystal would be able to handle this?

Thanks.
-Bell

 

[lbass]

It's not clear what it is you want to average. It would help if you showed some sample data at the detail level and then showed the summaries and identified the section in which they are displayed.

-LB

 

[Bell1991]

okay i have two tables

TableCounty
CountyID int
CountyName varchar(35)

TableHome
HomeID int (valid values 1, 2)
HomeStatus int (valid values 1,2,3)
TotalAssessment float

I need to get the counts of all possible values (i.e., HomeID count where HomeID = 1 and HomeID count where HomeID = 2) I can get this information by using a fomula:

if (HomeID) = 1
then 1

and i just change the field ID to the field i need to get the count for.

Now, i also need a formula to calculate the average number:

Number of Counts / Number of Records

So, in the end it would look like:

County1
Total number of records for county1: 10

ColumnName Count Average
HomeID1 4 40% -> (4/10)
HomeID2 5 60% -> (5/10)
HomeStatus1 3 30% -> (3/10)
HomeStatus2 4 40% -> )4/10)
HomeStatus3 3 30% -> (3/10)
TotalAssessment 150,000 -> sum(TotalAssessment) 15,000-> (150000/10)


So, i need help with how to get the average since i am using a formula for the count totals

HTH

 

[lbass]

These are not averages, but percentages of the total. I guess that you are using conditional formulas instead of secondary groupings so that you can display different breakdowns of the total in the same Group #1 footer. To get your counts, you have inserted sums on your conditional formulas, so to get the corresponding percentages of total, use formulas like the following:

//{@HomeID1%}:
sum({@HomeID1},{table.county}) % count({table.HomeID},{table.county})

//{@HomeID2%}:
sum({@HomeID2},{table.county}) % count({table.HomeID},{table.county})

//{@HomeStatus1%}:
sum({@HomeStatus1},{table.county}) % count({table.HomeID},{table.county})

etc.

Place these in your Group#1 header or footer. Click on the % icon in the toolbar to get the % sign.

-LB