Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MDX query in Excel
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MDX query in Excel
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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Edit Query from Excel will not open query in MSQuery | Excel Programming | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |