Excel Pivot Table help

Moredhel · 9 Jun 2010 at 12:50

Hi guys,

I've a pivot table which uses hours in the Values area. I need to show this as number of business days (divide it by 8)

I was wondering if there's a way of using a custom format to divide the number by 8?

The data comes from an MDX query from an OLAP cube, I tried adding a calculated measure to the OLAP cube to list it in days rather than hours, but that reduces the pivot table refresh time from near instant to about 10 minutes, which isn't really usable.

Lastly but very importantly, I can't use macros as this will be going into Excel Services when it's done.

I suppose the final option I'd have would be to use the JavaScript Object Model in SharePoint 2010 to update the field values to themselves / 8.

Any other ideas? I'm hoping for a quick fix rather than the pain of having to do it any of the ways I listed.

Participant · 9 Jun 2010 at 13:14

Can't you just add a calculated column on the end of the query data then use this field in the pivot table values section?

Moredhel · 9 Jun 2010 at 13:18

I did that. As I said it made the pivot table refresh time jump from near instant to 10 minutes.

The query data comes from an Analysis Services OLAP cube, I added the calculated measure to the cube, and it seems Excel does the calculation for that measure on the fly so it's really slow. It should be built with the rest of the cube, so I'm not sure why Excel has to calculate anything but that's the only reason I can think of for the jump in time.

Participant · 9 Jun 2010 at 13:46

Moredhel said:
I did that. As I said it made the pivot table refresh time jump from near instant to 10 minutes.

You did not do that, you added it to the cube. As I said, add it to Excel not the cube. The cube is not the spreadsheet. Put the calculation in the spreadsheet and it will have no bearing on the query calculation time.

I.e. once the data is in a table in Excel, do =A1/8 just to the right of the table.

Moredhel · 10 Jun 2010 at 10:00

That didn't do what I needed. Because I needed it within the pivot table. In the end I just used the Javascript Object Model in Excel Services to change it once the area has been rendered in the web part.

Now I have a new issue!

If the user changes the filter to show say, a new week in the Time column, the days in that week are automatically collapsed. I -really- need these to be expanded to show days by default, and I cannot for the life of me find an option to do this. Analysis services cubes expanded their data by default. I assumed this was something excel did but the change to Analysis Services 2005 makes it collapse by default!

Participant · 10 Jun 2010 at 11:05

Moredhel said:
That didn't do what I needed. Because I needed it within the pivot table.

You honestly don't know how to extend a pivot table's data range by one column?

Moredhel said:
Now I have a new issue!

If the user changes the filter to show say, a new week in the Time column, the days in that week are automatically collapsed. I -really- need these to be expanded to show days by default, and I cannot for the life of me find an option to do this. Analysis services cubes expanded their data by default. I assumed this was something excel did but the change to Analysis Services 2005 makes it collapse by default!

You using Office 2007? What happens if you expand all then refresh the data, does it collapse again?

Moredhel · 10 Jun 2010 at 11:11

Oh that expands it then, but it simply HAS to expand by default. Users will never expand it otherwise, and then they'll complain that data is missing. I know this because we've already had complaints.