Returning the Prompt Value for a Variable

[Former Member]

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]

Hi Brian

I'm not familiar with Bex queries but have you tried adding using the query with the filter to be the driver for the values of the second query, so in effect you have 3 queriues:

Query 1 with filter

Query 2 with sub-query based on filtered query 1

Regards

Charles

[Former Member]

Hi Charles,

Just for future reference, you cannot use sub-queries (or a number of other features) when sourcing from SAP BI.

I agree though, that would have been the solution.

Thanks,

Brian

[Former Member]

Ok, thanks.

Regards

Charles

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