Is it possible to get the current Page Item Selected for a pivot table
I've tried the following functions in VBA, but have had no success
CurrentPageName
CurrentPage
CurrentPageItem
ParentItems
I use Excel to query a SSAS OLAP Cube, the return of which I then model into various outputs. I do not have access to amend the cube.
I am hoping it is possible to be able to add custom calculated members to some dimensions in excel rather than on the server, but I'm struggling to find any resources that explains how it is done or if it is even possible. For example, the cube will return the dimension 'Source Country' with members 'England', 'Northern Ireland', 'Scotland', 'Wales', I would like to add a calculated member 'United Kingdom' to consolidate all those members.
Is this possible or should I just look to calculate this consolidation by formulas in the model.
Excel 2007
Win7 Pro
I am trying to automate the creation of 3 pivot tables. At first I was having a problem with deselecting all items in the drop-down except one, then I found the code to fix it.
I replaced this code:
ActiveSheet.PivotTables("PivotTable15").PivotFields( _
"Beta")
.PivotItems("Escalated").Visible = False
.PivotItems("Hang Up").Visible = False
.PivotItems("New Hire Requests").Visible = False
.PivotItems("No Trouble Found").Visible = False
.PivotItems("Priority Exchange").Visible = False
[code].....
This fixed the problem, but there are still two other pivot tables that need to be created after this first one. I scrolled down through the code and deleted the other code blocks for the selection of the PivotItems and replaced it with the code above, but then when I ran it I got the "Compile Error: Duplicate Declaration in current scope".
I read more through the forum and realize that it's because VBA is dimensioning it twice, and I read that you're supposed to Dimension at the beginning of your program, but how do I implement this into my coding?
I can't dimension literally at the start of the code, there is some formatting that needs to take place first. (Basically I paste in a bunch of data into Sheet1 of my workbook, center it, space it out, then insert a pivot table into the pre-existing Sheet2 based on that data, filtered on the blank entries in Column N. Then I need to create ANOTHER pivot table based on that same data in Sheet1, but filtered on the blank entries in Column O. Then I center the words in the Pivot tables' headers in Sheet2, and finally I need to create one last pivot table on pre-existing Sheet3 based on the data on Sheet1, filtered for 2 specific entry types in Column N.)
I am working now on macros changing the datas in a pivot table created from a OLAP cube. I have there some hierarchic dimensions in the format of Category.Group.Item etc. When creating a macro with recorder i get something like this...
ActiveSheet.PivotTables("cube").PivotFields("[Dim_Item]").CurrentPageName = _
"[Dim_Item].[All Dim_Item].[CategoryA].[GroupA4].[Item550321]"
Is somewhere out there any tutorial to that [All Dim_Item] thing? ... cause i feel there IS what i need, that with some cunning command i could show an item without knowing his group and category, but i cant get the macro working.
i have generated a olap cube and display it using pivot table. But i am unable to sort my data from acsending order. For example: I had right clicked on my pivot table > field settings > Advanced > Select "Ascending". The data (sum of quantity) remains unsort after i have checked ok.
View 2 Replies View RelatedIve created a pivot table which looks at an Access Database in the form of a Cube. One field has numbers in, but when it comes through as a cube, it treats these numbers as text. So when I have a the rows as this field, they appear in text order, eg. 1, 10, 100, 101, 2 etc (instead of 1,2, 3, 4 etc). How can I get this field to be recognised as a number field in the pivot? (its a number field in the access database). Ive applied a sort to the data where Ive made my own custom list, but this is just a temporary fix. Can i specifiy somewhere that this field is a number and should be treated as such??
View 3 Replies View RelatedI have a named range of values on Sheet2 (GPI). Sheet1 is an OLAP pivot table containing row label (GPI 14) and values (Net Rx Count) only.
Unfiltered this list is over 7,000 rows. I need VBA code to display only those rows where the GPI 14 value matches any value in the named GPI range on Sheet2.
In other words how can I display the select rows without manually selecting the items of interest AND without manually hard coding the values in the code as they will change.
Below is 1 of the many codes I tried. This appears to be the most intuitive but I get an 'invalid procedure" error at Set my PivotTable...
'Sub PivotAnalysis()
'
Dim myPivotTable As Excel.PivotTable
Dim myPivotField As Excel.PivotField
Dim myPivotItem As Excel.PivotItem
[Code]....
I have an array set up with values I want to look in a Pivot Field for and then pull data back based on that selection. It works great until there is an item in my array that is not listed in the Pivot Field. Then it pulls the data from the last item again, which skews my results. So my questions is, how can I skip to the next item in an array if it is not listed in the Pivot Field? Here is the section of code
ActiveSheet.PivotTables("PivotTable36").PivotFields("Product").CurrentPage _
= arrProductVals(intProduct, 2)
I am trying to write a list of pivot table page filter pivot items to the Immediate window - but only hidden items. The code below should do the job where the active sheet is a pivot table.
However when I change the page filter pivot items being hidden, the pivot items returned by the macro don't change. It seems to assume that all pivot items are hidden when in fact it may be only one or two. It will work though for pivot row items (pvt.RowFields) and pivot column items (pvt.ColumnFields) where the user changes them.
Does the pf.HiddenItems collection work for RowFields and ColumnFields but not PageFields? If so, is there a pivot field object that reliably holds hidden pivot items residing in the page filters?
Sub ListHiddenPageFilterPivotItems()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.ActiveSheet
Dim pvt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
I have two pivot tables, both of which source the same sheet of data. Each record in the data has two fields, Region ID of person and Region ID of facility, that reference the same list of Region IDs via vlookup (it's just numbers 1-12). Either one or both can be blank. One pivot outputs counts by person Region ID, and the other, by facility Region ID.
Using a combination of nested IF statements and vlookups, I tried making a third Region ID field that could be used as a slicer to control both tables, but what I end up with is an undercount in one of the tables. The only thing that works so far to output the correct counts is having two separate slicers, the facility Region ID being the slicer for one and the person Region ID being the slicer for the other. If the end user wants to see counts for one Region ID, they have to manually set one slicer equal to the other. But what we want is just for the end user to be able to control both tables just by pushing a single number, Region IDs 1-12.
I can make one of the slicers hidden but then how do I get the hidden slicer to automatically select Region ID values equal to the nonhidden slicer? Alternatively, I could somehow program a combo box or list to control the two different slicers, then the slicers would be hidden and the user would see only the combo box/list. (I guess in either of these alternatives, I could just use a report filter instead of a slicer; either way, I still have to get items in one to automatically select based on the user's selected items in another).
FYI, some of the options I looked up involved PowerPivot, which I do not have access to. VBAs/macros are ok.
I have a pivot table which I want to force the all of the pivot table items to be selected for a particular pivot table field. One would think that this would be as easy as unlocking all cells on the sheet with the exception of this pivot field and then locking the worksheet. This doesn't work though as I am generating multiple pivot tables on the same workbook for the same range and I get this message: "this command cannot be performed while a protected sheet contains another PivotTable report based on the same data source...".
My thinking is that I can do something along the lines of this:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim oPI As PivotItem
Application.ScreenUpdating = False
If Target.PivotFields("Item Sold").PivotItems.Count Target.PivotFields("Item Sold").VisibleItems.Count Then
[Code]..
This is failing right away though on the If Target.Pivot.... line.
I have a database table with selling prices. I want to get a count of number of customers by sales price range.
For example:
Price Count
$0-$5 #
$5-$10 #
$10-$15 #
Total
My nose says this is what a pivot table should do but there are too many sales prices and the pivot table craps out. How do I get them into ranges as above and then count them?
I've a Pivot Table which i would like to get a list of it.
It is from an OLAP cube from Oracle
Based on the image below, every month i refresh this PT, there will be an additional WK. Lets say WK22.
How do i export the whole list from WK08 to WK21 everytime after i refresh my PT?
How do i select the last item in the Page field? If there is WK22, den it will auto select WK22. If not WK21.
I have defined 2 pivot table report using the same master data. I wanted to default the page field in these reports. I notice that on refreshing the data, the pivot table page field updates with "All" instead of default one (if there is no data for the default page field). How do I fix this? I wanted to have the default page even though there is no data for that field in the master file.
View 5 Replies View RelatedI know in Pivot tbale we could add calculated field into the data ,but I fail to put the added in the "Page","Row" or the "Column". Eg. in the sheet of data,I have a column " DATE",but no column for year.Is it possible to add year in the "page" without adding anything into the "data" sheet?
View 5 Replies View RelatedI've created a PivotTable using VBA that contains hundreds of PivotItems, which would look bad when a PivotChart is made.
I'd like to set the PivotTable to make visible only the first X items (let's say 10). How would I do this in VBA?The macro recorder gives me the name of the PivotItem, but this varies so I'd like to use an index:
VB:
ActiveSheet.PivotTables("PivotTable4").PivotFields("UWI").PivotItems("Item1").Visible = False
ActiveSheet.PivotTables("PivotTable4").PivotFields("UWI").PivotItems("Item2").Visible = False
See attached.
Basically, I just want to do a pivot which shows the Name and Number for those names listed in the LIST tab.
The pivot source is the data tab, and the result is in the result tab.
I have the code to create the pivot, but filtering it for those specific names on the LIST tab is where I am getting stuck.
Attached File : Excel VBA Pivot Problem.xlsx
Is there a way to have a Pivot Table show only the Top 10 items based on dollar amount. Data covers a month of daily activity (+/- 250 rows), but i only want the Top 10 items based on Dollar amount. Is this possible?
I know filters can do top 10 but it doesn't consolidate similar items.
I've created a pivot table that is not grouping "like" items. I have verified all fields are numbers using the =isnumber() formula. All items present with the "true" value. Just in case, I tried doing text to columns and refreshing the data but that did not work either. All items are formatted the same. All data fields have values.
View 1 Replies View RelatedI'm working on pivot tables using excel macro. Basically , I need to filter out the year submitted according to the current year . I did this to filter out my pivot :
For Each PivItem In _
PT.PivotFields("Year Submitted").PivotItems
PivItem.Visible = True
Next PivItem
For Each PivItem In _
PT.PivotFields("Year Submitted").PivotItems
Select Case PivItem.Name
Case "2012"
[code]....
But this can only filter out year 2012 . I need to use this workbook for the next couple of years and I dont want to keep modifying the codes .
Is there any way how you can change value in page field in pivot table using formulas? I know this can be done with couple of lines of VBA, but I need to create VBA-free excel file.
View 4 Replies View RelatedI have created a system where users input data from a report into a spreadsheet, and after input it is sent off to various other workbooks depending on what was entered. I.e. The user is inputting test data on a location in a region. The regions make up the workbooks, and each location is a worksheet (While I can think of better ways to organise the data changing this is not an option here).
When the macro encounters a location in a region that hasn't been entered before it adds a new sheet based off the name of the location in the region's workbook, and creates a new named range for that sheet.
I'm running a pivot table in the central workbook, and I want to add the new named range to the page field when I create a new sheet. This is what I don't know how to do. EDIT: To clarify, the page field is choosing between which worksheet(via named range) is being shown currently.
I have to check if two items are in column A, and if yes, there values from column B shoulded be summed. I tried different combinations with IF, LOOKUP but didn't go far.
View 2 Replies View RelatedI am trying to use the bellow code to set one pivot item (MyItem) to true and the rest to false... unsuccesfully
Code:
For Each pt In Sheets("Schedule Dashboard").PivotTables(PivotTable1)
If pt.PivotFields("District").PivotItems(MyItem).Visible = False Then
pt.PivotFields("District").PivotItems(MyItem).Visible = True
Else
pt.PivotFields("District").PivotItems.Visible = False
End If
Next pt
I'm trying to write a macro to select the multiple sets of the same data for several PIVOT tables. I've tried Slicers but it seems that this takes up too much processing power and always times out.
My workaround is to do a macro that picks out the said data, however when i do the below, plus another 4-500 lines i get told that there are too many line continuations
Code:
ActiveSheet.PivotTables("PivotTable6").PivotFields( _
"[Postal District].[Postal District].[Postal District]").VisibleItemsList = _
Array("[Postal District].[Postal District].&[AB11]", _
"[Postal District].[Postal District].&[AB12]", _
[Code] ...
What I'm looking to do is express all the postcodes in one line or at least multiple post codes in one go, this is what I've tried:
Code:
"[Postal District].[Postal District].&[AB12].&[AB13]"
and
Code:
"[Postal District].[Postal District].&[AB12,AB13]"
But to no avail.
I copied and modified the pivottable code from http://www.ozgrid.com/VBA/hide-pivot-fields.htm. I am getting a "Run time 13" error on the line I colored purple. I tried removing different "Dim" statements to make it work and I'm having no luck.
Sub PickUpPivotTable()
Dim pt As PivotTable, pi As PivotItem
Dim lType As Long, lHarn As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation
Set pt = Worksheets("Monthly Pivot Summary").PivotTables("MonthlyPivotSummary")
strCri = "P/U"
For Each pi In pt.PivotFields("Type").PivotItems
lType = pi..........................
I have a Pivot Table on which I am unable to drag the row items (Salesperson Names) to a different position. What am I doing wrong? Attached Sample
View 9 Replies View RelatedI have to excel files, both have the same data. I created a pivot table macro on the first file. I want to be able to open the second file and use the shortcut to create the pivot table. the problem is that it refers back to the original workbook I created the macro on. How do I change the code to make it refer to the current workbook?
Code:
Sub Pivot()
'
' Pivot Macro
[Code]....
i have pivot table that has a field called "supp" is it possible to write a macro that will open up the "supp" drop down box select the first result .print the results of the pivot table. then goto the next selection in the same drop down box and print them results . repeat this until allresults have been printed.
when the pivot table is run weekly the results in the field "supp" will change
in my Pivot Tables page field i could have 20 results. 10 could be customer identification codes
5 material codes, 5 a different material code, i called "inter", and the rest supplier codes, example i05,i05/1,i05/2, fo1,f01/1,f01/2 are both material codes,
what i want to do is have a macro /macros to select and print groups
as follows
1. (all)
2. inter
3 any containing the words i05 and f01 which includes i05/1 etc
4. then the rest
if any does not exsist ignor. if possilbe a macro for each or a drop down box to select