Olap from access to excel

[mcgrcoAgain]

Hi,

I am trying to build an analysis tool to assist one of my colleauges. I start with a results tables in my access db with 2 days worth of the data in extatly the same format.

I then create a cube in excel with 2 dimensions. One shows a Legal entity -> Ultimate parent -> individual. The second demension is the reporting data.

The cube hirarchy fuctions fine and the two dates are displayed next to each other but the problem I have is that I can't get a differnces column only the grand total. I tried taking a copy of the field in the pivot layout but unlike normal pivots I cant click into the propertis. Is this a limitation of olap in excel. It’s the first time I've used it. Any suggestions or are there any free tools out there that make the job easier.

Any help is appreciated

 

[shades]

Howdy. I found in a couple of situations, it was easier to pull the data separately onto separate worksheets, then use another worksheet to consolidate (using formulas). It gives move flexibility in displaying what and how you want.
________
GLASS PIPES

 

[mcgrcoAgain]

Thanks Shades. Unfortunatly the nember of records prevents this and the whole purpose of the cubes was speed of analysis. Seems a shame to limit the olap functionality in excel.

 

[mcgrcoAgain]

Well done microsoft, another great fully fuctional feature

http://office.microsoft.com/en-gb/help/HP052389601033.aspx

You cannot create calculated fields (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) or calculated items (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.) in reports with OLAP source data.

 

[shades]

When you write "number of records", how many do you mean? I setup weekly data pulls into Excel, involving many worksheets with several hundred thousand data pieces in each [row/col combinations) (in a separate workbook, then used formulas for setup/summary in same workbook), and finally the analysis was completed on a another workbook. Setup was the difficult part. But then I could pull all data (with VBA) in eight workbooks, and automated setup of analysis and charts/tables, all in about 15 minutes. When they moved to a new system, the data increased, and eventually took 45 minutes.
________
Buy vapor genie