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
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
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