Need Help: How to Express One-to-many data relationships

[Ronald Kinion <ro...@zones.com>]

I've recently been handed a problem and would like some advice on how I
can use poi to accomplish it.  I don't work with spreadsheets too often
and so I'm unfamiliar with many concepts and terms and I found it
difficult to find in the archive messages concerning what I want to do.
Please forgive me if the answer is actually pretty easy to accomplish.  
 
We currently use poi to generate 'reports' based directly from java sql
ResultSets of a query to our oracle database. We've had this for many
years and it works great for the most part.  One of the features of our
reports are that user's are able to customize what information and how
many columns to express in a report.  The problem I've got though has to
do when we generate results for a query joining tables with
'one-to-many' relationships. For instance, in an order report a customer
might want a report on the products ordered and details about each
product such as quantity and serial numbers associated with the product.
Here is an example of what the ResultSet currently might look like:
(hope this doesn't get too mangled)
 

Order    Sku    ProductName     SerialNumber    ItemPrice    Qty
ItemTotal 
O1199    a1121  BasicWidget     S1000           1.00         1      1.00
O1234    a6789  WonderWidget    s1234           5.00         5
25.00
O1234    a6789  WonderWidget    s1235           5.00         5
25.00
O1234    a6789  WonderWidget    s1236           5.00         5
25.00
O1234    a6789  WonderWidget    s1237           5.00         5
25.00
O1234    a6789  WonderWidget    s1238           5.00         5
25.00
O1236    a6801  AmazingTool     abc99           15.00        1
15.00


As you can probably see with the second order number that just dumping
the results out like this means that when there are multiple serial
numbers for one product the same information will come back multiple
times. If a user tries to eyeball a total or generate a formula for the
total over all the orders that total would seem to be 141.00, but in
actuallity it would only be 41.00.  So what I've been asked to do is to
change our report generator so the above result would appear in the
excel files more like this:


Order    Sku    ProductName     SerialNumber    ItemPrice    Qty
ItemTotal 
O1199    a1121  BasicWidget     S1000           1.00         1      1.00
O1234    a6789  WonderWidget    s1234           5.00         5
25.00
                                s1235           5.00

                                s1236           5.00

                                s1237           5.00

                                s1238           5.00

O1236    a6801  AmazingTool     abc99           15.00        1
15.00


Now, I have some idea's to accomplish this but keeping track of all the
possible columns and checking each one of them to see if the value has
changed from the previous row before creating a new row started to seem
really complicated and confusing.  So I'm wondering if there are any
already existing techniques or features that would help to simplify
this, or event some good examples or tutorials that would show me how to
do this right.  Or maybe I'm thinking about this all wrong?  There is a
lot about the art of creating spreadsheets I don't understand so might
there be some other approach I can use? Any help or ideas would be
greatly appreciated.

Thanks, 

-- 
Ronald Kinion 
Senior Web Developer, MIS 
Zones, Inc. 
1102 15th Street SW, Suite 102 
Auburn, WA  98001-6509 
Phone: 253-205-3494 
Ronald.Kinion@zones.com <ma...@zones.com>  
www.zones.com <http://www.zones.com/>  


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org