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

rbuser

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

nicocizik

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.

rbuser

On 19/09/2014 16:53, Nico Cizik (Digital Metaphors) wrote:

I did mention that neither of the other linking options work.

rbuser

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

nicocizik

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.

rbuser

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.

nicocizik

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.

rbuser

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