hi all,

im trying to create a datafield for pivot table in vba, which is
sumproduct from two other fields..... so far, i have coded as shown
below:

Code:


strDataField = "No_Of_Units * Unit_Value"
pivot.CalculatedFields.Add "TotalUnitValue", strDataField, True
Set pv = pivot.PivotFields("TotalUnitValue")
pv.Orientation = xlDataField
pv.Position = 1

however, it calculates the product of sum of each fields, instead of
the sum of the product of each item in the field (the underlying
data)....

say i have:
unit value, no_of_units
1,2
2,3
3,4

what i wanted is sum of (1*2) + (2*3) + (3*4).
but what that piece of code gave me was (1+2+3) *(2*3*4)

anyone help is greatly appreciated!

-ted-

You can add a column to the source data, if it's an Excel database, and
do the row calculations there. Then, add the new field to the pivot
table, and you'll get the correct total.

tedy wrote:
hi all,

im trying to create a datafield for pivot table in vba, which is
sumproduct from two other fields..... so far, i have coded as shown
below:

Code:


strDataField = "No_Of_Units * Unit_Value"
pivot.CalculatedFields.Add "TotalUnitValue", strDataField, True
Set pv = pivot.PivotFields("TotalUnitValue")
pv.Orientation = xlDataField
pv.Position = 1

however, it calculates the product of sum of each fields, instead of
the sum of the product of each item in the field (the underlying
data)....

say i have:
unit value, no_of_units
1,2
2,3
3,4

what i wanted is sum of (1*2) + (2*3) + (3*4).
but what that piece of code gave me was (1+2+3) *(2*3*4)

anyone help is greatly appreciated!

-ted-



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Debra,

Thanks. My source data is an Access database, and i added a column in
the sql statement and add it as the new field. and it works great!!

i have another problem,

one of the field in the access database contains both positive and
negative numbers.

when i create the pivot table, it actually sums all of them together.

is there anyway, where i can separate the sum of all negative numbers
and the sum of all positive numbers as separate data fields for the
pivot tables?

thanks!

You could add another couple of calculations to your Access query. For
example:

QtyPos: IIf([Qty]=0,[Qty],Null)

QtyNeg: IIf([Qty]<0,[Qty],Null)

tedy wrote:
Hi Debra,

Thanks. My source data is an Access database, and i added a column in
the sql statement and add it as the new field. and it works great!!

i have another problem,

one of the field in the access database contains both positive and
negative numbers.

when i create the pivot table, it actually sums all of them together.

is there anyway, where i can separate the sum of all negative numbers
and the sum of all positive numbers as separate data fields for the
pivot tables?

thanks!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi again Debra,

I don't think i quite follow you.

My current sql statement is:

sqlStr = "Select *, Investments.Unit_Value*Purchases.No_Of_Units as " &
_
"TotalUnitValue FROM Agents Agents, Customers Customers, " & _
"Managers Managers, Investments Investments, Purchases
Purchases " & _
"WHERE (Agents.Agent_No = Customers.Agent_No AND " & _
"Investments.Investment_No = Purchases.Investment_No AND " & _
"Investments.Manager_No = Managers.Manager_No AND " & _
"Customers.Cust_No = Purchases.Customer_No)"

and i don't know how to add your suggestion to this sqlStr.

would you be able to elaborate please??

thank in advance...im really stressing out for this


cheers
tedy

p/s fyi, this is the sub i'm working on to create the pivot table:

Private Sub GeneratePivot(aRange As Range)

'This subroutine is used to generate the pivot table. It takes the
range
'as an argument to place the pivot table

'variable declaration
Dim activePage As MSForms.Page
Dim refComboBox As MSForms.ComboBox
Dim dataNo As Integer
Dim rsData As ADODB.Recordset
Dim sqlStr As String
Dim strTable As String
Dim strPrimaryKey As String
Dim fillRange As Range
Dim objPivotCache As PivotCache
Dim pivot As PivotTable
Dim pv As PivotField
Dim strDataField As String
Set fillRange = aRange.Cells(3, 1)

'query preparation
On Error GoTo ErrorHandler
Set rsData = New ADODB.Recordset
sqlStr = "Select *, Investments.Unit_Value*Purchases.No_Of_Units as
" & _
"TotalUnitValue FROM Agents Agents, Customers Customers,
" & _
"Managers Managers, Investments Investments, Purchases
Purchases " & _
"WHERE (Agents.Agent_No = Customers.Agent_No AND " & _
"Investments.Investment_No = Purchases.Investment_No AND
" & _
"Investments.Manager_No = Managers.Manager_No AND " & _
"Customers.Cust_No = Purchases.Customer_No)"


'Connect
rsData.Open sqlStr, con, adOpenStatic

'set the pivot cache object with external data source
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)

'assign the recordset to the pivot cache
Set objPivotCache.Recordset = rsData

'set the pivot table
Set pivot =
objPivotCache.CreatePivotTable(TableDestination:=f illRange)
pivot.SmallGrid = False

'set the pivot row field with the value from the user selection
Set pv = pivot.PivotFields(Me.lbRowFields.Value)
pv.Orientation = xlRowField
pv.Position = 1

'set the pivot column field with the value from the user selection
Set pv = pivot.PivotFields(Me.lbColumnFields.Value)
pv.Orientation = xlColumnField
pv.Position = 1


'if the user wants to display no of units, then assign it as
datafield
If Me.optNoOfUnitsNet.Value Then
strDataField = "No_Of_Units"
Set pv = pivot.PivotFields(strDataField)
pv.Orientation = xlDataField
pv.Position = 1
End If

'if the user wants to display total value, then assign it as
datafield
If Me.optTotalValue.Value Then
strDataField = "TotalUnitValue"
Set pv = pivot.PivotFields(strDataField)
pv.Orientation = xlDataField
pv.NumberFormat = "$#,##0.00"
pv.Position = 1
End If

'if the user also selects the pagefield, then set the pagefield
If Me.lbPageFields.Value < "NONE" Then
Set pv = pivot.PivotFields(Me.lbPageFields.Value)
pv.Orientation = xlPageField
pv.Position = 1
' End With
End If

'check if the recordset contains data
If rsData.EOF Then
MsgBox "No data exists!", vbExclamation, "Information"
rsData.Close
Set rsData = Nothing
Exit Sub
End If

'Closing peek
rsData.Close
Set rsData = Nothing

'close the pivot table command bar
Application.CommandBars("PivotTable").Visible = False

Exit Sub

ErrorHandler:

'On Error, exit sub
Exit Sub

End Sub

just additional info for my last post:

it might be quite confusing why i wanted to separate the positive no
and negative no as separate datafields if you are just looking at my
sub to create the pivot table....

the sub code i posted earlier basically only takes two choices:
1. no of units as datafield OR
2. total unit value as datafield

but i want to change it to give the user three options (which will be
reflected in the userform as well):
1. no of units as datafield (this is the net value) OR
2. positive no of units as datafield and negative no of units (these
are the gross value) as datafield OR
3. total unit value as datafield

thanks again!

Try something like this:

sqlstr = "Select *, " _
& "IIf([Purchases.No_Of_Units]=0,[Purchases.No_Of_Units],Null) " _
& "AS QtyPos, IIf([Purchases.No_Of_Units]<0," _
& "[Purchases.No_Of_Units],Null) AS QtyNeg, " _
& "Investments.Unit_Value*Purchases.No_Of_Units as " _
& "TotalUnitValue FROM Agents Agents, Customers Customers, " & _
"Managers Managers, Investments Investments, Purchases Purchases " & _
"WHERE (Agents.Agent_No = Customers.Agent_No AND " & _
"Investments.Investment_No = Purchases.Investment_No AND " & _
"Investments.Manager_No = Managers.Manager_No AND " & _
"Customers.Cust_No = Purchases.Customer_No);"


tedy wrote:
Hi again Debra,

I don't think i quite follow you.

My current sql statement is:

sqlStr = "Select *, Investments.Unit_Value*Purchases.No_Of_Units as " &
_
"TotalUnitValue FROM Agents Agents, Customers Customers, " & _
"Managers Managers, Investments Investments, Purchases
Purchases " & _
"WHERE (Agents.Agent_No = Customers.Agent_No AND " & _
"Investments.Investment_No = Purchases.Investment_No AND " & _
"Investments.Manager_No = Managers.Manager_No AND " & _
"Customers.Cust_No = Purchases.Customer_No)"

and i don't know how to add your suggestion to this sqlStr.

would you be able to elaborate please??

thank in advance...im really stressing out for this


cheers
tedy

p/s fyi, this is the sub i'm working on to create the pivot table:

Private Sub GeneratePivot(aRange As Range)

'This subroutine is used to generate the pivot table. It takes the
range
'as an argument to place the pivot table

'variable declaration
Dim activePage As MSForms.Page
Dim refComboBox As MSForms.ComboBox
Dim dataNo As Integer
Dim rsData As ADODB.Recordset
Dim sqlStr As String
Dim strTable As String
Dim strPrimaryKey As String
Dim fillRange As Range
Dim objPivotCache As PivotCache
Dim pivot As PivotTable
Dim pv As PivotField
Dim strDataField As String
Set fillRange = aRange.Cells(3, 1)

'query preparation
On Error GoTo ErrorHandler
Set rsData = New ADODB.Recordset
sqlStr = "Select *, Investments.Unit_Value*Purchases.No_Of_Units as
" & _
"TotalUnitValue FROM Agents Agents, Customers Customers,
" & _
"Managers Managers, Investments Investments, Purchases
Purchases " & _
"WHERE (Agents.Agent_No = Customers.Agent_No AND " & _
"Investments.Investment_No = Purchases.Investment_No AND
" & _
"Investments.Manager_No = Managers.Manager_No AND " & _
"Customers.Cust_No = Purchases.Customer_No)"


'Connect
rsData.Open sqlStr, con, adOpenStatic

'set the pivot cache object with external data source
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)

'assign the recordset to the pivot cache
Set objPivotCache.Recordset = rsData

'set the pivot table
Set pivot =
objPivotCache.CreatePivotTable(TableDestination:=f illRange)
pivot.SmallGrid = False

'set the pivot row field with the value from the user selection
Set pv = pivot.PivotFields(Me.lbRowFields.Value)
pv.Orientation = xlRowField
pv.Position = 1

'set the pivot column field with the value from the user selection
Set pv = pivot.PivotFields(Me.lbColumnFields.Value)
pv.Orientation = xlColumnField
pv.Position = 1


'if the user wants to display no of units, then assign it as
datafield
If Me.optNoOfUnitsNet.Value Then
strDataField = "No_Of_Units"
Set pv = pivot.PivotFields(strDataField)
pv.Orientation = xlDataField
pv.Position = 1
End If

'if the user wants to display total value, then assign it as
datafield
If Me.optTotalValue.Value Then
strDataField = "TotalUnitValue"
Set pv = pivot.PivotFields(strDataField)
pv.Orientation = xlDataField
pv.NumberFormat = "$#,##0.00"
pv.Position = 1
End If

'if the user also selects the pagefield, then set the pagefield
If Me.lbPageFields.Value < "NONE" Then
Set pv = pivot.PivotFields(Me.lbPageFields.Value)
pv.Orientation = xlPageField
pv.Position = 1
' End With
End If

'check if the recordset contains data
If rsData.EOF Then
MsgBox "No data exists!", vbExclamation, "Information"
rsData.Close
Set rsData = Nothing
Exit Sub
End If

'Closing peek
rsData.Close
Set rsData = Nothing

'close the pivot table command bar
Application.CommandBars("PivotTable").Visible = False

Exit Sub

ErrorHandler:

'On Error, exit sub
Exit Sub

End Sub



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Debra,

Thanks!! it works.... i just have to modify the pivot table because the
QtyPos and QtyNeg if passed as it is, will return a Count function in
the pivot rather than Sum....

Otherwise, the sql statement works really great!

Thank you so much. You are a legend!

You're welcome!
You should be able to change the summary function from Count to Sum, in
the Pivot Table. If not, in the formulas, you can change the Null to a zero.

tedy wrote:
Hi Debra,

Thanks!! it works.... i just have to modify the pivot table because the
QtyPos and QtyNeg if passed as it is, will return a Count function in
the pivot rather than Sum....

Otherwise, the sql statement works really great!

Thank you so much. You are a legend!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html