Solved

Pivot Table - Show as Percent of Column Total greyed out

Posted on 2015-02-14
11
299 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:pdvsa
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:pdvsa
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@ 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
Comment Utility
Thank you.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Welcome!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now