Showing posts with label quickly. Show all posts
Showing posts with label quickly. Show all posts

Tuesday, March 27, 2012

Filter and Except Function

Can any one quickly explain me the difference between the two?

I have a cube which contains dimensions such as REGION , MARKET,PRODUCT etc. While I cretaed this cube the ALL level was populated in cube as I did not have any control over it. I want to access the OLAP data with the MDX. I am trying something like this but its not working.Any suggestions?

strSource = "With "
strSource = strSource & " member REGION.region_all as 'aggregate({except({[REGION].members},{[REGION].[ALL]})})'"
strSource = strSource & " member market.market_all as 'aggregate({except({[market].members},{[market].[ALL]})})'"
strSource = strSource & " member PRODUCT.product_all as 'aggregate({except({[PRODUCT].members},{[PRODUCT].[ALL]})})'"
strSource = strSource & "SELECT "
strSource = strSource & "{[Measures].members} ON COLUMNS,"
strSource = strSource & _
"NON EMPTY [METRIC].members ON ROWS"
strSource = strSource & " FROM ocwcube"
strSource = strSource & " where (region_all,market_all ,product_all)"
Debug.Print strSource

Can you describe the desired layout of results in more detail - it's not clear what you want from the above code? Also, for simplicity, you might want to first debug the MDX query in a tool like Management Studio, then develop the code to construct it.sql