none
Implementing SmartPivot style list filter for Excel Pivot Table RRS feed

  • Question

  • Hi experts,

    As a learning project as well as a useful tool for work, I'd like to achieve the following requirement:

    Implement an Excel Add-In with the sole purpose to provide users the ability to use a list to filter a field of a pivot table.

    Details:
    1) A demonstration of same functionality can be viewed here:

    https://www.youtube.com/watch?v=mI68oC1431M (From 03:58)

    2) It should be implemented as an Excel Add-In with its own menu and button (Same as in the video)

    3) It should be implemented with C++ or C#. I prefer C++ but from my understanding it's much easier to communicate with MS Office with C# and I'm willing to learn it.

    4) Said functionality should be available to Pivot Tables generated from the following sources:

    From a range of data / From SSAS Cube / From Excel named Table / From Power Pivot Data Models

    5) User is able to click the Add-In button, see a dialog box with the following controls: (Again similar as in the video)

        - "Field" menu to select the field to apply the list

        - "Filters" text box to accept variable inputs. Let's start small and only accept two kinds of inputs:

           i) one column of copy-pasted Excel ranges

           ii) Manually entered list with line-break as delimiter

        - "Apply" button to apply the filters; "Cancel" button to close the dialog box without any change applied.



    Since I'm not familiar with either C# or Office dev, I'd like to get some discussion with you:

    1) What kind of tutorials / books I should read? I'm searching online but not able to find specific tutorials to do this. I got a lot of elementary + general VSTO tutorials though. I also know there are some good books for VSTO but they are kind of old.

    2) What tech should I use? I know VSTO may be a better option but I also know I can use C++ with COM.

    3) (Optional) If I insist to use C++ instead of C#, what is my best approach?

    Thank you for reading my long post!

    Friday, November 2, 2018 8:51 PM

All replies

  • Hi Markus,

    << 1) What kind of tutorials / books I should read? I'm searching online but not able to find specific tutorials to do this. I got a lot of elementary + general VSTO tutorials though. I also know there are some good books for VSTO but they are kind of old.

    You could read some of VSTO's articles on Ribbon XML, as well as articles on SSAs cube/excel named Table/power Pivot data. For example, the following links:

    2.2.27 menu (Menu with Title)
    2.2.29 menu (Dynamic Menu Root XML Element)


    << 2) What tech should I use? I know VSTO may be a better option but I also know I can use C++ with COM.

    You could use VSTO, which is a good choice.

    For the differences between VSTO and COM, please refer to the following link:

    What is the difference between a COM Add in and a VSTO Add in


    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Monday, November 5, 2018 9:50 AM
    Moderator
  • Hi Bruce, thanks for the help!

    I read somewhere that to manipulate pivot tables I need COM instead of VSTO. I also found that one OLAP plug in actually performs the exact job, and I;m going to read its source code whence I'm better at C#.

    Tuesday, November 6, 2018 6:55 PM
  • Hi Markus,

    It is glad to hear that you have found the solution and thanks for sharing it with us. 

    If you have any question, or update, please feel free to let me know. 

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, November 9, 2018 2:28 AM
    Moderator