pdf report with nested grids

Code Examples Report PDF
#1

Hi there,

I am playing around with pdf report and I really like what I see…but now it’s getting difficult

table_house
-id_house
-my_house

table_cars
-id_cars
-id_house
-my_cars

table_drivers
-id_drivers
-id_house
-id_cars
-drivers

Now I want a PDF that will look like this:

HEAD

Block 1:table_house (only 1 result)

->I don’t want to have this block go over 2 pages unless there are a lot of drivers listed
Block 2.1:table_cars WHERE id_house = table_house.id_house (only 1 result)
Block 2.2:table_drivers WHERE id_house = table_cars.id_house AND id_cars = table_cars.id_cars (multiple data displayed as grid)

->I don’t want to have this block go over 2 pages unless there are a lot of drivers listed
Block 3.1:table_cars WHERE id_house = table_house.id_house (only 1 result)
Block 3.2:table_drivers WHERE id_house = table_cars.id_house AND id_cars = table_cars.id_cars (multiple data displayed as grid)

…and so on, depending on numbers of results on cars

FOOTER

and on top of all this I want to call images from a table that will display on bottom of each site…maybe not on each site but in footer…I also tried to draw a line after Block 1 but your macro did not work for me (I probably made a mistake). Right now all my data on Block 2 (this is as far as I got with project at this point) shows up in one line. So all results are on 1 line. I need X Y Positioning for Block 1 though. Maybe you guys can give me a hint how to set this up in a nice way. Is it possible to call a grid application to display Block x.2 ?

#2

Re: pdf report with nested grids

getting there…Block 1 and Block 2.1 work…now I need to get Block 2.2 nested in each Block x.1

also I don’t really get how I can use fpdf code…there are not enough examples to get through this topic and “Click here to access the FDPF a documentation.” does not work. How do you draw a simple line for example??

$pdf->Line;

How can I use http://www.fpdf.org/ codes? For example a line break

$this->Ln(4);

Where can I see what paramteres of each code should look like for your macros???

#3

Re: pdf report with nested grids

ok…to move my next block add in code: sc_pdf_ln(10);

to draw a line I will use images…solved thanks to my support buddys that helped my in chat…this way I can do anything concerning layout of PDF…great tool…closed for now

#4

Re: pdf report with nested grids

Can you make a striped demo from your report and upload this here? Thank you!

#5

Re: pdf report with nested grids

I am still having 2 problems

  1. Getting the SELECT statement for my second subselect working, depending on my first subselect
  2. I need a pagebreak so each subselected data “car” starts on a new page using a pagebreak…

So I want my PDF look something like this:

/------------------ Page 1 -----------------/
sc_pdf_print($cell_id_house);
sc_pdf_print($cell_data);
sc_pdf_ln(10);
foreach ({car} as $NM_ind => $Dados) each car data should start on a new page, but the PDF code sc_pdf_accept_page_break does not work for me
{
sc_pdf_print_sub_sel($cell_car_id_car[$NM_ind]);
sc_pdf_print_sub_sel($cell_car_id_house[$NM_ind]);
sc_pdf_print_sub_sel($cell_car_data[$NM_ind]);
sc_pdf_ln(10);
foreach ({results} as $NM_ind => $Dados) only print those drivers where id_car and id_house match $cell_car_id_car, $cell_car_id_house
{
sc_pdf_print_sub_sel($cell_drivers_id_driver[$NM_ind]);
sc_pdf_print_sub_sel($cell_drivers_id_car[$NM_ind]);
sc_pdf_print_sub_sel($cell_drivers_id_house[$NM_ind]);
sc_pdf_print_sub_sel($cell_drivers_data[$NM_ind]);
sc_pdf_ln(5);
}
}
/-------------------------------------------/

My Select statement works for my first subselect…I get only those cars, that belong to my house…

SELECT
id_car,
id_house,
data,
FROM
table_car
WHERE id_house = ‘{id_house}’

…but not for my second subselect which is nested in each of my first subselects…

SELECT
id_driver,
id_house,
id_car,
data
FROM
table_drivers
WHERE id_house = ‘{id_house}’ AND id_car = ‘{id_car}’

do I have to apply any PHP within the Page Code or should it work using the Select statement? Unfortunatly there are not enough tutorials to get through this topic. In the end it should look like this:

-House of 2 cars
PageBreak
–Car A
—Driver A1
—Driver A2
Pagebreak
–Car B
—Driver B1
—Driver B2

#6

knuffiwuffi, did you get an answer to the subselect within a subselect? I am having the same issue because I am using this in a similar way. I have one invoice within each there are multiple work orders that have 1 to many line items. I’ve gone as far as I can with modifying my CODE in FPDF even using the Events to load variables but you cannot modify a subselect WHERE criteria based upon the current subselect onRecord event. You can use it within the primary select statement but you cannot modify a subselect SQL statement.

Visual representation:
Customer Invoice
Pagebreak

  • Work Order A
    — Line Items (1:M) - use the primary key from the workorderdetails table (pkey: woiid).
    — woiid: 10547
  • Work Order B
    — Line Items (1:M) - use the primary key from the workorderdetails table (pkey: woiid).
    — woiid: 10548

A subselect cannot be altered using the Scriptcase functions:
$save_where = {sc_where_orig};
$save_current_filter = {sc_where_filter};
$save_current_where = {sc_where_current};

These will only allow you to change the primary SQL select statements.

Any tips or feedback on how to change the WHERE clause of subselect SQL statement as it iterates through the first subselect statement? Hence this would change the WHERE clause of the subselect statement within the first subselect statement?

#7

I too am struggling her. I have a report I am trying to create for a quote where I have the initial quote table, beneath that I have a quote options table of which there might be several options for each quote, and below this table I have an options detail table which references multiple line items for each option. I can’t seem to fin a way to produce the line items on the report whilst essentially having the option numbers as sub headings, a bit like a nested grid inside a nested grid. Have either of ytou managed to progress this idea further, or come up with an alternative method to implement?