Crosstab adding a non summarised field

[thefox149]

I have a cross tab that has summarised fields I wish to add another column of data however I don't want this to be summarised is this possible?

2005 Sumof$$ 5( not summarised)
2006 sumof$$ 7 (not summarised)

 

[lbass]

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?

-LB

 

[thefox149]

I can't use min/max as it may change

Here is an example of the current xtab

Month (Avg)
Jan05 14.5
Feb05 15.8
Mar05 13.6

What I wish to do is put in the staff count which has already been calculated in my intial query and insert it into my xtab

Month (Avg) Staff Vol
Jan05 14.5 6
Feb05 15.8 7
Mar05 13.6 6.5

I have the staff value I just want to put it in the xtab without summarising it

 

[lbass]

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.

-LB

 

[shakespeare5677]

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.

Good luck.

Max