Re: Multidimensional array and conditional

Skrol29 I am trying to do the same in an Excel document with a multi-dimensional array (1 level) and get the top level working no issue but am challenged by the next level.  Any thoughts?  My Code Below:

DATA: (JSON THAT I CONVERT TO A TRADITIONAL ARRAY)
{"success":"true","companies":[
{"name":"Verizon Communications Inc.","id":107212169,"
impacts":[{"solution_impact":"5586382.9223007"},{"solution_impact":"98763324.327"}
]},

PHP CODE:
// Load the template
$TBS->LoadTemplate($template);

// Merge data

$TBS->MergeBlock('blk1',$data1);
$TBS->MergeBlock('a,b', $data1);
$TBS->MergeBlock('s', $data1);

// specific merges depending to the document
if ($template_ext=='xlsx') {

    // merge cells (exending columns)
    $TBS->MergeBlock('cell1,cell2', $data1);
//    $TBS->MergeBlock('b2', $data1);
   
    // change the current sheet
    $TBS->PlugIn(OPENTBS_SELECT_SHEET, 1);
   
    // Display a sheet
    $TBS->PlugIn(OPENTBS_DISPLAY_SHEETS, 'Results');
   
} elseif ($template_ext=='ods') {

    // no need to change the current sheet, they are all stored in the same XML subfile.
    // merge data in Sheet 2
    $TBS->MergeBlock('cell1,cell2', 'num', 3);
//    $TBS->MergeBlock('b2', $data);
   
   
    // Delete a sheet
    $TBS->PlugIn(OPENTBS_DELETE_SHEETS, 'Delete me');
   
    // Display a sheet
    $TBS->PlugIn(OPENTBS_DISPLAY_SHEETS, 'Display me');
   
} elseif ($template_ext=='docx') {

                           
    // delete comments
    $TBS->PlugIn(OPENTBS_DELETE_COMMENTS);

} elseif ($template_ext=='odt') {

    // delete comments
    $TBS->PlugIn(OPENTBS_DELETE_COMMENTS);
   
}

// Define the name of the output file
$file_name = str_replace('.','_'.date('Y-m-d').'.',$template);

// Output as a download file (some automatic fields are merged here)
if ($debug==3) { // debug mode 3
    $TBS->Plugin(OPENTBS_DEBUG_XML_SHOW);
} elseif ($suffix==='') {
    // download
    $TBS->Show(OPENTBS_DOWNLOAD, $file_name);
} else {
    // save as file
    $file_name = str_replace('.','_'.$suffix.'.',$file_name);
    $TBS->Show(OPENTBS_FILE+TBS_EXIT, $file_name);
}

TEMPLATE:
Company Name          Revenue (M)                  Solution 1                          Solution 2      Solution 3      Solution 4      Solution 5      Solution 6
[a.name;block=row] [a.revenue;ope=xlsxNum] [a_sub1.solution_impact[0]]    

As you can see above I would like the information from the impacts sub array to print across to a fixed number of up to 30 solutions.               

I was actually able to get the above partially working using the examples above thank you!

Here is where I am stuck.  I have a multi-dimensional array 'companies' at the top level (works fine) and impacts at the sub level.  I used the block definition for sub1 like you have above and was able to get the first value in the impacts array to function properly but cannot figure out how to get the remaining (variable length) items to show.  The current test array has two values the first one works by placing [a_sub1.solution_impact;ope=xlsxNum] in the desired cell.

I would also like to present the field from that array (solution_name) as the header for each column.  Here is what I have tried:

// Load the template
$TBS->LoadTemplate($template);

// Merge data
$TBS->MergeBlock('a,b', $data1);

// Define the name of the output file
$file_name = str_replace('.','_'.date('Y-m-d').'.',$template);

// Output as a download file (some automatic fields are merged here)
if ($debug==3) { // debug mode 3
    $TBS->Plugin(OPENTBS_DEBUG_XML_SHOW);
} elseif ($suffix==='') {
    // download
    $TBS->Show(OPENTBS_DOWNLOAD, $file_name);
} else {
    // save as file
    $file_name = str_replace('.','_'.$suffix.'.',$file_name);
    $TBS->Show(OPENTBS_FILE+TBS_EXIT, $file_name);
}


COLUMN A ROW 1 Company Name
COLUMN A ROW 2[a;block=begin;comm=text:p;sub1=impacts][a.name;block=row]
COLUMN A ROW 3[a;block=end;comm=text:p]

COLUMN B ROW 1[b0.impacts.solution_name]  // DOESNT WORK
COLUMN B ROW 2[a_sub1.solution_impact;ope=xlsxNum] // WORKS

COLUMN B ROW 1[b1.impacts.solution_name] // DOESNT WORK
COLUMN B ROW 2?????

Thank you in advance!

Hi,

You've forgotten to define the block bound of block "a_sub1".

Replace : With :
By the way: in the TBS field [a.name;block=row], the parameter "block=row" is ignored because the block has been defined previously by [a;block=begin;...] and [a;block=end]

Perfect THANK YOU!!!! that worked for the block.  Is there also a way to put the headers in the top row i.e.impacts->solution_name into Row 1 only?

You can try something like this:

|

Thank you I was able to get the names to show but I think the format I am trying may require a different approach. 

Because I am trying to put the names in a single header with the block content below.  When I tried your approach I had to put the block start in the header which them looped the header as well.  So my results looked like this:

Company          Solution A    Solution B
Verizon             xxx.xx        xxxx.xx

Company          Solution A    Solution B
General Electics  xxx.xx        xxxx.xx

etc.

I am looking to do
Company          Solution A    Solution B
Verizon             xxx.xx        xxxx.xx
General Electics  xxx.xx        xxxx.xx
...

I think this could be done as a separate block in the header and a separate block for the content but am struggling with getting that to work since they come from the same source.

Are you data correctly ordered ?
In the design you're looking to do, the first solution is A for each company, the second solution is B for each company, ... and so on...
Is is the same in your record source $data1 ?

If it's so ,then the solution is easy: the header row can be done as separate block.
If it's not, then TBS is not able to perform a pivot table for you. You have to prepare your data first.

Here is an example of my data (my $data1 variable):



As you can see its pretty standard array