Report Studio - Generated SQL/MDW Correct but report missing data

[Drepso]

Hi

Hoping someone can point me in the right direction.



Cognos 8.1 (Report Studio)

Issue:

I have created a report which is not returning all records that I need. It is a list report with a aggregated (total) count. I checked the generated sql/mdx and ran this in SQL Server Man Studio and got the correct results.

There are no nulls in any of the fields in the report which could be suppressing results.

When I validate the generated sql/mdx and run the query I get the following error message:

RQP-DEF-0103 Cross joins(between query subjects:SQL 1, TableA,TableB)

I tried the two options suggested in the knowledge base:

- Cross Product Allowed - set to allow (the report ran but returned the same results)

- Creating a fact table. Merging the two tables that gave the above error message into one query subject. (Recreated the report and received the cross join error again but didn't appear to give a second table.

i.e. Cross joins (between query subjects SQL1, TableA

I don't understand why the cognos generated sql returns the correct result and report studio doesn't. Secondly, I am not sure if the cross join error is causing the ommission of records. I understand that cross joins are an issue which need to be fixed, but I don't know if the cause of my problem is one or possible multiple issues.

Apologies for the long winded post, just trying to get as much info out there as possible.

Appreciate any help.

Thanks

 

[sln007]

Drepso,
The generated SQL from Report Studio is created prior to actually being submitted to run. Therefore, no runtime error. The project model needs to have all joins between tables created in Framework Manager.....even if you have all of the join where-clause items identified in the filters.
So the SQL will run outside of Cognos, but not in it.

Steve N.
State of Ohio, MIS