SSRS - Creating distinct parameter values from SSAS OLAP cube

0
votes

I have a list of names and email addresses (formatting below) and need to pull back only the names portion, and I need them to be in a distinct list to populate in a parameter.

Current List

Desired List

  • Jane Doe
  • John Smith

Since the data source is an SSAS OLAP cube I cannot write a "select distinct" query within my dataset. Instead I am limited to using the Query Designer via a shared data source connected to the cube.

Has anyone else ever run into anything like this and found a solution? I've been looking all over and can't find anything.

Any help would be appreciated!

Thanks

1
reporting-servicesssasolap-cube

1 Answers

0
votes

Try this:

With MEMBER [Measures].[Name] 
AS
LEFT([Customer].[Customer Name].CurrentMember.Name,
InStr([Customer].[Customer Name].CurrentMember.Name,'(')-2
)
select [Measures].[Name] on columns,
DISTINCT([Customer].[Customer Name].[Customer Name]) on rows
from [Mycube]

It will give you a list of distinct customer names as long as customer name is composed of:

name (email)