dynamic data item in prompt query

[dax]

Hi

I'm trying to solve what I thought would be a simple problem.  I have two sets of users which I categorise as either "Finance" or "Non-Finance".  All of these users have access to a particular report and this report has a single tree prompt where they can select a member.  My requirement is that for the Finance group I want the tree prompt to show a particular hierarchy - say:

[My namespace].[my dimension].[some hierarchy].[first level of some hierarchy]

and for the Non-Finance group I want the prompt to display the next level down in that hierachy e.g.

[My namespace].[my dimension].[some hierarchy].[first level of some hierarchy].[second level of that hierarchy]

I have a data item named [Is Finance User] that I'm using to determine the type of user and it's defined like so:

case when #CSVIdentityNameList ('+')# contains 'FINANCE' then 'Yes' else 'No' end

I thought I could just create a data item using a case when and return the appropriate hierarchy:

case when ([Is Finance User]='Yes') then
([My namespace].[my dimension].[some hierarchy].[first level of some hierarchy])
else
([My namespace].[my dimension].[some hierarchy].[first level of some hierarchy].[second level of that hierarchy])
end

But I get an error of:


The THEN or ELSE clause of the expression 'memberUniqueName(case when "lots of groups here" case when  contains 'FINANCE' then 'Yes' else 'No' end = 'Yes' then
([My namespace].[my dimension].[some hierarchy].[first level of some hierarchy]) else
([My namespace].[my dimension].[some hierarchy].[first level of some hierarchy].[second level of that hierarchy]) end)' should return a single value, not a set of members



I have googled this for hours and tried lots of different ways of trying to solve this and have not been successful.  I have a secondary solution using a conditional block but I'd really rather find a way to do this
using the prompt query as I have a number of these reports to update and the conditional block seems "clunky".

Appreciate any thoughts or ideas on this.  Let me know if I've not explained something clearly.

EDIT: I'm working with a DMR data source using MS SQL server.

Cheers
Dax

[Lynn]

Have you looked into a prompt macro using a 'token' data type? This post is a similar requirement to yours:

http://www.cognoise.com/index.php/topic,31696.0.html

[dax]

Hi Lynn

I've seen that post you referred to but I'm not sure how I can apply it to my situation.  If I could prompt the user to choose between "Finance" and "Non-Finance" I could get this to work using a similar solution to the GO example you posted but I need the user type to be determined without user interaction.  One of my earlier attempts was to create a data item that created the MUN based on the value of the [Is Finance User] and gave up as kept getting errors.

Anyway, for now I'm going to go with using a condition block as I need to get these reports done by the end of the month, although if you (or anyone else) has any other ideas I would love to hear them.

Thanks :)
Dax