I have a chart where the dimension is month and country. And one of the list box on the page is Country(which is part of the cycle through dimension)
Let's say the metric shown on the chart is Sale.
And this is what I want to have happen:
Whenver the user navigates to the page, I want to have the Country list box selected with top 5 sales countries, depending on whatever the selections are on other list boxes.
Thanks for the reply. I may not fully explained the situation.
What I want to do is to "pre-select" the top 5 countries in the list box, so when the user comes to the page, the chart does not show "all" countries. As you can visualize, if there are no countries selected in the list box, the chart will show all countries, and that is not usable. So what I want to do is to start the chart with 5 top countries, and then the user can modify the selection to their liking. I've made a macro with manual code to pre-select the countries in the list box, and the macro is activated when the page is activated. However, I am looking for a bit more dynamic solution.
Peter is right that setting the Chart properties will allow you to display only the Top 5 without any selections. Set Analysis may also be able to do this.
From your description, it sounds like you want the user to start with the top 5, but be able to select more or less as they choose. That may require the macro. Here's what I came up with:
Sub Selector thresh = CInt(ActiveDocument.Evaluate("Max(Aggr(Sum(Sales), Customer), 5)"))
Set fld = ActiveDocument.Fields("Customer")
fld.Clear
Set val = fld.GetPossibleValues
j = 0
For i = 0 to val.Count - 1 amt = CInt(ActiveDocument.Evaluate("=Sum({<Customer={'" & val.Item(i).Text & "'}>}Sales)"))
If amt >= thresh Then If j = 0 Then fld.Select val.Item(i).Text, true Else fld.ToggleSelect val.Item(i).Text, true End If
j = j + 1 End If Next End Sub
This will probably require modification depending on your data types. My example uses Customers instead of Countries, but the logic should be the same. The difference you may have is if your Sales are not Integers, then you need to change the CInt to CDbl.
Thresh gets set to the fifth highest sales, which will be your ranking cut-off. We then compare each possible selection against that thresh level and if it's greater than or equal to it, we select it.
