How to add a calculated dimension to a chart from a VBScript macro ?

[#1]

Hi all,

I need to create a bunch of graphs and wanted to quickly write a little macro that would replicate the same layout for all the required graphs.

So I started to write some lines of code but quickly hit a problem.  Here is the (simplified) code:

Function CreateGraphs()
  Set chart = ActiveDocument.ActiveSheet.CreateLineChart
  chart.AddDimension "=IF([DeterminationCode]='XYZ', [SamplingTime])"
  chart.AddExpression "MeasurementValue"
End Function

For some reason, this line "chart.AddDimension" is silently ignored.  If I add the same calculated dimension interactively: no problem, all is working perfectly.  But I'm unable to reach the same result via the macro.

It looks like this issue has already been raised in a previous post but I could find the right way to workaround the issue.

Any suggestion to help me?

Thanks a lot, have a great day.

--

Gaëtan

[#2]

Hi Gaetan,

as far i can see AddDimension with calculated dimension is not working on line/bar charts but works if pivot table is created. So my idea is macro first create pivot table with calculated dimension and then change the new created chart type to line chart. The folloing macro do the job:

sub CreateGraphs
    set chart = ActiveDocument.ActiveSheet.CreatePivotTable
    set s=ActiveDocument.Sheets("Main")
    ' loop through all sheet objects to find the chart ID of the new created chart
    for i=0 to s.NoOfSheetObjects-1
         objid = s.SheetObjects(i).GetObjectId
        ' filter only chart objects because we create chart 
        if left(replace(objid,"Document\",""),2) = "CH" then
             newchart = s.SheetObjects(i).GetObjectId
             newchart = replace(newchart,"Document\","")
          end if
     next
     set linechart = ActiveDocument.GetSheetObject(newchart)
     'add dimensions and expressions. At this point the chart is still pivot table
     linechart.AddDimension "=IF(Name='A','AA','Name')"
     linechart.AddExpression "count(id)"
     'change the chart type to line chart
     linechart.SetChartType  4   ' line
End sub

Hope this helps!

Stefan

[#3]

Hello Stefan,

Thanks for your help, it indeed looks like a good idea but according to the first tests, I can add the calculated dimension to the pivot tabe (that's a first good news!) but the SetChartType operation does not work...

Here is the code:

Set chart = ActiveDocument.ActiveSheet.CreatePivotTable
chart.AddDimension "=IF([DeterminationCode]='XYZ', [SamplingTime])"
chart.AddExpression "MeasurementValue"
If chart.SetChartType(4) Then
  MsgBox "ok"
End If

The message "ok" never appears and I see a pivot table with the correct dimension/expression.  If I then change the type "manually", it just works fine.

What can be wrong?

[#4]

Hi Gaetan,

which version of qv did you use? I'm working on v9 and the code works

Regards!

Stefan

[#5 · Accepted solution]

Hi again,

Got it to work...The trick is to add the calculated dimension to a pivot chart, then allow "fast change" to line chart before calling SetChartType. See code below.

Thanks for the top Stefan, it really helped.

' Create new chart
' Note: adding calculated dimension does not work on line charts so let's start
'       with a pivot table and change the type later
Set pchart = ActiveDocument.ActiveSheet.CreatePivotTable
' Get the object ID
pchartid = Replace(pchart.GetObjectId, "Document\", "")
' Add calculated dimension
pchart.AddDimension "=IF([DeterminationCode]='XYZ', [SamplingTime])"
' Add expression
pchart.AddExpression "MeasurementValue"
' Now switch to a "line chart" type; to do so, we first 
' need to allow "FastChange"
set p = pchart.GetProperties
p.GraphLayout.FastChange.Line = true
pchart.SetProperties p
' Do change type to Line chart
pchart.SetChartType 4
' Now retrieve the line chart object
Set chart = ActiveDocument.GetSheetObject(pchartid)
Set p = chart.GetProperties
' Revert the "fastchange" trick to be able to change the type of the graph
p.GraphLayout.FastChange.Line = false
' **
' ** Do your own customization of the line chart object here **
' **
' Apply your change
chart.SetProperties p

[#6]

Thanks. I'll have it in mind 🙂

[#7]

Hi Stefan,

Stefan Stoichev wrote:

which version of qv did you use? I'm working on v9 and the code works

Indeed, I should have mentionned it : I'm using V10 SR2.

Again, thanks for your precious help.  Now I'm looking for a solution to change the X-axis to a continous one, It does not look to be exposed in the API, ...? I may need to create a new thread about this one...

Have a great day.

[#8]

Hi Gaetan,

and i can see the how countinuos in the API

Regards!

Stefan

[#9]

Stefan,

Stefan Stoichev wrote:

and i can see the how countinuos in the API

What do you mean?  Do you know how to force a "continous X-axis" in a VBScript macro?

Have a great evening.

  Gaëtan

[#10]

Sorry Gaetan it was typo and i mean: can't  😞