sysdesigner
Member
- Joined
- Feb 8, 2004
- Messages
- 10
Hi,
We have a table with the following structure...
|autogen_key|report_id|report_period|numeric_val|line_number|
autogen_key == an autogenerated key -- also used to indicate the order that the numbers were inserted in. (same as the order received)
report_id == the unique identifier of the number set (one set of values per report)
report_period == the month that the report is for
numeric_val == the numeric value for a column and row on a report
line_number == the line number(row on the report) that the value is associated with
EG. If the report associated with an input file contained two rows with 3 columns of data in each, then the table would be loaded as follows:
|1|employeereport|200506|0|1|
|2|employeereport|200506|2|1|
|3|employeereport|200506|1|1|
|4|employeereport|200506|0|2|
|5|employeereport|200506|0|2|
|6|employeereport|200506|3|2|
That would be used to build a report like this..
Employee
Number Vacation Sick Other
000100 0 2 1
000103 0 0 3
We are doing this so that an unlimited number of rows/columns can be added in the future without database changes occurring. For example if the user adds "maternity leave" to the report and the input file, then the table is already set up to accomodate it.
My question is, how is best to explicitly tie the ordinals of the row data to the column headings? If the user wants to change the position of the columns, then we want the data associated with a report in the table to reflect that as well.
Thanks,
Shawn
We have a table with the following structure...
|autogen_key|report_id|report_period|numeric_val|line_number|
autogen_key == an autogenerated key -- also used to indicate the order that the numbers were inserted in. (same as the order received)
report_id == the unique identifier of the number set (one set of values per report)
report_period == the month that the report is for
numeric_val == the numeric value for a column and row on a report
line_number == the line number(row on the report) that the value is associated with
EG. If the report associated with an input file contained two rows with 3 columns of data in each, then the table would be loaded as follows:
|1|employeereport|200506|0|1|
|2|employeereport|200506|2|1|
|3|employeereport|200506|1|1|
|4|employeereport|200506|0|2|
|5|employeereport|200506|0|2|
|6|employeereport|200506|3|2|
That would be used to build a report like this..
EMPLOYEE LEAVE REPORT
Leave Type
Number Vacation Sick Other
000100 0 2 1
000103 0 0 3
We are doing this so that an unlimited number of rows/columns can be added in the future without database changes occurring. For example if the user adds "maternity leave" to the report and the input file, then the table is already set up to accomodate it.
My question is, how is best to explicitly tie the ordinals of the row data to the column headings? If the user wants to change the position of the columns, then we want the data associated with a report in the table to reflect that as well.
Thanks,
Shawn