in reply to Re^12: Unable to create a Pivot chart with visiable Columns
in thread Unable to create a Pivot chart with visiable Columns

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();

Replies are listed 'Best First'.
Re^14: Unable to create a Pivot chart with visiable Columns
by poj (Abbot) on Sep 12, 2018 at 15:33 UTC

    Just add the code before the $workbook->close. Adjust the row, col, width values to suit your data.

    $row2 = 44; $col2 = 4; my $width = 4; for my $device (@device){ my $text = $workbook1->add_shape( type => 'rect', text => $device, l +ine=>1 ); $worksheet2->insert_shape( $row2, $col2, $text ); $col2 += $width; } $workbook1->close();
    poj

      Thanks a lot Sir! This is working! but I have one more question how I can exclude IEE7 Browser ( Specific ) from the list? I need to filter the same.

        Filter the data in this loop

        for ($k = 0; $k <= $#responseid; $k++) { next if ($responseclientbrowser[$k] =~ /IEE7/i);
        poj