Are you experiencing a Cross Joins Error in Cognos Reporting?
If you have ever spent time working with Cognos reports you more than likely had the error “Cross joins (between query subjects: SQL1…blah, blah….) are not permitted for the user who has the identity.‘*’ ”
Why it Happens
This error occurs because there is no relationship between the tables at the model level in Cognos. It can be very frustrating when building reports. Before I learned this trick, the only way to get around it was to try and take a different approach to get the same results using different tables. The information required for the report may not always be available on different tables, so this is where the trick really comes in handy.
An Easy Solution
Simply open Cognos Report Studio and navigate to the query you are getting this error from. Then look under the Query Properties (left side, down low on screen). You will see the property named ‘Cross Product Allowed.’ Select the drop down and choose ‘Allow.’ Run the report to test it and save the report. Refer to the screenshot below for what you should be seeing.
It’s as easy as that! This solution has worked wonderfully for us, and we hope it will help you too. So the next time you get the dreaded error message about cross joins, remember this simple trick to help get the data you want on the report.If you have any additional questions or would like to learn more about Cognos Reporting, feel free to contact DigitalThinker, Inc.