Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is there a way of specifying either bar width or chart dimensions dynamically?
Several of the bar charts I use have highly variable numbers of values for their dimension - ranging from one to 50
This means that if I set the chart dimensions large, I sometimes get one vast bar, and if I set them small then bars or legends get dropped
I would like the bar chart to display with constant bar width, however many bars appear
If I could set bar width (keeping the chart size large) that would be fine, But I would be equally happy to flex the size of the chart with the number of bars, if that was easier.
Or is there a third way?
Am happy to use a macro if that will help.
Using QV9 - 10 not compatible with web applications.
Ralph Lucas
Hi Ralph,
for dynamically adding dimension, you can use inline wizard .
example :
LOAD * INLINE [
Dimension
Period
PRODUCTNAME
];
Reload the app
you have to use macro for dynamically adding this field into the chart.
MACRO:
LOAD * INLINE [
Dimension
Period
PRODUCTNAME
];
Dim vDim
Dim vExp
sub onAnySelection
set x = ActiveDocument.GetCurrentSelections
s = x.Selections
v = x.VarId
bDim = false
bExp = false
for i = lbound(s) to ubound(s)
'msgbox v(i)&" = "&s(i)
if v(i) = "Dimension" then
bDim = true
vDim = s(i)
end if
if v(i) = "Measures" then
bExp = true
vExp = s(i)
end if
next
if bDim and bExp then
buildChart
end if
end sub
sub buildChart
set chart = ActiveDocument.GetSheetObject("CH03")
'clean chart
set dims = chart.GetProperties.Dimensions
set exps = chart.GetProperties.Expressions
'msgbox("Dimensions = " & dims.Count)
'msgbox("Expressions = " & exps.Count)
for i=0 to dims.Count
chart.RemoveDimension 0
next
'add dimensions
set dimSelection = ActiveDocument.fields("Dimension").GetSelectedValues
for i=0 to dimSelection.Count - 1
'msgbox(dimSelection.Item(i).text)
chart.AddDimension dimSelection.Item(i).text
next
for i=0 to exps.Count - 1
chart.RemoveExpression 0
next
set expValues = ActiveDocument.fields("Measures").getPossibleValues
set expSelection = ActiveDocument.fields("Measure Name").GetSelectedValues
for i=0 to expValues.Count - 1
'msgbox(expValues.Item(i).Text)
chart.AddExpression expValues.Item(i).Text
'rename an expression label
set p = chart.GetProperties
set exps = p.Expressions
set expItem = exps.Item(i).Item(0).Data.ExpressionVisual
expItem.Label.v = expSelection.Item(i).Text
'msgbox(expItem.Label.v)
chart.SetProperties p
next
end sub
using this macro you can add dimension and expression dynamically.
NOTE: Measures and Measure Name is expression i have used
For bar width:
Check out the option : Chart Properties -> Presentation -> Bar Settings
Bar Distance
Cluster Distance
Allow thin bars
Regards,
Prabhu