Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
2007: pivot table driving our users nuts.
Assuming you have a Dimension with 5 levels: Year, Quarter, Month, Week and Day
Using OWC 11, you could drag the Day level to the pivot table, and you'd see all the days one below the other. Now with Excel 2007, you drag the Day level and Excel decides to behave as if you had dragged the Year level: It shows all the years below one another. You must then expand the year level so that the quarters appear, and then right-click on a year, choose "show/hide fields" and click on Year to hide the year level. Do it again for Quarter, then again for Month, and finally for week. A total of 17 clicks for something that needs 1 click with OWC 11. This is driving our users nuts. Am I missing something ? Is there a better way to do this ? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table driving our users nuts.
Alan
You can display the pivot table in the 'old view' on the pivot table tab. The default now is the new 'compact' view -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "AlanKohl" wrote in message ... Assuming you have a Dimension with 5 levels: Year, Quarter, Month, Week and Day Using OWC 11, you could drag the Day level to the pivot table, and you'd see all the days one below the other. Now with Excel 2007, you drag the Day level and Excel decides to behave as if you had dragged the Year level: It shows all the years below one another. You must then expand the year level so that the quarters appear, and then right-click on a year, choose "show/hide fields" and click on Year to hide the year level. Do it again for Quarter, then again for Month, and finally for week. A total of 17 clicks for something that needs 1 click with OWC 11. This is driving our users nuts. Am I missing something ? Is there a better way to do this ? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table driving our users nuts.
Hi Nick, I've tried that too, it doesn't affect the issue I described,
unfortunately. "Nick Hodge" wrote: Alan You can display the pivot table in the 'old view' on the pivot table tab. The default now is the new 'compact' view -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "AlanKohl" wrote in message ... Assuming you have a Dimension with 5 levels: Year, Quarter, Month, Week and Day Using OWC 11, you could drag the Day level to the pivot table, and you'd see all the days one below the other. Now with Excel 2007, you drag the Day level and Excel decides to behave as if you had dragged the Year level: It shows all the years below one another. You must then expand the year level so that the quarters appear, and then right-click on a year, choose "show/hide fields" and click on Year to hide the year level. Do it again for Quarter, then again for Month, and finally for week. A total of 17 clicks for something that needs 1 click with OWC 11. This is driving our users nuts. Am I missing something ? Is there a better way to do this ? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table driving our users nuts.
Hi Alan
If I drag a date field to the Row area, then Group by Year, Quarter.Month,Day each of these grouped fields appears one below the other in the Row area of the Field List dialogue.Dragging Year, Quarter and Month to the Report Filter Area, just leaves days showing in the row area. Dragging any of them around doesn't display what you describe. -- Regards Roger Govier "AlanKohl" wrote in message ... Hi Nick, I've tried that too, it doesn't affect the issue I described, unfortunately. "Nick Hodge" wrote: Alan You can display the pivot table in the 'old view' on the pivot table tab. The default now is the new 'compact' view -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "AlanKohl" wrote in message ... Assuming you have a Dimension with 5 levels: Year, Quarter, Month, Week and Day Using OWC 11, you could drag the Day level to the pivot table, and you'd see all the days one below the other. Now with Excel 2007, you drag the Day level and Excel decides to behave as if you had dragged the Year level: It shows all the years below one another. You must then expand the year level so that the quarters appear, and then right-click on a year, choose "show/hide fields" and click on Year to hide the year level. Do it again for Quarter, then again for Month, and finally for week. A total of 17 clicks for something that needs 1 click with OWC 11. This is driving our users nuts. Am I missing something ? Is there a better way to do this ? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table driving our users nuts.
I have a pivot table that is connected to an external source (in this case
SQL Server Analysis Services SP2). In this external source, the Calendar dimension has Year, Quarter, Month, Week and Day levels. When I drag just one of those levels from the Fields list, all of the levels get dragged to the row area of the pivot table instead of just the level I picked. (sorry for the delayed reply, I didn't get notified of your response for some reason) "Roger Govier" wrote: Hi Alan If I drag a date field to the Row area, then Group by Year, Quarter.Month,Day each of these grouped fields appears one below the other in the Row area of the Field List dialogue.Dragging Year, Quarter and Month to the Report Filter Area, just leaves days showing in the row area. Dragging any of them around doesn't display what you describe. -- Regards Roger Govier "AlanKohl" wrote in message ... Hi Nick, I've tried that too, it doesn't affect the issue I described, unfortunately. "Nick Hodge" wrote: Alan You can display the pivot table in the 'old view' on the pivot table tab. The default now is the new 'compact' view -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "AlanKohl" wrote in message ... Assuming you have a Dimension with 5 levels: Year, Quarter, Month, Week and Day Using OWC 11, you could drag the Day level to the pivot table, and you'd see all the days one below the other. Now with Excel 2007, you drag the Day level and Excel decides to behave as if you had dragged the Year level: It shows all the years below one another. You must then expand the year level so that the quarters appear, and then right-click on a year, choose "show/hide fields" and click on Year to hide the year level. Do it again for Quarter, then again for Month, and finally for week. A total of 17 clicks for something that needs 1 click with OWC 11. This is driving our users nuts. Am I missing something ? Is there a better way to do this ? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table driving our users nuts.
Hi Alan
I have no experience with taking data from a SQL Server. Presumably it is "tying" these levels together. Can you not just pick up a straight calendar date for each transaction, then get XL to Group those days by Year, Quarter, Month and Day? That way, you can drag each around independently, and that was what I was describing in my post. -- Regards Roger Govier "AlanKohl" wrote in message ... I have a pivot table that is connected to an external source (in this case SQL Server Analysis Services SP2). In this external source, the Calendar dimension has Year, Quarter, Month, Week and Day levels. When I drag just one of those levels from the Fields list, all of the levels get dragged to the row area of the pivot table instead of just the level I picked. (sorry for the delayed reply, I didn't get notified of your response for some reason) "Roger Govier" wrote: Hi Alan If I drag a date field to the Row area, then Group by Year, Quarter.Month,Day each of these grouped fields appears one below the other in the Row area of the Field List dialogue.Dragging Year, Quarter and Month to the Report Filter Area, just leaves days showing in the row area. Dragging any of them around doesn't display what you describe. -- Regards Roger Govier "AlanKohl" wrote in message ... Hi Nick, I've tried that too, it doesn't affect the issue I described, unfortunately. "Nick Hodge" wrote: Alan You can display the pivot table in the 'old view' on the pivot table tab. The default now is the new 'compact' view -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "AlanKohl" wrote in message ... Assuming you have a Dimension with 5 levels: Year, Quarter, Month, Week and Day Using OWC 11, you could drag the Day level to the pivot table, and you'd see all the days one below the other. Now with Excel 2007, you drag the Day level and Excel decides to behave as if you had dragged the Year level: It shows all the years below one another. You must then expand the year level so that the quarters appear, and then right-click on a year, choose "show/hide fields" and click on Year to hide the year level. Do it again for Quarter, then again for Month, and finally for week. A total of 17 clicks for something that needs 1 click with OWC 11. This is driving our users nuts. Am I missing something ? Is there a better way to do this ? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table driving our users nuts.
Unfortunately that's not possible, we have more than 50 millions transactions
in the database. Thanks anyway. "Roger Govier" wrote: Hi Alan I have no experience with taking data from a SQL Server. Presumably it is "tying" these levels together. Can you not just pick up a straight calendar date for each transaction, then get XL to Group those days by Year, Quarter, Month and Day? That way, you can drag each around independently, and that was what I was describing in my post. -- Regards Roger Govier "AlanKohl" wrote in message ... I have a pivot table that is connected to an external source (in this case SQL Server Analysis Services SP2). In this external source, the Calendar dimension has Year, Quarter, Month, Week and Day levels. When I drag just one of those levels from the Fields list, all of the levels get dragged to the row area of the pivot table instead of just the level I picked. (sorry for the delayed reply, I didn't get notified of your response for some reason) "Roger Govier" wrote: Hi Alan If I drag a date field to the Row area, then Group by Year, Quarter.Month,Day each of these grouped fields appears one below the other in the Row area of the Field List dialogue.Dragging Year, Quarter and Month to the Report Filter Area, just leaves days showing in the row area. Dragging any of them around doesn't display what you describe. -- Regards Roger Govier "AlanKohl" wrote in message ... Hi Nick, I've tried that too, it doesn't affect the issue I described, unfortunately. "Nick Hodge" wrote: Alan You can display the pivot table in the 'old view' on the pivot table tab. The default now is the new 'compact' view -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "AlanKohl" wrote in message ... Assuming you have a Dimension with 5 levels: Year, Quarter, Month, Week and Day Using OWC 11, you could drag the Day level to the pivot table, and you'd see all the days one below the other. Now with Excel 2007, you drag the Day level and Excel decides to behave as if you had dragged the Year level: It shows all the years below one another. You must then expand the year level so that the quarters appear, and then right-click on a year, choose "show/hide fields" and click on Year to hide the year level. Do it again for Quarter, then again for Month, and finally for week. A total of 17 clicks for something that needs 1 click with OWC 11. This is driving our users nuts. Am I missing something ? Is there a better way to do this ? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table driving our users nuts.
Hi Alan
Just a trifle large, even for XL2007<bg Someone else with experience with SQL databases may be able to jump in and help you - sorry I can't. -- Regards Roger Govier "AlanKohl" wrote in message ... Unfortunately that's not possible, we have more than 50 millions transactions in the database. Thanks anyway. "Roger Govier" wrote: Hi Alan I have no experience with taking data from a SQL Server. Presumably it is "tying" these levels together. Can you not just pick up a straight calendar date for each transaction, then get XL to Group those days by Year, Quarter, Month and Day? That way, you can drag each around independently, and that was what I was describing in my post. -- Regards Roger Govier "AlanKohl" wrote in message ... I have a pivot table that is connected to an external source (in this case SQL Server Analysis Services SP2). In this external source, the Calendar dimension has Year, Quarter, Month, Week and Day levels. When I drag just one of those levels from the Fields list, all of the levels get dragged to the row area of the pivot table instead of just the level I picked. (sorry for the delayed reply, I didn't get notified of your response for some reason) "Roger Govier" wrote: Hi Alan If I drag a date field to the Row area, then Group by Year, Quarter.Month,Day each of these grouped fields appears one below the other in the Row area of the Field List dialogue.Dragging Year, Quarter and Month to the Report Filter Area, just leaves days showing in the row area. Dragging any of them around doesn't display what you describe. -- Regards Roger Govier "AlanKohl" wrote in message ... Hi Nick, I've tried that too, it doesn't affect the issue I described, unfortunately. "Nick Hodge" wrote: Alan You can display the pivot table in the 'old view' on the pivot table tab. The default now is the new 'compact' view -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "AlanKohl" wrote in message ... Assuming you have a Dimension with 5 levels: Year, Quarter, Month, Week and Day Using OWC 11, you could drag the Day level to the pivot table, and you'd see all the days one below the other. Now with Excel 2007, you drag the Day level and Excel decides to behave as if you had dragged the Year level: It shows all the years below one another. You must then expand the year level so that the quarters appear, and then right-click on a year, choose "show/hide fields" and click on Year to hide the year level. Do it again for Quarter, then again for Month, and finally for week. A total of 17 clicks for something that needs 1 click with OWC 11. This is driving our users nuts. Am I missing something ? Is there a better way to do this ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help, this is driving me nuts | Excel Discussion (Misc queries) | |||
question driving me nuts | New Users to Excel | |||
Sum and Count are driving me nuts!! | Charts and Charting in Excel | |||
Driving me nuts. Need more nested than 7 | Excel Discussion (Misc queries) | |||
Excel / VB is driving me nuts!! | Excel Worksheet Functions |