I can't help very much because I don't understand what you want. I haven't tried running the code, but you imply that it isn't giving you what you want and I don't understand what it is you DO want. I have spent some time looking at the data given at the top and the desired output at the end and I haven't a clue how the one is supposed to map to the other. Some of the questions I have are:

Are users other than root supposed to be ignored?

If not, where did the data go to?

If so, where did the data come from?

Where did "Aantal" come from?

Your answer may be to use data tables instead of pivot tables. This is a little known technique that goes back to Lotus 1a for DOS and possibly further. If you are doing anything like what I think, my pseudocode would be as follows:

Import the CSV
Extract the unique users and dates using the database functions
Calculate the lowest and highest dates
Paste the users to the destination (leftmost output) column
Assign all dates from lowest to highest to the destination (uppermost 
+output) row
Enter the DSUM formula in the top left output cell
Create a data table including an extra row and column
Label the extra row & column "Total"
[download]

Now, there are all sorts of powerful tricks you can perform using named ranges to deal with changing data, but I would advise you to get a simple version working first.

Regards,

John Davies

John,

Using the win32::ole (on MS) I'd like to create a pivottable (which succeeds btw). And the issue here is that I'm unable to have the perl-created pivottable sort the days in descending order.

So instead of 20,21,22,...,29,30,31 I'd want the pivottable to show the days in reverse order 31,30,29,....,21,20

These are su actions performed on systems. I'd like to show the number of occurences per action/combination per day.

- never ignore root

- "Sum of Aantal" = "Sum of Count" (failed translation...)

cheers, bjhs

I'm still far from sure what you want. I didn't ask about root, but users OTHER THAN root. Even assuming counts rather than sums, I can't see how your numbers come from your data. I don't know how close my code comes to what you want, but I'd be amazed if it didn't need you to do some work.

I've used a data table as I suggested as these are far more customisable than pivot tables. I haven't tidied up my code, so I'm sure that there are lots of improvements that I could make. But I want to watch TV, so you can have it now or you can have it right next month. :-)

use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{EnableEvents}   = 0;
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;
my $nSheets = $wb->Sheets->Count; #I want 2 sheets - no more, no less.
+ No matter what $luser's default is.
if ($nSheets < 2) {
    for ($nSheets+1..2) {
        $wb->Sheets->Add({After=>$wb->Sheets($nSheets)});
    }
}
if ($nSheets > 2) {
    for (3 .. $nSheets) {
        $wb->Sheets(3)->Delete;
    }
}
my $shtData = $wb->Sheets(1);
my $shtTbl  = $wb->Sheets(2);
$shtData->{Name} = "Data";
$shtTbl ->{Name} = "Table";

$shtData->Cells(1, 1)->{Value} = "Day";
$shtData->Cells(1, 2)->{Value} = "User";
$shtData->Cells(1, 3)->{Value} = "Qty"; #Perhaps, but I have no idea
$shtData->Cells(2, 1)->{Value} = "-";
$shtData->Cells(2, 1)->{HorizontalAlignment} = 5; #xlFill
$shtData->Range("A2")   ->Copy($shtData->Range("B2:C2"));
$shtData->Range("A1:B1")->Copy($shtData->Range("A4"));
$shtData->Range("A1:B1")->Copy($shtData->Range("A7"));

$wb->Names->Add({Name=>'zTopData'      , RefersTo => '=Data!$A$1'});
$wb->Names->Add({Name=>'zEndData'      , RefersTo => '=Data!$C$2'});
$wb->Names->Add({Name=>'zData'         , RefersTo => '=OFFSET(zTopData
+,0,0,ROW(zEndData)-ROW(zTopData),COLUMN(zEndData)-COLUMN(zTopData)+1)
+'});
$wb->Names->Add({Name=>'zTopCriterion' , RefersTo => '=Data!$A$4'});
$wb->Names->Add({Name=>'zEndCriterion' , RefersTo => '=Data!$B$5'});
$wb->Names->Add({Name=>'zCriterion'    , RefersTo => '=OFFSET(zTopCrit
+erion,0,0,ROW(zEndCriterion)-ROW(zTopCriterion)+1,COLUMN(zEndCriterio
+n)-COLUMN(zTopCriterion)+1)'});
$wb->Names->Add({Name=>'zDayData'      , RefersTo => '=OFFSET(zTopData
+,0,0,ROW(zEndData)-ROW(zTopData),1)'});
$wb->Names->Add({Name=>'zUserData'     , RefersTo => '=OFFSET(zTopData
+,0,1,ROW(zEndData)-ROW(zTopData),1)'});
$wb->Names->Add({Name=>'zDayCriterion' , RefersTo => '=Data!$A$4:$A$5'
+});
$wb->Names->Add({Name=>'zUserCriterion', RefersTo => '=Data!$B$4:$B$5'
+});
$wb->Names->Add({Name=>'zDayOutput'    , RefersTo => '=Data!$A$7'});
$wb->Names->Add({Name=>'zUserOutput'   , RefersTo => '=Data!$B$7'});

my $nRows = 98;
for (1 .. $nRows) { #Insert some random data
    $shtData->Range('zEndData')->EntireRow->Insert;
    $shtData->Cells($_ + 1, 1)->{Value} = int(rand(11) + 21); #Dates f
+rom 21 to 31
    $shtData->Cells($_ + 1, 2)->{Value} = int(rand(4)  +  1); #4 users
    $shtData->Cells($_ + 1, 3)->{Value} = int(rand(32) +  1); #Somethi
+ng from 1 to 31
}

$shtData->Range('zDayData')->AdvancedFilter({
    Action        => 2,
    CriteriaRange => $shtData->Range('zDayCriterion'), 
    CopyToRange   => $shtData->Range('zDayOutput'), 
    Unique        => 1});

$shtData->Range('zUserData')->AdvancedFilter({
    Action        => 2,
    CriteriaRange => $shtData->Range('zUserCriterion'), 
    CopyToRange   => $shtData->Range('zUserOutput'), 
    Unique        => 1});

my $nLastRowData = $shtData->UsedRange->Rows->{Count};
$shtData->Range($shtData->Range('zUserOutput')->{Address} . ":B" . $nL
+astRowData)->Copy($shtTbl->Range('A1'));
my $nLastRowTbl = $shtTbl->UsedRange->Rows->{Count};
$shtTbl->Range("A2:A" . $nLastRowTbl)->Sort({
    Key1        => $shtTbl->Range("A2"), 
    Order1      => 1, 
    Header      => 0,
    Orientation => 1,
    DataOption1 => 0});

my $rngDays = $shtData->Range($shtData->Range('zDayOutput')->{Address}
+ . ":A" . $nLastRowData);
my $nMin = $xl->Min($rngDays);
my $nMax = $xl->Max($rngDays);
my $col = 2;
for (my $day = $nMax; $day >= $nMin; $day--) {
    $shtTbl->Cells(1, $col++)->{Value} = $day;
}

$shtTbl->Range("A1:A3")->EntireRow->Insert;
$shtData->Range('zCriterion')->Copy($shtTbl->Range("A1"));
$shtTbl->Range("A4")->{Formula} = "=DCOUNT(zData,Data!C1,Table!A1:B2)"
+;
my $lastrow = $shtTbl->UsedRange->Rows   ->{Count};
my $lastcol = $shtTbl->UsedRange->Columns->{Count};
my $lastcell = $shtTbl->Cells($lastrow+1, $lastcol+1)->{Address};
$shtTbl->Range("A4:" . $lastcell)->Table({
    RowInput    => $shtTbl->Range("A2"), 
    ColumnInput => $shtTbl->Range("B2")});
[download]

Regards,

John Davies