Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table (same level hierarchy fields)
I understand that Pivot Tables aggregate data based on hierarchy's--however,
is there any way (perhaps using vba) to modify a Pivot Table to display 2 or more fields on the same line if the fields are on the same hierarchy level? I often utilize Pivot Tables by showing sales data by item and filter this data using fields in the page area. I would love to be able to display item number and description on the same line; however, since it functions as a hierarchy it automatically displays them on 2 lines. I'm new to vba, but I know vba allows you to do things that are otherwise impossible. Can vba allow you to do what I'm asking? Below I've listed an example of how it is currently displayed, and below it, how I'd like it to display. Item Description Sales 22222 2 (what it does) Widget --------------------------------------------------------------------------------- Item Description Sales 22222 Widget 2 (what I'd like) Thanks, Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table (same level hierarchy fields)
Right click on a field (preferably the parent field), choose Field settings
Then click the layout button You should get a small dialog box with the choices (radio button) Show items in tabular form (radio button) Show items in outline form .... .... choose show items in tabular form. that should work (*** Also, just play with the auto formats off the format menu.. Pivot tables can sometimes be tough to read and these have a lot of good formats.) "goofy11" wrote: I understand that Pivot Tables aggregate data based on hierarchy's--however, is there any way (perhaps using vba) to modify a Pivot Table to display 2 or more fields on the same line if the fields are on the same hierarchy level? I often utilize Pivot Tables by showing sales data by item and filter this data using fields in the page area. I would love to be able to display item number and description on the same line; however, since it functions as a hierarchy it automatically displays them on 2 lines. I'm new to vba, but I know vba allows you to do things that are otherwise impossible. Can vba allow you to do what I'm asking? Below I've listed an example of how it is currently displayed, and below it, how I'd like it to display. Item Description Sales 22222 2 (what it does) Widget 2 --------------------------------------------------------------------------------- Item Description Sales 22222 Widget 2 (what I'd like) Thanks, Jeff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table (same level hierarchy fields)
By far the easiest way that I have found is to insert a new column into the
source data and concatenate the Part Number to the Description and call it something like Full Part. Now you can just drop the full part on the pivot and all is well... -- HTH... Jim Thomlinson "goofy11" wrote: I understand that Pivot Tables aggregate data based on hierarchy's--however, is there any way (perhaps using vba) to modify a Pivot Table to display 2 or more fields on the same line if the fields are on the same hierarchy level? I often utilize Pivot Tables by showing sales data by item and filter this data using fields in the page area. I would love to be able to display item number and description on the same line; however, since it functions as a hierarchy it automatically displays them on 2 lines. I'm new to vba, but I know vba allows you to do things that are otherwise impossible. Can vba allow you to do what I'm asking? Below I've listed an example of how it is currently displayed, and below it, how I'd like it to display. Item Description Sales 22222 2 (what it does) Widget 2 --------------------------------------------------------------------------------- Item Description Sales 22222 Widget 2 (what I'd like) Thanks, Jeff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table (same level hierarchy fields)
"goofy11" wrote: I understand that Pivot Tables aggregate data based on hierarchy's--however, is there any way (perhaps using vba) to modify a Pivot Table to display 2 or more fields on the same line if the fields are on the same hierarchy level? I often utilize Pivot Tables by showing sales data by item and filter this data using fields in the page area. I would love to be able to display item number and description on the same line; however, since it functions as a hierarchy it automatically displays them on 2 lines. I'm new to vba, but I know vba allows you to do things that are otherwise impossible. Can vba allow you to do what I'm asking? Below I've listed an example of how it is currently displayed, and below it, how I'd like it to display. Item Description Sales 22222 2 (what it does) Widget 2 --------------------------------------------------------------------------------- Item Description Sales 22222 Widget 2 (what I'd like) Thanks, Jeff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table (same level hierarchy fields)
Thanks, this is exactly what I was looking for. While playing around with
it, I noticed that it doesn't work so well if you add more than 2 fields in the row area (creates another line)...but most of the time I can get by with just 2. Thanks again! "TomHinkle" wrote: Right click on a field (preferably the parent field), choose Field settings Then click the layout button You should get a small dialog box with the choices (radio button) Show items in tabular form (radio button) Show items in outline form ... ... choose show items in tabular form. that should work (*** Also, just play with the auto formats off the format menu.. Pivot tables can sometimes be tough to read and these have a lot of good formats.) "goofy11" wrote: I understand that Pivot Tables aggregate data based on hierarchy's--however, is there any way (perhaps using vba) to modify a Pivot Table to display 2 or more fields on the same line if the fields are on the same hierarchy level? I often utilize Pivot Tables by showing sales data by item and filter this data using fields in the page area. I would love to be able to display item number and description on the same line; however, since it functions as a hierarchy it automatically displays them on 2 lines. I'm new to vba, but I know vba allows you to do things that are otherwise impossible. Can vba allow you to do what I'm asking? Below I've listed an example of how it is currently displayed, and below it, how I'd like it to display. Item Description Sales 22222 2 (what it does) Widget 2 --------------------------------------------------------------------------------- Item Description Sales 22222 Widget 2 (what I'd like) Thanks, Jeff |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table (same level hierarchy fields)
Good idea. The previous post gave a great solution, but I can see how your
idea can work as well. I think I would potentially use your idea if I needed to have more than two item charactistics in my row, since the other posted solution seems to only work with 2 fields. "Jim Thomlinson" wrote: By far the easiest way that I have found is to insert a new column into the source data and concatenate the Part Number to the Description and call it something like Full Part. Now you can just drop the full part on the pivot and all is well... -- HTH... Jim Thomlinson "goofy11" wrote: I understand that Pivot Tables aggregate data based on hierarchy's--however, is there any way (perhaps using vba) to modify a Pivot Table to display 2 or more fields on the same line if the fields are on the same hierarchy level? I often utilize Pivot Tables by showing sales data by item and filter this data using fields in the page area. I would love to be able to display item number and description on the same line; however, since it functions as a hierarchy it automatically displays them on 2 lines. I'm new to vba, but I know vba allows you to do things that are otherwise impossible. Can vba allow you to do what I'm asking? Below I've listed an example of how it is currently displayed, and below it, how I'd like it to display. Item Description Sales 22222 2 (what it does) Widget 2 --------------------------------------------------------------------------------- Item Description Sales 22222 Widget 2 (what I'd like) Thanks, Jeff |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table (same level hierarchy fields)
I have done that in the past but I found that sometimes it messed up the auto
formatting. Is there an easy way around that? -- HTH... Jim Thomlinson "TomHinkle" wrote: Right click on a field (preferably the parent field), choose Field settings Then click the layout button You should get a small dialog box with the choices (radio button) Show items in tabular form (radio button) Show items in outline form ... ... choose show items in tabular form. that should work (*** Also, just play with the auto formats off the format menu.. Pivot tables can sometimes be tough to read and these have a lot of good formats.) "goofy11" wrote: I understand that Pivot Tables aggregate data based on hierarchy's--however, is there any way (perhaps using vba) to modify a Pivot Table to display 2 or more fields on the same line if the fields are on the same hierarchy level? I often utilize Pivot Tables by showing sales data by item and filter this data using fields in the page area. I would love to be able to display item number and description on the same line; however, since it functions as a hierarchy it automatically displays them on 2 lines. I'm new to vba, but I know vba allows you to do things that are otherwise impossible. Can vba allow you to do what I'm asking? Below I've listed an example of how it is currently displayed, and below it, how I'd like it to display. Item Description Sales 22222 2 (what it does) Widget 2 --------------------------------------------------------------------------------- Item Description Sales 22222 Widget 2 (what I'd like) Thanks, Jeff |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table (same level hierarchy fields)
no real good answer here.. For my solution it worked, BUT I know what you
mean.. once you start messing with formats of pivot tables, just got to keep trying stuff and see what works. You know how it is.. I sometimes do your solution too (make additional descriptive columns). "Jim Thomlinson" wrote: I have done that in the past but I found that sometimes it messed up the auto formatting. Is there an easy way around that? -- HTH... Jim Thomlinson "TomHinkle" wrote: Right click on a field (preferably the parent field), choose Field settings Then click the layout button You should get a small dialog box with the choices (radio button) Show items in tabular form (radio button) Show items in outline form ... ... choose show items in tabular form. that should work (*** Also, just play with the auto formats off the format menu.. Pivot tables can sometimes be tough to read and these have a lot of good formats.) "goofy11" wrote: I understand that Pivot Tables aggregate data based on hierarchy's--however, is there any way (perhaps using vba) to modify a Pivot Table to display 2 or more fields on the same line if the fields are on the same hierarchy level? I often utilize Pivot Tables by showing sales data by item and filter this data using fields in the page area. I would love to be able to display item number and description on the same line; however, since it functions as a hierarchy it automatically displays them on 2 lines. I'm new to vba, but I know vba allows you to do things that are otherwise impossible. Can vba allow you to do what I'm asking? Below I've listed an example of how it is currently displayed, and below it, how I'd like it to display. Item Description Sales 22222 2 (what it does) Widget 2 --------------------------------------------------------------------------------- Item Description Sales 22222 Widget 2 (what I'd like) Thanks, Jeff |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table (same level hierarchy fields)
Darn... I was hoping you were going to save me a whole pile of time... I
pretty much always have to format my reports and that would have made life easy. -- HTH... Jim Thomlinson "TomHinkle" wrote: no real good answer here.. For my solution it worked, BUT I know what you mean.. once you start messing with formats of pivot tables, just got to keep trying stuff and see what works. You know how it is.. I sometimes do your solution too (make additional descriptive columns). "Jim Thomlinson" wrote: I have done that in the past but I found that sometimes it messed up the auto formatting. Is there an easy way around that? -- HTH... Jim Thomlinson "TomHinkle" wrote: Right click on a field (preferably the parent field), choose Field settings Then click the layout button You should get a small dialog box with the choices (radio button) Show items in tabular form (radio button) Show items in outline form ... ... choose show items in tabular form. that should work (*** Also, just play with the auto formats off the format menu.. Pivot tables can sometimes be tough to read and these have a lot of good formats.) "goofy11" wrote: I understand that Pivot Tables aggregate data based on hierarchy's--however, is there any way (perhaps using vba) to modify a Pivot Table to display 2 or more fields on the same line if the fields are on the same hierarchy level? I often utilize Pivot Tables by showing sales data by item and filter this data using fields in the page area. I would love to be able to display item number and description on the same line; however, since it functions as a hierarchy it automatically displays them on 2 lines. I'm new to vba, but I know vba allows you to do things that are otherwise impossible. Can vba allow you to do what I'm asking? Below I've listed an example of how it is currently displayed, and below it, how I'd like it to display. Item Description Sales 22222 2 (what it does) Widget 2 --------------------------------------------------------------------------------- Item Description Sales 22222 Widget 2 (what I'd like) Thanks, Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fields names do not show in excel 2007 pivot table fields list | New Users to Excel | |||
Pivot table hierarchy question | Excel Worksheet Functions | |||
Pivot table totals in low levels of hierarchy | Excel Discussion (Misc queries) | |||
How to filter only the first level in the hierarchy | Excel Discussion (Misc queries) | |||
number of fields in the row fields in pivot table | Excel Discussion (Misc queries) |