nuBuilder Forum

Posted: **Fri Jan 03, 2014 6:04 am**

Hi,

I have a report where I get data from multiple tables. The query generates from 1 – 12 rows which I then process one at a time. Each row creates a page in the report. What I am trying to do is generate just one page with all of the data. Instead, what I am getting is one page for each row in dataTable and every page is identical. The simplest solution is to just print the first page but ideally I would like to limit it to just 1 page of output.

Here is my query:

Code: Select all

    SELECT  
       parms.parms_unit,
      people.people_id,
       people.peo_name,
       people.peo_building_unit,
       people.peo_type,
       people.peo_company,
       people.peo_cell_phone,
       people.peo_home_phone,
       people.peo_work_phone,
       people.peo_address_1,
       people.peo_address_2,
       people.peo_city,
       people.peo_state,
       people.peo_zip,
       people.peo_email,
       people.peo_lease_start_date,
       people.peo_lease_end_date,
       unit.unit_id,
       unit.unit_building_unit,
       unit.unit_address_1,
       unit.unit_city,
       unit.unit_state,
       unit.unit_zip,
       unit.unit_park_1,
       unit.unit_park_2,
       unit.unit_garage,
       pets.pet_id,
       pets.pet_building_unit,
       pets.pet_dog,
       pets.pet_cat,
       pets.pet_license_num,
       pets.pet_breed,
       vehicle.vehicle_id,
       vehicle.veh_building_unit,
       vehicle.veh_make,
       vehicle.veh_model,
       vehicle.veh_year,
       vehicle.veh_color,
       vehicle.veh_license_num,
       vehicle.veh_exp_date,
       DATE_FORMAT(CURDATE(),'%M %d, %Y') AS today_date
	from parms, people
	LEFT JOIN unit ON people.peo_building_unit = unit.unit_building_unit
	LEFT JOIN pets ON people.peo_building_unit = pets.pet_building_unit
	LEFT JOIN vehicle ON people.peo_building_unit = vehicle.veh_building_unit
WHERE people.peo_building_unit = parms.parms_unit
ORDER By peo_type desc

Here is a summary of the logic for the report:
- Run above query
- Alter dataTable to add a column for each field in report
- Define a variable for each field in the report and initialize them with underscores
- Read each row in dataTable and move data to the appropriate variable
- Update the fields in dataTable that were created by the alter statement above with values from the variables

I was hoping that by only updating the dataTable once at the end that it would only create one page but that is not the case. It still generates one page for each row in dataTable.

Do you have any suggestions?

Let me know if you want to see the entire report script.

Thanks,

John Klassen

Posted: **Fri Jan 03, 2014 7:30 am**

John,
I'm not sure I understand your question correct but

Why do you add columns in your datatable?
I think the answer is to put the lines in detail section of report builder.
You could do a limit on your sql.

Johan

Posted: **Fri Jan 03, 2014 3:58 pm**

johan wrote:I think the answer is to put the lines in detail section of report builder.
You could do a limit on your sql.

Johan is right.
You coud also adjust the heigth values in Detail Section and Report Properties according to your needs:

: Detail section; DetailSection.png (48.04 KiB) Viewed 15390 times

: Report properties; ReportProperties.png (14.36 KiB) Viewed 15390 times

Posted: **Sat Jan 04, 2014 7:09 am**

Hi,

Let me give more details on what I am trying to accomplish. This application is used by residential property managers to track information about some condominium units. I am trying to generate a 1 page report that reads the various tables and displays the data including owner data, tenant data, property agent data, unit data, pets, vehicles, etc. This 1 page report is then given to the tenant, owner or agent where they can update the data in the report.

The original data is stored in the following tables:
- Parms – where user enters the unit number
- People – information about tenants, owners and agents
- Unit – information about the unit
- Pets – each unit can have 0, 1 or 2 pets
- Vehicles – each unit can have 0, 1, or 2 vehicles

I realize that the 'people' table could be normalized with the tenants, owners and agents in separate tables. Since this is the last report for this application, I do not want to make any changes to the layout of the data.

When I run the query, it returns anywhere from 1 to 12 rows. For example, if the unit has an owner, but no tenant or agent and no pets and no vehicles, it would return one row. On the other hand, if the unit has a tenant, owner, agent, 2 pets and 2 vehicles, the query would return 12 rows. There would be some duplicate data between the different rows but also some unique data with multiple pets and multiple vehicles.

As I mentioned in the original post, I alter the dataTable by adding all of the fields that are in the report. It is my understanding that I can only use dataTable when generating a report. That means that I have to use dataTable to store the output of the query AND also the input to the report. Since the query output can have 2 rows to describe the 2 vehicles, I was not sure how to display both vehicles in the report. The easiest solution seemed to be to create a variable for each field in the report, use logic to load the variables while reading each row in dataTable, and then generating the report.

Finally I am using the detail section of the report to display all of the data. As I mentioned before, all of the information I need is in each page of the report. But I only need one copy. I don’t need to have up to 12 pages that are all identical.

John Klassen

Posted: **Sat Jan 04, 2014 6:30 pm**

John,
in which section of the report you created the Report Objects needed to print all data?
All objects you have in sections other than Detail section are printed on ALL pages of the report.

Posted: **Sun Jan 05, 2014 1:45 am**

Max,

All report objects are in the Detail section. There is nothing in the other sections.

John

Posted: **Sun Jan 05, 2014 7:16 am**

John, some suggestions to find a (better) solution:

can you use UNION and/or GROUP BY instead of JOIN in your SQL? This could reduce the number of rows in dataTable without PHP code;
you add columns to dataTable, but do you delete unnecessary rows?
are you sure that the space nedded to print all data is minor that Detail section height?

Posted: **Sun Jan 05, 2014 8:12 pm**

John,

Maybe even better to put these things in page header or report header (only used once in your report)

- Parms – where user enters the unit number
- People – information about tenants, owners and agents
- Unit – information about the unit

Johan

Posted: **Sun Jan 05, 2014 10:29 pm**

Max and Johan,

Thanks for your suggestions.

I moved all of the fields from the Detail Section to the Report Section and I now get a 1 page report.

Although I may have been able to reduce the number of pages, the only solution that I really wanted was just one page.

Thanks again.

You can close this post.

John Klassen

Posted: **Sun Jan 05, 2014 10:37 pm**

nuBuilder Forum

How do I get a 1 page report?

How do I get a 1 page report?

Re: How do I get a 1 page report?

Re: How do I get a 1 page report?

Re: How do I get a 1 page report?

Re: How do I get a 1 page report?

Re: How do I get a 1 page report?

Re: How do I get a 1 page report?

Re: How do I get a 1 page report?

Re: How do I get a 1 page report?

Re: How do I get a 1 page report?