+ Reply to Thread
Results 1 to 5 of 5

Custom values in filter of pivot?

  1. #1
    Registered User
    Join Date
    07-28-2009
    Location
    Newport, Wales
    MS-Off Ver
    Excel 2003
    Posts
    15

    Custom values in filter of pivot?

    Hi there,

    Inherited some reports from another part of the business that heavily relies upon pivot tables. Unfortunately, I've NEVER had any experience with pivot tables and I'm stuck. The handover was brief and the previous owners are no more so I can't ask questions. Hopefully you can help.

    I'm a DBA / Cognos developer so understand the back end of this better than the what the spreadsheet is doing! I have a pivot table that obtains it's data from an MS Query query that connects to a SQL database. One of the fields it returns is called dateapp.

    In the pivot table fields, there is a filter called dateapp2 and dateapp is not used in the field list. The underlying table, there is only data in it > 01/01/2010 however, in the filter there is an entry reading "2005" and in addition dates from 01/01/2006 to 31/12/2009. This is proving a pain when trying to pull of YTD figures as I have select each date for 2010 seperately.

    So first question, what is dateapp2? A custom field? How do I find out what it is doing? i.e. is it manipulating the dateapp field at all?

    And secondly, where are the custom values coming from?



    I appreciate that an attachment would be extremely useful but unfortunately due to the nature of the business I work in I am unable to upload anything from my PC to the internet.

    If you need any further info to help you help me, please let me know


    Thanks in advance.
    Last edited by T4FF; 07-20-2010 at 11:36 AM. Reason: Solved

  2. #2
    Registered User
    Join Date
    07-28-2009
    Location
    Newport, Wales
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Custom values in filter of pivot?

    To add, this file is an xls and I'm using 2007

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Custom values in filter of pivot?

    Quote Originally Posted by T4FF
    So first question, what is dateapp2? A custom field? How do I find out what it is doing? i.e. is it manipulating the dateapp field at all?
    With the PT active ...

    Given the external source I would say the first thing to do would be to view the SQL to see if the dateapp2 field is created in the query.

    You can do this via the PT Options tab on the Ribbon -> Change Data Source -> Connection Properties -> Definition ... see Command Text box for SQL.

    If the field is not created in the query then to view the Calculated Field (again via Options tab in the Ribbon) Formulas -> List Formulas or use Calculated Field and select dateapp2 from the dropdown list.

    Quote Originally Posted by T4FF
    secondly, where are the custom values coming from?
    This is obviously going to be partly dependent upon how dateapp2 is being generated but in general terms PTs will by default cache prior member values.
    In 2007 you can disable this via PT Options Tab -> Options -> Data tab -> Number of Items to Retain per Field -> None.

    You should then find when you next refresh the PT cache the legacy items will have been purged.

  4. #4
    Registered User
    Join Date
    07-28-2009
    Location
    Newport, Wales
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Custom values in filter of pivot?

    Thanks for your help DO
    Quote Originally Posted by DonkeyOte View Post
    With the PT active ...
    Given the external source I would say the first thing to do would be to view the SQL to see if the dateapp2 field is created in the query.
    Sorry I should have been clear, the SQL query is effectively a Select *, no manipulation of the data.

    Quote Originally Posted by DonkeyOte View Post
    You can do this via the PT Options tab on the Ribbon -> Change Data Source -> Connection Properties -> Definition ... see Command Text box for SQL.
    Doing this effectively returns nothing. It brings out headings for Calculated Fields and Calculated Items but that's it - nothing listed but....

    Quote Originally Posted by DonkeyOte View Post
    This is obviously going to be partly dependent upon how dateapp2 is being generated but in general terms PTs will by default cache prior member values.
    In 2007 you can disable this via PT Options Tab -> Options -> Data tab -> Number of Items to Retain per Field -> None.
    Having changed this setting and refreshed the data, the field dateapp has disappeared!?!?! It is no longer available as a selection anywhere.

    I can't quite explain that one but presumably the cache had messed things up a little? I'll just amend it to use the dateapp field.

    Interested if any can offer an explanation of what just happened lol!!



    EDITED TO ADD: Just having a look at other fields where this is similar - is it something to do with grouping?
    Last edited by T4FF; 07-20-2010 at 11:26 AM.

  5. #5
    Registered User
    Join Date
    07-28-2009
    Location
    Newport, Wales
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Custom values in filter of pivot?

    Just answered my own question!!

    I've refreshed the data and a load [field name]2 columns have dropped off and the reason I suspected it was grouping is that all the decision reasons (i.e. accepted, rejected) have been replaced with codes (sorry I'm aware that this makes less sense without the spreadsheet).

    So to test, I selected a few codes and pressed group and sure enough it created decisionreason2.


    Thanks for your help on this without the cache setting I wouldn't have been able to correct this.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1