interesting pivot table questing

[linqlin]

let us consider the following access table
mod Region rev qty
020 china 20 10
023 india 30 10
020 japan 40 8
020 china 10 2
(database table)

, and we define the query: SELECT [mod], Region, Sum
(rev) AS srev, Sum(qty) AS sqty, srev/sqty AS cru
FROM Table1 GROUP BY [mod], Region; we then use the pivot
table to get CRU , we get the following pivot table result:
regionAll)
mod 020
Data
sum of srev sum of sqty sum of CRU
total 70 20 7.5



the cru is 7.5, which is not correct, if we defined a
calculated item called CRU(srev/sqty), then the cru is
3.5, which is
correct. However, if we define the region to be china, the
result returned by pivot table using database query is
correct. and can be seen as follows in pivot table :
regionAll)
mod 020
Data
sum of srev sum of sqty sum of CRU
total 30 12 2.5

The reason is that CRU can't be aggregated. so how to
design the datbase query in the pivot table, when user
aggragates region, mod or
whatever, we can still return the cru correctly.
..

 

[Simon Murphy]

Why not point your pivot table straight at the table and
use a calculated field in the pivot?
cheers
Simon