Topic: Report Help

Hi All,
I'm moving right along with my project, thanks to the outstanding support I've been receiving from folks on the forum.
.
I'm trying to print a one page form with the information on my Equipment form. See the attached PDF file.
.
Everything works fine until I get to the bottom of the form with the Service information.
The Table on the bottom shows ALL of the service records for this piece of equipment.  However I would like to show only the latest service record info.
.
I found a code section on the Report Writer and it looks to me like this is where you might put in some Pascal code to select only certain records.  I tried putting the following into the code section:
strftime('%m/%d/%Y',date(julianday((select max(SvcDate) from tbl_Service
where tbl_Service.id_tbl_Equip = tbl_Equip.id)));
, but it doesn't like it.  It's telling me that it wants a ")" just before the "max"
.
If I run the report WITHOUT the code above, it works but it returns a separate page for each one of the Services in the table.  As I mentioned, I only would like the latest record info.
.
I've never tried anything like this in the Report Writer so I basically have no idea what I'm doing.
.
Any thoughts will be appreciated.
Thanks, Frank

Post's attachments

Attachment icon Equip Form.pdf 75.01 kb, 205 downloads since 2021-09-06 

Re: Report Help

The record selection is outside the report Writer. You need to select the records before you call the report writer.


You need to use the Report SQL function instead of the Report function. The Report SQL let you select which records should be printed. The other function prints all.

3 (edited by derek 2021-09-07 10:19:25)

Re: Report Help

You could always use the service records grid as your report writer selection parameter.
This gives you the flexibility of choosing which service record you want to print, not just the most recent one.
Derek.

Post's attachments

Attachment icon servicerecords.zip 338.38 kb, 192 downloads since 2021-09-07 

Re: Report Help

teco049,
Thanks for the suggestion.  I took a look at the Report SQL but I'm not sure how to use it?  It looks like it wants some SQL code to select records but I'm not sure how to go about formatting a request.  It looks like it might be a fairly steep learning curve on how to use it.  I believe I have been able to use the code from Derek to do what I want to do.
-------------------------------------------------------------------------------
Derek,
I was finally able to implement your suggestions and things are looking good so far.
I had a problem with my Report, in that I had everything in the Master band and NO Data band.  Needless to say that was making it difficult to do what I wanted and you suggested.
Also I had the formatting of Section 1 of the Report Action button incorrect.
.
Once I got all this stuff straightened out things started to work the way I wanted.
.
Thanks
Frank

Re: Report Help

Derek,
I'm now able to print All of the Service Records, modeled after your example code and this works fine for All of the Service records.
.
What I would like to do is to print a report showing only what needs to be serviced next.
.
Lets say there have been 5 service records for a piece of equipment.  I would like a report showing all of the pieces of Equipment and their last Service Record ONLY.  For this report I don't want to see all the previous service history for each piece of equipment.  This report would tell my user only what they need to service next.
.
I'm thinking that it would be something like using max(SvcDate), but I'm not sure how to select just the most recent Service Record.
.
Thanks, Frank

6 (edited by derek 2021-09-21 09:30:42)

Re: Report Help

Hi Frank,
You could probably do this in FastReport itself but I prefer to do all the work (where possible) in MVD as it's more efficient and then just pass through what is needed for printing.
In the case of only the most recent service records for all items of equipment, I'd use REPORTSQL (please see the attached).
Derek.

Post's attachments

Attachment icon servicerecords 2.zip 343.07 kb, 186 downloads since 2021-09-21 

Re: Report Help

Derek,
Your program looks good.  It's been suggested to me before that I use Report(SQL) but when it opens up with that blank page I didn't have any idea how to use it.
.
Your example gives me a good starting point as to what type of stuff goes into a query.
.
Thanks, Frank