pie chart in a report

I apologize if this has already been discussed - I read through several 
threads but didn't really find a solution.

I need to include a pie chart on a report showing an total employee earned 
wage % and a total company paid benefit % in the pie.  It needs to change 
with every record employee record.  The chart's in the group footer.

The chart looks just like it should in design view (2 colors) but when I 
switch to either report view or print preview, it becomes a one-color chart.

Here's the SQL.  If this has to do with this Row Source, would you please 
explain it simply? As a relative novice, I don't yet have a tremendous 
understanding of how that all fits together.

TRANSFORM Sum(qryChartTest.TOTBEN) AS SumOfTOTBEN
SELECT qryChartTest.EMPLID
FROM qryChartTest
GROUP BY qryChartTest.EMPLID
PIVOT qryChartTest.TOTBEN;


Thanks much.
-- 
h.lovera
0
Utf
1/18/2010 6:35:02 PM
access.forms 6864 articles. 2 followers. Follow

2 Replies
665 Views

Similar Articles

[PageSpeed] 38

I cannot answer your pie chart question but your crosstab query does not look 
right in that you are transforming on same field as you are pivoting.  
The results would look like this --
EMPLID  1  4  5  9  15
20044        4
20045    1
20048                9
20052                    15
20055            5

-- 
Build a little, test a little.


"h.lovera" wrote:

> I apologize if this has already been discussed - I read through several 
> threads but didn't really find a solution.
> 
> I need to include a pie chart on a report showing an total employee earned 
> wage % and a total company paid benefit % in the pie.  It needs to change 
> with every record employee record.  The chart's in the group footer.
> 
> The chart looks just like it should in design view (2 colors) but when I 
> switch to either report view or print preview, it becomes a one-color chart.
> 
> Here's the SQL.  If this has to do with this Row Source, would you please 
> explain it simply? As a relative novice, I don't yet have a tremendous 
> understanding of how that all fits together.
> 
> TRANSFORM Sum(qryChartTest.TOTBEN) AS SumOfTOTBEN
> SELECT qryChartTest.EMPLID
> FROM qryChartTest
> GROUP BY qryChartTest.EMPLID
> PIVOT qryChartTest.TOTBEN;
> 
> 
> Thanks much.
> -- 
> h.lovera
0
Utf
1/20/2010 4:06:04 PM
Sorry for my ignorance but this is exactly the problem.  I don't know what 
field should be in the TRANSFORM and what should be in the PIVOT.  I don't 
understand those 2 functions.

Can you explain them?

Thank you.
-- 
h.lovera


"KARL DEWEY" wrote:

> I cannot answer your pie chart question but your crosstab query does not look 
> right in that you are transforming on same field as you are pivoting.  
> The results would look like this --
> EMPLID  1  4  5  9  15
> 20044        4
> 20045    1
> 20048                9
> 20052                    15
> 20055            5
> 
> -- 
> Build a little, test a little.
> 
> 
> "h.lovera" wrote:
> 
> > I apologize if this has already been discussed - I read through several 
> > threads but didn't really find a solution.
> > 
> > I need to include a pie chart on a report showing an total employee earned 
> > wage % and a total company paid benefit % in the pie.  It needs to change 
> > with every record employee record.  The chart's in the group footer.
> > 
> > The chart looks just like it should in design view (2 colors) but when I 
> > switch to either report view or print preview, it becomes a one-color chart.
> > 
> > Here's the SQL.  If this has to do with this Row Source, would you please 
> > explain it simply? As a relative novice, I don't yet have a tremendous 
> > understanding of how that all fits together.
> > 
> > TRANSFORM Sum(qryChartTest.TOTBEN) AS SumOfTOTBEN
> > SELECT qryChartTest.EMPLID
> > FROM qryChartTest
> > GROUP BY qryChartTest.EMPLID
> > PIVOT qryChartTest.TOTBEN;
> > 
> > 
> > Thanks much.
> > -- 
> > h.lovera
0
Utf
1/21/2010 4:57:01 PM
Reply:

Similar Artilces:

Trying to put a pivot table report on an existing sheet causes err #3
Thanks to Frank Kabel, I can now make great pivot tables. The problem I am having is that I want to write a macro to generate pivot tables, and I want the pivot table report to be saved on a worksheet called "Sum of Open", which is overwritten each time the macro runs. I have created the sheet named "Sum of Open" which is in the same workbook, but when i try to save it to there ('Sum of Open!'), I get an error: "Your formula contains and invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, ...

building chart from multiple sheets
I have 5 sheets and would like to build a stacked bar graph using data from each. Say I have 4 values in A1, A2, A3 & A4 on each of these sheets. Each stacked bar is to respresent the 4 values from a single sheet. Is there any way to do this with out having to build a table on an extra page? Thanks, Brad K If I understand your data structure, you will need to use a range to consolidate the data. http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://...

How to change order of drop lines relative to the plotted data in Excel 2007 chart
What is the procedure for changing the order of drop lines relative to the plotted data in an Excel 2007 chart? I would like the chart;s drop lines to remain active, but appear "sent to the back" behind the plotted data. I attempted to help myself by searching extensively, but I was unable to find the solution. I think it may require a VBA entry, but, if so, I will require assistance. Are the references below pointing me in the right direction? http://msdn.microsoft.com/en-us/library/bb210119.aspx http://msdn.microsoft.com/en-us/library/bb216878.aspx http://m...

Chart Events
Hi, I was referred to an article by Jon Peltier for how to trap chart events and it was very helpful with trapping mouse clicks on all areas of a chart. I've got some code that inserts small jpg images on the chart whenever a single chart point is selected. That portion works well. I'd like a user to use the arrow keys to move from point to point rather than mouse clicking on each point. One reason for this is that some groups of datapoints are tightly packed and difficult to click on separately. The Chart Events routines provided by the article don't recognize subse...

Report Writer #4
Is it possible in Great Plains 7.5 to do cross tab reports in Report Writer? ...

Export Multiple Reports into Single Word Document
I have a series of 3 reports that make up a weekly report. Manager wants to be able to push a button and export all 3 reports into a "single" word document so that they follow one after the other. Im stumped. Anyone know of a way to do this? Any suggestions would be great. Thanx RHM -- Message posted via http://www.accessmonster.com ...

Report Detail creeping?
I have a report based on a query but when I preview the report the detail sections seem to expand with a space before the first line of each detail getting larger. Can anyone think of anything that can cause this? Thanks Tony ...

How do I break a chart data range
Most values in my chart are under 200000 but I have one that's 1.3 million which makes the others look tiny and unreadable. Is there a way to break the range to show the large value and the smaller values closer together? Hi have a look at http://www.tushar-mehta.com/excel/newsgroups/broken_y_axis/index.html http://peltiertech.com/Excel/Charts/axes.html#Broken -- Regards Frank Kabel Frankfurt, Germany Tara wrote: > Most values in my chart are under 200000 but I have one that's 1.3 > million which makes the others look tiny and unreadable. Is there a > way to break the range...

Excel 2007
The dynamic names I used to utilize in Excel 2003 to define the data range for charts in my worksheets don't work anymore, because excel modifies them upon saving the file : Filename.xls!DynamicNamedRange gets changed to [0]!DynamicNamedRange for all the chart series using this syntax. Is there another syntax I can use ? Any workaround ? If the data is in a Table (new XL2007 feature); the chart is automatically dynamic best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "jcollot" <jcollot@discussions.microsoft.com> wrote in message ...

Report Sum question
Hi all I have a report that shows output from a League Table Most years have just Played/Won/Drawn/Lost/For/Against/Points format However, a handful of years had an added Bonus Point feature, where the team who had the highest aggregate score over the home and away ties got a bonus point. In a year where there are no bonuses the field in the table is left blank, whereas bonus years are recorded 0, 1, 2, 3 etc etc I have figured out how, on my report to sum the points and bonuses using =Nz([Points],0)+Nz([Bonus Points],0). The report is all encompassing of all years (one year ...

More options for Area charts
Suggestion: add support for an arithmetic x-axis. Currently the x-axis for Area charts must either be categories or time. Time is of course a numeric value that can be reformatted to any numeric format, but the axis scale must still be specified in units of days, months, or years--this is inadequate, for instance if the x-axis is percent, then the entire range of x-values will be less than the smallest currenly permited minor unit (one day=100%). Why not allow full control over the axis scaling? Jerry ---------------- This post is a suggestion for Microsoft, and Microsoft responds to...

Secondary Axis chart
I need to create a chart with two axis. I want both the data series to be displayed as columns on the same graph. So far I have only been able to a secondary axis chart with a column and line - by going to custom charts in 'type of chart'. Anyone know how I can make both the data series appear as columns? I have also tried going to the Format menu, clicking on Selected Data Series and then clicking Secondary axis on the Axis tab. This makes one series go in front of the other though and I can't get them to go side by side so you can see both series. (Hope that made sen...

No indication that copied chart is on the clipboard
I want to copy a chart. I click on it to activate it, then press Ctrl+C to put it on the clipboard. There is no indication that it is on the clipboard. In Excel 2003, there was a moving dashed line around the chart that indicated it was on the clipboard. In Excel 2007 there is a moving dashed line around any range of selected data that I copy to indicate it is on the clipboard, but not around a chart. How do I know that a copied chart is on the clipboard? Hello, When you press Ctrl+C to copy the chart, look at the Home tab on the upper left side of the ribbon. At the bottom of the H...

MO Shortage report
There is no way to print the MO shortages??? So....I am looking at creating a custom report with Crystal....I am just unsure which tables to hit....looks like alot of temp tables are created when you generate a pick list....anyone have an idea what tables these are?? Thanks Tami Tami, Start with PK010033 (picklist file) and tie it into IV00102 (item quantity master). ...

Displaying Check Boxes in Report
I have check boxes in the detail section of my report....there are five check boxes and are setup vertically. Sometimes there is more than one box checked, such as box 1 and box 5....when this is the case and I run the report there is a gap of white space between the 1 check box and 5 check box. How can I keep the check box results together on my report and eliminate the white space when more than one box is checked? Thanks... I would create a union query to normalize the checkbox fields. Only include the true records/values in the union query. Use this union query as the record sou...

Understanding Mailbox Manager detailed report
Hello everyone. Can anybody guide me to a resource where I can deeply understand the detailed report (attached report.txt file) from every run of the Mailbox Manager? There are some items like these: * Folder Deleted Items Contents: 0 Items (2.00 KB) * Folder / Contents: 0 Items (1.00 KB) * Folder /Outbox Contents: 0 Items (1.00 KB) What does the size enclosed by parenthesis mean? Than you very much. The number in parenthesis is the total size of the items listed in that folder. I'm looking at my reports and I don't have any 0 item folders with a size larger than zero. Stran...

Chart Automation
I am currently creating Excel charts manually. I have been extracting data from a data warehouse (flat file), using a pivot table I slice the data and cut and paste it into the appropriate Excel chart. I have almost 100 charts at this time. This process is extremely time consuming, not to mention dangerous. I am trying to decide how I should implement chart automation. Ultimately I would like to feed a flat file into a database (I have Access available) and have all the charts automatically update. All charts are distributed to management, so I would also like to have the ability to uploa...

add to values in the chart not on the worksheet
Hi, I have a set of values which I have plotted. A B 1 1 2 4 3 9 4 16 5 25 Now, I want to add a constant value to values in column B without calculating them again in a column. I am wondering if i can do this directly in the "chart dialog box". I will appreciate any help. Thanks. Perhaps this will help: http://www.officearticles.com/excel/perform_arithmetic_on_a_group_of_cells_in_microsoft_excel_worksheets.htm ************ Anne Troy www.OfficeArticles.com "ab" <abhi_nav@nospam.hotmail.com> wrote in message news:dj1bjr$i3h$1@emac1.ocs.lsu.edu... > Hi, >...

IE error when viewing reports
Hello. I recently fixed out Reporting Services configuration so that the Reports are now available within CRM, but now when I try to run one, I get one of 2 the Internet Explorer errors of: 1 - Line: 412 Char: 2 Error: 'ClientControllerReportViewerControl' is null or not an object 2 - Line: 262 Char:1 Error: 'HoverImageReportViewerControl_ctl01_ctl01_ctl01_ctl01' is null or not an object Any ideas on how to fix this so I can actually run reports? Any help is appreciated. Both of the controls mentioned are part of the SQL Reporting Services Report Viewer control. F...

Which chart type to use
This should be pretty simple, I need to compare the body weight and brain weights of 3 different animals. THere is a signifigant difference between the brain and body weight of each, for instance, Humans are avg 160 lbs and brain is 3.5 lbs. I tried a column chart but you dont see the brain data, it is too small compared to the body weight. What type of chart would best display this data? Thanks. XY chart, body weight along X, brain weight along Y, data labels to identify the species. Use one of these utilities to provide the custom data labels: Rob Bovey's Chart Lab...

retrieving chart data source
Opening the document then data source was working fine. Now when I double click the word 2007 chart, the data source no longer automatically opens in excel. the Chart Tool: Design Data area is grayed out. ...

Access report date problem.
I have created a lot of graphs to show various information. The problem I'm having is that I need the graphs to show the last 14 days of data. The criteria I have used is >Now()-15. This seems to work fine until it comes to the end of the month and then it will not cross over into the next month untill it has 14 days for that month instead of, say, 23/5/10 - 6/5/10. it only shows up to 31st. Any help would be greatly appreciated. Thanks pinchin1 wrote: >I have created a lot of graphs to show various information. The problem I'm >having is that I need the gr...

Building Gantt Chart using FlexGrid- how to start?
Hello, I need to build a Gantt Chart using data in Access. I am creating a production schedule with multiple tasks and resources; it is quite complex. I found the FlexGrid demo and it seems to be useful, but as a beginner in Access I'm not sure how to start. Is there a walkthrough or step-by-step that will show me how to make the chart? Thanks in advance, Emily I'm not sure that the flexgrid is going to work for this. It's going to require your entire column be the same width, so that's going to make it tricky to show variable widths in your graph. I could see doing...

Provide a "high/low" chart (stock chart) in horizontal mode
An ability to provide a "high/low" chart similar to a stock chart in a horizontal mode would provide an ability to compare values in a minimum and maximum mode. For example, nonlethal weapons have a minimum safe distance and a maximum effective range. Reaction times have a minimum cognition time and a maximum response time. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link...

Mekko charts in Excel 2007
I'm trying to create a mekko area chart in Excel 2007 like I do in Excel 2003. However, it appears that one can not change the X axis of an area chart to timescale. Does anyone know how to create mekko charts in 2007? frustrated In article <hcydnfJ3Q_F7I7vbnZ2dnUVZ_gOdnZ2d@comcast.com>, gburdell@nowhere.com says... > I'm trying to create a mekko area chart in Excel 2007 like I do in Excel > 2003. However, it appears that one can not change the X axis of an area > chart to timescale. Does anyone know how to create mekko charts in 2007? > > frustrated ...