Large Excel Database. Need Help with reporting.

  • Thread starter Thread starter Bryan Schmidt
  • Start date Start date
B

Bryan Schmidt

Hello all,

For work, I have a large Excel Database that is 14 columns wide, and
so far, 74577 Records. So it takes all 65536 Rows of the first Sheet,
and 9043 Rows of the second sheet. And I am getting about 100 new
records a day. With a single sheet of data, I can use Pivot Reporting
to my advantage, so I do a lot of manually copying of data into blank
sheets to run Pivot Reports. When I try to create a Pivot Report with
"Multiple Consolidation Ranges" I loose a lot of flexibility with my
reporting. I am looking for alternatives to storing this data, and
still have the same functionality for Pivot Reporting that I do with a
single sheet. Does anyone know of a solution to this?

Thank you :D
-Bryan Schmidt
 
Bryan Schmidt said:
Hello all,

For work, I have a large Excel Database that is 14 columns wide, and
so far, 74577 Records. So it takes all 65536 Rows of the first Sheet,
and 9043 Rows of the second sheet. And I am getting about 100 new
records a day. With a single sheet of data, I can use Pivot Reporting
to my advantage, so I do a lot of manually copying of data into blank
sheets to run Pivot Reports. When I try to create a Pivot Report with
"Multiple Consolidation Ranges" I loose a lot of flexibility with my
reporting. I am looking for alternatives to storing this data, and
still have the same functionality for Pivot Reporting that I do with a
single sheet. Does anyone know of a solution to this?

Thank you :D
-Bryan Schmidt
Click to expand...

Have you considered Access as a better tool for the job?

Regards.

Bill Ridgeway
Computer Solutions
 
2007?
Click to expand...

It's 2003. I haven't considered Access, because not everyone who
needs access to the data has Access. I guess I could export and
import data as needed. How easily does that work?
 
Hello all,

For work, I have a large Excel Database that is 14 columns wide, and
so far, 74577 Records. So it takes all 65536 Rows of the first Sheet,
and 9043 Rows of the second sheet. And I am getting about 100 new
records a day. With a single sheet of data, I can use Pivot Reporting
to my advantage, so I do a lot of manually copying of data into blank
sheets to run Pivot Reports. When I try to create a Pivot Report with
"Multiple Consolidation Ranges" I loose a lot of flexibility with my
reporting. I am looking for alternatives to storing this data, and
still have the same functionality for Pivot Reporting that I do with a
single sheet. Does anyone know of a solution to this?

Thank you :D
-Bryan Schmidt
Click to expand...

Try this one:

DataTime Documentation
http://cam70.sta.uniroma1.it/DataTimeDocumentation/

DataTime Universal (downloadable)
http://cam70.sta.uniroma1.it/DataTimeUniversal/
 
It's time to rethink the tool or the use of the tool.

1. Excel 2007 from my simple tests, accommodates heaps more rows and
columns and calculates faster because it can use the second core of a
modern dual core PC.

2. Pivot Tables are really cool and powerful but if your dataset
becomes very big, the Pivot Table no longer is able to provide it's
features which is based on keeping all the data in memory.

3. For large datasets you can use Access - and there are Pivot
features in Access 2003 as well, although not as powerful as Excel
(AFAIK). Access does not usually run slower as you add more data,
unlike Excel.

4. If your dataset is very big and your pivot parameters are numerous,
you will want to sit down and engineer the solution using Data
Analysis Cubes - in which case, you are looking at Microsoft SQL
Server Data Analysis tools - you make optimised OLAP Cubes in a
designer, store it in SQL Server. Then you use Excel to connect to
those Cubes and pluck out the reports. It is very much more formal,
needs this infrastructure, but the speed is very fast in execution.

5. Whatever design you use in the "back end", you can produce Excel
summaries and subsets to deliver to people who do not have / want the
infrastructure.

HTH
Ananda
 
Back
Top