How to build multiple joins in Report Studio

[elee123]

Hello Everyone,

I've got an example below that involves 3 to 4 left joins to the same table, but the Join in Report Studio's Toolbox only allows me to join 2 tables together . So how can I achieve this in Report Studio?

Select field1, field2 From Table1
LEFT JOIN Table2
ON Table1.field2 = Table2.field2;
LEFT JOIN Table3
ON Table1.field2 = Table3.field2;
LEFT JOIN Table4
ON table1.field2 = table4.field2;

[Lynn]

You can drag additional joins into the query and join result set of the join from the first two tables with the third and so on....but.....the performance will probably be very poor. It would also not be considered a best practice approach. Nor would using hard coded SQL.

The better idea would be to put this into your Framework Model.

[elee123]

Hi Lynn,
Can you please help me to understand why the performance would be poor if we do joins in Report Studio?

[Lynn]

When you join two queries in report studio the entire result set of both queries need to be fetched and then joined locally on the cognos server rather than in the database. If your result sets are large then this can be slow.

If you have the need to do the type of joining you describe in your original approach then you might to better to reconsider the design of your database and/or your FM model.


Sent from my iPhone using Tapatalk

[elee123]

Thanks Lynn