Ask - OR Clause in MDX where clause

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Ask

Ask questions, discuss or help others by answering


	Ask a question


	Subscribe..

OR Clause in MDX where clause

Apr 3 2012 12:00AM by kbasu

Hi Jason,

I looked at your post below to while searching for OR clause in MDX http://beyondrelational.com/modules/2/blogs/65/posts/11570/or-operation-in-mdx.aspx

This is good but my case is something like this

SELECT
	non empty {[Measures].[Access Fee]} on columns ,  
	non empty [Member].[Member ID].[Member ID].members on rows
FROM
	[Emergency Room]	
WHERE
	{
		{
			[Diagnosis].[Diagnosis Subcategory].&[102 Injury to blood vessels]
			,[Diagnosis].[Diagnosis Subcategory].&[128 Air and space transport accidents]
			,[Diagnosis].[Diagnosis Subcategory].&[149 MALIGNANT NEOPLASM OF LYMPHATIC AND HEMATOPOIETIC]
			,[Diagnosis].[Diagnosis Subcategory].&[37  Inflammatory diseases of the CNS]
		}*
		{
			[Procedure].[Procedure].&[21333]
			,[Procedure].[Procedure].&[21342]
			,[Procedure].[Procedure].&[21357]
		}
	}

I want to select data with OR between the two sets in the WHERE clause.

If done individually this one returns me no data because [Procedure].[Procedure].&[21333] ,[Procedure].[Procedure].&[21342] ,[Procedure].[Procedure].&[21357]

but this one returns data [Diagnosis].[Diagnosis Subcategory].&[102 Injury to blood vessels] ,[Diagnosis].[Diagnosis Subcategory].&[128 Air and space transport accidents] ,[Diagnosis].[Diagnosis Subcategory].&[149 MALIGNANT NEOPLASM OF LYMPHATIC AND HEMATOPOIETIC] ,[Diagnosis].[Diagnosis Subcategory].&[37 Inflammatory diseases of the CNS]

However my full query returns no data which means its not an OR clause. Can you help in any way?

Thanks

Submitted under: Microsoft SQL Server · SSAS · Business Intelligence · ·

Previous: about timer jobs in sharepoint
Next: How to assign the muliple result sets to muliple varaibles in sql execute task

kbasu

838 · 0% · 34

3 Replies

Will the below statement work?

SELECT non empty {[Measures].[Access Fee]} on columns ,  
non empty [Member].[Member ID].[Member ID].members on rows FROM [Emergency Room]	WHERE
{
	{([Diagnosis].[Diagnosis Subcategory].&[102 Injury to blood vessels],[Procedure].[Procedure].[All])
		,([Diagnosis].[Diagnosis Subcategory].&[128 Air and space transport accidents] ,[Procedure].[Procedure].[All])			
		,([Diagnosis].[Diagnosis Subcategory].&[149 MALIGNANT NEOPLASM OF LYMPHATIC AND HEMATOPOIETIC],[Procedure].[Procedure].[All])						
		,([Diagnosis].[Diagnosis Subcategory].&[37  Inflammatory diseases of the CNS],[Procedure].[Procedure].[All])				
		,([Diagnosis].[Diagnosis Subcategory].[All],[Procedure].[Procedure].&[21333])
		,([Diagnosis].[Diagnosis Subcategory].[All],[Procedure].[Procedure].&[21342])			
		,([Diagnosis].[Diagnosis Subcategory].[All],[Procedure].[Procedure].&[21357])}

commented on Apr 3 2012 7:36PM

Jason Thomas

19 · 9% · 2987

Worked great. Thanks a bunch!!!

commented on Apr 5 2012 4:44PM

kbasu

838 · 0% · 34

Jason,

Thanks for your previous reply. It solved my main problem. However a new one has come up which I am not exactly sure how to handle.

In the OR clause is it possible to put EXCEPT around a set? As I had mentioned in my first post this set in WHERE clause returns no data { [Procedure].[Procedure].&[21333] ,[Procedure].[Procedure].&[21342] ,[Procedure].[Procedure].&[21357] }

Is it possible to tweak this WHERE clause only this time use EXCEPT around the [Procedure].[Procedure] items

{([Diagnosis].[Diagnosis Subcategory].&[102 Injury to blood vessels],[Procedure].[Procedure].[All])
    	,([Diagnosis].[Diagnosis Subcategory].&[128 Air and space transport accidents] ,[Procedure].[Procedure].[All])			
    	,([Diagnosis].[Diagnosis Subcategory].&[149 MALIGNANT NEOPLASM OF LYMPHATIC AND HEMATOPOIETIC],[Procedure].[Procedure].[All])						
    	,([Diagnosis].[Diagnosis Subcategory].&[37  Inflammatory diseases of the CNS],[Procedure].[Procedure].[All])				
    	,([Diagnosis].[Diagnosis Subcategory].[All],[Procedure].[Procedure].&[21333])
    	,([Diagnosis].[Diagnosis Subcategory].[All],[Procedure].[Procedure].&[21342])			
    	,([Diagnosis].[Diagnosis Subcategory].[All],[Procedure].[Procedure].&[21357])}

Any help will be appreciated.

Thanks.

commented on Apr 11 2012 1:25AM

kbasu

838 · 0% · 34

Your Reply

Sign Up or Login to post a comment.