Advice needed for Pivot table

[Guest]

Good day everybody,

I am new to Pivot table and I need to build a database for a pivot table to
use.

The database will have the dimension of :
1. Company
2. Category
3. Account

The values are :
1. Last Year monthly amount (i.e. 12 figures)
2. Last Year-to-date amount(i.e. 12 accumulative figures)
3. This Year monthly amount (i.e. 12 figures)
4. This Year-to-date amount(i.e. 12 accumulative figures)
5. Next Year monthly amount (i.e. 12 figures)
6. Next Year-to-date amount(i.e. 12 accumulative figures)

I would like to seek your advise on how do I organise my data structure to
enable pivot table to work efficiently.

A. Do I make the values to be separate fields i.e. 6x12 = 72 fields?

B. Do I make them with three fields as below.
Year = 2003, 2004, 2005 (i.e. Last Year, This Year, Next Year)
Period = 1 to 12 (i.e. 1=Jan, 2= Feb.....)
Amount

Method A will have less records but more fields.
Method B will have more records but less fields.
Which method will give the best structure for Pivot table?

For Method B, if the records in my database(DBF format) has more than 65,536
records(exceed Excel maximum rows), will I have problem to link it to Pivot
table?

Thank you in advance and I hope to hear from you.

Best regards,
Julie

 

[arno]

Hi Julie,

Method B will have more records but less fields.
Which method will give the best structure for Pivot table?

Go for B. Make sure you have lot's of columns with all the categories
you (might) need. Eg. one for year (2004), one for period (02) and
anotherone for year&period (like 200402), one for type of year
(current, previous, next, cumulated current/previous/next) etc. etc.

For Method B, if the records in my database(DBF format) has more than 65,536
records(exceed Excel maximum rows), will I have problem to link it to Pivot
table?

You will not have a problem as long as you do _not_ import the data
into an excel sheet. Instead, use your dbf-file as a data-source
directly (odbc or whatsoever).

regards

arno