Creating a Pivot table template (No data), adding filters

[CascadeDiver]

Hi all.

I'm creating a pivot table template on a dashboard withing a blank "Master" file. I would like to apply specific filters to one of the tables. However in this blank file there is no data so the pivot table does not see anything to filter, thus prohibiting me from applying filters because they don't yet exist. The column of data I am looking to filter is a hidden helper column in the data source with TRUE/FALSE. I only need to see the TRUE. I can very easily filter for TRUE once the data is in the file but per company request I need to have that area locked down as I won't be the end user.

Any suggestions?

 

[mrshl9898]

That's an interesting situation.

Can you just add in 1 row of dummy data?

xxx xxxxxxx xxxx TRUE


If not, could use VBA I suppose to apply the filter/s

Sub ReportFiltering_Single()
'PURPOSE: Filter on a single item with the Report Filter field
'SOURCE: www.TheSpreadsheetGuru.com

Dim pf As PivotField

Set pf = ActiveSheet.PivotTables("PivotTable2").PivotFields("Fiscal_Year")

'Clear Out Any Previous Filtering
  pf.ClearAllFilters

'Filter on 2014 items
  pf.CurrentPage = "TRUE"

End Sub

 

[Fazza]

maybe simplest would be to add a query that makes a copy of the data, except it is filtered - "WHERE helper = TRUE" (& may as well omit the field in the result set)
then for this special pivot table, use that as the data source
then be sure to refresh this query before refreshing the pivot table
if there is a lot of data and you don't need the full detail level in this query (that is to say in this pivot table), optionally also aggregate the data within this query
this could be on a worksheet not visible to users

another way, a bit neater, is to filter the dataset when creating the pivot table. this way the pivot table only uses the filtered dataset,
SELECT all the wanted fields (so excluding the helper field)
FROM sourcetable
WHERE helper = TRUE

know that whatever you're doing, lockdown to hide some data is not secure

 

[CascadeDiver]

Thanks for the ideas. I'll give them a try and see which flows the best. I was thinking it might be easiest to apply filters when the table refreshes.

    'Set Filters
    With RawData.PivotFields("True/False")
    On Error Resume Next
        .PivotItems("FALSE").Visible = False
        .PivotItems("TRUE").Visible = True
    End With

Adding On Error Resume Next that way initially when there is no data it will skip over this. I could probably wrap this in an If statement. If we have data apply filters, Else, End If.

Thoughts?