is there a way to return one number from a separate analysis much like Excel VLookup would do?

jssharrah

I have an analysis that shows the top 10 customers by rank(Revenue). I would like to create a dashboard where one tile shows the rank for a customer name provided via a presentation variable.

Gianni Ceresa

You can use an analysis as filter for another one, if you have an analysis giving you the name of the customer, you can use it as filter for the second analysis with the ranking.

Or, of course, use a prompt (either directly or via a presentation variable) for the job.

jssharrah

Thanks for responding. I must not understand your solution correctly. If I try to filter the RANK calculation for just one customer, I always get a RANK of 1. I want to figure out how to get the correct RANK to be returned for a single customer, much like a VLOOKUP would do in Excel.

Gianni Ceresa

You maybe aren't really looking for the equivalent of VLOOKUP...

You have dashboard page, this means that you can have multiple analysis in it and therefore not a real need to get a value into another one.

So the question is more about how to get the RANK of a given customer without having all the customers in the analysis, right?

Because as you saw the RANK works, by default, on the set of rows. Which means that if you filter for the customer directly the RANK is going to be 1 because you have only 1 row. OBIEE will always filter on the customer too early, making RANK work only on a single row.

This is why you must use Selection Steps instead of filter in your analysis (Selection Steps is like post-aggregation filters and other manipulations, while filters are pre-aggregation).

In this screenshot I did just that, starting with all the products ("P1 Product") without any filter, and then in Selection Steps I add a 2nd step to keep only one product. I did select one manually (I don't have a variable set), but you see at the bottom of the window that you can override the manual selection with a variable.

jssharrah

I've never been taught what selection steps could be used for. Very cool. What you suggested works perfectly for the hard coded value. But, attempting to use a Presentation Variable will change the company name, but I'm back to RANK = 1. Here is the Step I set up:

If I run the analysis on it's own, I get the correct response for the hard coded value:

However, embedding that analysis on a dashboard, controlled by a prompt with a presentation variable, I get this:

Prompting for a different company, I get the same results. Name is correct but not the rank.

Sorry to be so dense, but I must be missing a step.

Gianni Ceresa

Could you look at the logs for the 2 situations? (the one with 32 and the one with 1)

Because in theory it should work, in practice it could depends on other things as well. But here it also goes into the used model etc., and only you have this model.

Also what about the exact product name, version and bundle patch? Is it recent and fully patched? If it isn't the case, it is maybe a bug which has been fixed.

jssharrah

Thanks for your help on this. Your solution would work if OBIEE would cooperate! I've passed this issue to our tech folks to see if they can resolve it. (Or apply a bug fix.)