Subreports blank if using MagicSQL but not when parameterised.
?This is a tough one that you may not be able to answer!
We have modified the RB11 source code to allow for a peculiarity of our
reports. Each of our customers has a database with views starting XZ_
(e.g. XZ_CURRENT_MEMBERS, XZ_GROUPS) which are used in the datadictionary
and are what the customer sees when they create a new report as being the
available tables. However, the views that are actually accessed start with
a two letter code unique to that company (e.g. IS_CURRENT_MEMBERS,
IS_GROUPS). The replacement of the code seems to work OK in as much as
that a trace running on the SQL server shows that the query being executed
contains all the company version (the IS_ versions in this example) and if
the report is a single main report the correct data is returned. Running
the SQL captured from the trace manually returns all the records I would
expect.
However, if there is a subreport using a different pipeline and joined
using magicSQL to the main pipeline/report, the sub reports remain blank.
If I use ParameterizedSQL then they don't and the correct data is
returned. I notice from tracing through that when I open the pipeline for
the subreports with MagicSQL linking, the SQL shows correctly but the
pipeline EOF and BOF are both true.
Stranger still is that when we had report builder 7, the code worked fine,
and I think I'm correct in saying RB7 used only magicSQL.
The question is this: I've hunted high and low in the code to find a cause
for the data not being returned. Can you point me to somewhere that might
be the cause of this please? I'm happy to hunt further but I need a
starting point. Somewhere you think a table alias or name may not get
resolved correctly or somewhere it may be incorrectly calculating how many
records are returned.
Thanks.
--- posted by geoForum on http://delphi.newswhat.com
We have modified the RB11 source code to allow for a peculiarity of our
reports. Each of our customers has a database with views starting XZ_
(e.g. XZ_CURRENT_MEMBERS, XZ_GROUPS) which are used in the datadictionary
and are what the customer sees when they create a new report as being the
available tables. However, the views that are actually accessed start with
a two letter code unique to that company (e.g. IS_CURRENT_MEMBERS,
IS_GROUPS). The replacement of the code seems to work OK in as much as
that a trace running on the SQL server shows that the query being executed
contains all the company version (the IS_ versions in this example) and if
the report is a single main report the correct data is returned. Running
the SQL captured from the trace manually returns all the records I would
expect.
However, if there is a subreport using a different pipeline and joined
using magicSQL to the main pipeline/report, the sub reports remain blank.
If I use ParameterizedSQL then they don't and the correct data is
returned. I notice from tracing through that when I open the pipeline for
the subreports with MagicSQL linking, the SQL shows correctly but the
pipeline EOF and BOF are both true.
Stranger still is that when we had report builder 7, the code worked fine,
and I think I'm correct in saying RB7 used only magicSQL.
The question is this: I've hunted high and low in the code to find a cause
for the data not being returned. Can you point me to somewhere that might
be the cause of this please? I'm happy to hunt further but I need a
starting point. Somewhere you think a table alias or name may not get
resolved correctly or somewhere it may be incorrectly calculating how many
records are returned.
Thanks.
--- posted by geoForum on http://delphi.newswhat.com
This discussion has been closed.