Select Filters(PivotItems) in PivotTable Filters

[pkarunkar]

In continuation with Issue #519 I tried to add PivotFilter and it keeps saying the WARNING but never adds the filter. . Please check on this.

$data = ConvertFrom-Csv @"
Region,Date,Fruit,Sold
North,1/1/2017,Pears,50
South,1/1/2017,Pears,150
East,4/1/2017,Grapes,100
West,7/1/2017,Bananas,150
South,10/1/2017,Apples,200
North,1/1/2018,Pears,100
East,4/1/2018,Grapes,200
West,7/1/2018,Bananas,300
South,10/1/2018,Apples,400
"@ | Select-Object -Property Region, @{n = "Date"; e = {[datetime]::ParseExact($_.Date, "M/d/yyyy", (Get-Culture))}}, Fruit, Sold

$xlfile = "C:\Users\PavithrasagarK\Desktop\Newfolder\multiplePivotTables2.xlsx"
#Remove-Item $xlfile -ErrorAction SilentlyContinue
$data | Export-Excel $xlfile -AutoSize -TableName FruitData
$excel = Open-ExcelPackage $xlfile 

$pivotTableParams = @{
    PivotTableName  = "ByRegion"
    Address         = $excel.Sheet1.cells["F1"]
    SourceWorkSheet = $excel.Sheet1
    PivotRows       = echo  Date Region
    PivotFilter     = echo Fruit 
    PivotData       = @{'sold' = 'sum'}
    PivotTableStyle = 'Light21'
    #GroupDateRow    = "Date"
    ##GroupDatePart   = echo Years Quarters
}

Add-PivotTable @pivotTableParams
Close-ExcelPackage $excel -Show

Warning:
WARNING: Could not add 'Fruit' to Filter/Page fields in PivotTable ByRegion.

[pkarunkar]

Okay.. I got the Filter added by using the below property. Yet not sure how to set the filter values.

$pt.MultipleFieldFilters = $true

[pkarunkar]

This works in EXCEL COM OBJECT.

function with {
 param(
  [Parameter(Mandatory = $true, Position = 0, ValueFromPipeLine = $true)]
  [Object]$Object,
 [Parameter(Mandatory = $true, Position = 1)]
  [String]$Block
 )
 begin {
  $code = $Block -replace '(?m)^\s*(?=\.)', '$Object'
 }
 process {
  [ScriptBlock]::Create($code).Invoke()
 }
}

$ex = New-Object -ComObject Excel.Application 
$wb = $ex.Workbooks.Open("C:\Users\PavithrasagarK\Desktop\Newfolder\multiplePivotTables2.xlsx")
$sh = $wb.Sheets.Item("Sheet2")
$pivot = $sh.PivotTables("ByRegion2").PivotFields("Fruit") 
$pivot.CurrentPage = "(All)"
$pivot.EnableMultiplePageItems = $True
With $pivot @'
        .PivotItems("Bananas").Visible = $False
        .PivotItems("Apples").Visible = $False
'@

$wb.Save()
$wb.Close()
$ex.Quit()

[dfinke]

If the Pivot Table is created on a separate sheet, do you get the same pivot filter warning?

[pkarunkar]

@dfinke : Yes it is throwing the Warning irrespective of the Sheet. We have to mandatory use MutipleFieldFilters Key as true to avoid that warning go away and make the Filter Visible in excel ($pt.MultipleFieldFilters = $true ). So you may consider making the $pt.MultipleFieldFilters Key as true when someone uses PivotFilter in the Params.

Also, Second thing is. How to Select the PivotItems from the filter... I was able to to do it in Com Object as mentioned in comment above. ?? Looks like EPPlus does not have PivotItems Method.

Let me know if you want me to open a seperate Issue for the second question.

[jhoneill]

    PivotRows       = echo  Date Region
    PivotFilter     = echo Fruit 

@dfinke did I see this in one of the examples somewhere , it seems a fairly baroque why of doing

    PivotRows       = 'Date', 'Region'
    PivotFilter     = `Fruit' 

Perhaps on when working with a keyboard where " and ' aren't available ? :-)

@pkarunkar

Could you please do get-help Add-PivotTable -Parameter Address

Your example works by changing cell F1 to F3

:-)

[pkarunkar]

@jhoneill Great .. yes, it works with F3 as cell address.

I want to be able to Uncheck the PivotItems from Filter like Com object does.

With $pivot @'
        .PivotItems("Bananas").Visible = $False
        .PivotItems("Apples").Visible = $False
'@

Isn't this available in EPPlus?? I couldnt see a mehod PivotItems popping up :)

I know I could have used Foreach inplace of WITH.. :) but it was easy to copy paste the function. one of those LAZY Admins Kind.

[jhoneill]

It isn't. The pivot table doesn't exist until you open the file in Excel; EEPlus only writes the specification for how the pivot table should be built.

[pkarunkar]

Okies then. No worries. I can live with Com object to get that done. As usual, Thank you Very much. Closing this request.

[dfinke]

baroque

@jhoneill

Of, relating to, or characteristic of a style in art and architecture developed in Europe from the early 17th to mid-18th century, emphasizing dramatic, often strained effect and typified by bold, curving forms, elaborate ornamentation, and overall balance of disparate parts.

I like the word art. :)

Yeah, I don't use that for a single string.

$list = echo fruit region data other $list = 'fruit','region','data','other'

The second line is 11 more keystrokes, and ripe for fat fingering. Usually I use Expand Alias in vs code to turn echo to Write-Output.

[jhoneill]

I think it was one of my university professors who called a particular kind of coding "Baroque"

It's good for a cmd line ... but in a script it may flummox* others. There was something else I saw, and it might have been you or someone else

this: if ($x = 1) is almost always a bug, the writer sub-vocalized "x equals one" but meant if ($x -eq 1) What they wrote assigned 1 to X, the assignment operation gives a value (1) which evalutes as boolean true and the condtion always runs (and they've hosed what was in $x two bugs for the price of one). If they wrote X=0, the assignment value (0) is boolean false and the condition never runs.

But the case I saw condensed

$x = Get-Something
if ($x) 

to if ($x = Get-Something) . Here, if get-something returns a value it is in X and the assignment value makes the conditional bit run, if it returns nothing (or 0, empty, null) the assignment has a false value. I in front of my screen going "You clever ..." at whoever it was, and then "but who will understand it ?".

• I'm like word of the day toilet paper, without ever having to leave your desk :-)

[dfinke]

I've seen the compressed if, may have used that early on, that's too much for me. I agree, being clever is more problems than they're worth. I figured echo was worth it. Yes for others to follow, that's a problem.

[dfinke]

@pkarunkar Thank you for the donation! Very much appreciated. Makes building these tools worth it. Putting words into @jhoneill mouth, I know he agrees and more.

[pkarunkar]

Are you kidding me? We should be thanking you :) Things you built should get more than what comes from donation. I wish you get more and Microsoft should officially accept ImportExcel module into their default powershell installed modules. And people should come forward to donate not just for using the module also for the support you provide.

Free is hard to come these days. As I said in our first few conversations, there should be a better place to put the donation link.. I still went to the readme file to find it . You see how WIKI pages are asking for donation with BIG Red texts :) :) something similar to that.

On Wed, Jan 9, 2019 at 5:43 PM Doug Finke notifications@github.com wrote:

@pkarunkar https://github.com/pkarunkar Thank you for the donation! Very much appreciated. Makes building these tools worth it. Putting words into @jhoneill https://github.com/jhoneill mouth, I know he agrees and more.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/dfinke/ImportExcel/issues/521#issuecomment-452897457, or mute the thread https://github.com/notifications/unsubscribe-auth/Aq2jYSV9yVTJTXeTli2DrkIkm22HE_8Gks5vBnCqgaJpZM4ZyMKK .

-- Wtih Regards PAVITHRASAGAR.K

[pkarunkar]

Ok I take back my words on the donation link.. I see it is in the first page :)

[dfinke]

Haha, cool

[pkarunkar]

@dfinke : here is what Don Jones and me talking about your module in his powershell blog. :)

[dfinke]

Thanks for sharing that @pkarunkar, yeah not sure what Microsoft's motivation is. I believe that EPPlus was originally started by Microsoft, I think under a different name, they took it to a certain level and then others took it over and pushed it forward.

[honylalwani]

@dfinke First of all I would like to thank you on creating such a great module which I think will save a lot of time.

I am pretty new with powershell and I just installed your module today. I am currently working on a project where I collect, manipulate data in powershell through AD and export it in Excel. What I am trying to do is, after exporting the file to excel, I would like to create 4 different pivot tables from the data I am exporting.

These pivot tables will have following data: Name..........Group..........Description.............Filter

Now I would like to add Group and Description in a row field of Pivot table and collapse all rows, then add a filter and choose a filter from the filter options and then repeat the task for other pivot tables but selecting different filter.

What would be your guidance or is it even possible from your module. I am seeking some training haha.

Will really appreciate your help.

[dfinke]

Thanks.

Take a look here to get started.

https://github.com/dfinke/ImportExcel/blob/master/Examples/PivotTable https://github.com/dfinke/ImportExcel/blob/master/Examples/PivotTable/MultiplePivotTables.ps1

[honylalwani]

Thanks Doug, I will start from here and try to achieve what I want to.