Links between tables are not set correctly

rbuser

Hello,
We're currently using Report Builder 12,
Database type: MSSQLServer

In the Data section of my report when i add two tables and try to link
them with a common field, the linking sql generation is missing
the 'inner join' producing incorrect results when i run the report.

The current workaround is to use reports that were generated before we
upgraded and simply modify them as they retain the correct link.

Example:
report that works (from an older report builder):

i linked tableName1.ID with tableName2.ID and set it to include all
records from tableName1, regardless of whether any matching tableName2
records can be found

the corresponding sql select for that link looks like:

select
tableName1.ID,
tableName2.ID
from
tableName1
INNER JOIN tableName2 on
(tableName2.ID = tableName1.ID)


The report generated using Report builder 12 with the exact same two
tables and link will generate the following sql query for that link:

select
tableName2.ID
from tableName2


Please let me know if there's a new feature that i'm missing that's
causing this, or if it's really a defect and there's already a fix for
it.

Thank you




Rania

--- posted by geoForum on http://www.newswhat.com

nicocizik

Hi Rania,

Which exact version of RB 12 are you using (.05, .04, etc.)? Which
version did you upgrade from?

Are you viewing the generated SQL by selecting the SQL tab in the query
designer or are you viewing the MagicSQL generated? Note that you need
to control-click the dataview to view the actual SQL sent to the DB.

Has the report stopped functioning correctly?

rbuser

?Hi Nico,

Thank you for the fast reply.
I was viewing the MagicSQL generated.
Only after i 'control clicked', closed the form, and then went back to the
magicSQL was i able to see join in the sql query.

After profiling the DB i noticed the join too,
however the report wouldn't generate the proper output without adding
order by statement in the sql, even though without the order by the query
returns the exact set of records in the same order. The order is very
specific and not just based on the field that i'm linking on.

The rule of thumb that i used to create reports is that if you have a
'group' in the report design then generally you should sort by that field.
However the report did not have any groups, only a link and a subreport in
the details section.




Rania

--- posted by geoForum on http://www.newswhat.com

nicocizik

Hi Rania,

This is interesting. Which exact version of ReportBuilder are you using?

It is possible that this was enhanced for later versions of RB to limit
the usage of a table join unless it is absolutely needed. Since you
seem to be taking manual control over the OrderBy clause, you need to be
sure the detail dataset is ordered by the linking field first in all cases.