locked
PowerPivot or regular Pivot Tables ? RRS feed

  • Question

  • Hi,

    I'm trying to make a decision about the method of work I'm going to use with Excel and my database.

    I'm building a large Excel file that should get data from my database for daily use.

    I set an option in the Excel to refresh the data using VBA code that should refresh the pivot tables.

    The refresh method should take as a parameter the current date, hence, I should pass a date parameter to queries for the pivot tables.

    My doubt is about whether to use PowerPivot tables for the data, or to use regular pivot tables that will use stored procedures with parameters.

    Are PowerPivot tables capable of being refreshed through VBA ? with parameters ? I already know the kind of work with regular pivot tables that use stored procedures with parameters.

    Thanks!

    Saturday, September 29, 2012 10:55 PM

Answers

  • What you gain with VBA refresh of pivot tables against a stored proc is the ability to custom code your queries and display configurations without the need of a 'middle layer' like PowerPivot.

    However, that 'middle layer' gives you so much functionality that I cannot think of many cases in which the pure VBA approach will offer you an advantage.

    Taking user input can be done ,in PowerPivot, using many distinct approaches - for example: 

    - a slicer selection (the easiest way)

    - linked tables

    - VBA refresh of the pivot table. (but unlike your approach, the data can come from the PowerPivot model and not from a stored procedure)

    - an SSRS report off the PowerPivot model in SharePoint





    Javier Guillen
    http://javierguillen.wordpress.com/



    Monday, October 1, 2012 1:24 PM
    Answerer

All replies

  • In PowerPivot, you can load *all dates* and then dynamically calculate what the most current date is (for example).  With this, you can then have the pivot table display that only for that date.  This will change as your refresh data on a daily basis.  Is this what you are looking for?



    Javier Guillen
    http://javierguillen.wordpress.com/

    Sunday, September 30, 2012 12:09 AM
    Answerer
  • I would like to get the wanted date from the user, and then to provide the data according to it.

    what about refreshing the pivot tables from VBA? Is regular pivot tables are better choice than PowerPivot ?

    Sunday, September 30, 2012 1:54 AM
  • What you gain with VBA refresh of pivot tables against a stored proc is the ability to custom code your queries and display configurations without the need of a 'middle layer' like PowerPivot.

    However, that 'middle layer' gives you so much functionality that I cannot think of many cases in which the pure VBA approach will offer you an advantage.

    Taking user input can be done ,in PowerPivot, using many distinct approaches - for example: 

    - a slicer selection (the easiest way)

    - linked tables

    - VBA refresh of the pivot table. (but unlike your approach, the data can come from the PowerPivot model and not from a stored procedure)

    - an SSRS report off the PowerPivot model in SharePoint





    Javier Guillen
    http://javierguillen.wordpress.com/



    Monday, October 1, 2012 1:24 PM
    Answerer