Smarter aggregate calculations

[willbown]

Does anyone have a neat way of doing this?

I have a table (SCORES) of university departments that have been ranked according to their research quality, from 5* through 5,4,3 and 2 to 1. There is a score for every department at every UK university. Each department is categorised into one of 69 disciplines ranging from Physics to French. The last field is the number of staff in the department.

In a separate table of the 69 disciplines (DISCIPLINES), I want to calculate the total number of staff in all, eg, French departments rated 4. The function I'd like would look line this:

Sum(SCORES::staff; quality = 4)

This sums the staff values in records where the quality field has a value of 4.

The same extension can naturally be applied to all the existing aggregate functions, eg the Count function.

I have a way of doing this at present but it is labour and data intensive and clogs up the database. I have to create a key field in SCORES where there are only entries if the quality is 4 and then create a relationship based on that. And do that for all the different grades.

WB.

--

William Bown

Director

Research Research Limited

Tel +44 20 7216 6502

Fax +44 20 7216 6501

http://www.ResearchResearch.com

Unit 111

134-146 Curtain Road

Hoxton

London EC2A 3AR

[LiveOak]

Using the Sum() function is the way to go. The missing piece is that you need to isolate records based upon several criteria. To do this you need to create relationships based upon "composite" keys. You need to create a separate relationship for each Sum() or modify the left side key to create different totals based upon discipline and quality selected. For example:

VariableLeftKey (calculation, text) = gDisciplines & gQuality

using globals to make it selectable or

FixedLeftKey (calculation, text) = "French" & "4"

for a fixed total for French departments with a rating of 4.

By using multiple keys and relationships, you can set the process up once and there is no need to exclude any records from the file. This could require quite a few relationships (number of disciplines x 5 possible scores), but once created allows the totals to be viewed on a single layout or printed on a single page.

-bd

[tlsparker]

Working from LiveOak's theme above, you could also create a concatenated calc field DiscQual= Discipline&Quality. Then, create a Summary Field = Total (Staff). Once these fields are created, you create a layout with a subsummary part (subsummary by DiscQual). Place the Total (Staff) summary field on the layout within that subsummary part and you should have your report. This is the quickest way I can think of.