Read resident table in VBScript Macro

[#1]

I currently have a table which controls the display of certain measures in a macro.  Right now I am reading from a database via ADO to capture this table in my macro, but I was wondering if I could more easily read from the table line by line in a recordset directly from QV after loading the table in the script.  This would be much more efficient and failsafe.

Here is my macro below:

Public Sub FetchRollups

    selectedLevel = ActiveDocument.fields("Levels").GetSelectedValues.Item(0).Text

    Dim conn

    Dim rs

    'Instantiate connection and recordset objects

    Set conn = CreateObject("ADODB.Connection")

    Set rs = CreateObject("ADODB.Recordset")

    'Open the connection and capture the P&L Rollup table

    conn.ConnectionString = "DSN=CXAPPSVR03"

    conn.Open

    Set rs = conn.Execute("SELECT * FROM TEST.DBO.C_X_PLDEF WHERE LEVEL <= " & selectedLevel)

    'Grab the relevant chart and associated properties

    set chart = ActiveDocument.GetSheetObject("CH02")

    prevcount = chart.GetProperties.Expressions.Count

    'Add all expressions from recordset

    Do While not rs.EOF

        'Add expression and capture its ID

        x = chart.AddExpression(rs("ExpressionValue").Value)

        'Set expression properties such as label

        Set prop = chart.GetProperties

        prop.Expressions.Item(x).Item(0).Data.ExpressionVisual.Label.v = String(rs("Level")*3, " ") & rs("ExpressionText").Value

        chart.SetProperties prop

        'Continue Loop

        rs.MoveNext

    Loop

    'Delete old pdimensions

    for y=prevcount-1 to 0 Step -1

        chart.RemoveExpression y

    Next

    'Cleanup chart

    'chart.SetPixWidth 0,200

    conn.Close

    'chart.SetProperties p

End Sub

[marcus_sommer]

You could load your expression-table directly in the load-script and put each one afterwards in a variable and use these variables as expression. A macro wont needed.

Connect ...

ExpressionsTable:

Select * From ExpressionFrom Database;

for i = 1 to FieldValueCount('Expressions')

     set expr1 = peek('Expressions', $(i) - 1, 'ExpressionsTable');

....

- Marcus

[#3]

But then how do I get these expressions into a pivot table dynamically? There can be a different number of expressions.

[marcus_sommer]

The expressions themself are static but the content is dynamically:

expr1 = $(expr1)

expr2 = $(expr2)

....

The same could be done with the label and comment from the expression and also by the conditions which then control different number of expressions.

- Marcus

[#5]

I'm confused. How would I control the number of expressions conditionally displayed?

[marcus_sommer]

Conditions for dimensions/expressions are only available since QV 11. Your expressions-record could beside the label, expression and comment also have a field for the condition - a simply true/false or also an expression on any fields.

If you use a version older as QV 11 you can't control the number of expressions on this way, then you must put the expressions with a macro. But the load the expressions from the database into a table/fields/variables could be done and then per macro with an OnOpen-Trigger put in the chart you want.

- Marcus

[#7]

All - I don't think the above solution is going to work.  I simply have too many conditional expressions, and the performance is terrible.  My macro is really only slow because I am reading a Table Box.  That read process is incredibly slow based on me commenting in and our parts of the script.

There has to be an easier way.

[marcus_sommer]

It isn't really clear what you mean. The dynamically befilling from the expressions (with which solution) is too slow or the calculations from these objects by open/select?

- Marcus

[jarno_loubser]

I have the same requirement - reading a resident table in macro code. Reading through a front-end object is just too slow.

Anyone?

[marcus_sommer]

The fastest way to read a table is always directly within a load-statement. If you couldn't work with dynamically expressions per variables, you could also create a tablebox with these data within the gui and read these object in a loop per macro. I didn't understand what should be too slow with such a solutions ...

- Marcus