locked
Pivottable Report Filter set to a non existing value RRS feed

  • Question

  • Hi,

    trying to set a Report Filter using vba (currentpage = non-existing-value) to a value not existing in the column range of the datasource (excel sheet) leads to an runtime error.

    Is there any smart way to work around this?

    Requirement is to show only those rows from the datasource that match a certain filter value, but as the datasource is populated dynamically it could be the case that it does not contain this specific value. In this case the pivot table should show "no rows".

    A neat feature would be the possibility to set the filter to (None) (similar to (ALL)).

    May be one can restrict/filter the datasource values itself but it is used by several PivotTables also.

    A possible way might be to change the Report-Filter to a Row-Filter and use a Label-Filter like below:

    PivotTables(1).PivotFields("a").PivotFilters.Add Type:=xlCaptionEquals, Value1:=non-existing-value

    But in this case the structure of the Pivottable would change, that should be avoided if possible.

    Any ideas?

    Thanks in advance

    Regards

    chris

    Thursday, October 15, 2015 2:49 PM

Answers

  • Hi,

    I don't find a better way to filter with non existing value.

    Could you provide the detail steps and same data of how the structure changes?

    Regards

    Starain 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by chris_vba Tuesday, October 27, 2015 10:25 AM
    Tuesday, October 27, 2015 2:09 AM

All replies

  • Get around the error by making sure that the value exists first:

    If Application.Countif(Worksheets("Data source").Range("A:A"),"Possible Value") = 0 Then

    'What to do if your values does not exist

    Else

    'filter the pivot table since the value exists

    End if


    Thursday, October 15, 2015 4:38 PM
  • Hi,

    thanks for your answer.

    To handle the error is not the problem but as written the requirement is

    "Requirement is to show only those rows from the datasource that match a certain filter value, but as the datasource is populated dynamically it could be the case that it does not contain this specific value. In this case the pivot table should show "no rows"."

    The only solution i found for this is to change the report filter to a row filter and apply an labl- or value-filter.

    Question is if there is any way to get the desired result using a report filter also.

    Regards

    Chris

    Monday, October 19, 2015 8:29 AM
  • "a value not existing in the column range of the datasource (excel sheet) leads to an runtime error.

    Is there any smart way to work around this?"

    That was the question I was answering.

    If you want to show "No Rows" then that needs to be a value in that column of your default table.  You could add the value to the table through code, normally hide it, and show it only if the desired value does not exist.


    Monday, October 19, 2015 12:24 PM
  • Thanks for your answer.

    Unfortunatly answering only to parts of the question isnt helpful at all.

    If you had reached the third paragraph

    "A neat feature would be the possibility to set the filter to (None) (similar to (ALL))."

    you hadn't taken "no rows" literally perhaps.

    Tuesday, October 20, 2015 7:14 AM
  • A pivot table must have at least one value selected - so your idea to have None as an option does not work. 
    Tuesday, October 20, 2015 4:18 PM
  • I cant confirm your statement.

    One could easily define a label-/value Row-Filter to exclude all values that leads to an "emtpy" pivot table.

    Wednesday, October 21, 2015 8:30 AM
  • "I cant confirm your statement."

    Try manually setting the filter to have no values - Excel does not allow that.

    Wednesday, October 21, 2015 11:25 AM
  • Your statement was "A pivot table must have at least one value selected".

    In fact it seems that you ment "A report filter must have at least one value selected" i guess.

    Thursday, October 22, 2015 8:52 AM
  • We were talking about filters: thus, my statement that a pivot table needs to have at least one value selected was actually about Excel pivot table filters, whether they are page, row, or column filters.
    Thursday, October 22, 2015 12:36 PM
  • Then your statement doesnt hold.

    Assume 0,1 being potentilonally in the source column data for some row-filter.

    Now you declare a label filter on it with label = 1.

    That will still work even there is no 1 in the souce data for that "column".

    Then the pivot table wil show no rows.

    Friday, October 23, 2015 9:15 AM
  • Hi chris_vba,

    Base on my test with label filter, yes, it will display no rows. How the structure of PivotTable be changed for this way?

    On the other hand, how about capture the error and resume remain code (On Error resume next)?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Monday, October 26, 2015 1:55 AM
  • Thanks for your answer.

    If i understand your first question correctly you adress the structure change i mentioned in my original post. What i ment is that actually i have a Report Filter on top of the sheet. The specific row is set to invisible.

    If i change the report filter to a row filter it will be part of the pivottable itself.

    Well, still the column could be set to invisible.

    That's what from my point of view is actually the most approbiate and robust approach.

    Skipping the error, as long as i dont miss something here, seems not to be an option as at the end i have to show a "filtered" pivottable showing no rows anyway.

    One option might be to manipulate the source data, but i dont like this much for some reasons.

    Firstly there are several pivot tables build on the same source data. Secondly i prefer the row filter in this case because of its robustness. Just a pivottable configuration (in this case always the same filter value is wanted).

    In fact i was some kind of surprised of the, let's say degrees of freedom, the vba pivot api seems to offer but i fact doesnt. (The documentation is sparse to say the least).

    So the main aim of my question here was to find out if i missed some clever but simple trick here.


    • Edited by chris_vba Monday, October 26, 2015 10:19 AM
    Monday, October 26, 2015 10:18 AM
  • Hi,

    I don't find a better way to filter with non existing value.

    Could you provide the detail steps and same data of how the structure changes?

    Regards

    Starain 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by chris_vba Tuesday, October 27, 2015 10:25 AM
    Tuesday, October 27, 2015 2:09 AM
  • I don't find a better way to filter with non existing value.

    Thanks for your answer.

    On the structure change: Changing a report filter to a row filter adds a additional column to the pivot table, doesnt it?

    However, the thread could be closed as there is no further developement towards a programmtic vba based solution like adding the desired filter to a pivot field regardless of source data and filter type.

    Tuesday, October 27, 2015 10:25 AM