#1   Report Post  
Craig
 
Posts: n/a
Default Pivot Table services

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

  #2   Report Post  
Nick Hodge
 
Posts: n/a
Default

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



  #3   Report Post  
Gary Rowe
 
Posts: n/a
Default

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


  #4   Report Post  
 
Posts: n/a
Default

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



.

  #5   Report Post  
 
Posts: n/a
Default

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


.



  #6   Report Post  
 
Posts: n/a
Default

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


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Selection for Pivot Table Burak Emer Excel Discussion (Misc queries) 0 December 15th 04 09:19 AM
How to create a calculated field formula based on Pivot Table resu dha17 Excel Discussion (Misc queries) 1 December 15th 04 06:39 AM
Pivot table yg Excel Discussion (Misc queries) 2 December 14th 04 01:20 AM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 04:56 AM
convert excel list to pivot table GI Excel Discussion (Misc queries) 0 December 6th 04 07:45 PM


All times are GMT +1. The time now is 07:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"