Hi Roland,

You mention the cube not being suitable for data input but a pivot grid can be configured to be editable. This can be done using an extender called default_editable_cube.

One rule is that each editable cell must represent the column of a single underlying record. Aggregated cells cannot be made editable.

The primary key of the underlying table must also be present as cube fields. However, they do not need to be placed in the pivot grid as rows or columns. They just need to be defined as cube fields.

Does this work for you?

Hi Anne,

That does sound exactly as what I need. I haven’t worked with these extenders yet but I will definitely be checking this out. I’ll be reporting here how it goes. Thank you! :)

Glad to be able to report that it’s working. Data fields are editable from inside the cube. One thing that doesn’t seem to work is editing row fields even when they are not part of the pk. Is there a reason why that might be the case?

I tried to turn this row field into a second data field but then both data fields get grouped together which is not the desired result. I merely want to add as an extra column. I probably have to change the data model and merge both data fields into one.

Hi Roland,

What exactly do you mean with ‘editing row fields’? The only cube fields that can be edited are the cube fields placed as values, not the ones placed in the rows and columns sections.

Hi Anne,

I put one column that is not part of the pk in the cube as a row field. This made sense because this column contains data that varies only for a part of the pk. I’ve changed the data model in the mean time by moving this column to a different table. It doesn’t have to be editable via the cube anymore so in this case I’ve already found a working solution.

Here’s an illustration of the original cube:

PART  | TOLERANCE |  2XS   XS    S    M    L   XL  2XL  3XL  4XL
----------------------------------------------------------------
Waist |         * |    .    .    .    .    .    .    .    .    .
Neck  |         * |    .    .    .    .    .    .    .    .    .
Length|         * |    .    .    .    .    .    .    .    .    .
Sleeve|         * |    .    .    .    .    .    .    .    .    .

“PART” and “TOLERANCE” are both row fields, the sizes are column fields and the dots are the (editable) values.

It made sense to organize the cube this way because the tolerance only varies per part and not per size. I think it should technically be possible to have an editable row field when it’s not part of the pk.

My original workaround was to add “tolerance” as a pseudo size so it would end up in the cube as a single column after all the sizes and thus also with editable values. Would you recommended this solution in case I encounter the issue again in the future?

Hi Roland,

Are the “PART” rows expanded so the values for "TOLERANCE” are visible?

If not, the pivot will assume the value to be an aggregated total for each "PART”, even if there is only one underlying "TOLERANCE”. This will prevent the cell from being editable.

Yes they were expanded by default. Without this setting the “TOLERANCE” column wasn’t visible unless the user opened them all by hand. Are you saying they should’ve been editable then? Unfortunately I cannot test it right now because I’ve changed the data model.

Yes, they should have been editable at this point, provided there is a single underlying record for each cell.

I get it. It wasn’t editable because any given “TOLERANCE” record existed for every size. Don’t row and column fields normally always occur multiple times in the table behind a cube? I’m wondering how to shape the data model to make this possible.

The logic behind this is that it is not possible to process the update of an aggregated value.

I don't know what the aggregation type was set to, but assuming it was set to average: What does it mean to change the average value of multiple tolerances? Should the increase or reduction be ‘spread out’ over the various records?

This is why the UI prevents updates on totals and on cells representing an aggregation of multiple records.

If the back-end logic is able to process this, the data should be provided pre-aggregated (for instance using a view) so the UI will see it as a single updatable record.

I understand why aggregation makes it impossible to edit a cube field. But you mentioned earlier there can only be a “single underlying record for each cell”. That’s why I was wondering how I could still make a row field (outside the pk but unique per row) editable because I think row fields always have multiple underlying records (one for every column field).

In this example every combination of SIZE and PART will have its own TOLERANCE field in the underlying table. They are always the same for all sizes of every PART but they will be multiple records nonetheless. In my mind they could be editable because they are always identical  but they weren’t.

I currently have a similar cube without the PART column. The editable values (the dots in the illustration) contain “sum” as the summary type. This works because they are unique. Were I to add a row field again I’d have no idea how to make them editable. Input of a value would be allowed to translate into multiple records all with that same value but perhaps current cube logic simply doesn’t allow that. Maybe it’s because of worries that the cube could potentially be rendered unusable when a series with a unique value is tempered with directly on the table. That’s only a guess though but it would be understandable from a design point of view.

Adding a row or column field can only make the cells more ‘specific’.

For instance, when you have a single underlying row in the view for a cell, which represents the combination of a PART and SIZE, adding the TOLERANCE could never cause the cell to suddenly represent multiple underlying rows.

You can actually see the underlying rows by double-clicking a cell.

The pivot grid should remain editable in this case. The only reason for them not to be editable would be that the PART is not expanded, causing the cells to be considered totals even though every part only has a single TOLERANCE.

If this doesn't work as described, this could be a bug. Feel free to create a ticket if so.