Author: Qubeczek
date: May 14, 2005 - 12:51
Hello,
I'm looking at Demo program. thera are default Season measurment, and Employee.
I tried to select those Employee, whos sales is 50 % of all saleas at some season.
What I need:
1. Sort the emploees by value of "Suma" measurment at some Season (by value of some column)
2. Make a new value od "Suma" measurment, where is the suma of curent row with all values of previous rows ( i tried of "Sum with previosu row", but is not what a I wanted).
3. Filter of employee, for wich my new value is less then half of total "Suma" value.
How to do it ?
If it's not posiible in Demo, is it posiible in my own program ?
answer | answer with quote
Author: Alex Zencovich
date: May 14, 2005 - 17:32
You need to look for "Running Total for column" - it seems like as you wanted in 2
Create calc. measure with your criteria, like
Result = Sum[COLUMN]/Sum[RUNNING_TOTAL_BY_COLUMN];
Then set measure filter for that, new measure like Min Value = 2
answer | answer with quote
Question for the total of calculated measure
Author: Rocco Freda
date: May 11, 2005 - 7:18
I've this question:
I've write this code in TPivotMap.OnCalcMeasureGetFunction
......
if FunctionName = 'clienti' then
begin
if Param[0] >0 then
Value := 1
else
Value := 0
end;
then in run time i've create a new calculated formula (TEST) with this sintax
begin
func clienti;
Result := clienti("VALUE1");
end;
VALUE1 is a measure with ctsumma
But in the TOTAL of the column TEST i've always "1" and not the sum of the all "1" value (example: "150")
is possible to have this?
Thank you very much for your support.
Rocco
answer | answer with quote
Author: Alex Zencovich
date: May 11, 2005 - 8:14
it is ok
the total (and subtotal) values calulated independently not as real summ of all cells above (like in usual tables)
So your calc measure takes total value for "VALUE1" and try to convert it by calculated formula (for example result := Sqrt(Value1); )
answer | answer with quote
Author: Rocco Freda
date: May 11, 2005 - 9:22
VALUE1 represents the amount in money of a client that have buy a product.
es:
product client value asbuy
------- ------ ----- -----
pizza mario 1000 1
franco 0 0
luca 100 1
--------------------------
TOTAL 1100 2
For count the clients that have bought at least a product i have think to do this:
if Param[0] >0 then
Value := 1
else
Value := 0
but as you tell, is not a real sum of all cells above (like in usual tables)
It's also possible to do this adding a column by editing an appropriate sql query at the fact table, but i must make many indices like this for count clients for products..clients for supergroup of product,clients for group of product and so on and the table have more than 150.000 lines at the moment.
There is another problem, every index is a measure!
do you have any suggestion??
ex. if is possible force the total and subtotal line
Thanks
Rocco
answer | answer with quote
Author: Alex Zencovich
date: May 11, 2005 - 10:18
Why not use another "ctCount" measure fro same data field? As I can see it will provide same result as you expected, is itn't?
answer | answer with quote
Author: Guest
date: May 11, 2005 - 11:15
Yes is true but...i have try this :
"value_0" is a ctsumma of field "value_0"
"value_1" is a ctsumma of field "value_1"
"asbuy" is a ctcount of field "value_0"
the output is this:
product client value_0 value_1 asbuy
------- ------ ------- ------- -----
pizza mario 1000 0 1
franco 0 1000 1
luca 100 1 1
--------------------------------------
TOTAL 1100 1000 3
becouse probably ctCount count all the rows the correct view is this:
product client value_0 value_1 asbuy
------- ------ ------- ------- -----
pizza mario 1000 0 1
franco 0 1000 0
luca 100 1 1
------------------------------------
TOTAL 1100 1000 2
I try to use filter by map cells with a value > 1 in measure manager but everything disappear and i have only the total
thanks for your patience..
Rocco
answer | answer with quote
Author: Alex Zencovich
date: May 11, 2005 - 12:17
Try to set TPivotCube.ExcludeZeros to True before build - it will exclude field zero values from counting (by default it is false because no way to know is zero actual value or just empty place)
answer | answer with quote
Problem with nonexistent row.
Author: Josef Zvonicek
date: May 17, 2005 - 8:25
I am novice in using Pivote Cube and i have this problem.
I have simple sample to learn working with Pivote Cube. I have two dimmension in row PivotMap: Date and KodVyk. Grid looks like this:
Body
Date KodVyk Value
11.1.1993 3.00
17201 1.00
01102 1.00
10043 1.00
All is ok.
When I add next row dimmension 'ICP' grid looks like this:
Body
Date KodVyk ICP Value
11.1.1993 3.00
17201 77001374 1.00
01102 1.00
77001374 0.00 //**blue value
77001400 1.00
10043 77001400 1.00
Blue 'Body' value zero appears in grid but this row doesn’t exist in my table. Why is it in grid? The grid would look like this:
Body
Date KodVyk ICP Value
11.1.1993 3.00
17201 77001374 1.00
01102 77001400 1.00
10043 77001400 1.00
Can you help me somebody?
Thanks Josef.
answer | answer with quote
Author: Alex Zencovich
date: May 17, 2005 - 10:11
Set TPivotMap.HideEmptyRows to True
answer | answer with quote
Using latest version of Tee Chart
Author: Josef Zvonicek
date: Jun 03, 2005 - 10:15
I am trying pivot cube component with delpi 6. I want to use Tee Chart version 7 pro. Can I use it with Pivot Chart?
answer | answer with quote
Author: Alex Zencovich
date: Jun 03, 2005 - 10:53
You need to have PivotCube VCL sources for that. Otherwise Delphi IDE will not allow you that due version control checks.
answer | answer with quote
My own dimension editor
Author: Josef Zvonicek
date: Jun 03, 2005 - 13:17
In PivotMap is dimension editor and filter for every dimension. How can i create my own filter editor. I can read items of first row dimension by Map.RowCellsCount and Map.RowCells.Caption to my check list box but i don't know how can i deactive (or active again) them in Pivot Map.
Can you help me?
answer | answer with quote
Author: Alex Zencovich
date: Jun 03, 2005 - 13:52
1. You need to use TPivotMap.DImensions[Index].Items to fill dimension editor. Use TMapDimensionItem.State to set item state to disActive,disInvisible,disDisabled
call PivotMap.RefrshData(True) to apply changes
2. If you want show/hide several rows/columns you need to set PivotMap.Rows[Index].Visible to True/False;
call PivotMap.RefrshData(False) to apply changes
answer | answer with quote
Add custom hierarchy group runtime
Author: Nelson
date: May 20, 2005 - 20:35
HI, is it possible to add a hierarchy group at runtime ?
Tks
answer | answer with quote
Author: Nelson
date: May 20, 2004 - 20:45
I mean, without using dimension editor
answer | answer with quote
Author: Alex Zencovich
date: May 21, 2004 - 6:05
It's possible via moving from TPivotMap.DImensions[index].Items[index] to
TPivotMap.DImensions[index].Items[index].Items etc. You may try to check how it work with DImension editor and do the same by own code.
answer | answer with quote
Author: Nelson
date: May 21, 2004 - 21:19
Thanks for your help. It worked fine.
answer | answer with quote
Author: Nelson
date: Jul 21, 2004 - 21:08
Hi Alex,
I've tried to add custom hierarchy group at runtime withou using dimension editor and worked fine, but in the new relese it's not working anymore.
Do you have any idea what is going on ?
Tks
answer | answer with quote
Author: Alex Zencovich
date: Jul 22, 2004 - 4:53
Hi Nelson,
1. Check call to TPivotMap.RefreshData after changes made (the most probable error)
2. Check changed dimension proeprty HierarchyChanged after changes made. It must be True for correct work.
answer | answer with quote
Author: Nelson
date: Jul 22, 2004 - 15:17
Hi Alex,
Tks for the answer but is still not working.
It looks fine when I see on Dimension Editor but it does not reflect the hierarchy on pivot grid.
Tks again
answer | answer with quote
Author: Alex Zencovich
date: Jul 22, 2004 - 15:54
Hi Nelson,
Do you use TPivtoMap.RefreshData(True)? Show me your code please.
answer | answer with quote
Author: Nelson
date: Jul 22, 2004 - 18:24
Hi Alex,
Here is the code:
x:= TMapDimensionItem.Create ;
with x do begin
Name:= 'Holding';
isCustomGroup:= true;
isGroup:= true;
Dimension:= pvm.Dimensions[0];
end;
x.AddItem({dimension item})
pvm.Dimensions[0].Expanded:= true;
pvm.Dimensions[0].HierarchyChanged:= true;
pvm.RefreshData(true);
Tks
Nelson
answer | answer with quote
Author: Alex Zencovich
date: Jul 22, 2004 - 18:52
At least you must add line of code
pvm.DImensions[0].AddItem(x)
before
pvm.Dimensions[0].Expanded:= true;
answer | answer with quote
Author: Nelson
date: Jul 22, 2004 - 20:20
Hi,
Sorry but I forgot to put the line on the example I sent you.
In my code there's a statement: pvm.DImensions[0].AddItem(x)
Tks,
Nelson
answer | answer with quote
Author: Alex Zencovich
date: Jul 22, 2004 - 20:33
Also, you need to fill dimension ietm properties, like ID,Owner,Key,State
answer | answer with quote
Author: Nelson
date: Jul 26, 2004 - 12:56
Hi Alex,
It's me again...
I filled dimension item properties and still not working.
Older version, worked fine !?
Nelson
answer | answer with quote
Author: Alex Zencovich
date: Jul 26, 2004 - 13:29
I will glad to help you, but you do not provide for me code which is not work. Your code(above) has gross errors. I point you how it must be fixed.
The same type of code still working in PivotMap.Loadxxxxx routines where custom hierarchies restored from saved state.
This is advanced techniques and I'm unsure it work for you in earlier versions.
Just little question: Which values you are provide for ID,Owner,Key?
answer | answer with quote
Author: Alex Zencovich
date: Jul 26, 2004 - 13:44
To enhance that type of tasks I will add procedure like TPivotMap.CreateCustomHierarchy group in next build, I think it is a good idea if it is really needed.
answer | answer with quote
Author: Alex Zencovich
date: Jul 26, 2004 - 13:41
Little comment:
This is possible if you create own hierarchy via connecting child items to existed item. In this case you not need to fill most properties
answer | answer with quote
Author: Nelson
date: Jul 26, 2004 - 14:17
Hi Alex,
Here is the code I'm trying to run:
function CreateHier(n: string): TMapDimensionItem;
begin
result:= TMapDimensionItem.Create;
result.Name:= n;
result.isCustomGroup:= true;
result.isGroup:= true;
result.Dimension:= pvm.Dimensions[0];
result.state:= 0;
end;
var
ucb, mao, ios: TMapDimensionItem;
i, n: integer;
begin
n:= pvm.Dimensions[0].Count-1;
ucb:= CreateHier('A');
mao:= CreateHier('B');
ios:= CreateHier('C');
pvm.Dimensions[0].AddItem(ucb);
ucb.AddItem(mao);
ucb.AddItem(ios);
for i:= 0 to n do begin
with pvm.Dimensions[0].Items[0] do
if (Key = '16') or (Key = '20') or (Key = '19') or (Key = '03') then
mao.AddItem(pvm.Dimensions[0].Items[0])
else if (Key = '22') or (Key = '02') then
ios.AddItem(pvm.Dimensions[0].Items[0])
else ucb.AddItem(pvm.Dimensions[0].Items[0]);
pvm.Dimensions[0].DeleteItem(pvm.Dimensions[0].Items[0]);
end;
pvm.Dimensions[0].Expanded:= true;
pvm.Dimensions[0].HierarchyChanged:= true;
pvm.RefreshData(true);
end;
Tks
answer | answer with quote
Author: Alex Zencovich
date: Jul 26, 2004 - 14:59
That code will not work. as I wrote above you need to set ID and Key for unique values for that dimension for each newly created item.
answer | answer with quote
Author: Alex Zencovich
date: Jul 26, 2004 - 15:12
I was wrong. I put your code in demo application and it work . But setting ID and Key values required anyway (for proper calculations/ filtering).
answer | answer with quote
Author: Nelson
date: Jul 26, 2004 - 15:31
Hi Alex,
Tks for your help... I don't know why but it worked when I assigned for ID's values 65558, 65559, 65560,... (I tried with values 1,2,3,...)
answer | answer with quote
Author: Alex Zencovich
date: Jul 26, 2004 - 15:51
May be your dimension has less than 65000 items?
answer | answer with quote
Author: Nelson
date: Jul 26, 2004 - 20:22
Yes, my dimension has less than 65000 items.
answer | answer with quote
Author: Alex Zencovich
date: Jul 26, 2004 - 21:32
So when you assign ID = 65000 this is unique number for dimension. But ID = 1,2,3 usually exists in dimension before your code run
answer | answer with quote
Export to Excel localization problem
Author: Wilbert Veldhuyzen
date: Sep 05, 2005 - 8:44
Hi Alex,
I have a problem while exporting a PivotGrid to Excel on a dutch system (Regional settings). The formatting is mixed up with dots and comma's. A dutch thousand seperator is a dot in stead of a comma in the USA, and a decimal seperator is a comma in stead of a dot. Can you help me to fix this problem. (I can change the regional setting to english before exporting to excel, but I can't ask me users to do that.)
Thanks, Wilbert.
answer | answer with quote
Author: Alex Zencovich
date: Sep 05, 2005 - 10:06
Hi Wilbert,
I unsure if I right understand which problem you found.
I change my XP regional settings to dutch and I have not any problem with export.
Just be sure you have right format string im measure settings.
For example, format string to get value 1234567.890 with defaul money format will
#,0.00
you will get
1.234.567,89
The formatting string rules is independent on language setings, so .(point) is always decimal separator and ,(comma) is thousand separator. But real separator symbols still defined by your regional settings (in your case - back to front)
answer | answer with quote
Licence PivotCube
Author: Some guest
date: Sep 15, 2005 - 13:10
I have 5 PC with zCube.dll (PivotCube 7.02 free) Do I want to buy PivotCube, is it necessary to buy license for each PC?
answer | answer with quote
Author: Alex Zencovich
date: Sep 15, 2005 - 18:04
Please describe little mor detail.
Is that 5 PC client PC or developer PC?
Is this PivotCube VCL or PivotCubeX? What is your target application?
answer | answer with quote
Author: Mauro
date: Sep 22, 2005 - 12:45
Excuse, I speak Spanish (Argentina)
I have 1 developer PC and 4 client PC. I use Pivotcube VCL 7.02
I use PivotCube for CashFlow and business intelligence for a medium company.
Thank you.
answer | answer with quote
Author: Alex Zencovich
date: Sep 22, 2005 - 14:38
You need only 1 license for developer. PivotCube VCL is royalty-free, you not need aditional licenses for zCube.dll
answer | answer with quote
Measure
Author: Peter setier
date: Sep 27, 2005 - 1:17
I'm trying to evaluate the PivotCube can u help me.
How to get the difference between two Column
Column
Month July Aug Var
Row
Sales Office - Sales office 1 99999 99999 xxx
Sales office 2 99999 99999 xxx
using the calculated Measures manager. how to create a formula ?
Thanks.
answer | answer with quote
Author: Alex Zencovich
date: Sep 27, 2005 - 5:13
1. To simple task, you may set measure view (mark with green in measure manager) for difference with prev(next) column.
2. In calc formula it will like
Result = "Summa" - "Summa"[PREV_BY_COL];
answer | answer with quote
Error in installation
Author: peter setier
date: Oct 05, 2005 - 8:30
We have bought pivotcube vcl with source and during installation theirs an error
invalid compiler directive VARPROPSETTER (ZCube_TLB.pas)why ?
answer | answer with quote
Author: Alex Zencovich
date: Oct 05, 2005 - 11:49
Hello,
It is possible for D5/D6 installations of source code. Some files (zCube_TLB.pas and MSXML2_TLB.pas) is autogenerated by Delphi IDE (we use Delphi 7 and Delphi 2005 for development) so that may include some options.
Package have replacemnt for these files for D5/D6 or you may just remove these lines from code.
answer | answer with quote
How can i get an event when the cube reads data?
Author: Michael Bergner (Hans-Joachim Burri)
date: Feb 01, 2006 - 9:16
If i import data to the cube with cube.cubeactive=.t. or cube.CubeBuild i want to have an event to control the loading of the cube. The event OnNextRecord doesn't work. Only the event's OnBeforeMapRefresh and OnMapChange fire! What can i do?
answer | answer with quote
Author: Alex Zencovich
date: Feb 01, 2006 - 10:10
There 2 events which work owhen cube builds
1. OnNextRecord (It work if you can see progress bar in demo app when cube build)
2. OnCustomWrapData
answer | answer with quote
If you meet error Tabsheet1.Explicitxxxxx does not exist in demo app.....
Author: Alex Zencovich
date: Feb 20, 2006 - 5:53
Delete all explicit properties from DFM (I will fix demo package soon, but you may not need to download 17 MB package for that only).
There is bug in Delphi 2006 IDE - once you try to save unit, it will add a lot of "explicitxxxxx" proeprties to DFM, you never will know about that.
answer | answer with quote
PowerBuilder
Author: Guillaume
date: Apr 15, 2006 - 17:47
Hello,
I am just discvering this wonderfull ocx to be used in Sybase PowerBuilder 9 app.
My problem is that I could not load data within PivotCubeX ;
When issuing: CubeActive = True i get: "No Cube Struture Defined"
I went then in the attributes to check connection, dimensions, row, cols params but everything seems correct.
On the other hand, it appear that the properties changed in the attributes window are not restored/retrieved when running the application. (for instance when toolbar are disable in design mode they are still visible when running the app.). Does it mean that I will have to init the properties (and data) with code ?
Kind Regards,
When running the supplied VB demo app, how do I do have the data loaded from the DB (at the moment the only way I found to get data was to load a .cub file).
answer | answer with quote
Author: Alex Zencovich
date: Apr 15, 2006 - 18:35
1. There may be problem with data load if PowerBuilder 9 has non-standard implementing of IPersistStreamInit interface (this interface used to save/load ActiveX control properties).
You may see this error if PCX.CubeDImensions.Count = 0
If you cannot load stored properies, you may initialize them by code, like here http://pivotcube.com/cgi-bin/forum.cgi?add=0&file=0241
2. You need configure ADO conenction (PivotCubeX works via ADO) to database you use.
You may see database connection example in HTML or MS Access demo.
answer | answer with quote
Author: Guillaume
date: Apr 15, 2006 - 19:29
Alex,
Thank a lot for your quick and pertinent answer.
You are right, it seems that PowerBuilder do not store the properties; As soon as the form is close / reopen in the dev. environment the properties are lost
I have tried to initialize them by code on the fly and it works !
I will now have to dive in the doc. to better undertand the properties setup.
Is there some "Save Properties As" method that would export all the properties as there are currently displayed by this great ocx ?
This would help me to have quickly the properties (ready to use) correctly defined.
Kind Regards,
answer | answer with quote
Author: Alex Zencovich
date: Apr 15, 2006 - 21:27
Try to use next methods
PivotCubeX.Save(const stream : IStream,False)
and
PivotCubeX.Load(const stream : IStream,False).
There is methods inherited from IPersistStreamInit interface.
answer | answer with quote
Author: Guillaume
date: Apr 15, 2006 - 21:42
Thanks Alex,
I would be nice but I could not get this methods working since I don't know how to declare the variable: "const stream : IStream".
Also, I don't understand in which var the resulting properties will be stored.
Kind Regards,
Guillaume
As an temporary alternative I edit the frm file produced by vb
answer | answer with quote
Author: Alex Zencovich
date: Apr 16, 2006 - 6:16
Properties stored(loaded from) in stream parameter you should pass to these methods.
IStream is Windows standard interface type.
See here
http://msdn.microsoft.com/library/defau ... stream.asp
http://msdn.microsoft.com/library/defau ... 6eb33f.asp
answer | answer with quote
Sorting a date dimension
Author: Christian
date: Apr 21, 2006 - 15:57
Hello,
When I sort a date dimension (clicking it caption with the mouse) it sorts on it string representation instead of the date value.
Is there any property to change this behaivor?
Thank you
answer | answer with quote
Author: Alex Zencovich
date: Apr 21, 2006 - 18:53
Yes, you need to specify for this dimension default sort order (in PivotCube configuartion or open dimension window and set it in drop-down menu as dmKeySort) - sort by key value not by caption.
After default sort was changed, click in grid will change sorting order from ascending to descending and back but by key value.
answer | answer with quote
Measure Table Dialog - cell button
Author: Fabian Sosa Escalada
date: May 12, 2006 - 14:40
Hi,
On the right side of each data cell in PivotGrid, there is a button with a down arrow. When I click that button, a dialog appears with detail rows.
The meassure related to the referenced cell, allways appears in a column as SUMMA, and also appears as Count, even if the meassure is of type AVERAGE.
Is there a way to control that behavior and make the meassure appers as AVERAGE?
Is there a way to hide that button?
thanks in advance
Fabianse
answer | answer with quote
Author: Alex Zencovich
date: May 12, 2006 - 16:55
Hi,
There is cell editor dialog. Is show information as it keep in cube. It does not keep information as average because average it just summa/count.
But you may edit average value in one from editboxes (at bottom - is show actual cell value like it appear in grid) and it will fluent to all cells used in aggeregation
To disable this feature set PivotGrid.Settings.Specific.AutoEdit to False
answer | answer with quote
Images parameter for SaveToHTML method
Author: Siegmar Gieseler
date: Jun 17, 2006 - 13:27
I am trying to save a cube to HTML.
The HELP file shows the following sintax:
procedure ExportToHTML(FileName: string; Progress: boolean; AutoOpen: boolean);
But Delphi shows:
procedure ExportToHTML(FileName: string; Progress: boolean; AutoOpen: boolean,
const Images: array of TJPEGImage);
What means the "Images" parameter and where do I find the TJPEGImage class ?
answer | answer with quote
Author: Siegmar Gieseler
date: Jun 17, 2006 - 13:31
I have already found the "TJPEGClass" at "Jpeg" unit.
But I am using it this way:
const
Images: array of TJPEGImage = (nil);
begin
PivotGrid.ExportToHTML('CUBO.html', True, True, Images);
end;
answer | answer with quote
Author: Alex Zencovich
date: Jun 17, 2006 - 19:07
array of Images may be used if you need to add some charts (for example) to generated HTML.
answer | answer with quote