Home DADE
New Blog Posts: Merging Reports - Part 1 and Part 2

Cannot link detail query if master query is sorted on a Calc field

edited September 2014 in DADE
v15.05

Subject says it all really: if you master DataView generates SQL like ...

SELECT TOP 10 Jobs.Client,
SUM(Jobs.InvoicedEqlist) SUM_Jobs_InvoicedEqlist
FROM Jobs Jobs
GROUP BY Jobs.Client
ORDER BY 2

...then you cannot link on the field Client (visually with a drag) a
detail DataView that has SQL like

SELECT Jobs.Client, Jobs.Job_Ref
FROM Jobs Jobs
ORDER BY Jobs.Client

If you then try and create a link via the link editor then this "seems"
to work and it will appear but RD clearly isn't happy with it because i)
any subreport linked to the detail DataView will return ALL data in Jobs
for each record in master (doesn't matter which link option is used). If
you go back to the Data tab the link isn't there any more (visually) but
if you bring up the editor it clearly is.

If you remove the sort order field in the master DataView, all behaves
correctly. Surely this can't be WAD?

TIA, Paul

Comments

  • edited September 2014
    Hi Paul,

    Magic SQL linking is not supported when the master contains an aggregate
    field (which is why the link dragging does not work in your case).

    Try using parameterized or manual linking to get the result you are after.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited September 2014
    On 19/09/2014 16:53, Nico Cizik (Digital Metaphors) wrote:

    I did mention that neither of the other linking options work.
  • edited September 2014
    On 19/09/2014 17:44, Paul Toms wrote:

    And of course when making the link, if the default of Magic SQL isn't
    possible then surely a warning message should be shown - it shouldn't
    just do nothing. But that's a separate issue..for the time being NONE of
    the join options work (were wanted to use a parameterized join in fact
    anyway).

    Would be good to get this fixed because the report that we can't write
    is a replacement for a Crystal Report that a customer has been using for
    years and we can't have them thinking RB is an inferior product, can we :)
  • edited September 2014
    Hi Paul,

    I aplogize, I did not see that you had tried the other linking options.

    You are correct that the Magic SQL option in the link dialog should be
    disabled for this specific case. We will change this for the next
    release of ReportBuilder.

    I tested a similar situation with Parameterized Linking here and it
    appeared to function as expected. Below is a link to the example I
    created. Try running it and perhaps alter it so I can see the problem.

    http://www.digital-metaphors.com/tips/TestAggregateLinking.zip

    Also, note the parameterized linking should be the same as if you were
    linking TDatasets on a form. Try linking the datasets on a Delphi form
    outside of ReportBuilder and see if you get the same behavior.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited September 2014
    Nico,

    Thanks for the demo app - it illustrates the problem perfectly :)

    The report on page 1 shows customer # 4684 followed by ALL the orders in
    the Orders.db table. But customer # 4684 only ever placed one order (#
    1115). So the Parameterized SQL link you have used isn't working. This
    is the problem.


  • edited September 2014
    Hi Paul,

    Interesting, this appears to be an issue that only shows up some of the
    time. I was able to reproduce it however.

    I created a patch for you to try. Send a short email to
    support@digital-metaphors.com to receive the patch.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited September 2014
    Hello Nico,

    That's fixed it - and I see you have disabled "magic SQL" too. I'd say
    that this is a good one to catch :) Many thanks.

    Paul

This discussion has been closed.