B4J Report writer steps: a simple way to create a report writer application step-by-step

[PaulMeuris]

The latest step: number 6 in message #6: save/load the grid and more image processing
Message #7: an example with a new report tag...

In this tutorial you can read about the steps you can take to create a simple report writer application.
The first step in this process is to transform the DBField_selector application into a database browser.
Download the source code of the DBField_selector tool and make a copy of that project.
Rename the copy to "report_writer_steps".
And then read and execute the instructions in the tutorial text you can find in the attachment (report_writer_steps.pdf).
This is how the application looks like when you finish with the instructions:

Now that you have this application you can review and study the contents of any SQLite or MySQL database.
In the next step we will continue building up this application.
You can share your ideas on how to proceed in this thread.

 

[PaulMeuris]

Time for the next step...
When you are creating a report you need to know how to combine the information in a database.
In the second step you can add a SQL editor to the application. This will allow you to test scenario's for collecting data.
In the example below a list of the wines from the winestore database is created using an inner join with the winery table and limiting the resultset to the conditions set in the where clause of the SQL statement: wine type = 'Sweet' and wine year > 1995.

When you are satisfied with the resultset then you can export that list to a Word document with a Word table. You can even make a PDF document as well if you remove the comment in the code.

You can find the instructions to assemble the second step of this application in the attached PDF file (report_writer_steps.pdf).
Any questions? Feel free to ask.
The next step will be about the design of the report...

 

[PaulMeuris]

Step number 3...
Designing a report layout using a grid and labels.

The grid is 2000 pixels by 2000 pixels. An A4 page is set at 27 rows and 40 columns.
The fields from a database table can be dropped as a label in the grid.
Text labels can be added with a custom text.
All the labels can be moved around on the grid and will be "snapped" to the upper left corner of a cell.
The grid itself can be moved around as well.
The instructions for this step can be found in the attached PDF file (report_writer_steps_step3.pdf)

In the next step (or steps) more code is added to use report tags in the design grid and to scan the grid and create word documents…
A preview:

 

[PaulMeuris]

It's time for a new step in the development of the report writer application...
In step 4 you can read how to use a type variable "tagdata" to add some special content to the tag property of the label.

Type tagdata (labeltype As String, row As Int, col As Int, numrows As Int, numcols As Int, _
           imgdir As String, imgfile As String, _
           dbfile As String, dbpath As String, dbname As String, _
           tblname As String, fldname As String, strsql As String, text As String)

This tag content can be viewed using a tool tip when hovering over a label.

The text of a text label can now be changed and you can add an image to the grid.

You can find the instructions for this step in the attached PDF file (report_writer_steps_step4.pdf)

 

[PaulMeuris]

Step number 5: add report tags, field codes and make reports
Here's an example using report tags, field codes, text labels and database fields.

The result in Word looks like this:

You can find the instructions in the attached PDF file (report_writer_steps_step5.pdf).
In the next step you can save and load the grid labels. This will allow you to make grid templates to reuse.

 

[PaulMeuris]

Step number 6.
In this step you can find some code to save and load the grid labels.

And the image processing has improved by using an image dialog to set or change the width and height of the label that holds the image.
The dialog looks like this:

You can find the instructions for this step number 6 in the PDF file in the attachment (report_writer_steps_step6.pdf).

And now i will wait for your suggestions to improve this application.
Don't hesitate to reply with your bug reports (if any ).

Another example:

 

[PaulMeuris]

And here's one more example.
I have added a FRM (form) report tag...

The FRM tag contains the SQL statement for the customer and order data.
The first RST tag contains the same customer and order SQL statement. I have removed the customer fields from the grid.
The second RST tag contains the SQL statement for the items and parts data.

The SQL statements:

SELECT customer.CustNo,customer.Company,customer.Addr1,customer.City,orders.OrderNo,orders.SaleDate
FROM customer
INNER JOIN orders on orders.CustNo = customer.CustNo
WHERE orders.OrderNo = 1004

SELECT items.OrderNo,items.ItemNo,items.PartNo,parts.Description,items.Qty
FROM items
INNER JOIN parts on parts.PartNo = items.PartNo
WHERE items.OrderNo = 1004

And this is the result in Word:

Happy coding.