Huge Quantities of Data to Pivot and report

[Ziggy12]

Hi there
I am working with over 150k of records in xl2007. All works fine but I'm needing to merge data from different sources to create more complex reports. ie add more 150k lots of records and pivots

I have multiple pivots in one workbook linked to external workbooks and an Access DB. All diven by a userform that co-ordinates the pagefeilds on the pivottables.

The file is getting rather large 125mb and is not that emailable anymore and now there is a need to add even more pivottables linked to other data.

I have Chrsytal to query the really big DB's with available, and I also have Access although I'm no whiz with either.

What is the best way to tackle the problem of XL file size and make it distributable over a LAN in a thin client kind of way.

What do others do?

cheers
ziggy

 

[Derek Brown]

Have you considered upgrading to Excel 2010 where you can use PowerPivot to manage data from different data sources?

 

[Ziggy12]

Only we've got XL2007 and then only very recently - XL2010 Ha. Everyone else including our end users is on XL2003.

I think I'll have to learn Chrystal and do cross tabs to summarise and export to ODBC data base which XL will import as a dynamic list and then use sumifs to drive the report. Which will save a stack of hassel accomodating the way pivottable change shape when data is absent between pagefeilds.

Other plan is to use .cub files if I can get the server access to create them.

cheers
ziggy