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

No comments:

Post a Comment