I have created an automatic process to extract information from 40+ spreadsheets and build a Pivot Table (Data Pilot Table) from it. This includes adding a simple chart (also by macro) but this does not give me the functionality I am after. I want to extend the macro to include a Pivot chart sourced from the Pivot Table so that it is easy to select the data options to be included in the chart but I’m struggling to find information/help on the properties, methods, services etc. to do this, despite using xray on a manually created pivot chart of what I need.
Can anyone point me to help files or an example of a macro to build a pivot chart using a pivot table as its source so I can move forwards?
Thanks in anticipation…
Apologies for the delay in posting.
Attached file contains macros that create a Pivot Table and Pivot Chart based on it. It has been kept simple to aid understanding. The use of Xray or MRI is suggested for anyone who wishes to work out how to adjust features of the chart. Having initially struggled it was a relatively simple task but I have still not found a way to put the chart onto a separate sheet.
Many thanks to Ratslinger who passed me a lot of useful material.
Pivot-Pilot and Pivot Chart Example.ods
Update
Noting Ratslinger’s answer to putting the Pivot Chart onto a different sheet and following questions from local friends, I have updated the file (see below) to include;
a) Putting Chart onto another sheet.
b) Setting a different Chart Type
c) Making it a stacked version
d) Improved presentation by grouping the date field in the DataPilot/Pivot, which reflects directly into the Pivot Chart.
@Tavy_Dog_Walker Thanks for the code. Took a quick look & looks good. Will look into it more this weekend & if I find something to move it to a different sheet (whenever that may be) I will post here. Will probably dig more into settings & see how/if/what can be modified as I did with regular charts. +1!!!
Finally sat down with code. Easy to set chart on any sheet you want.
In the chart section, you can create sheet as you did for Pivot table (or use any sheet wanted), get the sheet by name and use that for the sheet in:
oPivotCharts = SHEET_YOU_CHOOSE.PivotCharts()
Works for me! Can provide any info if needed.
I now feel like Homer Simpson ….Duoooh!
I never thought of trying the obvious because I couldn’t find a way to do it manually, so I just assumed . Thank you BIG TIME!!
Hi Tavy, perhaps this may lead you in the right direction. If I understand correctly you need to be able to get the chart to represent the data extracted from the Pivot Table? Please see attached a couple of screen shots for a very simple pivot Table. To create the Chart, highlight the Pivot Table; goto “Insert” menu; select “Chart”. You can then create the chart based on the style you require.
You can select the data to display by either the drop down arrow on the Table, or using the Drop down arrow on the chart. (see Date field on Bottom Left corner)
I hope this helps or at least points you in the right direction?
Cheers
Thanks for responding. I am aware of manually inserting charts using the Ribbon commands and wizard. What I am trying to achieve is the same thing but programmatically so that my process that builds the pivot table also creates the Pivot Chart so that a less savvy user does not have to know of the intricacies of Calc and can easily get summarized information in a visual form.
I understand. Is your process simialr to the folloowing
Pivot table parameters don’t change?
The macros extract the data from vaious sources and repopulate your Pivot table data source.
Macro to remap the data source as file expands / shrinks etc
The User Pivot table does not change, just the underlying Data source?
Cheers.
The user collects data from a large number of Spreadsheets (.ods and .xlsx) and build a Pivot table. The number of files and some parameters within the files will change over the months. The Pivot Chart is my preferred way of being able to show the user a visualised trend with some selection options to either focus on specific items or remove distractions . The user will not change either the underlying data, The pivot table or the proposed pivot charts. They are just users of a tool.
Hello,
Don’t have much to offer but it may help. Until your question I had not used a Pivot Chart in any fashion. However have done a fair amount of macro coding using charts.
Haven’t found anything specific on the API but using MRI it was nearly the same as what I had been doing. Reading the author’s blog ( see → Pivot charts in LibreOffice ) an item in Part 1 that struck me was the need to use a listener to detect Pivot Table changes so the data in chart could be updated. Using MRI, it appears (although a cursory glance) similar to what I had been doing in regard to loading data… See code in samples on these posts:
Again, some brief playing around using MRI I was able to modify a variety of items in the chart. Very similar to what was done in Base charts post.
Unfortunately I am currently devoting a lot of time in database testing. Every once in a while I do poke my nose in this code as it interests me. If I come up with anything remotely creating a Pivot Chart using a macro I will post the information.
Edit:
You may have this already but here is code to get to data provider (this generated by Mri):
Sub Snippet
Dim oSheets As Variant
Dim oObj1 As Variant
Dim oPivotCharts As Variant
Dim oObj2 As Variant
Dim oEmbeddedObject As Variant
Dim oDataProvider As Variant
oSheets = ThisComponent.getSheets()
oObj1 = oSheets.getByName("YOUR_SHEET")
oPivotCharts = oObj1.getPivotCharts()
oObj2 = oPivotCharts.getByName("YOUR_PIVOT_CHART_NAME")
oEmbeddedObject = oObj2.getEmbeddedObject()
oDataProvider = oEmbeddedObject.getDataProvider()
End Sub
In the meantime I hope some of the above gives you a little bit more direction.
Gratefully received. I will investigate further later today. You have confirmed that there is not a lot of info available (at least not obviously!). If I make progress I will post findings to help others. I know trail blazing can be hard work!!
@Tavy_Dog_Walker Not sure if you saw it. In the post on Base Charts in the comments, I did point to a great source for chart API. If you missed, here is the link → Common Parts of all Chart Types. Turned out to be an invaluable source of information. I see much of that also in Pivot charts. What is missing is the specifics on data provider. Listeners are a different subject but not difficult.
Also note other sections there on charts. All of it is useful.
Very useful Info. Whilst charts have same structure, I’m trying to clarify how to utilise Pivot Table as a source. I’ve made a little progress on this but it looks like some trial & error steps are required whilst I get the model of the concept into my head!
Process is now working except being able to place the Pivot Chart on a separate sheet from the Pilot (Pivot) Table. This may not be possible as I have not found a way to do it manually yet. I’ll build a demo file for sharing later this week (Can’t share the actual data).
Thanks for the note. Look forward to sample! Please post as answer to your own question.
My solution is the Data file will be updated monthly via macros (yet to write). The Users want the same sort of information and have created separate Pivot Tables & Chart for each criteria. Users can still filter depending options included on the Pivot Table. I found it easy to set up & gives Users choices of what to display. on the Chart, if you want to include in another document
/home/ding/Pictures/Membership 2.png
Still a WIP at this end