OLAP Data Cube Question

[Guest]

Is there a way to have a data cube create an "average" in Excel? Looking at
the options, it only lists sum, min, max, & count. I really need an average
function. Is it possible to write a macro to do it w/ an average?

 

[Guest]

Nope... To the best of my knowledge if you create a cube those are your only
options. I assume that creating the pivot table directly in Excel (foregoing
the Cube) is not an option?

 

[Guest]

Well, I built a pivot table and it looked fine, but then my director saw it,
and she wanted an easier way to pull reports. She wants to be able to click
the date option, and have to only click on the quarter or month if she wants
to pull that report, instead of having to click each individual date for that
time period. If possible to do that with the pivot table, that will be even
better.

 

[Guest]

So you have a date field which is actual dates. Right click on that field and
select Group. You can group by Day, Month, Quarter and/or Year. It is a great
way of aggregating your data.

 

[Guest]

When I grouped it, it created 2 new colomns, is there a way to group it where
the drop down for Date has the breakdown....my only worry is the person that
is going to be using this report, she isn't Excel savvy, so I don't want to
have something that is going to take her a while to learn how to use...

ex.
2005
Qtr 1
Jan
1
2
Feb
Mar
etc...

 

[Guest]

Try setting your grouping to Year Quarter and Month. Drag the year and
quarter to the top just above the table. This is where she can select the
appropriate period. Your dispayed data should now be just the months. If she
double clicks on any number in the table a new sheet will be spawned showing
her all of the info for that month. Try dragging the memebers of the time
dimension around until you get something you like. It takes a little practice
but you will get the hang of it. I also recommend adding a format to the
pivot table to make it easier for the user to understand what they are
seeing. Too many numbers are just data. Well organized reports make the
numbers into information. That takes practice. One day I am hoping to be good
at it myself.