Announcement

Collapse
No announcement yet.

Filter For Data Items In PivotTable

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Filter For Data Items In PivotTable



    Hello everyyone,
    I want to import data from an access database into excel using the 'get external data' wizard. The data is then displayed in a pivottable. The row fields are dates, the column fields are different investment funds. There are two kinds of performance figures: Return-on-investment (ROI) and Assets-under-Management (AUM). I want to have a page field (report filter) where I can select EITHER ROI or AUM to be displayed. Currently, moving ROI or AUM fields to the report filter area only allows me to select between different datapoints (note here that ROI and AUM are data items). In essence, I need a filter that allows me to select between two value types: ROI or AUM. Can anyone help?

  • #2
    Re: Filter For Different Dataitems In Pivottable

    I include an example file that shows the 2 separate pivottables I can create (assume that raw_data contains the entire dataset). I want to combine the 2 pivottables from sheet 'pivot' into one with a page field that allows me to filter either for ROI or AUM
    Attached Files

    Comment


    • #3
      Re: Filter For Different Dataitems In Pivottable

      fboehlandt, please post the price you are offering else these posts will be deleted.

      Comment


      • #4
        Re: Filter For Different Dataitems In Pivottable

        Hi,

        A small script would do this. If you are interested, let me know with the offer price.
        Kris

        ExcelFox

        Comment


        • #5
          Re: Filter For Data Items In PivotTable

          Hi everyone,
          this issue has been resolved. Thanks!

          Comment


          • #6
            Re: Filter For Data Items In PivotTable

            So why not share the answer?

            Comment


            • #7
              Re: Filter For Data Items In PivotTable

              In the command box of the data import wizzard one can enter a union query. It might be necessary to establish the connection first and then copy-paste the query (alternatively, one can use VBA to establish the command string). The query looks as follows:

              Code:
              SELECT [ID], [Fund], [MM_DD_YYYY], [Performance] , 'ROI' AS [Type]
              FROM `CC:\path\accessdb`.`RETURNS`
              UNION
              SELECT [ID], [Fund], [MM_DD_YYYY], [Assets_Managed], 'AUM' As [Type]
              FROM `C:\path\accessdb`.`ASSETS`
              The trick here is not to run a select but rather a union query with a dummy column denoting what performance figure is being displayed. This column then makes the page field of the pivottable. Note, however, that excel will not give you the option to import a union query using the data import wizard if you run it in access first. The best solution is to write a short vba script.
              cheers

              Comment


              • #8


                Re: Filter For Data Items In PivotTable

                Thanks.

                Comment

                Working...
                X