I have a query with one attribute and one measure but with a filter that uses a value from another table with no join hence I get the Cross Joins error.
If I set the query to allow cross joins I still get the error when I put the attribute and measure in a cross tab object but not when they are in a list object. I just get the warnings.
In both cases, the same query is used so the SQL that generates the error when I use a Crosstab is exactly the same as when I use the List.
What's going on here?
I am using Cognos 10.2
Just so you know that allowing cross joins can make your query work but it does not ensure data integrity. It might and it might not give correct data so you will be delivering an unpredictable report.
In the long run, it might be best if you wander over to your modeler counterpart's cubicle and explain to him the problem that you have encountered. He probably will be happy to know that you understand why you are getting the cross-join error. It is possible that he will be amenable to altering the model so that a join path between the table from where you want to get your filter and the other tables has been defined, or he could be in a position to explain why no such join path could or should be created. One would hope that the reason isn't some variant of he can't be bothered.
In general, I don't understand what value the allow cross-joins setting provides but that might just be me.
Thanks everyone for your input. The problem disappeared when we upgraded to C11, so we'll never know the cause!