Consulting

Results 1 to 3 of 3

Thread: SumProduct vs GetPivotData

  1. #1

    SumProduct vs GetPivotData

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    I haven't timed it, but my feeling would be that GetPivotData would be more efficient and probably more flexible here.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •