Countnig Dimensional Items?

[Matt Dee]

Hi Alex, is there a way to count unique dimensional items and have them show up as a count column?

I've got a flat data file like so:

CustID-1 M Day-1 Amount
CustID-1 M Day-3 Amount
CustID-2 F Day-1 Amount
CustID-2 F Day-2 Amount
CustID-2 F Day-3 Amount

If I add a count column that counts CustID and then group by gender and then CustiID I get the following:

M - CustID1 Count = 2
F - CustID2 Count = 3

What I end up with is a vist count (i.e. on how many days each customer visited and spent money) but not an actual customer count.

Is there a way to get around this?

Cheers,

Matt.

[AlexZencovich]

You should check how many sub cells has each cell (on rows or columns) - starting from TPivotMap.TopRow.Count.

You may create 'dumb' measure for this and fill values via TPivotGrid.OnGetCellValue event. But lowlevel cells will have 0 for sub dimension counts

[Matt Dee]

Cheers for the reply, I meant to post this morning that its been solved:

Create an additional fact field with the same source as the dimension and then create a custom measure that counts individual values in the 'fact' column.

Cheers,

Matt.

[anaves]

I've the same question as Matt. Alex kindly answered me, but I couldn't find the solution. I mean "create an additional fact field with the same dimension I want to count and then create a custom measure..".
Could you send the code for the OnCalcCustomMeasure as example ?
Tks

Best regards

[Matt Dee]

Sure. The following code assumes that you've got a Dimension, say CustomerID, and you select it twice, once to act as a dimension and the second to act as a counter.

The app I'm working with uses stores customer IDs as an integer, I haven't tried putting string values into the grid , so no idea if that will work.

Code: Select all

procedure TOLAPGrid.OnCalcMeasures(Measure, Count: Integer;
  Data: OleVariant; var Value: Double);
var
  i: integer;
  IsCount: boolean;
  aMeasure: TMeasureItem;
  aDataLump: OleVariant; //ignore my naming conventions ;-)
  Counter: integer;
begin
  IsCount := False;

  //...
  //Code to determine if this measure is set up as to count a 'fact'
  //...

  if IsCount then
  begin
    //count unique values
    aDataLump := Data[0];
    Counter := 1;
    for i := 1 to Count - 1 do
    begin
      if Data[i] <> aDataLump then
      begin
        Inc(Counter);
        aDataLump := Data[i];
      end;
    end;
    Value := Counter;
  end
  else
    //can pass through default value, i.e.
    //Value := Data[0];
    //or do whatever
end;


[AlexZencovich]

For string values you should use OnGetCellValue event handler. If will allow to pust string values in grid.

[anaves]

I'm realy ashamed !! Could understand, but not how to do it.
Alex, could you please send the code for count how many Clients do I have in PivotCube Demo ? I mean showing it in the grid as one measure.
I really think this kind of information is very important, PivotCube offers fantastics options, but we have to have simple measures either, like how many Wares do I have ? How many Employeers ? And so on.

Matt, Alex, tks for your attention.

[Guillaumep]

Hello,

Maybe I mis-understood your problem but if you want to count how many client for each cell in grid :

1- create a cube measure :
- AliasName = ClientCount (example)
- FieldName = name of the field containing the Client ID
- CalcType = ctCount

2- once the map is open :
- turn Distinct to true for the map measure

--> the measure now counts the distinct values in the field, i.e. count the number of client.

Now, if you want to count the number of client where sales > 10 (example), you have to add a facttable field.

1- fill this field
- with 0 if sales <= 10 (excluded)
- with the clientID if sales > 10 (included)

2- create a cube measure :
- AliasName = ClientCountWhereSalesMoreThanTen (example)
- FieldName = name of the field containing the Client ID or 0
- CalcType = ctCount

3- turn ExcludeZeros to True (0 will be excluded from count)

4- once the map is open :
- turn Distinct to true for the map measure

--> the measures now count the distinct values in the field, without 0, i.e. the number of client where sales are > 10.

I hope it will help you,

Have a nice day.
Guillaume

[anaves]

Hi Guillaumep.
That's exactly what I'm looking for, a distinct clause. But it didn't work. Here are my pice of code, a buttonClick that opens the analisys.

qryANALISE.Active := False;
If PivotCube1.Active then
PivotCube1.Active := False;

qryANALISE.Active := True;

PivotCube1.Build;
PivotMap1.Measures[2].Distinct := True;
PivotMap1.RefreshData;

Anything wrong ?
Tks for your help.

[Guillaumep]

Try to call "PivotMap.RefreshData(True);"
Instead of "PivotMap.RefreshData;"

because default value is False for RefreshData

[anaves]

YES, distinct works fine, only one count in the grid.
But, how can I see the TOTAL in Total Colum ( botton of the grid ) ? In the others measures are OK.

Tks

[anaves]

Complementing my last question:
When using distinct = False I can see the TOTAL ( sum of Rows Values ). When disctinct = True the TOTAL equals 1.

Best Regards and tks for your attention.

[Guillaumep]

I'm sorry but I think I haven't understood what you expect to see in your report

Can you show a simple example from "Fact Table" to "Grid result", please.

Maybe I could help you to build your cube with these pieces of information.

[anaves]

I'll send you a private message because I have to send scrrenshots, Ok ?
( could I do it in forum ? )
Tks [/img]

[Guillaumep]

I got your private message. As far as I understand, "Entregador" means "Drivers" and is different from "Clientes"... Right ?

1. Your measure "Qtd Clientes" counts the number of client. Value 1 on "Column Totals" means all facts are related to the same client. If it's true, the measure is OK.

2. If you want 14 on Totals, you should create a measure (with ctCount and distinct) on "Entregador" field. It will equals 1 for each "Entregador" (naturally ) and 14 on Totals (depending on filters).

Conclusion :

You tell me "I'd like to know how many drivers..." (and not "how many clients..."), it means the measure should be created on driver's field instead of client's field.

I hope I understood you and you can understand me

Best regards,
Guillaume