Home› Datapipelines
ReportBuilder 22.04 now available including Delphi 12.1 support!
New Blog Post: Helper Routines

Links between tables are not set correctly

rbuserrbuser
edited April 2012 in Datapipelines
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

Comments

  • nicociziknicocizik
    edited April 2012
    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?

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • rbuserrbuser
    edited April 2012
    ?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
  • nicociziknicocizik
    edited April 2012
    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.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
This discussion has been closed.