Filter that uses presentation variable doesn't work

Question

I have an analysis that navigates to another analysis in the dashboard in OBIEE 11g.  There are 3 values that need to be passed from the first analysis to the second.  Two of the filtered column values are equal to column values from the first table.  I used "is based on on results of another analysis" for those, which seems to be working.

However, I want the third filtered column value to be a column value that is LIKE a column value from the first analysis.  I can't use "is based on on results of another analysis" since that only equates them.  I'm trying to use a Presentation Variable, i.e. the column value in the "navigated to" analysis is LIKE p_value, where p_value is the presentation value.  However it always results in no data returned.  To verify that p_value is populated, I put it in a Title and took out the filter to get results, and it's showing the value.  It also is showing the value correctly in the filter when it says "No Results".  To verify that it would return data as I am expecting, when I put a hard coded value in the filter instead of p_value, it shows the right results.

Ideally I would like to say that the column value in the second analysis is like a value in a column in the first analysis but there doesn't seem to be a way to do that.  I am trying to to use a SQL expression in the filter where the column in the second analysis is like the value in the first analysis, but that doesn't restrict the values returned to only what was clicked on in the first analysis.  In other words if the value selected in the first analysis is 'ABC', then I only want values returned in the second analysis that begin with 'ABC' but it's returning everything in the second analysis that is contained in the first table that also matches the other two filters.  If the first has an 'XYZ' value, then the second one is showing 'XYZA', 'XYZB' etc., even though 'ABC' was clicked on and 'XYZ' is nowhere to be seen.

So I have two questions:

* Why does the "like p_value" filter not return anything?
* Is there another way to filter the column value to be like another column value?

Thanks.

Gianni Ceresa · Answer

user4109719 wrote:I have an analysis that navigates to another analysis in the dashboard in OBIEE 11g. There are 3 values that need to be passed from the first analysis to the second. Two of the filtered column values are equal to column values from the first table. I used "is based on on results of another analysis" for those, which seems to be working.

Hi,

When you navigate from an analysis to another in OBIEE if you filter the same columns with "is prompted" it will automatically filter them. Using "is based on result of another analysis" is really bad from a perf point of view and actually not needed ...

For your 3rd filter why don't you just build it normally using your variable? If you say the presentation variable is there and has the right value, just setup the filter on your column based on that and done.

Your filter "LIKE" doesn't return anything because you don't want "your column" = @{p_value} but you want something like: "your column" LIKE '@{p_value}%' which is clearly different and works in a different way. The "LIKE" operator doesn't means "contains" and doesn't use %. If you look at the physical query you see that and this explain why it doesn't work.

Sherry George · Answer

Did you check the query that was generated for the second report? how does the filter look in there?