Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot tables: multiple consolidation ranges
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot tables: multiple consolidation ranges
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why do consolidation ranges in pivot tables not show field names | Excel Discussion (Misc queries) | |||
MSDN article on Excel Limitations in Pivot Tables for Multiple consolidation ranges | Excel Worksheet Functions | |||
Pivot Table-Multiple Consolidation Ranges | Excel Discussion (Misc queries) | |||
Multiple Consolidation Ranges in pivot tables | Excel Worksheet Functions | |||
Pivot Tables - Multiple Consolidation Ranges | Excel Programming |