Dynamic Query

[Iton]

Sorry my English, I am from Ukraine ^)


I use dynamic query (by my programm), dymanic add Dimension to PivotCube, use DimensionToColumn (or ToRow) for show arrays.
but, it`s code work by second run (click button):
...
//create fields to ibQuery, SQL for ibQuery, Dim for Pivot Cube etc.
...
//in this place, i want to activate PCube, but it work if i run this code 2 or more run

ibQuery.Open;
ibQuery.FetchAll;
PivotCube1.Activate;

and if i change query, Cube build-self on first query

[AlexZencovich]

1. There are no method like
PivotCube.Activate;
so I do not know which way you really use. I guess PivotCube.Active := True;

In that case you should to set PivotCube.Active to False before you change configuration

[Iton]

Sorry, my mistake - PivotCube.active
yes, i use first pivotcube.active:=false, then create all dimension, fields etc. run query and set pivotcube.active to TRUE.
but cube not build in first run ^(

[AlexZencovich]

Check if you open query (and query retrieve all field information) before you have created dimensions and measures

[Iton]

i use code like this:
proc ButtonClick;
begin
ibQuery.close;
PCube.Dimensions.Clear;
ibQuery.FieldDefs.Clear;
...
ibQuery.sql.text:=ibQuery.sql.text+'name_comp,';//Dynamic add field to query

if ibQuery.Fields.FindField('NAME_COMP') = nil then
begin
ibQuery.FieldDefs.Add('NAME_COMP', ftString);
fFieldDef := ibQuery.FieldDefs.Find('NAME_COMP');
fField := fFieldDef.CreateField(ibQuery);
fField.FieldName := 'NAME_COMP';
fField.Name := 'NAME_COMP';fField.FieldKind:= fkData;fField.Size:=255;
DM:=TDimensionItem.Create(pc.Dimensions);
dm.DataSet:=ibQuery;
DM.FieldName:='NAME_COMP';
DM.AliasName:='NAME_COMP';
DM.DisplayName:='Company';DM.Enabled:=true;
map.DimensionToColumn('NAME_COMP',$00000003,PVCol.ItemCount-1);
map.RefreshData(true);
end;
//and some like [UP]
//THEN

ibNumDistr.SQL.Clear;ibNumDistr.SQL.Text:=query;
ibNumDistr.Active:=true;
ibNumDistr.Open;ibNumDistr.FetchAll; //I check this place - all fields and data i give

pcube.Active:=true;

and now i can see my dimension in PVDimToolBar
i want to move my Dimension to Column or row

[AlexZencovich]

1. I was unable to find PivotCube.Active := False in that code

2.
map.DimensionToColumn('NAME_COMP',$00000003,PVCol.ItemCount-1);
map.RefreshData(true);
should be placed AFTER
PivotCube.Active := True;

It will have affect only if cube already active.

[Iton]

i change code:

ibQuery.Close;
ibQuery.FieldDefs.Clear;
pc.Active:=false;
pc.Dimensions.Clear;
...

ibNumDistr.SQL.Clear;ibNumDistr.SQL.Text:=query;
ibNumDistr.Open;ibNumDistr.FetchAll;


//CREATE DIMENSIONS

pc.Active:=true;

if ibNumDistr.Fields.FindField('NAME_COMP') = nil then
begin
ibNumDistr.FieldDefs.Add('NAME_COMP', ftString);
fFieldDef := ibNumDistr.FieldDefs.Find('NAME_COMP');fField := fFieldDef.CreateField(ibNumDistr);
fField.FieldName := 'NAME_COMP';fField.Name := 'NAME_COMP';fField.FieldKind:= fkData;fField.Size:=255;
DM:=TDimensionItem.Create(pc.Dimensions);
dm.DataSet:=ibNumDistr;
DM.FieldName:='NAME_COMP';
DM.AliasName:='NAME_COMP';
DM.DisplayName:='Company';DM.Enabled:=true;
end;
map.DimensionToRow('NAME_COMP',$00000003,o);o:=o+1;
map.RefreshData(true);


but, after second run count fiedls in ibQuery not 0
i use ibQuery.FieldDefs.Clear; for clear //if i use ibQuery.FieldList.Clear; - i get message "read-only property"

[Iton]

Query = ibNumDistr

[AlexZencovich]

probably will much easier create/dspose Query each time before cube rebuild instead of fileds clearing.