how much do pivotfilters from 2007 break if viewed in 2003?

[beckyinDC]

I have a consolidated function for refreshing all pivotcaches (done first looping through all caches)and then applying filters to pivottables as needed (looping through pivottables to do so). The filtering portion worked 5 days ago but appears to be broken at this point.

Sample:

         With pt 'pivottable object is defined by looping through all pivottables                
          'On Error Resume Next 'I did this in case no values exist for a field in the range I filter upon- have commented out to trobuleshoot
                If .Name = "IssuesOpenedToday" Then 
                    With .PivotFields("Date Opened")
                        .ClearAllFilters
'NEXT LINE- ERROR OCCURS-Object or application -defined error
                        .PivotFilters.Add Type:=xlDateBetween, Value1:=officialdate & " 12:00:00 AM", Value2:=officialdate & " 11:59:59 PM"
                     End With  'more code follows...more filters set on different pivottables as well as the End IF

what can i do:
-add data to source and see pivot table get updated with new data.set the values filters
-create new pivot table off same source within same file- but get the same limitation
- go back to a version of the file from 5 days ago and see the date filter option enabled and the date filter is set. what is different about the two files at this point I dont know.
--source data worksheet is unprotected and there are no locked cells involved on the listobject that is the source data.
--worksheet where pivottable resides is also unlocked, and there are no other sheets with pivot tables referencing the data.

both files (not working from today and working from 5 days ago) are .xls docs in compatibility mode.


maybe this is because I opened file via 2003? ...we have read-only users who would view the spreadsheet from 2003. I was using pivot tables to gather/compute the data (when run against win 2010 or 2007) and then displaying it for the 2003 crew by paste values. meanwhile the same file needs to be able to retain the functionality so that next time it can compute again. Recently have been doing the checking to see they were ok.

pt.PivotFields("Date Closed").PivotFilters.count = 0, so no filters exist on the field in question

seems like the problem is at a higher level because several of the pivotfilters appear to be having similar problem

officialdate is getting populated with a date- I can see that so its just a matter of applying the filter- which like I said- worked before...hmmm.

going manually to the report filters - Date filters appears to be grayed out....I want to check on what type of value the filed is supposed to be, but under field settings I dont see format number button.

any explanation/help would be appreciated- thank you

 

[beckyinDC]

urrrgh....so it was a compatibility issue- fair enough- I was hoping we could get around that since the macros would only get run on 2010 or 2007...and wasnt quite understanding all the nuances of it. it seems to work as a .xls file until you actually take it to 2003.

proved it partially by converting to .xlsm- which brought back my filter capability. then I was able to save as a different name .xls and still am able to filter by date...but that may be kind of complicated for some of the folks that would be using this. I dont have a 2003 client near me to check further on that today- will tomorrow.

we need the file to be a .xls to get the doc on sharepoint at the cusotmer site- security roadblock occurs for .xlsm files- also I dont know if 2003 users would be able to easily pull the file down?

on the other hand we need dealing with the files to be as simple as possible- the folks running the macros are normally busy with what the DSR reports...the DSR is secondary and tacked onto days that may be super-busy and as noted maybe all of the users arent excel-savvy across the board. fair enough.

well....I need to break and rethink this tomorrow- December is when they plan to upgrade customer clients to 2010. unfortuneately I was hoping to deploy this version of our spreadsheet a couple weeks ago...it has been fun to work on, but it was a learning curve for me and took awhile.

I might be able to make it work as is- technically the file gets used in 2010 or 2007 then gets loaded to sharepoint for dissemination ot the larger 2003 audience. at the point it goes to sharepoint all calcualtions are done- and its a separate file each day. technically the file should not get updated by the 2003 users, thoguh I dont know if that is preventable -maybe need help from sharepoint administrators.

alright-cheers to everyone upgrading away from 2003 sooner than later

 

[beckyinDC]

Ok so 2 options seem reasonable after some thinking and re-reading of doucmentation:

--switch the 2007 filters over to 2003-style value filters. then knock on wood the remaining concerns for 2003 are only the aesthetic ones that I am aware of and can deal with. probably will try this- I have 9 filters to work on- guess I need to loop through values and look for a match...

--wait for upgrade to 2010 and ignore 2003....I dont trust the upgrade to 2010 will happen in a few months though, so waiting might not make sense- will present options to some folks around here.

Some notes on what happened, in case it helps anyone else:

The compatibility report did mention the pivot table filters would break as a minor fidelity loss. If data may get reflected wrong, I would call it major. Also the message was kind of vague I thought, and I had expected that saving as a .XLS file was the test of whether the pivots would work as much as I needed. Seems like saving as .XLS is one thing, then pulling it up against a 2003 machine is a step beyond that and that is when my filters no longer work....or maybe its at Pivot table refresh after having saved as .XLS? It didnt happen immediately.

I had suppressed error messages (since data might not exist when a filter condition was applied, or a getpivottable command was computed I had used on error and iferror conditions on worksheet functions routinely) to avoid #ref and pop-ups during processing). Oh well- working through it