PivotTable Filter Automaticly adds new data

Hi,

I have a strange problem.

MS Excel 2003 pivot table linked to SSAS 2005 Cube.  One of the dimensions 
let's say Customer is filtered on the pivot table. 5 from total of 100 
Customers are selected in filter.  Next day assume 2 new customers added to 
customer dimension and when the pivot refreshed those 2 new customers are 
automaticly added to filter and the result is 7 customers selected out of 
102 total.

Do you know any ways to prevent this and keeping the only original filter 
without adding new records?

Thanks in advance

erdal



0
erdalim01 (2)
3/20/2008 11:45:50 AM
excel 39879 articles. 2 followers. Follow

2 Replies
535 Views

Similar Articles

[PageSpeed] 2

On Mar 20, 12:45=A0pm, "erdal akbulut" <erdali...@yahoo.com> wrote:
> Hi,
>
> I have a strange problem.
>
> MS Excel 2003 pivot table linked to SSAS 2005 Cube. =A0One of the dimensio=
ns
> let's say Customer is filtered on the pivot table. 5 from total of 100
> Customers are selected in filter. =A0Next day assume 2 new customers added=
 to
> customer dimension and when the pivot refreshed those 2 new customers are
> automaticly added to filter and the result is 7 customers selected out of
> 102 total.
>
> Do you know any ways to prevent this and keeping the only original filter
> without adding new records?
>
> Thanks in advance
>
> erdal

this is a macro I made to filter accounts that are (almost) equal to 0
in a pivottable. I don't know your pivot, but you can make it work in
your.
If you have problems, send an example to my emailadress

Sub VerbergenPivotItems()
Sheets("pivot").Select
Dim item, rngTableItem As Range, Terr As Boolean
[A5].Select 'ga zeker ergens in de draaitabel staan
For Each item In
ActiveSheet.PivotTables("PivotTable1").PivotFields("grootboekr.").PivotItems=

  On Error GoTo fout
  Terr =3D False
  Set rngTableItem =3D ActiveCell.PivotTable.GetPivotData("bedrag",
"grootboekr.", item.Value)
  On Error GoTo 0
  If Not Terr Then
    If Abs(rngTableItem.Value) < 0.0001 Then
 
ActiveSheet.PivotTables("PivotTable1").PivotFields("grootboekr.").PivotItems=
(item.Value).Visible
=3D False
    End If
  End If
Next
fout:
If Err.Number =3D 1004 Then Terr =3D True: Resume Next
End Sub

greetings

bart
0
3/20/2008 12:57:12 PM
"erdal akbulut" <erdalim01@yahoo.com> wrote in message 
news:ej2Yz$niIHA.4844@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I have a strange problem.
>
> MS Excel 2003 pivot table linked to SSAS 2005 Cube.  One of the dimensions 
> let's say Customer is filtered on the pivot table. 5 from total of 100 
> Customers are selected in filter.  Next day assume 2 new customers added 
> to customer dimension and when the pivot refreshed those 2 new customers 
> are automaticly added to filter and the result is 7 customers selected out 
> of 102 total.
>
> Do you know any ways to prevent this and keeping the only original filter 
> without adding new records?
>
> Thanks in advance
>
> erdal
>

Ok, I have recorded a macro while doing some filtering, here is the result.

 ActiveSheet.PivotTables("PivotTable1").PivotFields("[Cusstomer]"_
        ).HiddenItemsList = Array("[AAA]", _
        "[BBB]", _
        "[CCC]")

So appereantly Excel keeps a hidden item list when filtering therefore it is 
normal that new items appearing in the filter cause they are not included in 
the hiddenitemlist.

While working on owc I have used allincludeexclude property and 
includeditems collection to filter reports programaticly, I could not find 
similar in Excel Pivot Tables.

Does somebody know equalients of allincludeexclude property and 
includeditems collection of OWC in excel ?

Thanks,

erdal 


0
erdalim01 (2)
3/21/2008 10:51:06 AM
Reply:

Similar Artilces:

Is there an easy way to filter duplicate rows of data in excel?
When I run a query and download the information to Excel, there will be duplicate rows of data (If a product went through an operation twice). I want to filter out the duplicate rows. With the amount of data I'm working with, it is too difficult to filter manually. "Yumin" wrote: > When I run a query and download the information to Excel, there will be > duplicate rows of data (If a product went through an operation twice). I > want to filter out the duplicate rows. With the amount of data I'm working > with, it is too difficult to filter manually. ...

B/W/L Wine Shop add-in on Partnersource
I have a RMS customer who is looking for a wine shop customer loyalty program which integrates with RMS. I went up on Partnersource at: https://mbs.microsoft.com/partnersource/products/rms/documentation/installationsetupguides/Customization_Kits_for_Vertical_Markets.htm They have a sample wine shop program there which the customer saw and really liked. Does anyone know who sells this particular program and their contact information? Thanks You can download all the installation files and source code from that link. There is no charge or specific company you need to contact. This add-on...

how do I automatically repeat data in several excel workbooks #2
I am trying to update the same text / numerical data in 3 different workbooks at one, can anyone help? Eg, if I input a name in one sheet I want it to appear in all 3. Hi you can link the other workbooks to this cell. e.g. - select the target cell and enter the equation sign '=' - now select with your mouse the sourc e cell and hit ENTER "stufroments" wrote: > I am trying to update the same text / numerical data in 3 different workbooks > at one, can anyone help? Eg, if I input a name in one sheet I want it to > appear in all 3. ...

Add 1 hour to data
I have data 1 tru 200 with time "12:01:04". How can I add a hour to read "1:01:04" so when they move the clock in March '10 I will have the correct data. Thank you -- smile Try in B1: =A1+"1:00:00" Copy down Success? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "israel" wrote: > I have data 1 tru 200 with time "12:01:04". How can I add a hour to read > "1:01:04" > so when they move the clock in March '10 I will have the corre...

synchronization error of data migration manager
Dear all, I've just install Microsoft data migration manager. I have passed initial configuration manager successfully. Then my screen do a start up as below for a moment: http://i51.photobucket.com/albums/f381/organisme/Kerjoan/CRM/datamigrationerror.jpg But suddenly it goes back to login screen with login and cancel button only withoutt any error message (do nothing). Any body know what it might be wrong? Any help will be very appreciated. http://i51.photobucket.com/albums/f381/organisme/Kerjoan/CRM/datamigrationerror2.jpg PS. I'm using crm 4.0 I'm system administrator. Tha...

Adding a Date data-type field with a Make-Table query
I am using a make-table query to make a table. Some of the fields are being populated with this query, however I need to add several more fields with null values so that they can be updated by subsequent update queries. The default field-type that is created if I just put in a field name is Text. How can I create a Date field-type field from a make-table query which has a null value? Well, you can create a DateTime field by using something like: DateField: CDate(0) But the field won't be empty, it will contain 0, or, stated as a date value, Dec 30, 1899 12:00 am. I don't kn...

How do I change data labels on scatterplot
I create scatter plots using two values (salary, year of hire) that relate to particular employees. I want to label each point with the contents of a third cell (initials of the individual related to the point). I have figured out how to change the label one point at a time, but I'd like to simply relate the contents of a third column to the markers defined by two other columns. Help!! -- ahannigan The free Excel add-in "Chart Data Labels" will do that. It can also include the cell formatting in the data label and link the cell contents to the label. Download from ......

Data file not closed properly
Everytime our users have Outlook open then close it (properly), but need to get back into it immediately, they receive this message. "The date file 'personal folder' was not closed properly." "This file is being checked for problems" This message started appearing about a week ago. I can't find any information about it or what's the real cause. Is it because of SP3 for Office? Does anyone have a clue?? Please help... ...

automatic reply #5
i'm going on holidays and was wondering knew how to do an automatic reply to all incoming emails ...

Relative paths to external data?
I know how to import external data from a .csv, but how can I have the path be relative so I can move the directory around without having to update the ..csv file location manually? I'm not quite sure what you're doing. But if you're doing this from a macro, maybe you could just use application.getopenfilename. It would allow you to browse for your file. Or if the .csv file is in the same location as the workbook that contains the macro, you could find the path of that workbook by using thisworkbook.path So you could use... dim myCSVFileName as string mycsvfilename = thisw...

New Exchange 2003 Administrator
Hi, I was hoping I might be able to get a few questions I have answered here. I am relatively new with Exchange, but am now in charge of managing it for a 20-person company. We are running Exchange Server 2003 Standard SP2. Here are my questions: 1) I am pretty sure Exchange 2003 came with an Outlook 2003 CD. Does anyone know more about this? We are currently running Outlook 2000 on our workstations, so how would using the copy of Outlook 2003 that came with Exchange 2003 work? Does Exchange 2003 require each workstation connection to have a license (if so, then I guess we're...

Can you change something in Excel to automatically insert an equa.
I'm generally used to Lotus 1-2-3 where for a calculation I can just enter 1+2 and have it display 3. In Excel I always have to enter =1+2. If you don't enter the = it will just display 1+2 after you hit enter. Is there something in the settings you can change so that it will automatically enter the = sign? The only other option is to enter "+1+2" which will return 3 This is an Excel requirement. A tradeoff from Lotus is if you WANT the cell to display "1+2" in Lotus, you must enter the apostrophe " '1+2 ", in Excel, you don't. HTH ...

New to exchange #2
Hi, I have decided to start playing with exchange 2003. I have a network in the 192.168.1.0 subnet that has two servers in it both domain controllers and both running win2k server. I want to make some prevision for email to the users on the domain after I have upgraded both servers to 2003. I use a Linux firewall that also provides a DMZ . The DMZ is used to host a small web server and is on the 192.168.2.0 subnet, any request to port 80 is forwarded to the web servers IP address. What would be the best way to start playing with exchange ? Do I need a mail relay in the DMZ that push to t...

I downloaded Outlook and it worked for a day. Now no new mail
I downloaded Outlook 4 days ago. the first night all my email messages came through. The next day I logged on and nothing new. It has been like this for 3 days now and I don't know what to do. When I check my bellsouth account there are tons of new messages. Please help if you know what's wrong. Thank you!! ...

being new to computors how do i save a email address
how do i save a email address that has been sent to me? Right click on it in the header and choose "Add to Contacts" "thisis becomingtoadvanedforme" <thisis becomingtoadvanedforme@discussions.microsoft.com> wrote in message news:8C0C07EF-7147-470D-80DD-46E351FC8427@microsoft.com... > how do i save a email address that has been sent to me? ...

How do I get Excel to automatically enter the contents of a cell .
Can you provide some more detail on exactly what it is you're trying to achieve? The phrase "automatically enter the contents of a cell" can be interpreted a 1000 different ways in Excel. Thanks HTH Jason Atlanta, GA >-----Original Message----- > >. > ...

Size of new (empty) email message is nearly 1mb
Hi all I'm using Outlook 2010 and suddently, for some reason, the size of a new email I create is nearly 1mb. This startedhappening on Wednesday morning (emails before that are very small), but I'm not sure what I've done (if anything) to cause this. Looking at the source of a sent email I can see that the stylesheet is HUGE, it contains a very long list of entries such as @list l0:level1 {mso-level-number-format:none; mso-level-suffix:none; mso-level-text:""; mso-level-tab-stop:0cm; mso-level-number-position:left; margin-left:0cm; text-i...

Can't insert a new row
I have about 20 rows of data and I'm trying to insert a row of data before line 6. I highlight cell 6, click Insert /Rows and then I get an error, "To prevent the possible loss of data, Microsoft Excel cannot shift nonblank cells off the work sheet." It goes on with some more garbage, but it's already obvious that Microsoft doesn't know what it's talking about. How do I get this insert to work? Thanks! -- skywaytraveler ------------------------------------------------------------------------ skywaytraveler's Profile: http://www.excelforum.com/member.php?ac...

Missing information when filtering
Hi, My report is about our clients and employees working with them. I need to make a filter on the client's name and the month. When I filter on the client's name, I have an employee name missing but the information about that employee missing is under other employees. When I don't have a filter on a specific client, that employee is there. Can anyone tell me what the problem could be pease? Thanks, Julie Would need to see the SQL for the report and how you apply your filter. -- Build a little, test a little. "JulieL" wrote: > Hi, > ...

Can I install Publisher 97 on a new computer with Vista?
I'm about to bite the bullet and replace my 9 year old computer, but Vista scares me. ;-) I use Microsoft Publisher 97 once in a while to perform simple tasks, and I'd hate to lose it. Will I be able to install it onto a new computer, or will I have to update? Thanks, Marcia You can certainly try. Whether it works properly is another issue. If I had to guess, you will have some problems that cannot be resolved. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" <Marcia R.spamlessinlasvegas@cox.net> wrote in mes...

Data Analysis disappears from the Data ribbon for no reason
Every now and then (usually at the most inconvenient moment) Data Anylysis disappears from the data ribbon. If I return to Excel Options to try to reload it, it says it is already loaded. ...

Get an error Adding new user
I've installed MS CRM on windows 2003 server. - I've created an Active Directory OU called MSCRM. - After installing MS CRM. I remove the Restricted Access check box on the default administrator created by the installation. - When I try to create a new user via the WEB UI. using Domain\UserName I get an error ("An error Occured. Contact your system administrator"). - If I try to add users from the CRM deployment Manager I dont see any users on the domain in the wizard. The list is empty. - The only way I can add users to my installation is using the sample data add uti...

Print sorted data
I'm want to sort data frfom various worksheets and that this data be printed all in one page, actually if the information is from five diferent worksheets, the print out comes out in five diferent pages.. I'm using Microsoft Office 2003. Please help. Hi Joseph Maybe you can use this http://www.rondebruin.nl/print.htm#non-contiguous -- Regards Ron de Bruin http://www.rondebruin.nl "Joseph Solis" <Joseph Solis@discussions.microsoft.com> wrote in message news:186904AC-D5B4-41D6-8ED6-961D5AB77479@microsoft.com... > I'm want to sort data frfom various worksh...

Northwind Data Base 2007
Is there a Access 2007 version of the Northwind Data Base? If not, is there an equivalent sample with macros and VBA code? Ed Hi Ed, Try this (assuming you're using Access 2007): 1. Open Access 2. On the Getting Started screen there are some categories listed in the left task pane 3. Click the one called Sample 4. You should see the Northwind 2007 template now in the middle of the screen 5. Click on the icon, enter the name and location on the right task pane, and then click the Download button Or this: 1. Search your computer for the file called Northwind.accdt 2. On a Win XP machin...

How can I link data to a shape's Title/Label?
I know I can link data to a shape - but that is a seperate field to its Title or Text field. Is there a way to link data to a shape's Title/Text/Label? Well, kinda. You can of course create a textfield in the shape that is connected to a shapedata field, which can be displayed. The down side is either you're relegated to modifying custom shapes or writing custom code. al "AlienChild" <AlienChild@discussions.microsoft.com> wrote in message news:D873E05A-634C-45A7-AFB2-203AAC4E53F6@microsoft.com... > I know I can link data to a shape - but that is a se...