Catalog producing incorrect joins

[goofaholix]

I'm used to reporting in Crystal or SQL where I have full control over what table/fields are joined to what and with what join.

I'm now doing Impromptu reports and it's important that I leave my client with a good Catalog so that unskilled users can write reports.

However I have a couple of reports that have 5-7 tables and the Catalog is joining one or two tables to the wrong table resulting in multiple rows.

I could change the Catalog by deleting the potential to join to the wrong table but I believe it's likely another report in the future may have the need to join to it.

Doing it all in SQL would be the easiest for me, but harder for my client to maintain in the future.

Is there any way to tweak which table should be joining to which in particular reports without manually editing SQL?

At what point do you decide sod the Catalog and go fore pure SQL?

Thanks.

Bruce

 

[DrSimon]

Bruce
2 comments here:
a) If you are getting the 'wrong' joins - check in Catalog/Joins to see what joins exist in the catalog and fix as approrpriate
b) If you have a table that needs joins in one report and not in another, you get round this by creating an alias table and having different joins in the alias.
There's normally no problem in doing this.
The problem you are more likely to encounter is how to explain the difference between the 2 versions of the table to the users. No easy answer usless they understand the concept of joins. The way I've got round it is to create different catalog folders and put all related tables (replicated where necessary) in the same top level folder folder. I then tell the users to 'stay' in the appropriate folder when writing reports.
Generally you don't need 'sod' Impromptu. Once the users get used to it, it is a very good report writer. But I do sympathise with your frustration - this site really helps though.
Simon Rouse

 

[drlex]

I agree with Simon's post, but would point out that the use of relative weighting on the tables may prevent a report using unwanted 'intermediate' tables in generating the SQL.

soi la, soi carre

 

[griffindm]

Relative table weightings can help, but aliasing the tables to create star or snowflake schema solutions for each fact table, separate from other fact tables is a better solution.

What you can do to help the users is to 'bundle' all of the dimensional tables used with a fact table into a top level folder. This way a simple rule for users to select a dimensional lookup from the folder closest to the fact table can avoid their selecting another alias of the table that would involve unwanted intermediate tables in the SQL.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com