Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Pivot Table - Show as Percent of Column Total greyed out

Posted on 2015-02-14
11
312 Views
Last Modified: 2015-02-19
Hello,

I have two queries in the attached: Works, Notworks
I have designed them as a pivot table.

The "Show as Percent of Column Total" seems to be greyed out in the "Notworks" query and it is not greyed out in the "Works" query.  I dont know why because I have taken the SQL and copied and pasted from "Works" to "Notworks" so they are identical

I have compacted and repaired and decompiled.

I imagine it is something simple but I have spent too much time on this.    

Please see attached 2010 db and open the queries and show as Pivot and select the field [Percent] and try to "Show as Percent of Column Total" which is found in the Pivot Table Tools / Tools group on the far right.  It is greyed out only in "NotWorks".

thank you
Database1.accdb
0
Comment
Question by:pdvsa
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40610057
I am not sure,
Note that the WORKS pivot has actual "Percents" displayed in the Percent column, and also has a Grand total,
...The NotWorks query only has "values" in the percent column and no grand totals.
I have not looked a Pivots in Access since they were deprecated,
...So if you find the reason for this "Percents vs Values" discrepancy, you will have solved the mystery here.
;-)
(and note that since they were deprecated, you will eventually have to find another way to do this...)

...but also note that your pivot table is based on a complex "Group by" query.
A group by query, by design, is a "summary" query and will not show individual values
...so it may not know what you mean by "total" (total of a total?)
So it seems like you are getting a summary, then trying to use a Pivot to get another summary of your summaries?

Why not just try making the Pivot from the raw data alone.
Or calculate the percentage in a simple query,...then do a pivot of that simple query, to include the percent.

But let's see if another expert, who has more knowledge of Pivots, chimes in...

JeffCoachman
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40610401
Show an example of the working PivotTable. Limit the fields, in the source queries, to only include the relevant fields.

Explain the difference between the two queries. Works, and NotWorks.
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40610633
The SQL statements are identical, but it looks like, that Access is storing in the sys-tables further information on Pivot-View.
Interestingly the design view graphic of the 2 queries looks different too.
In my Access 2010 i did following tests: copy both queries >> look the same as originals. The not working lacks the data field, but you can drag from the right field selection box the Percent field. Then it is correct and if you save it, this correct view comes again. So this shows, that Access keeps some not documented properties somewhere, which make a difference. Maybe you tested someting with the NotWorking Query or you didd something slightly different in design view, which is not reflected by the SQL, but is kept in some Access query properties.
Database1x.accdb
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:pdvsa
ID: 40611147
bonjour-aut, thank you for the response.  I however can not see what you did to fix my issue.  Can you let me know?

I do not follow this part of your solution:  "The not working lacks the data field, but you can drag from the right field selection box the Percent field."  ===>I do see the [Percent] field and I have dragged it both out and in the pivot and the "Show as Percent of Column Total" remains greyed out.  

thank you for the help.
0
 

Author Comment

by:pdvsa
ID: 40611149
I have also tried to copy and paste the SQL of the query you fixed into my db but the Show as Percent of column total is still greyed out.
0
 

Author Comment

by:pdvsa
ID: 40611156
I also tried to recreate the query to remove any possible hidden properties and still the "Show as Percent of column total is still greyed out"  for the [Percent] field
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40611413
Please see my 2 screenshots: 'NotWorks1.jpg' shows original state, where i drag and drop the Percent field. 'NotWorks2.jpg' shows the result as also seen in the db under NotWorks_copy query.
NotWorks1.jpg
NotWorks2.jpg
0
 

Author Comment

by:pdvsa
ID: 40611497
hello....thank you for the pics.  I still do not see enough with the pics.  I do not have an issue with dragging the field [Percent]m to the pivot.  The issue is enabling the greyed out button.   Please see the image below.  I also see you are in pivot chart view but I am in pivot table view.  I do see that in your query in the previously attached db that the button is enabled and I need to know how you did that.   I do not believe simply dragging [Percent] over is the solution because I have done this many times.  Please let me know what is next.  

GreyedOut
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 40611724
@ n the Pivot Table Tools / Tools group on the far right.  It is greyed out only in "NotWorks".
You need subtotals to enable the Show As Button, and not detail entries.

To enable Show as button.
Select Type heading, Select Total > Subtotal
Select Percent heading, Select Show/Hide > Hide Details
Select a value in Percent column, Tools > Show As > Percent of Grand Total

You may add subtotals for other fields.
0
 

Author Closing Comment

by:pdvsa
ID: 40620347
Thank you.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40620403
Welcome!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question