I was wondering
if anyone may have some suggestions for a couple of user
issues I am trying to solve with Excel's Pivot Table
Services.

I have built a couple of OLAP cubes in which Pivot table
services has been mandated as the front tool. There are
two sets of functionality users are asking for that I
have not been able to provide answers too. I know this
funtionality exists in other front end tools (ie. such as
business objects query md), however right now other tools
are not an option.

The tools pieces of functionality or work arounds I am
looking for a
1) The ability for a user to formulate a predefined list
of dimension members that they report on. For example if
i have a customer dimension that contains a million
customers, but i report on the same 50 customers
everyday, can i save this list of customers some how and
customize the table view to only show my customers on my
list. I do not want to have to select each customer
everyday.

2) Is it possible to open a dimension in the pivot table
at a particular level. For example if I have a dimension
that has three levels (starting at level 1 - the highest
level). Pivot table services will always open the
dimension at level one. Is there a way that when i drop
the dimension into the pivot table it will open at level
2 or 3?

If I am not clear on something please let me know. Any
assistance would be greatly appreciated.

Thanks

Craig

On 1), do the users have some kind of ID. If so they can filter their
customers by setting this ID as a page field. This way they will be able to
select their ID from the drop-down and see just their customers.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Craig" wrote in message
...
I was wondering
if anyone may have some suggestions for a couple of user
issues I am trying to solve with Excel's Pivot Table
Services.

I have built a couple of OLAP cubes in which Pivot table
services has been mandated as the front tool. There are
two sets of functionality users are asking for that I
have not been able to provide answers too. I know this
funtionality exists in other front end tools (ie. such as
business objects query md), however right now other tools
are not an option.

The tools pieces of functionality or work arounds I am
looking for a
1) The ability for a user to formulate a predefined list
of dimension members that they report on. For example if
i have a customer dimension that contains a million
customers, but i report on the same 50 customers
everyday, can i save this list of customers some how and
customize the table view to only show my customers on my
list. I do not want to have to select each customer
everyday.

2) Is it possible to open a dimension in the pivot table
at a particular level. For example if I have a dimension
that has three levels (starting at level 1 - the highest
level). Pivot table services will always open the
dimension at level one. Is there a way that when i drop
the dimension into the pivot table it will open at level
2 or 3?

If I am not clear on something please let me know. Any
assistance would be greatly appreciated.

Thanks

For question 1) try selecting the fifty customers you want and group them and
then save the spreadsheet and use the spreadsheet to open the OLAP cube. For
question 2) try saving the OLAP cube file in a spreadsheet with the
dimensions opened and see if that preserves the format.

"Craig" wrote:

I was wondering
if anyone may have some suggestions for a couple of user
issues I am trying to solve with Excel's Pivot Table
Services.

I have built a couple of OLAP cubes in which Pivot table
services has been mandated as the front tool. There are
two sets of functionality users are asking for that I
have not been able to provide answers too. I know this
funtionality exists in other front end tools (ie. such as
business objects query md), however right now other tools
are not an option.

The tools pieces of functionality or work arounds I am
looking for a
1) The ability for a user to formulate a predefined list
of dimension members that they report on. For example if
i have a customer dimension that contains a million
customers, but i report on the same 50 customers
everyday, can i save this list of customers some how and
customize the table view to only show my customers on my
list. I do not want to have to select each customer
everyday.

2) Is it possible to open a dimension in the pivot table
at a particular level. For example if I have a dimension
that has three levels (starting at level 1 - the highest
level). Pivot table services will always open the
dimension at level one. Is there a way that when i drop
the dimension into the pivot table it will open at level
2 or 3?

If I am not clear on something please let me know. Any
assistance would be greatly appreciated.

Thanks

Thanks for the reply Nick,

Each user has a id defined so they can log into the cube,
however we do not have any type of row level access to
the data (ie. we do not associate a user with particular
clients). Basically these lists can change frequently
and should be able to be maintained by the end user. Not
sure if what you mentioned below would still be
applicable?

Craig

-----Original Message-----
Craig

On 1), do the users have some kind of ID. If so they
can filter their
customers by setting this ID as a page field. This way
they will be able to
select their ID from the drop-down and see just their
customers.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England



"Craig" wrote in
message
...
I was wondering
if anyone may have some suggestions for a couple of
user
issues I am trying to solve with Excel's Pivot Table
Services.

I have built a couple of OLAP cubes in which Pivot
table
services has been mandated as the front tool. There
are
two sets of functionality users are asking for that I
have not been able to provide answers too. I know this
funtionality exists in other front end tools (ie. such
as
business objects query md), however right now other
tools
are not an option.

The tools pieces of functionality or work arounds I am
looking for a
1) The ability for a user to formulate a predefined
list
of dimension members that they report on. For example
if
i have a customer dimension that contains a million
customers, but i report on the same 50 customers
everyday, can i save this list of customers some how
and
customize the table view to only show my customers on
my
list. I do not want to have to select each customer
everyday.

2) Is it possible to open a dimension in the pivot
table
at a particular level. For example if I have a
dimension
that has three levels (starting at level 1 - the
highest
level). Pivot table services will always open the
dimension at level one. Is there a way that when i
drop
the dimension into the pivot table it will open at
level
2 or 3?

If I am not clear on something please let me know. Any
assistance would be greatly appreciated.

Thanks



.

Thanks for the response Gary.

Your suggestions seem to provide the desired
functionality however I have a couple other questions, i
was wondering if you might have quick answers for me.
The cubes are going to be rebuilt on a nightly basis. So
I assume if data changes then all that would need to be
done is a refresh data in the Pivot Table services.
However it is also possible that a strucutre may change
(ie. a hierarchy). If this situation occurs does this
mean that the list would have to be regenerated? Would
the same reasoning apply if new data needs to be added to
the report?

Thanks
Craig
-----Original Message-----
For question 1) try selecting the fifty customers you
want and group them and
then save the spreadsheet and use the spreadsheet to
open the OLAP cube. For
question 2) try saving the OLAP cube file in a
spreadsheet with the
dimensions opened and see if that preserves the format.

"Craig" wrote:

I was wondering
if anyone may have some suggestions for a couple of
user
issues I am trying to solve with Excel's Pivot Table
Services.

I have built a couple of OLAP cubes in which Pivot
table
services has been mandated as the front tool. There
are
two sets of functionality users are asking for that I
have not been able to provide answers too. I know
this
funtionality exists in other front end tools (ie. such
as
business objects query md), however right now other
tools
are not an option.

The tools pieces of functionality or work arounds I am
looking for a
1) The ability for a user to formulate a predefined
list
of dimension members that they report on. For example
if
i have a customer dimension that contains a million
customers, but i report on the same 50 customers
everyday, can i save this list of customers some how
and
customize the table view to only show my customers on
my
list. I do not want to have to select each customer
everyday.

2) Is it possible to open a dimension in the pivot
table
at a particular level. For example if I have a
dimension
that has three levels (starting at level 1 - the
highest
level). Pivot table services will always open the
dimension at level one. Is there a way that when i
drop
the dimension into the pivot table it will open at
level
2 or 3?

If I am not clear on something please let me know.
Any
assistance would be greatly appreciated.

Thanks


.

One other thing... can we create a group that spans
mutilple level in our hierarchy?

For example, if we have a heirarchy that represents
different cars, so Level one would be Ford, That would
break into level 2 which would be Windstar, FreeStar,
Focus, Contour then level three would break down into the
different years 200, 2001, 2002, etc...

Can I create a group that selects all of the 2002 data
for each of the different level1 names?

I currently get a warning saying it cannot create that
selection.

Thanks,
Craig
-----Original Message-----
For question 1) try selecting the fifty customers you
want and group them and
then save the spreadsheet and use the spreadsheet to
open the OLAP cube. For
question 2) try saving the OLAP cube file in a
spreadsheet with the
dimensions opened and see if that preserves the format.

"Craig" wrote:

I was wondering
if anyone may have some suggestions for a couple of
user
issues I am trying to solve with Excel's Pivot Table
Services.

I have built a couple of OLAP cubes in which Pivot
table
services has been mandated as the front tool. There
are
two sets of functionality users are asking for that I
have not been able to provide answers too. I know
this
funtionality exists in other front end tools (ie. such
as
business objects query md), however right now other
tools
are not an option.

The tools pieces of functionality or work arounds I am
looking for a
1) The ability for a user to formulate a predefined
list
of dimension members that they report on. For example
if
i have a customer dimension that contains a million
customers, but i report on the same 50 customers
everyday, can i save this list of customers some how
and
customize the table view to only show my customers on
my
list. I do not want to have to select each customer
everyday.

2) Is it possible to open a dimension in the pivot
table
at a particular level. For example if I have a
dimension
that has three levels (starting at level 1 - the
highest
level). Pivot table services will always open the
dimension at level one. Is there a way that when i
drop
the dimension into the pivot table it will open at
level
2 or 3?

If I am not clear on something please let me know.
Any
assistance would be greatly appreciated.

Thanks


.