Returning the Prompt Value for a Variable

[#1]

Hi Experts,

What I am currently trying to do is to return the values from a prompt so that I can use them in a variable. I have two Queries that are built off of two different Universes out of BEx. I do not have the same dimensions in both queries. I am merging the Universes based on the 'Employee ID' field, but I need to display Amount measures from both queries. Currently, this is functioning if I do not run any filters.

However, as soon as I put a query filter on a dimension that does not exist within both Universes, I have a problem where Query1 is returning a dataset of maybe 10 records, while Query2 still returns a dataset of 100 records. Obviously this presents a problem when aggregating the Amount measures.

I have tried a number of different ways to create a relationship between the measures and Employee, but I can't get things specific enough if I don't have the filtered dimension in the report. Suppose that I have Company Code filtered to 1200, I would like to show on the report only the employees from Company Code 1200. This is fine if I drag in the Employee dimension from Query1. However, once I drag in the Amount measure from Query2, the numbers are totalled for the entire 100 Employees, rather than the 10.

I figure that I should be able to specify my results by leveraging some sort of formula. I'm currently experimenting with:

=sum([Query2].[Amount] ForEach ([Company Code];[Query1].[Employee]) Where ([Company Code] = [Prompt1]))

Prompt1:

=UserResponse([Query1];"Prompt1")

The problem with this is that the UserResponse does not return an array of values that were selected (otherwise it would say #MULTIVALUE). Instead, it seems to concatenate the selections by the user into one field. Unfortunately I can't use that for anything. Is there a way to capture the actual value from the prompt in an array?

Alternatively, is there a better way to accomplish what I'm doing?

Thanks,

Brian Comeau

[Former Member]

Brian, have you tried merging Employee dimensions and building a table like this:

[Query1].[Category], [Query1].[Measure], [Query2].[Measure]

?

I just tested on XI3.1 and got it.

Luis

[Former Member]

Luis,

Did you apply any filters (restrictions) on only one dataset?

Try that and let me know.

Thanks,

Brian

[Former Member]

Yes, I did.

I restricted the first the Data Provider (Query1) and it worked ok.