Could i get your help on some report please? Actually I have developed some report where I have used pivot table for getting Subtotals of each column by using the following code: It gives the Sum for each columns but the problem is that i'm also getting the Sum for the columns with % (Percentages), instead, i want to get Average or to get the correct %. E.g. in following output, under the column 'Paper cost %' , the paper cost % is the (Paper Cost / Total Sales * 100) which is correct for each line, but against the Subtotal it is also taking the Sum of the above 2 lines, if possible to have the same formula (Paper Cost / Total Sales * 100) just for this % column, or at least to get the Average so it will show 44 (30+58/2) instead of 88 (30+58).
Total Sales Paper Cost Paper cost %
13077 3884 30
10190 5866 58
Subtotal 23267 9750 88
I also tried using (Set pvf = gWO.AddDataField(pvt, papercostp, xlAverage) but it did not give the average value.
Here is the code, I need the changes for the last line 'papercostp'
Set pvf = gWO.AddRowField(pvt, bdnaam)
pvf.Subtotals(2) = True
Set pvf = gWO.AddRowField(pvt, aorder)
Set pvf = gWO.AddDataField(pvt, salesamount)
Set pvf = gWO.AddDataField(pvt, papercost)
Set pvf = gWO.AddDataField(pvt, papercostp)
I tried using (Set pvf = gWO.AddDataField(pvt, papercostp, xlAverage) but it did not give the average value.
Here is the code, I need the changes for the last line 'papercostp'
Set pvf = gWO.AddRowField(pvt, bdnaam)
pvf.Subtotals(2) = True
Set pvf = gWO.AddRowField(pvt, aorder)
Set pvf = gWO.AddDataField(pvt, salesamount)
Set pvf = gWO.AddDataField(pvt, papercost)
Set pvf = gWO.AddDataField(pvt, papercostp)
