Forms :: Pivot Chart To Show Progress In Multiple Projects Over Time
Aug 30, 2013
I'm aiming to create a stacked area chart to display the progress of multiple projects over time, so we can review and forecast project load over time.
A project passes through the statuses Prospect, Confirmed, Started, Complete. The current status of each is calculated in qryStatus from the dates in tblProject. The past and forecast statuses for each project will probably be calculated each time the chart is created (or maybe not necessarily so?)
To create the stacked area chart, the x-axis will be months (e.g. Sept 2013 - July 2015). On the y-axis will be the count of ProjectID, and the series will be the different project statuses.
The sticking point is getting from the Date* fields to the past and forecast statuses in each month. After that I imagine it'll be reasonably straightforward to put into a pivot/chart.
I am trying to publish an Excel Pivot Chart, but keep getting Run Time Error 1004 - Application-defined or object-defined error...
I have tried the code 2 ways:
Code: Sub PublishChart() Worksheets("Historical_Pivot").Activate With ActiveWorkbook.PublishObjects.Add(xlSourceChart, _ Path & "" & "AWA" & "_" & "JAMALCO" & "_" & "Chart" & ".htm", _ "Historical_Pivot", "JAMALCO", xlHtmlStatic, "ABC REQ BACKLOG_2013", "") .Publish (True) .AutoRepublish = False End With End Sub
Code: Sub PublishChart() Dim wb As Workbook Dim ws As Worksheet Set wb = ThisWorkbookSet ws = wb.Sheets("Historical_Pivot") ws.ChartObjects("JAMALCO").Activate With wb.PublishObjects.Add(xlSourceChart, Path & "" & "AWA" & "_" & "JAMALCO" & "_" & "Chart" & ".htm", ws _ , "", xlHtmlStatic, "", "") .Publish (True) End With End Sub
I've attached a rudimentary example db to give an example of what I'm trying to achieve.
There is a form on the db called Resourcing with several combos.
When I choose a Training_Type from the first combo it automatically filters the Project_Title combo to show only the projects that match that training type. When I choose a Trainer_Name the text box for team automatically completes.
The bit I'm struggling with is Trainer_Name. As you will see from the table Course_Details not every trainer can deliver every Project_Title. So what I want is to display only the trainers associated with the project title chosen rather than displaying the whole team in the Trainer_Name combo.
Someone suggested a junction table but I'm not sure how this would answer my question. I'm thinking some sort of Dlookup but don't know how I would write it when it has to include data from four fields.
We have a chart that is showing jobs by customer. I'd prefer it be in a pie chart but it's way too many to read. We changed it to a bar which is much easier to read but it displays the percentages as 100% for everything.
I have a pivot chart that has site locations, and hours on the bottom, and then count of records with each site. Which is how many people used this site during this time for each bar. My problem is when I generate my pivot chart, every single time I have to fix the scale on the right hand side because it has decimal points in it. They only appear when I add dates/times to it.I want to give this to my boss that has access runtime, so all he has to do is click the button to get the chart and then print or send it off. How do I get rid of those decimal points when the report is generated?
I made a form pivot chart. When I open it by going to forms and opening, it opens in the pivot chart view. However, I created a button on a switchboard form for easy access to this pivot chart form and when it opens from the button it is in form view and not pivot chart view. How can I get it to open in pivot chart view from this button so that the users don't have to keep switching it to pivot chart view manually?
I have a pivotchart subform who's Filter Property I've set to:
Code: [EEIC_ID] In ([Forms]![MainDataControl].[EEICBuffer],0) And IIf([Forms]![MainDataControl].[TypeIDBuffer]="",[AG_TYPE_ID] Like "*",[AG_TYPE_ID] In ([Forms]![MainDataControl].[TypeIDBuffer],"")) And IIf([Forms]![MainDataControl].[AgencyBuffer]="",[AGENCY_ID] Like "*",[AGENCY_ID] In ([Forms]![MainDataControl].[AgencyBuffer],"")) And IIf([Forms]![MainDataControl].[FacilityBuffer]="",[FACILITY_ID] Like "*",[FACILITY_ID] In ([Forms]![MainDataControl].[FacilityBuffer],""))
The issue is that none of the parameters are recognizing the textbox controls even though the references appear to be correct. Per access.mvps.org/ access/forms/frm0031.htm, I've attempted several versions of the syntax.
I believe that I've done this successfully in several other forms, though none have been been via a pivotchart. What's more, this exact same filter string worked when the pivotchart was its own popup and the referenced form was a separate window. Once I embedded it (being the pivotchart) as a subform, however, I began to be presented with the "Enter Parameter Value" dialog, even though the control's address had not changed. Note: the form with the embedded pivotchart is separate from the control's parent form.
In essence: the filter property of a pivotchart subform has a parameter that references a control on a seperate popup form, and every one of the syntactual statements I've tried returns an "Enter Parameter Value" dialog (where they did not when the pivotchart was not a subform). I've also attempted to isolate this issue by putting the text box controls on the parent form (rather than the separate popup form) and referencing them via the pivotchart subform, but this provided no resolution (the same thing happened).
I am trying to change dynamically the text of the PivotChart, according to the current filter. This is a user request. The VBA code should run whenever the view is changed to pivotchart. I'm im Access 2007
This is my code so far:
Private Sub Form_AfterFinalRender(ByVal drawObject As Object) If Not (Me.CurrentView = acCurViewPivotChart) Then Exit Sub Me.ChartSpace.HasChartSpaceTitle = True Me.ChartSpace.ChartSpaceTitle.Caption = Me.Filter End Sub
This generates an error: Runtime error '-2147467259 (80004005)': Cannot change chart attributes in an event handler
I am stuck. Is there any way around? I have placed a button to the datasheet view of the form to take users to PivotChart view. I can change the chart title from the code of that button. But I can't prevent users from using the built in ribbon button for pivotchart view, so I need to run this code from an event.
I have a pivot chart based on a crosstab query. I would like the items on the category axis (x-axis) to appear left to right in the order that they appear in the query results.
Some specifics: Tables: tblFreq FreqID (PK, Number, Range 1-7) Frequency (text)
tblResp RespID (PK,Number, Range 1-5) Response (text)
tblResults ResultID (PK,AN) FreqID_FK RespID_FK
Query: TRANSFORM CInt(Nz(Count(tblResults.ResultID),0)) AS CountOfResultID SELECT tblFreq.Frequency FROM tblResp INNER JOIN (tblFreq INNER JOIN tblResults ON tblFreq.FreqID = tblResults.FreqID_FK) ON tblResp.RespID = tblResults.RespID_FK GROUP BY tblFreq.Frequency, tblFreq.FreqID, tblFreq.FreqID ORDER BY tblFreq.FreqID PIVOT tblResp.Response;
[code]...
Which I suppose is alphabetically ordered.I am unable to use OrderBy in the forms property sheet because tblFreq.FreqID is not an available field, even though it's an expression in the query.
I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
I have a need to display a pivot chart by clicking a button on my menu screen. At the moment my button will just display the query results and if I then click on View/PivotChart View my chart displays.
Code: Private Sub Command316_Click() DoCmd.OpenQuery "R06X - OOS Chart", avViewNormal End Sub
Value 1 which is the number of working Employees and Value 2 which is the total number of Employees
Both are subject to change but my main problem is that Value 2 (Total of Employees) i want to display as 100% in a chart and Value 1 is the percentage of employees utilized.
I have the calculation working fine (Value 1/Value 2).
But if for example i have 35 employees as Value 2, how do i make that show as 100% in the chart?
I get things like 35 or 3500%?
I have played about endlessly changing values etc but just cant get it.
Hi, I have table (Table A) whose column (Column A) is a lookup table linked to Table B. Column A's type is of number. Table B has 2 columns. Column B1 is of type autonumber (set as primary). Column B2 is of type text. When I am populating Column A, I select from a drop down list which shows the values of Column B2. Column A will populate with what appears to be Column B2 values. However, whenever I create a chart with Column A, the values in the legend always show the numeric value of Column A, not the values of Column B2 as I want. This occurs in pivot charts as well. Here is an example:
In this case Table A = A_Incident_Main_Table Column A = A_Incident_Main_Table.System_Name
TRANSFORM Count(*) AS [Count] SELECT (Format([Date],"mmm"" '""yy")) AS Expr1 FROM A_Incident_Main_Table GROUP BY (Year([Date])*12+Month([Date])-1), (Format([Date],"mmm"" '""yy")) PIVOT A_Incident_Main_Table.System_Name;
Here is another example:
SELECT A_Incident_Main_Table.System_Name, Count(*) AS [Count] FROM A_Incident_Main_Table GROUP BY A_Incident_Main_Table.System_Name;
A_Incident_Main_Table.System_Name is poplulated in with values from the lookup table (Table B). In both examples, I see only the numeric values in the legend, not the text values of Table B.
I am using 2013 access and i cannot find a chart details i.e. pivot or bar charts. Therefore I have exported the information into an excel . if there is no charts functions on this access .
Is it possible to export the information yet the pivot chart stays in the excel so i don't have to keep creating the charts information.
I have a problem with seting up color for my pivot chart. First I set up color I want(pic 1).but then when I use filter for End Customer all the colors will revert back to default settings (pic2).Is there a way how to force it so it sticks with colors I chose? VBA code?
It work but the image only show only 2 or 3 images, other chart only show nothing. is there something that i missed ? How to export all these chart (9 chart) as image without corrupting ?
I have made a database where i plan the company's resources in form of hours. The idea is that every employee will put in their own data for the upcoming 4 weeks. I do this in a table where i store employeeID, weeknumber, year, PlanneHoursUsed, ActualHoursUsed.
This again i take into a pivot chart to see a forecast for the next month.
I have somehow achieved to get up a graph where i for every month can see the sum of all planned hours and all used hours (See attachment). BUT; my boss wants to see a running total instead of a "weekly image". On the graph attached one can see the numbers for every week. But i would want to see instead a running total.. For instance week1 then week2 would be the sum of 1 and 2, week 3 would be 1 ad 2 and 3 and so on. So the graph would be inclining throughout the year.
Is this possible with functions with my current data? Or do i need to have another field in my table where i store a running total? (This would be tricky when we get a lot of data and when somebody changes an old value...)
I have a query that has a pivot chart, i need to be able to save this as an image so i can insert it into a html email but i cannot find anything, I am using access 2010 ...
I am having a terrible time getting this to work. I have a mainform that contains 11 multi-list boxes. That mainform when I select whatever I want in any of the 11, select all items in each of the 11, or select nothing and click my show results works great. In my detail section it displays the information it should. I need to now take that and put it in a report with a pivot graph. I created a subform that is my pivotgraph and the reason for a subform is the end-user still wants the ability to filter more should they choose.
In addition, the regular graph you can put in the report does not allow me to put in multiple items. For instance I need to have sumofmbrstargeted and sumofmbrsconverted in the values and it will say I can only choose 1 value and then it will say up to 6 items and I have a 8. So, I opted for a subformpivotchart. If I create the following code, the subform updates based on the filters on this mainform everytime. Works like a charm.
Code: Private Sub cmdGetGraph_Click() DoCmd.OpenForm "Graph", acFormPivotChart, , GetFilterFromListBoxes End Sub
The problem is, I want the Graph in my Report. If I take the Report and do the same basic thing:
Code: Private Sub cmdGetReport_Click() DoCmd.OpenReport "Search", acViewPreview, , GetFilterFromListBoxes End Sub
It asks for the parameters again. Example, LOB, Plan, Prod_NM. I tried just disregarding the report and created yet another subform for the reporting piece and embedded the graph and that still asked for the parameters. It works great for the subform as the graph and I want to have the ability to use the GetFilterFromListBoxes, apply it to the Search Report with the embedded Graph.
I have a pivot chart in ms access and i want to have auto-axis for y-scale. I want that when the data will be changed the scale would be automatically arranged.
I have a bar pivot chart made in a form this load fine, I have the below VBA code in place to control the bar color depending on what the bar show.
Set frms = Forms![OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0) Dim i As Long With frms For i = 0 To Forms![OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0).SeriesCollection.Count - 1 With .SeriesCollection(i).Name Select Case Forms![OtherCases created by Indv by Month
[code].....
When I then open the Report this is not working at all each bar just get a random color regardless what the bar show.how to convert the above to fit a Pivot chart form a form that is displayed on a report.
