MDX OR Operator

Hi, On my reports, I have paramters which work as AND. So, it'll return me data where LOB1= @.LOB1 AND LOB2 = @.LOB2 and so on. I want to change this operator to OR so it should return me data where LOB1= @.LOB1 OR LOB2 = @.LOB2 and so on.

LOB1,2,3 & 4 are part of the same hierarchy and LOB1 is parent of LOB2 and so on.

SELECT NON EMPTY { [Measures].[Billing Charge AM] } ON COLUMNS, NON EMPTY { ([Cost Center].[Hierarchy].[Cost Center].ALLMEMBERS * [Dim Rel Server App].[Application NM].[Application NM].ALLMEMBERS *

[Dim Server].[Server NM].[Server NM].ALLMEMBERS * [Date].[Date SK].[Date SK].ALLMEMBERS * [Product].[Products].[Product Tier 3].ALLMEMBERS * [Date].[MMM-YYYY].[MMM-YYYY].ALLMEMBERS * [Dim Server].[DR].[DR].ALLMEMBERS * [LOB].[LOB].[LOB Level 4].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [TRAC - DC] WHERE ((STRTOSET (@.LOB1)),(STRTOSET (@.LOB2)),(STRTOSET (@.LOB3)),(STRTOSET (@.LOB4)),(STRTOSET (@.Region)),(STRTOSET (@.Market)),(STRTOSET (@.DistributedProducts)),(STRTOSET (@.ReportingGroup)),(STRTOSET (@.Application)),(STRTOSET (@.Server),strtoset(@.CostCenter))) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

What you are after is also known as an Asymmetric Set. I have written a stored proc that makes it easier to generate these sets which is part of the Analysis Services Stored Procedure project on codeplex www.codeplex.com/asstoredprocedures.

If you check ou the wiki page it explains a bit more about what these sets look like:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=AsymmetricSet&referringTitle=Home

It uses Martial Status and Gender as an example, but you should be able to do the same thing with LOB1,2,3,4 if they are all different attributes. There are other samples in the MDX examples that you can download from codeplex.

If you can load the ASSP assembly on your server, I think you should be able to change your query to look like the following:

SELECT NON EMPTY { [Measures].[Billing Charge AM] } ON COLUMNS, NON EMPTY { ([Cost Center].[Hierarchy].[Cost Center].ALLMEMBERS * [Dim Rel Server App].[Application NM].[Application NM].ALLMEMBERS *

[Dim Server].[Server NM].[Server NM].ALLMEMBERS * [Date].[Date SK].[Date SK].ALLMEMBERS * [Product].[Products].[Product Tier 3].ALLMEMBERS * [Date].[MMM-YYYY].[MMM-YYYY].ALLMEMBERS * [Dim Server].[DR].[DR].ALLMEMBERS * [LOB].[LOB].[LOB Level 4].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [TRAC - DC] WHERE

ASSP.AsymmetricSet(((STRTOMEMBER (@.LOB1)),(STRTOMEMBER (@.LOB2)),(STRTOMEMBER (@.LOB3)),(STRTOMEMBER (@.LOB4)))

,(STRTOSET (@.Region)),(STRTOSET (@.Market)),(STRTOSET (@.DistributedProducts)),(STRTOSET (@.ReportingGroup)),(STRTOSET (@.Application)),(STRTOSET (@.Server),strtoset(@.CostCenter))) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

|||

Hi Gosbell,

Thanks for the solution. Now I face one more issue.

If I want to pass multiple values strtomember () function will throw error.

If I use strtoset () function, it throws the following error.

No Appropriate Overload functuion was found in the Stored Procedure.The parameters are incorrect.

I am unable to use AsymmetricSet(tuple) because it strttotuple() also throws error if I pass multiple values.

Can you please let me know how to use

ASSP.AsymmetricSet(((STRTOMEMBER (@.LOB1)),(STRTOMEMBER (@.LOB2)),(STRTOMEMBER (@.LOB3)),(STRTOMEMBER (@.LOB4)))

as

ASSP.AsymmetricSet(((STRTOSET (@.LOB1)),(STRTOSET (@.LOB2)),(STRTOSET (@.LOB3)),(STRTOSET(@.LOB4)))

Please let us know the solution ASAP.This would be of great help.

Thanks

Dinesh.

|||I have just checked in a change to the ASSP source that will allow the AsymmetricSet function to work with sets as parameters, but you would have to download the latest change set from the source code tab and compile it yourself in order to get this change at the moment. It will be part of the next release, but I'm not sure at the moment exactly when we will do the next release.