How do I get a 1 page report?

How do I get a 1 page report?

Postby JohnKlassen » Fri Jan 03, 2014 2:34 pm

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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: How do I get a 1 page report?

Postby johan » Fri Jan 03, 2014 4:00 pm

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
johan
 
Posts: 298
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: How do I get a 1 page report?

Postby massiws » Sat Jan 04, 2014 12:28 am

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:
DetailSection.png
Detail section
DetailSection.png (48.04 KiB) Viewed 7807 times

ReportProperties.png
Report properties
ReportProperties.png (14.36 KiB) Viewed 7807 times
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: How do I get a 1 page report?

Postby JohnKlassen » Sat Jan 04, 2014 3:39 pm

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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: How do I get a 1 page report?

Postby massiws » Sun Jan 05, 2014 3:00 am

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.
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: How do I get a 1 page report?

Postby JohnKlassen » Sun Jan 05, 2014 10:15 am

Max,

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

John
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: How do I get a 1 page report?

Postby massiws » Sun Jan 05, 2014 3:46 pm

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?
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: How do I get a 1 page report?

Postby johan » Mon Jan 06, 2014 4:42 am

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
johan
 
Posts: 298
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: How do I get a 1 page report?

Postby JohnKlassen » Mon Jan 06, 2014 6:59 am

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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: How do I get a 1 page report?

Postby massiws » Mon Jan 06, 2014 7:07 am

.
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy


Return to Report Writer