SumProduct vs GetPivotData

[Shred Dude]

General question regarding the use of Sumproduct as opposed to GetPivotData...

Any opinions out there on using one approach versus the other in a situation where you're creating a report driven from data brought into excel as a recordset from a database?

eg:

Dump Recordset to a PivotTable's Datasource range with copyfromrecordset
Refresh PivotTable
Retrieve data elements for your report with =getpivotdata(...)

vs>

Dump Recordset to a range with copyfromrecordset
Use Sumproduct against that range to retrieve data elements for your report


I'm playing with a scenario, and have achieved my report goals with both approaches. Just curious about best practice / preferences out there on which approach to use.

Or should I expect that I should be able to generate a recordset from my DB that won't require further manipulation in Excel and could be dumped straight to the report body? I know my DB design and SQL skills have a ways to go!

Thanks for any insights and opinions!

Shred

[Bob Phillips]

I haven't timed it, but my feeling would be that GetPivotData would be more efficient and probably more flexible here.

[Shred Dude]

As it turned out for me, GetPivotData won out. Since SumProduct always returns a number, and I was dealing with Times, a zero was giving me midnight values where I didn't need them, yet there were instances where midnight was correct, so I still needed to capture those. Wrapping GetPivotData in an IfError to throw off a "", worked well for my reporting needs.


Thanks as always for the input.