I'm not sure with version of Excel you are using. There's a few of
different ways you may be able to go about. Note, the first 2 options
assume the data is within Excel. The third option is okay for small scale
stuff, but not too good after that. The last option is by far the best one
for large scale situation.
Option 1: Using the Data Filters within Excel
If the data is within Excel, you can use either the Auto Filter or Advanced
Filter options provided you are looking to just have rows hidden of what you
don't want to see.
Option 2: Using DB Functions
You can use the DB Functions within Excel though this requires learning up
on their usage and following through to their specifications.
Option 3: Use MS Query
This is a possible option, but be very careful as this use ADO within Excel
and as far as ADO within Excel itself is concerned, this has been a major
memory leaker thus the application will crash pretty quickly for those items
that has larger amounts of data. Note, I am not saying ADO is a memory
leaker in all cases such as within Access, but I am saying it is for when
used within Excel. As a result of this memory leak issue within Excel, when
we setup MS Office on the computers, I purposely have the MS Query feature
set to "Not Available". Yes, I ran into this issue directly and it happened
so fast, I had to more or less immediately stop attempting to use it.
There's also a documented article by MS on this very issue.
http://support.microsoft.com/kb/319998 There's also other things about MS
Query that I didn't care for.
Option 4: Use a third party Add-In tool such as ShowCase Query by SPSS
While this option still has a few minor issues involved, it by far has the
most flexibility. Even with the few minor issues, can work around those
issues using the various tools and tricks. This is the option that I use
quite heavily as most of our data is stored in an enterprise data base and
this is the only viable option we have to pull the data into Excel. Not
only that, but with the various events that comes with ShowCase Query, I can
dictate what actions gets done at what points of the process of running and
pulling of the data. Yes, this will require learning more of VBA for those
that are only novices in VBA, but it's a very effective option. Note, with
this option, you can first perform SQL within the query itself to do the
filtering and which data you want to pull. It can also do a few of the
calculations as long as the calculations are either record based or group
based and nothing like a running total column that requires multiple record
calculations that are not based on group data. For calculations like a
running total column, that would need to be done within Excel rather it be
formula based or VBA based. For various reasons, I prefer VBA or formula
even though formula is a lot easier to write. However, when it comes to
different situations, formula based in many cases has to be modified while
VBA codes doesn't have to be modified, thus why I prefer VBA over formula.
Yes, I work with a larger amount of data thus another factor of why I prefer
VBA over formula not to mention VBA is much more efficient than formula in
many cases if done right.
--
Thanks,
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
test said:
hi,
we have the MDX Query which we want to use as a filter in excel.
MDXQuery:
select Measures.members on 0, Filter([testdim].[Group Name].members,
[testdim].[User Name] = UserName )on 1
from [testCube]
How do we creat a filter in Excel?
