I have a pivottable (pt), the table has 1-x row, and lets say grouped by year
so like this:
Row labels Number of cars
2013 14
2014 54
2015 5
What i would like to do is make a chart with specific series from my pivot table like this:
int nSeries = chart.NSeries.Add(“B7:B9”, true);
But i would like to get the range “B7:B9” dynamic from the pivot table object by the name of the column. How do I achieve this
as an example: pt.datafields[“number of cars”].range (ish)
You may add pivot chart based on pivot table, see the document for your reference.
Alternatively, you may get the pivot table in the sheet and call PivotTable.RefreshData() and PivotTable.CalculateData() methods before specifying chart’s data range.
I do not want all the columns in the pivot table in this chart and because of that i think i need to add the ranges one at the time (also because i need the different series to be of different chart types (block, line etc.)
if have tried many things at this moment, but can’t seem to get any of them working.
pt.GetCellByDisplayName("") returns null (not sure of this is at pivot field reference or a pivot-datafield reference
if i look at my pivottable object, i can see the datafields is there but the .Range property on all of them is NULL.
the pt.DataBodyRange only gives me the dataarea (start row/column -> end row/column) of the pivot table, but i cannot reference anything by name in there and besides the DataBodyRange returns 14 rows even though there is only 7 in my pivottable
Did you try setting range (after refreshing pivot table and calculate its data) for the chart in the format like following? int nSeries = chart.NSeries.Add(“B7:B9”, true);
what is the result?
Could you share your sample code (runnable) and template file to show the issue, we will check it soon.
@Amjad_Sahi
This works fine: int nSeries = chart.NSeries.Add(“B7:B9”, true);
and has been working all along. My problem is that i need to get the datarange “B7:B9” from the pivottable since i do not know that the range is from “B7:B9” , might as well be “B3:B499”
Well, maybe you could use pivotTable.RowRange --> it will give you row field range (e.g A2:A7 – generally it refers to range in single column). If you need to get body range, you may try pivotTable.DataBodyRange that will give you the data range area. I am afraid, there is no other way round to cope with it. Please note, in MS Excel Pivot table has only these two ranges part and nothing else, so you have to devise your own code accordingly for the task.
I have several questions, but first of all the pivotTable.RowRange does not give me the row field range as “A2:A7” it gives me 4 attributes (startColumn, startRow, endColumn, endRow) as integers which are not useable as dataranges (missing the column part).
The DataBodyRange is constructed the same way.
The pivotTable object has a collection of dataFields, which are those i have added to the pivotTable and named. Each of these has an attribute called “Range” which is null.
pivotTable.ColumnFields[“FieldName”].Range
Why is this attribute null? should i not contain the datarange of the specific dataField?
It would make a lot of sense to be able to get the different ranges from the pivotTable directly from the datafields added.
When you bind a chart to at pivotTable source, why are the ranges in the pivot not added to the Nseries property of the chart object? Is there anyway to accomplish this, because this would mean that you don’t have to bind each series yourself. The chart works fine with just the PivotDataSource, but the Nseries property is null
You cannot get the ranges from PivotTable’s fields. You have to use RowRange, DataBodyRange, TableRange1 and TableRange2 attributes for your custom needs.
Could you provide your sample expected pivot table and chart (based on data ranges), you may create your task manually in MS Excel and save the Excel file to provide us (please zip the file prior attaching), we will check it.
It seems I have found a solution to the problem.
The reason Nseries is not populated after adding PivotTable as datasource is because I needed to call:
chart.RefreshPivotData();