nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Column Filter Condition...

User_9GFDT

Hi All,

I have a subject area built where the FILTER(measure_column USING (condition)) function does not appear to working at all. For example FILTER(facts.num_purchased USING (Region.state = 'NY')) does not appear to filtering by New York.

All other the subject areas in my environment that use this function are fine.

Any quick thoughts on why this is?

Thanks

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

Your problem description is quite vague. What do you mean "does not appear to filtering by New York" - did you verify this in detail? And if so - what do the query logs say in detail? Did you check the logs and the physical query emitted against the source database?

User_9GFDT

Hello,

Thank you for responding

What I mean is, just running number purchased, just the fact measure, without any filter in the column or the report, returns 10,000, for all time periods and locations. Adding NY to the report filter, pairs it down to 500. Trying that filter directly in the column itself using the filter condition Filter(measure_column (using(condition)) does not work, it still returns 10,000 where I would expect 500.

The SQL being issued in the query logs are submitting the correct syntax. Its very very strange, I was wondering if I missed a setting somewhere or accidentally switched something off.

Gianni Ceresa

Hi,

are you saying that the physical SQL send the filter but you get 10'000 back even if the query does return to your BI server 500 ?

That's sounds like ... impossible to me ...

User_9GFDT

I agree it sounds impossible, that's why I'm here, wondering if I'm missing something very simple.

SriniVEERAVALLI

Keep look into this, I believe something is wrong in your report !!

Thomas Dodds

Check your syntax ... if need be simplify your report to the bare minimum and get the filter correct first ...

2016-02-19 12_13_51-Oracle BI Answers.png

If that doesn't work, then time to check the SQL sent to the Database (one where it works with report filter; one where is is not working with column formula filter) ... post those SQLs.

User_9GFDT

Thank you all for the responses. Going to tackle this issue again next week. Will let you know if I figure it out.

User_9GFDT

Hi All, just wanted to respond. The physical sql DOES NOT look like its picking up the column condition at all. Which is strange because in the Logical SQL its there, but when comparing two physical SQL statements (one that works and this one that doesn't).

Can I try to force it in the XML or SQL and see what happens or does anyone have a thought?

Thanks.

Thomas Dodds

does the physical SQL have the filter condition in the where clause or does it have the filter column included in the selected columns / group by clause?

Post the SQL

User_9GFDT

Hi Thomas, thanks for responding.

It doesn't have the filter condition at all. The syntax appears as if the column is completely vanilla. The syntax is appearing as a simple SELECT Count FROM Fact Table.

Just for reference, I'd tried rebuilding the RPD, same problem, and I tried this on a different version and same problem.