Can author influence sequence in which Detail Filters are applied within a query

[FerdH4]

I'm working with v11.2.3 and getting unexpected results (result set) in a query with nine Detail Filters.

I've successfully tested each filter alone and get the correct result set each time.  Even when 6, 7, or even 8 Filters are active, the result sets are correct.

But I get wrong result set when all nine Filters are active.  Records which should not be excluded are being excluded even though they were not excluded in earlier tests with fewer than 9 Filters active. 

I am using Minimum and Maximum expressions on separate fields in separate Filters.  Quite literally, it looks like if I could control the other of the execution of one or specific Filters the results might be correct.

Is there anything that I can do inside of a single Query to influence the execution order of my Filters?

[dougp]

It can't be about order of operation.  Filters in Cognos are combined using AND.  Draw a Venn diagram where each circle represents the data resulting from a single filter.  The intersection of all of the circles is the result of applying all of the filters.  If the results look wrong, it is because your filter is wrong.

[cognostechie]

I'm working with v11.2.3 and getting unexpected results (result set) in a query with nine Detail Filters.

I've successfully tested each filter alone and get the correct result set each time.  Even when 6, 7, or even 8 Filters are active, the result sets are correct.

But I get wrong result set when all nine Filters are active.  Records which should not be excluded are being excluded even though they were not excluded in earlier tests with fewer than 9 Filters active. 

I am using Minimum and Maximum expressions on separate fields in separate Filters.  Quite literally, it looks like if I could control the other of the execution of one or specific Filters the results might be correct.

Is there anything that I can do inside of a single Query to influence the execution order of my Filters?

I usually control the filters by writing my own SQL. Inner query and outer query, that way you can apply filters to inner query before applying to outer query

[dougp]

I usually control the filters by writing my own SQL. Inner query and outer query, that way you can apply filters to inner query before applying to outer query

That's always a good double-check.  So if you write custom SQL for this, you get different results than what Cognos is producing?  Cognos is just generating SQL for you based on what you have created in Reporting combined with the way the model was defined.  So what's different between the SQL that Cognos is generating and the SQL that you wrote that produced the result you wanted?  Did you make a mistake in the report?  Is there an incorrect relationship in the model?  ...?


Your original question was about the order of filters.  Now it's about the order of subqueries.

That can be done by chaining multiple queries together.  Create Query1.  Use Query1 as the source for Query2.  etc.  If you're using CQM Cognos will write an old-style query using subqueries in the FROM clause.  If you are using DQM, Cognos will write exactly the same query, but using CTEs.

While one of the following queries may return faster, they will return exactly the same results.

Code Select Expand

with
Query1 as (
select col1, col2
from MySource
where col1 = 1
)
select col1, col2
from Query1
where col2 = 2
;

with
Query1 as (
select col1, col2
from MySource
where col2 = 2
)
select col1, col2
from Query1
where col1 = 1
;

select col1, col2
from MySource
where col1 = 1
  and col2 = 2
;

[cognostechie]

That's always a good double-check.  So if you write custom SQL for this, you get different results than what Cognos is producing?  Cognos is just generating SQL for you based on what you have created in Reporting combined with the way the model was defined.  So what's different between the SQL that Cognos is generating and the SQL that you wrote that produced the result you wanted?  Did you make a mistake in the report?  Is there an incorrect relationship in the model?  ...?

You provided a very simple query as an example but the results are different in case of complex and/or multiple queries. With inner and outer queries you can control which filter is applied where and make sure they are exclusive which is what the OP's problem seems to be. Many people create multiple filters without realizing that they are not exclusive and one filter will clash with the other causing the results to be wrong. It is true that Cognos will still re-write the SQL you write but it does not alter the logic of your SQL. I am working for a company which used Deltek's  Costpoint which is an ERP only for government contractors (project based sales only) so the data is small but there are hundred of reports which take hours and even days to run. I re-made those reports using hand written SQL and they run in seconds now! If any report is using joins from a package then never create joins in the report. It's better to write your own SQL which is a complete SQL containing all queries and use that complete SQL in one query in the report instead of creating separate small SQLs and then joining them in the report. That's what did the trick.