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