Crosstabs by their nature summarize across records, unless you use a rowfield that is unique for each record. Since you want to show one value for a row group, that suggests that the value is the same for each row within that group. Therefore, you should be able to use a maximum as a summary.
If this isn't the case, you probably need to provide more information about the field in question--is it a formula, and if so, what is the content?
In your example, if you are inserting the summary based on a subquery, then "6" will be the result for every row for the month of January; therefore, a maximum will work. Otherwise, if the average is based on the number of staff (staff volume), you should be able to insert a count on {table.staffID} to get the summary, instead of using the summary.
Since I am having a problem that almost directly results from the solution I'm about to propose, I would advise you to be careful in using it. But I had the same problem and I solved by creating a fake table in the database:
1: create a fake table that has a field shared with your data table
2: entere a bunch of fake records that would duplicate the data from your main table when queried properly such as
0,1
0,2
1,1
1,2 etc.
3: get the data for the report using a command which links the two tables. The records should be ordered in such a way that the fake(s) would come in as the last ones in their group.
4: create a formula that would return the value of the grouping field, or the word "Average" based on the record
5: use a formula to return actual data as expected if this is a regular record, or the average that you wish to display if the record is fake
6: finally, base your crosstab on the grouping/naming formula, and the counter/average formula.
Just make sure that your running totals/averages and whatever else is cleared out properly for each crosstab.
It might take a little getting used to but the thing words fine unless you have two groups, which is where I am stuck now.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.