J
JS2004R6
I'm trying to figure out how to create a report, based on a table that is
created via code. The table does not have a fixed number of columns, so I'm
not sure how to address that issue in the report design.
Here's how the process goes:
1. User selects a company from a list (via form) and clicks button to get
reports for that company.
2. Code finds all records that match the company and appends them to a blank
table (tblSelectedCompany). In this table, each row is one record (monthly
report) for the company (like this):
Col1 (Widget1) Col2 (Widget2) ...
Row1 (Rpt_Jan06) $125 $55
Row2 (Rpt_Mar06) $200 $62
....
3. The report needs to be formatted so that each row is a column, and each
column is a row (like this):
Rpt_Jan06 Rpt_Mar06 ...
Widget1 $125 $200
Widget2 $55 $62
....
So, code takes the records from this table and creates a new table based on
the number of records found. The fields for this new table are (for the
example above): ItemName, Report1, Report2. If there were seven reports
then there would be additionals fields for Report3 thru Report7. The code
loops through the tblSelectedCompany records and appends them to
tblReportData (thus getting the "correct" formatting for the report).
4. This is where I'm stuck. I don't know how to create a report that will
support being based on a table that dynamically changes the number of fields
(in the detail section) of the report.
Thank you for taking the time to read all of this. If it hasn't made any
sense please let me know and I'll try to explain it better. Otherwise, I'll
take any and all suggestions on how to create the report, or re-visit the
creation of the tables or how it's all setup.
Thanks much!
James
created via code. The table does not have a fixed number of columns, so I'm
not sure how to address that issue in the report design.
Here's how the process goes:
1. User selects a company from a list (via form) and clicks button to get
reports for that company.
2. Code finds all records that match the company and appends them to a blank
table (tblSelectedCompany). In this table, each row is one record (monthly
report) for the company (like this):
Col1 (Widget1) Col2 (Widget2) ...
Row1 (Rpt_Jan06) $125 $55
Row2 (Rpt_Mar06) $200 $62
....
3. The report needs to be formatted so that each row is a column, and each
column is a row (like this):
Rpt_Jan06 Rpt_Mar06 ...
Widget1 $125 $200
Widget2 $55 $62
....
So, code takes the records from this table and creates a new table based on
the number of records found. The fields for this new table are (for the
example above): ItemName, Report1, Report2. If there were seven reports
then there would be additionals fields for Report3 thru Report7. The code
loops through the tblSelectedCompany records and appends them to
tblReportData (thus getting the "correct" formatting for the report).
4. This is where I'm stuck. I don't know how to create a report that will
support being based on a table that dynamically changes the number of fields
(in the detail section) of the report.
Thank you for taking the time to read all of this. If it hasn't made any
sense please let me know and I'll try to explain it better. Otherwise, I'll
take any and all suggestions on how to create the report, or re-visit the
creation of the tables or how it's all setup.
Thanks much!
James