Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivottable
I have vba code that creates a pivottable with an
external informix database as the source. Part of the SQL statement is listed below: SELECT loc_id, ........ invt_mthend_qty*invt_avg_cost FROM informix.grp_name grp_name...... WHERE item.item_grp_nbr = grp_name.grp_nbr ...... When the code attempts to calculate the pivottable, the field "inv_total.invt_mthend_qty*invt_avg_cost" is inconsistently displayed as inv_total.invt_mthend_qty*invt_avg_cost OR EXPR. Any thoughts on why the reference is not consistent? Thanks Fred |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivottable
"Fred" wrote ...
I have vba code that creates a pivottable with an external informix database as the source. Part of the SQL statement is listed below: SELECT loc_id, ........ invt_mthend_qty*invt_avg_cost FROM informix.grp_name grp_name...... WHERE item.item_grp_nbr = grp_name.grp_nbr ...... When the code attempts to calculate the pivottable, the field "inv_total.invt_mthend_qty*invt_avg_cost" is inconsistently displayed as inv_total.invt_mthend_qty*invt_avg_cost OR EXPR. Any thoughts on why the reference is not consistent? Consider this: SELECT ColumnA * ColumnB It is an expression using two columns, rather than a column in its own right. It doesn't have a name but needs one: each column in the results set must have a unique name so it can be identified and distinguished. You haven't given it a name so the data provider has done it for you, I guess by using the expression text or EXPR, EXPR1, EXPR2 etc when the expression is too long or not unique. Why the naming convention is inconsistent, I cannot speculate. However, if you give the expression an explicit name, I assume it will always be consistent. To give the expression a name, use an alias. You did so with your table names FROM informix.grp_name grp_name which can be more clearly written as FROM informix.grp_name AS grp_name Hence, use something like: SELECT invt_mthend_qty*invt_avg_cost AS total_mth_yield using a meaningful alias, of course. Jamie. -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivottable
Thankyou Jamie.
I successfully made the changes with the AS statement. I have modified the code a couple of times using inv_total.invt_mthend_qty*invt_avg_cost or EXPR as the field, when the code would error out. Hopefully the AS statement resolves it and I don't have to guess if it will work this time or the next. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable | Charts and Charting in Excel | |||
Creating a PivotTable w/o selecting data in an existing PivotTable | Excel Discussion (Misc queries) | |||
PivotTable | Excel Discussion (Misc queries) | |||
PivotTable - PivotTable Field name is not valid - error! | Excel Programming | |||
Creating a PivotTable Report from an Another PivotTable Report | Excel Programming |