Inserting new columns/rows by code VCL & ActiveX

[raul]

Need to insert new column or lines by code.
( not Calculated Measures ! )

For instance:

Columns -> A1 A2 A3 A4 X1 X2 X3

It is not possible to insert a column X1 as (A1 + A2), and X2 as (A3 + A4) and X3 as (X2 - X1).

will have to export to EXCEL and add the columns manually
but..
try to use SQL statement..
Programatic...
PCX.FactTableType = fttSQL
PCX.FactTableSQLText = "select A1, A2, A3, (A1 + A2) as X1, (A3-X1) as X2 from myExcel)

Or setting “Data set type” property to “SQL” and the Sql statement in “SQL Text” property. using Property Pages, “Data source” tab, and “Connection and fact table” button of the PivotCubex Control

[AlexZencovich]

I'm sorry I could not understand well what you mean. But if you ask about possibility to insert new column in PivotGrid - it is impossible.

The only way I can suggest is add to dimension a 'template' items and fill them with your internally calculated values when they should be shown. In other time they should be marked as filtered.
I do not know if it may be acceptable.

[raul]

Hi Alex, excuse me if I come back into the same topic, maybe it can be usefull for anyone. I'll try to explain it with my poor English.
I talked about unresolved problem exposed in VCL forum by Siegmar Gieseler User
Posted: Thu Jul 13, 2006 10:30 pm Post subject: Inserting new columns/rows by code ?
asking if is it possible to insert new column or lines by code.
Example:
If you have in Excel file (myExcel.xls)
Columns ->
A1 A2 A3
---------------
Val1 Val2 Val3
100 200 300

You will have in Cube only
Val1 Val2 Val3
150 200 300
but you wish have
Val1 Val2 Val3 Xcalc1 Xcalc2 <- new 2 columns with calculated values
150 200 300 350 150

Xcalc1=Val1 + Val2
Xcalc2=Val3 - Val1 <- new calculated values

I know it is imposible, to add new columns by code
and the user's final conclusion was
"If it is not possible I will have to export to EXCEL and add the columns manually" or add columns in Excel before to load in cube

but using ...
PCX.FactTableType = fttSQL
PCX.FactTableSQLText = "select Val1, Val2, Val3, (Val1 + Val2) as Xcalc1, (Val3-Val1) as XCalc2 from myExcel)

Or setting “Data set type” property to “SQL” and the Sql statement in “SQL Text” property. using Property Pages, “Data source” tab, and “Connection and fact table” button of the PivotCubex Control

you have a solution to this problem.
in fact inside cube you have these 5 fields
Val1 Val2 Val3 Xcalc1 Xcalc2 (fields)
150 200 300 350 150 (values)

to use like fact table fields in Dimensions and Measures and moving to row or column

I tested and worked fine.
It is not like "add a new column or line" by code but is "alike". It maybe resolve problems like that exposed by Siegmar Gieseler User in his post

Sorry if it is so obvious or not a usefull contribution.
Thanks

[AlexZencovich]

Oh, sorry. I really misunderstood you. Thank you for good example, I think it will useful for everyone who meet similar problem.
But not, it will work only for simple aggregations like SUM or COUNT(when order calculations is not important).

The way I suggest you (with template items) seems to be little more universal, like it will allow you to get more complex data in columns like
X1= A1/A2
etc