Conversion of RB 5.0 Reports to RB 7.02

rbuser

?My company have used Report Builder extensively for years. We have
encountered 2 major problems since moving from Delphi 4 with RB 5.0 to
Delphi 7 using RB 7.02.
1. When trying to convert a batch run of older reports using the Delphi
components, the SQL within the report is not converted correctly on
loading. An error is generated and the report cannot be run. It appears
the WHERE conditions are transferred to the FROM clauses by being turned
into JOINs and this is apparently causing grief.
2. Some of our reports originate from customers and use "User Defined
Fields" within their DB tables. These UDFs are only present on the
customers machine, but it would be nice if we could do the conversion for
them without RB generating an error and bailing because it cant find the
field.

We have several hundred reports which we maintain, and currently are
finding a 31% failure rate on conversion. It is estimated by our
customer consultants that several thousand custom reports exist at client
sites, so this has become a major issue as you can understand.

Can anyone provide some info on how to automate the conversion of these
reports?



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

nardmoseley

Conversion of very old reports can require that you load the report, wait a
couple of seconds and then re-save.

For end-user reports you could perhaps write a utility that the user could
run.

rbuser

We have had broadly similar issues and have written a couple of functions
what 'validate' the template, which we have hooked up to the
Template.OnLoadEvent.

Our 'validate' routine does the following:

o Checks all the table names and renames for appropriate platform e.g. .DB
extension if Paradox, no extension if MS SQL (tablename needs to match data
dictionary entries, which change depending on platform).

o Checks all the fonts specified in the template are installed on the PC
and warns user if a font is missing (Windows automatic font substitution has
caused us a lot of grief over the years!)

o Checks all the field names and does automatic field name substitution if
required (should we have changed the field names in the database).

o Rebuilds the SQL query according to above changes and relevant
environment (e.g. Paradox or MS SQL db).

This means our software can load and run older templates without having to
manually update them all (we probably have 12,500 templates out there).

The only downside is that I don't think it automatically saves the template
after the conversion, so will re-run the same code every time you load the
template (nobody seems to have noticed any reduction in performance!).

If you think it would help, would be happy to send you source code...

Regards,

Pete Colson.

rbuser

Yes please Pete. Throw some source at me. I believe our problem is
mainly in the SQL statement so your solution to that aspect is
particularly relevant, I think. Nard Moseley from Digital Metaphors
suggested waiting a few seconds before resaving, but we get a fail on
load with these older reports. And we can leave them forever and they
still wont work. So your technique of intercepting the load looks good.
Appreciate the help.

Bill Miller




functions
e.g. .DB
data
PC
has
if
to
template
the
appears
turned
for
the
client



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

rbuser

Nard or anyone, actually.

I recently inherited this job from another programmer, and I'm not
particularly familiar with ReportBuilder at all, so go easy on me if I
say something dumb. Our code producing the 69% success rate on
conversions goes like below:

ppReport.Convert(ppReport.VersionNo);
ppReport.Modified := True;
ppReport.Template.SaveToFile;
// trick for RB
ppReport.Template.LoadFromFile;
ppReport.Modified := True;
ppReport.Template.SaveToFile;

At the moment it resides in a separate utility to convert a batch of
reports.

I believe our main problem lies with the rearrangement of the SQL
statement in the conversion of WHERE conditions into JOINs in the FROM
block.

Is there any way to turn this off? I mean is there any way to prevent RB
from altering a working SQL syntax and just accept it "as is"?

Thanks for any assistance,
Bill Miller

wait a
could
appears
turned
for
the
client



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

rbuser

Pete Colson wrote:


Pete

I have the same problem as Bill have,

Could you please send your code also to me?

My E-Mail is: rocco(dot)neri(at)corael(dot)it

Thanks in advance.

Rocco

nardmoseley

The TdaDataModule, TdaQueryDataView, and TdaSQL object classes all contain
Convert methods (see daDataModule.pas, daQueryDataView.pas, and daSQL.pas in
RBuilder\Source. Perhaps tracing that code will provide some insight into
what is happening)

The TdaSQL object maintains an object based description of a SQL statement
(SelectTables[], SelectFields[], etc). It uses this information to generate
a SQL text statement.

rbuser

Thanks Nard. I'll look into how I can possibly turn off the checking for
the presence of a field to allow our user defined fields to be processed
back at Head Office. Bill.

contain
daSQL.pas in
into
statement
generate
RB



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

rbuser

Nard, There is an error in the way RB performs the conversion from 5.0 to
7.02. The INNER JOIN statements are not always put in the correct order.

for example, our original FROM/WHERE blocks follow:
-----------------------------------------
FROM CostCentre CostCentre,
Department Department, PayPeriod PayPeriod,
Location Location, Award Award,
TransHistoricalMaster TransHistoricalMaster,
TransPerPaySequence TransPerPaySequence,
HistoricalAllowance HistoricalAllowance,
Allowance Allowance,
Department Department_2, Employee Employee
WHERE
(CostCentre.COSTCENTRECODE = HistoricalAllowance.COSTCENTRECODE)
AND
(Department.DEPARTMENTCODE = Employee.DEPARTMENTCODE)
AND
(PayPeriod.PAYPERIODCODE = Employee.PAYPERIODCODE)
AND
(Location.LOCATIONCODE = Employee.LOCATIONCODE)
AND
(Award.AWARDCODE = Employee.AWARDCODE)
AND
(TransHistoricalMaster.EMPLOYEECODE = Employee.EMPLOYEECODE)
AND
(TransPerPaySequence.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
AND
(HistoricalAllowance.EMPLOYEECODE = TransHistoricalMaster.EMPLOYEECODE)
AND
(HistoricalAllowance.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
AND
(Allowance.ALLOWANCECODE = HistoricalAllowance.ALLOWANCECODE)
AND
(Department_2.DEPARTMENTCODE = Department.PARENTCODE)
AND ( Allowance.PAYING = 1 )
--------------------------------------------------------

When loaded by RB Designer 7.02 this is converted to:
----------------------------------
FROM Employee Employee
INNER JOIN CostCentre CostCentre ON
(CostCentre.COSTCENTRECODE = HistoricalAllowance.COSTCENTRECODE)
INNER JOIN Department Department ON
(Department.DEPARTMENTCODE = Employee.DEPARTMENTCODE)
INNER JOIN PayPeriod PayPeriod ON
(PayPeriod.PAYPERIODCODE = Employee.PAYPERIODCODE)
INNER JOIN Location Location ON
(Location.LOCATIONCODE = Employee.LOCATIONCODE)
INNER JOIN Award Award ON
(Award.AWARDCODE = Employee.AWARDCODE)
INNER JOIN TransHistoricalMaster TransHistoricalMaster ON
(TransHistoricalMaster.EMPLOYEECODE = Employee.EMPLOYEECODE)
INNER JOIN TransPerPaySequence TransPerPaySequence ON
(TransPerPaySequence.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
INNER JOIN HistoricalAllowance HistoricalAllowance ON
(HistoricalAllowance.EMPLOYEECODE = TransHistoricalMaster.EMPLOYEECODE)
AND (HistoricalAllowance.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
INNER JOIN Allowance Allowance ON
(Allowance.ALLOWANCECODE = HistoricalAllowance.ALLOWANCECODE)
INNER JOIN Department Department_2 ON
(Department_2.DEPARTMENTCODE = Department.PARENTCODE)
WHERE ( Allowance.PAYING = 1 )
--------------------------------

As we are using an Advantage DB here, I posted a query on the generated
SQL to Advantage.Delphi and Alex Wong of ADS had this to say:

**********************************

The third line of the FROM clause is not constructed correctly. The
condition should be on the Employee table and the CostCentre table.
Instead, it is referencing the HistoricalAllowance table which is not
part of that join. It seems that the new viersion of the RB is not
generating a valid SQL statement.
To get the correct syntax, the INNER JOIN CostCenter should be moved to a
location after the HistoricalAllowance table, shown below.

The original statement uses the WHERE clause so the SQL engine will
determine the correct application of the join condition.

Alex


FROM Employee Employee
INNER JOIN Department Department ON
(Department.DEPARTMENTCODE = Employee.DEPARTMENTCODE)
INNER JOIN PayPeriod PayPeriod ON
(PayPeriod.PAYPERIODCODE = Employee.PAYPERIODCODE)
INNER JOIN Location Location ON
(Location.LOCATIONCODE = Employee.LOCATIONCODE)
INNER JOIN Award Award ON
(Award.AWARDCODE = Employee.AWARDCODE)
INNER JOIN TransHistoricalMaster TransHistoricalMaster ON
(TransHistoricalMaster.EMPLOYEECODE = Employee.EMPLOYEECODE)
INNER JOIN TransPerPaySequence TransPerPaySequence ON
(TransPerPaySequence.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
INNER JOIN HistoricalAllowance HistoricalAllowance ON
(HistoricalAllowance.EMPLOYEECODE = TransHistoricalMaster.EMPLOYEECODE)
AND (HistoricalAllowance.PAYSEQUENCE = TransHistoricalMaster.PAYSEQUENCE)
INNER JOIN CostCentre CostCentre ON
(CostCentre.COSTCENTRECODE = HistoricalAllowance.COSTCENTRECODE)
INNER JOIN Allowance Allowance ON
(Allowance.ALLOWANCECODE = HistoricalAllowance.ALLOWANCECODE)
INNER JOIN Department Department_2 ON
(Department_2.DEPARTMENTCODE = Department.PARENTCODE)

*****************************

I can provide quite a few other queries which are also not converted
correctly if required. Can you advise if RB will be fixed in a time frame
likely to help us?

Bill

contain
daSQL.pas in
into
statement
generate



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

rbuser

Pete, forgot to provide my email, if you have that source available pls
send. My email
bill.miller@nospam.payglobal.com
just remove the "nospam." as is the custom.
Thanks, Bill







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