Sorting GetPossibleValues in Macro

[wnorwood]

Hello,

I am using QV10.  I am building a straight table chart dynamically.  The dimensions and the expressions are sourced from tables.  I make a selection from a list box and the macro finds the field values and field names from the table for the selected value.  The table is sorted in the order that the fields should appear in the chart.

At first it seemed to be working perfectly, but sometimes the field values and field names are in different order so that the field gets the wrong name and the columns are in the wrong order.  The field names seems to be sorting correctly, it is the field values that are off. Below is the snipit of code that it performing this action.  Any help is appreciated!!

'add dimensions
set dimSelection = ActiveDocument.fields("Dimension").GetPossibleValues
set dimName = ActiveDocument.fields("DimensionName").GetPossibleValues


for i=0 to dimSelection.Count - 1

msgbox(dimSelection.Item(i).text)
msgbox(dimName.Item(i).text)
chart.AddDimension dimSelection.Item(i).text

'rename an expression label
set p = chart.GetProperties
set dims = p.Dimensions(i)
dims.Title.v = dimName.Item(i).Text
dims.LabelAdjust = 1 'center
chart.SetProperties p
'SET p=Nothing

next

[yavoro]

Hi,

I have similar problem to solve.

I want to GetPossibleValues in alphabetical order. But as far as i'm aware GetPossibleValue uses the load order of the field.

So if you make sure you load the values in that field in the order you want that might help.

I'm still looking for the alphabetical solution though.

Regards

Y

[#3]

Hi,

the problem is that your Diemnsion and Name is not stored with a connection to eachother.

If you load them into one field and then split them it would work.

LOAD

   Dimension & '|' & DimensionName AS Dimensionfield,

..

ORDER BY

   Dimension, DimensionName

And then split them in then macro

for i=0 to dimSelection.Count - 1

chart.AddDimension SUBFIELD(dimSelection.Item(i).text, '|', 1)

'rename an expression label

set p = chart.GetProperties

set dims = p.Dimensions(i)

dims.Title.v = SUBFIELD(dimName.Item(i).Text, '|', 2)

dims.LabelAdjust = 1 'center

chart.SetProperties p

'SET p=Nothing

next

Cheers

[marcus_sommer]

Alternative to read a field you could put the dimension + name in a tablebox - see APIGuide.qvw for examples like this:

set table = ActiveDocument.GetSheetObject( "CH01" )

CellRect = ActiveDocument.GetApplication().GetEmptyRect()

CellRect.Top = 0

CellRect.Left = 0

CellRect.Width = table.GetColumnCount

CellRect.Height = table.GetRowCount

set CellMatrix = table.GetCells( CellRect )

for RowIter=CellRect.Top to CellRect.Height-1

    for ColIter=CellRect.Left to CellRect.Width-1

        msgbox(CellMatrix(RowIter)(ColIter).Text)

    next

next

For sorting an array see examples like this (BubbleSort):

http://www.robvanderwoude.com/vbstech_data_array.php

- Marcus