List Box Expression Macro

[#1]

Hello,

I am trying to add an expression to a listbox using a macro and have got this code:

set chart = ActiveDocument.GetSheetObject("LB_CohortSelectBox")

set p = chart.GetProperties

set expr = p.Expressions.Item(0).Data.ExpressionData

expr.Definition.v = "sum(Amount)"

chart.SetProperties p

but the expression never gets added to the listbox. I also need to add a conditional show to this but I am stuck on this first step.

Can you help?

Many thanks,

Chris

[Gysbert_Wassenaar]

You'll want something like this: 

set chart = ActiveDocument.GetSheetObject("LB_CohortSelectBox")

set p = chart.GetProperties

p.Def.Name = "=aggr(sum(Amount),Product)"

chart.SetProperties p

To begin with a listbox isn't a chart so it has a different kind of properties interface. Also, sum(Amount) returns only one value. You probably want to aggregate the amount over one or more dimensions to get a list of values.

To conditionally show/hide an object you can use the Show Conditional expression box on the Layout tab of the properties window of the object. You can enter an expression there. You could also use a variable there and put the expression in the variable or set the value of the variable using a button or a trigger. There are plenty of examples on the forums. You should be able to find one or two if you search a bit.

[alexandros17]

This works

sub myModule



set chart = ActiveDocument.GetSheetObject("LB_CohortSelectBox")



set p = chart.GetProperties



set expr = p.Expressions.Item(0).Item(0).Data.ExpressionData



expr.Definition.v = "sum(Input)"



chart.SetProperties p



end sub

[#4]

Thank you for your help.

I have actually already set the Def.Name, If you look on the properties of a list box there is an expressions tab and it is here I wish to add an item.

Thanks again,
Chris

[#5]

Thanks for your suggestion but this didn't work for me. Is it because I am applying to a listbox and not a chart?

[alexandros17]

May I ask you why you want to add a number to a list box ?

This object contains distinct values not only a number comeing from a sum ... am I wrong? Probably this is not the best object to do whot you want?

[#7]

Sure, I am using an expression to show many items are in each option. This has a conditional show attached so the user can toggle if they wish to see this information or not. So this listbox displays a list of values in the field with or without a count of the items the filter will apply to basically.

We have a version working which is static but I now need to create the listbox dynamically based on an earlier selection which is working fine, it is just this final part of adding an expression to it.

Thanks,
Chris

[bgerchikov]

Here is how it works (at least for me):

set chart = ActiveDocument.GetSheetObject("LB_CohortSelectBox")

set p = chart.GetProperties

p.Expressions.Add

set expr = p.Expressions.Item(0).Data.ExpressionData

expr.Definition.v = "sum(Amount)"

chart.SetProperties p

PS I've spent several hours to figure out this method used instead of AddExpression in graphs or tables.

Thanks!