SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Hi,
I am creating Excel 2007 reports from Cube. There is a drop down for “Customer hierarchy” and “Employee Hierarchy”(bcoz they are placed in the report filter of Pivot table).
The user should choose items either from the “Customer hierarchy” or from the “Employee Hierarchy”, but not from the both.
Is there any workarounds to accomplish this without using Macro?

Hi Selva,

I do not think there is a way to do this, and also this would not be right way. You should design SSAS database so that it would work with any front end.
I would suggest that instead you would override measures so that if just customer or employee is selected, then you show measure values. If both customer and employee are below All level, then you show NULL or string stating "Select customer OR employee".

Here is sample MDX code (based on Adventure Works) that you could add to your cubes script to acomplish this:


IF NOT ( ([Employee].[Employees].CurrentMember IS [Employee].[Employees].[All Employees])
OR ([Customer].[Customer].CurrentMember IS [Customer].[Customer].[All Customers])
)
THEN
{AddCalculatedMembers(MeasureGroupMeasures('Internet Sales'))
, AddCalculatedMembers(MeasureGroupMeasures('Reseller Sales'))
}
= 'Select just Customer or Employee'
END IF;