- Butt Soup Barnes
- Nov 25, 2008
-
|
Is there a way to hide fields in a pivot chart without removing the actual data in the pivot table? I have 6 or 7 fields in my pivot table but only want to display 2 on a chart.
|
#
¿
May 9, 2011 18:58
|
|
- Adbot
-
ADBOT LOVES YOU
|
|
|
#
¿
May 6, 2024 22:34
|
|
- Butt Soup Barnes
- Nov 25, 2008
-
|
ZerodotJander posted:
Is there any particular reason you're using a pivot chart? In general for almost all applications I find it much more convenient to copy data out of the pivot table into a normal table, then chart that with a regular chart. If you want the chart to be dynamic based on the pivot table, just create the normal table with references instead of copying the data out directly.
I didn't even think about that, thanks.
One other question now:
I want my charts to be on a separate worksheet from the pivot table since they take up too much space if they are together. Is there any way for the pivot table report filter fields to appear on another tab? That way my manager can manipulate the chart without having to go back to the table tab.
|
|
#
¿
May 9, 2011 19:56
|
|
- Butt Soup Barnes
- Nov 25, 2008
-
|
ZerodotJander posted:
Yes, but it requires a little bit of VBA. It also works much better for the Report Filter level than lower level fields. I'm going to assume you are working with Excel 2007 or 2010 here.
First, you need to create a Support tab that contains your Pivot Table. Then, create a secondary table off to the side that is just references to the Pivot Table, duplicating it as a regular table but that will dynamically change if the Pivot Table changes.
Off to the side, also list all of the fields in your Report Filter field in a column- e.g., if you are filtering by month, make a list of all applicable months.
On your actual dashboard tab, create your desired chart, pulling the data from the secondary table.
Then, on your dashboard tab, add an ActiveX ComboBox - you have to go into your Excel Options and check off "Show Developer Tab in the Ribbon" under Popular, then you will have access to the Developer Tab and can Insert ActiveX ComboBox.
Go into Design Mode (next to the Insert button in the Developer Tab) and click on your new ComboBox, and click Properties. Note the name of your ComboBox - most likely it is ComboBox1, but it might be different if you've been experimenting. I find it helpful to switch to the Categorized view, instead of the Alphabetic view.
Under Data->ListRows, enter how big you want your dropdown to be - if it is less than the number of values to be in the dropdown, you'll get a scrollbar. For example, 12, if you want all months to be visible; 6 if you just want half.
You can change the Name of the ComboBox under Misc->(Name) if you want it to be something easier to remember.
Under Misc->ListFillRange, enter the reference for your list of values on your Support Tab (e.g., Support!$D$5:$D$27). This populates your ComboBox selector with the possible values for your Report Filter.
Now that you have your dropdown selector, you just have to tie it to your pivot table (and therefore chart). Close the properties box (everything saves automatically) and click on View Code (make sure your ComboBox is selected, you want the code to be associated with the ComboBox).
Enter in the following VBA (with Sheet and object names adjusted to your specific needs):
code:Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Sheets("Support").Visible = True
Sheets("Support").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Month").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Month").CurrentPage = ComboBox1.Value
ActiveWindow.SelectedSheets.Visible = False
Sheets("Dashboard").Select
Application.ScreenUpdating = True
End Sub
It might seem complicated if you haven't worked with ActiveX Objects or VBA in Excel before, but it's actually pretty straightforward.
Common sources of error:
Make sure that your Support tab's field list for the Dropdown is in the correct order, and everything is capitalized and spelled correctly - it has to match the values the PivotTable will find valid exactly.
Make sure you are referencing the correct ComboBox and PivotTable.
Make sure you set your Support tab's zoom to 100%. For some bizarre reason, the ComboBox will produce an error if the Dashboard and Support tab are not set to either 100% or exactly the same zoom. So just make sure that the Support tab is at 100% and then it doesn't matter what zoom the Dashboard is at.
Hope that provides what you need!
I won't have time to attempt this until tomorrow but I really, really appreciate your quick and thorough reply. I'll let you know how it works out.
|
|
#
¿
May 9, 2011 20:37
|
|
- Butt Soup Barnes
- Nov 25, 2008
-
|
ZerodotJander posted:
Awesome Excel advice
Hey man, thanks again for that write-up. It worked great and it inspired me to learn more about VBA. I just created a dashboard from scratch in Excel using VBA and my boss is incredibly happy.
I have another question though. Now that I have this Dashboard in Excel working the way I want it to, I want to integrate it with Access. Right now everything works fine because I am using sample data to generate the reports. In the future there will be upwards of 50,000 records I would have maintain, and obviously Access is far better suited for that.
Is it possible to store everything in Access - databases, pivot tables, etc. and generate the charts/dashboard in Excel without having to import data into another worksheet?
|
|
#
¿
May 13, 2011 15:59
|
|
- Butt Soup Barnes
- Nov 25, 2008
-
|
ZerodotJander posted:
I believe there is a way to import data from Access into Excel automatically and update stuff, but unfortunately I don't have any experience with it. Good luck!
Welp, I guess it's time to get my reading on and find some books on Access/VBA.
Thanks again for all your advice though, you may have literally saved my job due to impending cutbacks/layoffs.
|
|
#
¿
May 13, 2011 16:57
|
|
#
¿
May 9, 2011 18:58
