Data arrangement in Data Page and export to Excel

[Patrick]

Hi,

I have trouble exporting data. I tried around with the various spreadsheet functions, but have not found a solution. I want to display and ultimately export a parameter in Excel for further processing. The parameter is based on three index domains:

The standard output in data page looks like this:

The index "tr" is still in a filter. I shifted around to get more or less the view I want:

I would like to adjust the view, so that

1)Entries for "tr" are repeated in each line.

2)Zeros ("empty / default entries") are displayed.

Next, I want to export the data in this shape to Excel.

I tried "Spreadsheet::AssignTable(temp,items_id_bin_x0_full(la::tr,z,wv::w),"D2:D35","A2:C35","D1:D1","temp_items",0,1,1);"

There are 34 data entries. I receive an error: "spreadsheet::AssignTable(temp.xlsx, wp::items_id_bin_x0(la::tr,wp::z,wv::w), D2:D35, A2:C35, D1:D1, temp_items, 0, 1, 1) failed: width of the row array (3) + height of column array (1) does not match the dimension (3) of the identifier."

How can this be done?

Thanks!

Patrick

[Patrick]

Found the solution, sometimes it helpes to just write down the question in detail :)

Spreadsheet::AssignTable(temp,items_id_bin_x0(z,wv::w,la::tr),"D2:D35","A2:C35","D1:D1","items",1,0,3);

But still, one thing is missing: the display of zero values.

If I change row_mode to "1", i receive an error saying "spreadsheet::AssignTable(temp.xlsx, wp::items_id_bin_x0(wp::z,wv::w,la::tr), D2:D35, A2:C35, D1:D1, items, 1, 1, 3) failed: Dense row retrieval is only allowed if the row is represented by a single element (not a tuple)."

Is there a solution for that?

[Mohan]

Hello Patrick, 

If you were unable to solve the last question you posted, here is a solution. 

You parameter is a three dimensional parameter, so there is no way to write out the dense (0s and empty rows included) table to Excel. This is a design choice as the identifiers could go on to have very huge dimensions and writing dense data into Excel might result in undesired results. 

You can do what you want by writing a loop and writing it value by value. 

See procedure SpreadSheetFuncs in the attached project. I loop through the three indices, write the first index in column A, second index in column B, third index in column C and the data value in column D.