pivot tables: multiple consolidation ranges

  • Thread starter Thread starter matthew c. harad
  • Start date Start date
M

matthew c. harad

i have been using pivot tables for data analysis. i am
running into a problem, however, because i have more than
256 columns in my dataset, and excel only allows that many
columns on a worksheet.

to get around this limitation i am trying to use multiple
consolidation ranges as the data source for my pivot
table. i am having trouble getting this to work.

am i misuderstanding the purpose of multiple consolidation
ranges? i have 350 items in my dataset, and perhaps 1500
columns i would like to examine for each item. can't i
just lay all these columns out across multiple worksheets,
then use multiple consolidation ranges to bring them all
into my pivot table?

i have tried to do something like this but keep failing
and am confused about the results.

do i have to use a .cub file or something like that to do
what i want to do?
 
Hi matthew,

Thank you for posting in MSDN managed newsgroup!

From my understanding to this issue, you are going to analyse the data in Pivottable of Excel. However, there are many columns in your dataset
which exceeds the field limitations of pivottable. Then you try to use the "Multiple consolidation ranges" feature of Pivottable as the data source,
but failed.

So far as I know, when you choose the "Multiple Consolidation Ranges" feature and obtain the data from several worksheet, the layout of data
from different sheet should be similar so that Pivottable can analyze these information and perform the specified operation for you to build one new
pivottable. For example, the kb article Q213947 will introduce this for you. Please visit:
HOW TO: Create PivotTables from Multiple Consolidation Ranges in Excel 2000
http://support.microsoft.com/?id=Q213947

From this sample, you can find that three sample data blocks all contains some same column and row items. This way, pivottable will know how to
combine these data from different worksheet into one pivottable.

In your scenario, since there is so many columns in differenct worksheet, I don't think the "Multiple consolidation Ranges" will be the easy way for
you to obtain the necessary data.

Based on my experience, two methods below may provide some assistances for you regarding this issue:
1. In the client side, you will need to obtain all the data from the data source into local. Because Excel workbook can only have 256 columns, you
will need to store all the data into several worksheets. You will need to programmatically get all the necessary data from different worksheet and
then perform the analysis according to your scenario. Then you can create one pivottable in one worksheet of Excel to display the result. All
the procedure in this methods will spend a lot of time. We will need to wait for all the data downloaded from the wire and store them in the
memory and then search and calculate them.
This is to way, the local computer should have fast CPU and much memory to calculate and store all the data. Furthermore, you may need to
test this a lot to optimize the speed. If not, the performance will not be very excellent.

2. Since the size of data you want to process is not very small, I'd suggest you can perform all the analysis mission in the server-side. You can
write some mdx query to retrieve the result set and then display the result in the pivottable table. This way, we can take full advantage of the
powerful calculation capacity of data server and save the client processing time which will give the users a very smooth user experience.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top