Hi, I have seen this it was done manually but I am wondering how this can not be done automatically. Can I share the real Chart that I am trying thorough manual process? Its a challenge to me. how I can share the real chart with you if you would like to see? Please let me know Sir

You can add shapes including text boxes to a sheet but the difficulty is in positioning them correctly. For example

# add device text
# these need to be adjusted to 
# position correctly below category labels
$row2    += 23;
$col2     = 4;
my $width = 4;

for my $device (@device){
  my $text = $book->add_shape( type => 'rect', text => $device, line=>
+1 );
  $worksheet2->insert_shape( $row2, $col2, $text );
  $col2 += $width;
}
[download]

poj

Thanks for your responses Sir! Please check below Code snippet and kindly let me know if I need to replace some block of codes against the exiting code. This is very much required to isolate Devices keys and browsers keys in the chart and the requirement was for to keep the Devices keys like Computer - Mobile - Tablet below of the browsers names in the Chart like below browser1 Browser2 Browser3 Browser1 browser2 Browser3 Browser1 Browser2 Browser3 Computer Mobile Tablet Device/Browser

                        $strExcelFilename = "D:/PERL/" . "DEVICE-BROWS
+ER-REPORT" . ".xlsx";
                        
             $workbook1 = Excel::Writer::XLSX->new($strExcelFilename);
        my $format_bold = $workbook1->add_format( bold => 1, top=>1, b
+ottom=>1 ); 
        $worksheet1 = $workbook1->add_worksheet('Data');
        $row1= 1;
        
        #$chart = $workbook1->add_chart( type => 'column', embedded =>
+ 1 );
            
            foreach $worksheet1 ($workbook1->sheets()) {
         if( $worksheet1->get_name() eq $SUCCESS_COUNT_SHEET_NAME ) {
              $format = $workbook1->add_format(%header);
           $worksheet1->write(0,0, "User Id", $format);
           $worksheet1->write(0,1, "User Display Name", $format);
           $worksheet1->write(0,2, "User Email Id", $format);
           $worksheet1->write(0,3, "Device", $format);
           $worksheet1->write(0,4, "Browser", $format);
           $worksheet1->write(0,5,"Application Name", $format);
           $worksheet1->write(0,6,"TimeStamp", $format);
               }
      }
      for ($k = 0; $k <= $#responseid; $k++)  {
    
         
        $worksheet1->write($row1,0, $responseid[$k]);
        $worksheet1->write($row1,1, $responsdisp[$k]);
        $worksheet1->write($row1,2, $responsalter[$k]);
        $worksheet1->write($row1,3, $responseclientdevice[$k]);
        $worksheet1->write($row1,4, $responseclientbrowser[$k]);
        $worksheet1->write($row1,5, $responseapps5[$k]);
        $worksheet1->write($row1,6, $estdate[$k]);
        $app = $responseapps5[$k];
        $device = $responseclientdevice[$k];
        $browser = $responseclientbrowser[$k];
        $userid = $responseid[$k];
        
        ++$pivot{$app}{$device}{$browser};
          ++$pivot{$app}{'TOTAL'}{$browser};
          ++$pivot{$app}{'TOTAL'}{'TOTAL'};
          ++$pivot{$app}{$device}{'TOTAL'};
          ++$pivot{'TOTAL'}{$device}{$browser};
        #++$pivot{$app}{$browser};
           ++$categ{'device'}{$device};
           ++$categ{'browser'}{$browser}; 
           ++$app{$app};
    
    $row1++;
    #print Dumper $app;
    
    }
    
    $worksheet2 = $workbook1->add_worksheet('Summary');
    $worksheet2->set_column(0,0,20);
my @device  = sort keys %{$categ{'device'}};
my @browser = sort keys %{$categ{'browser'}};
my @applist = sort keys %app;

my $row2 = 0;
$worksheet2->write($row2,1,\@applist);
for my $device (@device){
   my $col2 = 0;
   $worksheet2->write(++$row2,$col2,$device,$format_bold);
   for my $app (@applist){
     $worksheet2->write($row2,++$col2,$pivot{$app}{$device}{'TOTAL'},$
+format_bold);  
   }
   
   for my $bro (@browser){
     $col2 = 0;
     $worksheet2->write(++$row2,$col2,substr($device,0,3).'-'.$bro);  
+ 
     for my $app (@applist){
       $worksheet2->write($row2,++$col2,$pivot{$app}{$device}{$bro}); 
+ 
     }     
   }  
   ++$row2;
}

# GRAND TOTAL
my $col2 = 0;
$worksheet2->write(++$row2,$col2,'GRAND TOTAL',$format_bold);
for my $app (@applist){
  $worksheet2->write($row2,++$col2,$pivot{$app}{'TOTAL'}{'TOTAL'},$for
+mat_bold);  
}



my $chart = $workbook1->add_chart( type => 'column', embedded => 1  );
$chart->set_size( width => 1000, height => 500 );
#my $col = 'B';
$col2 = 0;

for my $app (sort keys %app){
   ++$col2;
  $chart->add_series(
    name       => $app,    
    categories => ['Summary',1,$row2,0,0],
    values     => ['Summary',1,$row2,$col2,$col2]
  );
}

$chart->set_title ( name => 'Results of sample analysis' );
$chart->set_x_axis( name => 'Device/Browser' );
$chart->set_y_axis( name => 'User Count'  );

# add chart

$worksheet2->insert_chart( 22, 1 , $chart);
    $workbook1->close();
[download]

OK, I've created a PivotChart manually in Excel and I now understand what you are trying to achieve. I don't believe it is possible to create that special type of chart with Excel::Writer::XLSX.

If you are using Windows then it can be done with Win32::OLE. For example

#!/usr/bin/perl
use strict;
use Win32::OLE 'in';
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # Die on Errors.
printf "Win32:OLE Version %s\n",$Win32::OLE::VERSION;

# Create new book
my $dir  = 'c:\\temp\\';
my $file = 'chart.xlsx'; 

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
   || Win32::OLE->new('Excel.Application', 'Quit');  
$Excel->{DisplayAlerts} = 0;  
$Excel->{Visible} = 1;  
   
my $Book = $Excel->Workbooks->Add(); 
printf "Created $file with %s sheets\n",$Book->sheets->Count;; 
my $Sheet1 = $Book->Worksheets('Sheet1');
my $Sheet2 = $Book->Worksheets('Sheet2');

setup($Sheet1, 1500); #test data

# create PivotTable
my $range1 = $Sheet1->UsedRange->{'Address'};
print "Data Range  = $range1\n";

my $cache = $Book->{'PivotCaches'}->create(
  { 
    SourceType => xlDatabase,
    SourceData => $range1,
    Version    => xlPivotTableVersion14,
  } );
  
my $pvt = $cache->createPivotTable(
  {
    TableDestination => "Sheet2!R1C1",
    TableName        => "PivotTable1",
    DefaultVersion   => xlPivotTableVersion14,
  } );

$pvt->addDataField( 
   $pvt->PivotFields('User Id'),
   "Count of User ID",
   xlCount
);
$pvt->PivotFields("Application Name")->{Orientation} = xlColumnField;
$pvt->PivotFields("Device")->{Orientation} = xlRowField;
$pvt->PivotFields("Browser")->{Orientation} = xlRowField;

# create PivotChart
$Sheet2->{'Shapes'}->AddChart();
my $shape = $Sheet2->Shapes('Chart 1');
$shape->ScaleWidth(2,0);
$shape->ScaleHeight(2,0);

my $chart = $shape->Chart;
$chart->{HasTitle} = 1; 
$chart->{ChartTitle}->{Text} = "PivotChart Created ".scalar localtime;
$chart->{ChartType} = xlColumnClustered;

my $range2 = $Sheet2->UsedRange; 
printf "Chart Range = %s\n",$range2->{'Address'};
$chart->SetSourceData({ Source => $range2 });

# save new workbook
$Book->{'ShowPivotTableFieldList'} = 0;
$Book->SaveAs({'Filename' => $dir.$file});
print "Chart is on Sheet2\n";
$Excel->Quit;

#system ( "excel $dir$file" );

# random test data generator
sub setup {
  my ($sheet,$max) = @_;
  my @dev = ('Computer','Mobile','Tablet');
  my @bro = ('IE11','Chrome','Firefox','Safari');
  my @app = ('AppA','AppB','AppC','AppD','AppE');  
  my $col = 1;
  for ('User Id','User Display Name','User Email Id','Device',
              'Browser','Application Name','Timestamp'){ 
    $sheet->Cells(1,$col)->{'Value'} = $_;
    ++$col;
  }
  
  my $row = 2;
  for (1..$max){
    $sheet->Cells($row,1)->{'Value'} = "User $_";
    $sheet->Cells($row,4)->{'Value'} = $dev[rand(@dev)];
    $sheet->Cells($row,5)->{'Value'} = $bro[rand(@bro)];
    $sheet->Cells($row,6)->{'Value'} = $app[rand(@app)];
    ++$row;
  } 
}
[download]

poj

PS: Many thanks to bmann for this post which solved the problem I got stuck on