a tip re subreports in Alpha Five

[Richard Rabins]

a tip re subreports in Alpha Five

From [email protected]

sub reporting tip re Alpha Five http://nddi.net/videos/printhtml/


A little background:

One of the requirements that needs to be addressed in application development is the ability to create multiple sub-reports with your reporting tool. Sub-reports provide us a way to print information from child tables (e.g. Invoice Detail, Payments. Notes, etc.) related to the parent table (e.g. Invoice Header) in multiple different formats. This has been available in Alpha using "Sets" when based on DBF files. Unfortunately, DBFs are not the best option for professional applications for a number of reasons (a topic for discussion at some other time). But RDBMSs (MS SQL Server, MySQL, Oracle, etc.) cannot currently be used in a sub-report in the current Alpha Five report writer.

HOWEVER, once again Alpha provides a way to solve this problem.

The video shows a technique that not only gives you some incredible fine grained control of your layout, but also leverages existing HTML coding knowledge. What appears to be a rather simple little function - PrintHTML() - opens some seriously powerful capabilities for any developer's reporting toolkit. In short - you have COMPLETE control of your layout in any given segment of your report. If you can design it in HTML, you can display it on a report. This particular customer has a "boilerplate" need. We merge both HTML information stored in the event header table, with detail (sub-report style information) from the Event Detail tables to create nicely formatted proposals, contracts and invoices.

In addition, there are performance and flexibility gains by moving the initial creation of HTML and layout information to the SQL Server rather than Alpha as shown in the video.

Let me know if this gives you enough to go on, or feel free to contact me if you have any questions.
_________________________________________
Jim Dusoe

[Davidk]

Re: a tip re subreports in Alpha Five

Yikes! Going to have to try this one. Thanks... and my head is fuzzy now.

[frankbicknell]

Re: a tip re subreports in Alpha Five

That is simply wonderful news.

[frankbicknell]

Re: a tip re subreports in Alpha Five

and my head is fuzzy now.

After watching the video my head is all fuzzy now too, but it is not a good fuzzy. I think I might need a functioning sample to be able to follow that. I will give it a try though, it's an urge I can't control.

[asawyer13]

Re: a tip re subreports in Alpha Five

Excellent. I will be using this technique in my MySQL app that I'm getting ready to work on.
Alan

[asawyer13]

Re: a tip re subreports in Alpha Five

Would it be possible to get the html code for the example report so I can review how it's laid out?
Alan

[jdusoe]

Re: a tip re subreports in Alpha Five

I've had a few requests for the HTML and other code for this technique. I will be recording a more detailed video of this in the next week to 10 days and will post here along with the various code pieces.

[Steve Workings]

Re: a tip re subreports in Alpha Five

Andy Meer and I, in our current project, have been writing reports in the style Jim's doing, including storing all the code in the table and bypassing the Alpha report generator and PDF driver completely. It's wicked fast and quickly becoming my preferred method though, yes, it does take a little more work than using the built-in report generator.

[asawyer13]

Re: a tip re subreports in Alpha Five

Steve, Now you've gone and done it. lol I suspect we'll all want more information on what you're doing, I know it would be of interest to me.
Alan

[asawyer13]

Re: a tip re subreports in Alpha Five

@Jim,
Thanks, I am using mysql but I can do a stored procedure and/or trigger also to do something similar. I am interested in the html code of the finished report just so I can see how you laid out the page, so if you are willing to send it, even thru PM that would be great.
Alan

[asawyer13]

Re: a tip re subreports in Alpha Five

Does anyone have a good example of an html report that they've done that they're willing to send me the the html code?
I'm just wanting to use it as a basis for some html reports that I want to create, and I'm thinking it will be faster to use something that someone already has as a place to start. Just the html page. I don't need source code, just the finished html report.

[Steve Wood]

Re: a tip re subreports in Alpha Five

Here is a starter example of how it would work. Col 1 is a character example, col 2 is a numeric that you want to present as currency. The CRLF's just help the code look good in the browser source.
The last line "?lst" paints the HTML in the browser. You could also make lst your source for the HTML portion of an email.
If you had a lot of text to output, even if it is html, you might use delimiters like <<%txt%...%txt% to contain that text.

This is a simple example, you can build any sort of HTML if you take the time. You can also use CSS instead or in conjunction with the Table HTML.

(This is a DBF example. SQL would be similar but you might use toPropertyArray() or toString() to build a list of values).

dim lst as c = ""
dim hdr as c = "<tr><td>Column 1 Label</td><td>Column 2 Label</td></tr>" + crlf()
dim tbl as p
tbl = table.open("mytable")

lst = "<table>" + crlf() + hdr

'put in a query if needed

tbl.fetch_first()
while .not. tbl.fetch_eof()
lst = lst + "<tr><td>"+alltrim(tbl.field1)+"</td><td>"+str(tbl.field2,10,2,"$(")+"</td></tr>" + crlf()
tbl.fetch_next()
end while
lst = lst + "</table>"

lst = lst + "<hr>"
lst = lst + "This is your total due: <strong>" + str(tbl.amtdue,10,2,"$(") + "<strong>"

tbl.close()

?lst

Disclaimer: witten off the cuff, watch for typos

[MichaelCarroll]

Re: a tip re subreports in Alpha Five

Andy Meer and I, in our current project, have been writing reports in the style Jim's doing, including storing all the code in the table and bypassing the Alpha report generator and PDF driver completely. It's wicked fast and quickly becoming my preferred method though, yes, it does take a little more work than using the built-in report generator.

Steve,

Ok I get what jim is doing and then passing it to the report writer. What are you passing it to? A5w page?

Thanks,

Michael

[[email protected]]

Re: a tip re subreports in Alpha Five

Hi Richard, I saw this vid a couple of years ago and it was amazing, I've finally tracked down the thread but the vid is gone, is it possible to re-post the link?
cheers
Brad