Grand Total for a Field Outside of Grouping Level (1 Viewer)

[LimAccess]

I have created a report to show a company's monthly throughput based off a crosstab query as follows:

TRANSFORM Nz(Sum(tbl_Throughput))+0 AS SumOfUnitsSold
SELECT tbl_Throughput, tbl_Throughput.ProductType, tbl_Throughput.State
FROM tbl_Throughput
GROUP BY tbl_Throughput.Customer, tbl_Throughput.ProductType, tbl_Throughput.State
PIVOT tbl_Throughput.[Throughput_Year];

I want to create a calculation in the report, which shows market share for each customer for each product type. To do this I need to generate a grand total for product type outside of the grouping of customer.

E.g.
Product 1
5,000

Product 2
10,000

However, because I have put customer as the first grouping level and then product type as the second grouping level, whenever I create a total of product type, the totals are grouped per customer, with no grand total except the total number of units sold at the bottom of the report.

E.g.
Product 1
Customer A
2,500
Customer B
2,500

Product 3
Customer A
5,000
Customer B
5,000

Grand total: 20,000

How can I create a grand total per product type?

 

[MarkK]

Use DSum() or write a query that returns the exact data you need, and open a recordset. You can do this in a report, but completely independently of the report's recordsource . . .

private m_productTotalMarket as single

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
[COLOR="Green"]    'product type header, 
    'we calculate the total market size here so we can calculate market share later
[/COLOR]    m_productTotalMarket = DSum("Units", "tblThroughput", "ProductType = " & me.ProductType)
[COLOR="Green"]    'this code runs once for each product type[/COLOR]
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
[COLOR="Green"]    'calculate the market share as  . . .
[/COLOR]    me.tbMarketShare = me.UnitsSold / m_productTotalMarket 
End Sub