This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

A client has a custom report that contains Sales related data for example.
The columns give them values for Actuals, Budget, Variance and another column for Commentary
The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
This is where things start going South.

In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
The text per the formula bar would then be sent back to the cube and everyone could see the update.

In PAfE, attempting the same sequence leaves you with RECALC_0_0_
Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?

Hiyo George, my first question would be what is the current value on this setting?
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-refresh-data-writeback

for the use case you describe i'd expect it to probably to be worksheet (or constrained calc) for something to feel 'good' in the Perspectives compare.

This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

A client has a custom report that contains Sales related data for example.
The columns give them values for Actuals, Budget, Variance and another column for Commentary
The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
This is where things start going South.

In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
The text per the formula bar would then be sent back to the cube and everyone could see the update.

In PAfE, attempting the same sequence leaves you with RECALC_0_0_
Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?

Hi Ted,

Thanks for the reply. I have tried None, Worksheet and Workbook.

Data entered into the DBRW is fine, it is sent to TM1 and is accurate in the cube and refreshed in Excel.

The issue is more around have a chunk of text in a string field i.e. some kind of narrative, which needs to be updated.

Essentially we need to retrieve the text in the cell, be able to add or amend this and then write it back without copying it, pasting it elsewhere, amending then pasting as text in the original cell.
In Perspectives, pressing F9 with the formula active would evaluate the DBRW and return the text. Now we end up with the RECALC message.

I also tried setting "Refresh data on Excel recalculation Keys (F9, Shift F9) - Restart Excel after change", restarting Excel, logging in etc. but no luck.

Hope that this is explaining the issue a bit better.

Hiyo George, my first question would be what is the current value on this setting?
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-refresh-data-writeback

for the use case you describe i'd expect it to probably to be worksheet (or constrained calc) for something to feel 'good' in the Perspectives compare.

This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

A client has a custom report that contains Sales related data for example.
The columns give them values for Actuals, Budget, Variance and another column for Commentary
The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
This is where things start going South.

In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
The text per the formula bar would then be sent back to the cube and everyone could see the update.

In PAfE, attempting the same sequence leaves you with RECALC_0_0_
Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?

I did catch that part of the description, but i know that PA for Excel improved handling of F9 while in edit-mode in late '22, so I figured that part was at least somewhat covered, perhaps there is a gap in there still.

In the most basic incantation, hitting F9 on the formula or a selected argument in the formula, should reveal the computed value now (assuming no upstream invalidation/pending work in the worksheet construct). 

Are you not seeing that? What version¿ that feature was done in the low SC80's.

------------------------------
Ted Phillips
IBM

Original Message:
Sent: Fri January 26, 2024 02:32 PM
From: George Tonkin
Subject: Effective updating of String data via PAfE Custom Report

Hi Ted,

Thanks for the reply. I have tried None, Worksheet and Workbook.

Data entered into the DBRW is fine, it is sent to TM1 and is accurate in the cube and refreshed in Excel.

The issue is more around have a chunk of text in a string field i.e. some kind of narrative, which needs to be updated.

Essentially we need to retrieve the text in the cell, be able to add or amend this and then write it back without copying it, pasting it elsewhere, amending then pasting as text in the original cell.
In Perspectives, pressing F9 with the formula active would evaluate the DBRW and return the text. Now we end up with the RECALC message.

I also tried setting "Refresh data on Excel recalculation Keys (F9, Shift F9) - Restart Excel after change", restarting Excel, logging in etc. but no luck.

Hope that this is explaining the issue a bit better.

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg

Original Message:
Sent: Fri January 26, 2024 01:11 PM
From: Ted Phillips
Subject: Effective updating of String data via PAfE Custom Report

Hiyo George, my first question would be what is the current value on this setting?
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-refresh-data-writeback

for the use case you describe i'd expect it to probably to be worksheet (or constrained calc) for something to feel 'good' in the Perspectives compare.

------------------------------
Ted Phillips
IBM

Original Message:
Sent: Fri January 26, 2024 11:10 AM
From: George Tonkin
Subject: Effective updating of String data via PAfE Custom Report

This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

A client has a custom report that contains Sales related data for example.
The columns give them values for Actuals, Budget, Variance and another column for Commentary
The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
This is where things start going South.

In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
The text per the formula bar would then be sent back to the cube and everyone could see the update.

In PAfE, attempting the same sequence leaves you with RECALC_0_0_
Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
------------------------------

HI Ted,

PAfE is version 2.0.92.7

In the above I am in Automatic calculation mode in Excel. I make some updates then F9 in the formula. RECALC_0_0 is returned.
I then go to Manual calculation but F9 behaves the same in the formula bar. Was expecting either the old value or Peggy.

Custom Report settings per below:

Any other tips or ideas?

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg

Original Message:
Sent: Fri January 26, 2024 03:40 PM
From: Ted Phillips
Subject: Effective updating of String data via PAfE Custom Report

I did catch that part of the description, but i know that PA for Excel improved handling of F9 while in edit-mode in late '22, so I figured that part was at least somewhat covered, perhaps there is a gap in there still.

In the most basic incantation, hitting F9 on the formula or a selected argument in the formula, should reveal the computed value now (assuming no upstream invalidation/pending work in the worksheet construct). 

Are you not seeing that? What version¿ that feature was done in the low SC80's.

------------------------------
Ted Phillips
IBM

Original Message:
Sent: Fri January 26, 2024 02:32 PM
From: George Tonkin
Subject: Effective updating of String data via PAfE Custom Report

Hi Ted,

Thanks for the reply. I have tried None, Worksheet and Workbook.

Data entered into the DBRW is fine, it is sent to TM1 and is accurate in the cube and refreshed in Excel.

The issue is more around have a chunk of text in a string field i.e. some kind of narrative, which needs to be updated.

Essentially we need to retrieve the text in the cell, be able to add or amend this and then write it back without copying it, pasting it elsewhere, amending then pasting as text in the original cell.
In Perspectives, pressing F9 with the formula active would evaluate the DBRW and return the text. Now we end up with the RECALC message.

I also tried setting "Refresh data on Excel recalculation Keys (F9, Shift F9) - Restart Excel after change", restarting Excel, logging in etc. but no luck.

Hope that this is explaining the issue a bit better.

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg

Original Message:
Sent: Fri January 26, 2024 01:11 PM
From: Ted Phillips
Subject: Effective updating of String data via PAfE Custom Report

Hiyo George, my first question would be what is the current value on this setting?
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-refresh-data-writeback

for the use case you describe i'd expect it to probably to be worksheet (or constrained calc) for something to feel 'good' in the Perspectives compare.

------------------------------
Ted Phillips
IBM

Original Message:
Sent: Fri January 26, 2024 11:10 AM
From: George Tonkin
Subject: Effective updating of String data via PAfE Custom Report

This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

A client has a custom report that contains Sales related data for example.
The columns give them values for Actuals, Budget, Variance and another column for Commentary
The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
This is where things start going South.

In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
The text per the formula bar would then be sent back to the cube and everyone could see the update.

In PAfE, attempting the same sequence leaves you with RECALC_0_0_
Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
------------------------------

Hi George,

Have you tried replacing the DBRW formula with a DBR formula? Does this make any difference?

Kind regards,

Luc

HI Ted,

PAfE is version 2.0.92.7

In the above I am in Automatic calculation mode in Excel. I make some updates then F9 in the formula. RECALC_0_0 is returned.
I then go to Manual calculation but F9 behaves the same in the formula bar. Was expecting either the old value or Peggy.

Custom Report settings per below:

Any other tips or ideas?

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg

Original Message:
Sent: Fri January 26, 2024 03:40 PM
From: Ted Phillips
Subject: Effective updating of String data via PAfE Custom Report

I did catch that part of the description, but i know that PA for Excel improved handling of F9 while in edit-mode in late '22, so I figured that part was at least somewhat covered, perhaps there is a gap in there still.

In the most basic incantation, hitting F9 on the formula or a selected argument in the formula, should reveal the computed value now (assuming no upstream invalidation/pending work in the worksheet construct). 

Are you not seeing that? What version¿ that feature was done in the low SC80's.

------------------------------
Ted Phillips
IBM

Original Message:
Sent: Fri January 26, 2024 02:32 PM
From: George Tonkin
Subject: Effective updating of String data via PAfE Custom Report

Hi Ted,

Thanks for the reply. I have tried None, Worksheet and Workbook.

Data entered into the DBRW is fine, it is sent to TM1 and is accurate in the cube and refreshed in Excel.

The issue is more around have a chunk of text in a string field i.e. some kind of narrative, which needs to be updated.

Essentially we need to retrieve the text in the cell, be able to add or amend this and then write it back without copying it, pasting it elsewhere, amending then pasting as text in the original cell.
In Perspectives, pressing F9 with the formula active would evaluate the DBRW and return the text. Now we end up with the RECALC message.

I also tried setting "Refresh data on Excel recalculation Keys (F9, Shift F9) - Restart Excel after change", restarting Excel, logging in etc. but no luck.

Hope that this is explaining the issue a bit better.

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg

Original Message:
Sent: Fri January 26, 2024 01:11 PM
From: Ted Phillips
Subject: Effective updating of String data via PAfE Custom Report

Hiyo George, my first question would be what is the current value on this setting?
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-refresh-data-writeback

for the use case you describe i'd expect it to probably to be worksheet (or constrained calc) for something to feel 'good' in the Perspectives compare.

------------------------------
Ted Phillips
IBM

Original Message:
Sent: Fri January 26, 2024 11:10 AM
From: George Tonkin
Subject: Effective updating of String data via PAfE Custom Report

This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

A client has a custom report that contains Sales related data for example.
The columns give them values for Actuals, Budget, Variance and another column for Commentary
The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
This is where things start going South.

In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
The text per the formula bar would then be sent back to the cube and everyone could see the update.

In PAfE, attempting the same sequence leaves you with RECALC_0_0_
Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
------------------------------

Hiyo George, my first question would be what is the current value on this setting?
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-refresh-data-writeback

for the use case you describe i'd expect it to probably to be worksheet (or constrained calc) for something to feel 'good' in the Perspectives compare.

This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

A client has a custom report that contains Sales related data for example.
The columns give them values for Actuals, Budget, Variance and another column for Commentary
The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
This is where things start going South.

In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
The text per the formula bar would then be sent back to the cube and everyone could see the update.

In PAfE, attempting the same sequence leaves you with RECALC_0_0_
Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?

Hi George,

You need to enable constrained calculation to reinforce that behavior in PAfE, if it isn't already enabled. You may do so by adding the below line in your tm1features.json file:

{ "r56_EnableManualConstrainedCalc": true }

Once you do that, you will notice an additional option (Constrained Calc Scope) showing under "Refresh data on writeback". Select that and you should be able to use F2+F9 to update the values in PAfE. 

Regards,

Hiyo George, my first question would be what is the current value on this setting?
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-refresh-data-writeback

for the use case you describe i'd expect it to probably to be worksheet (or constrained calc) for something to feel 'good' in the Perspectives compare.

This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

A client has a custom report that contains Sales related data for example.
The columns give them values for Actuals, Budget, Variance and another column for Commentary
The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
This is where things start going South.

In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
The text per the formula bar would then be sent back to the cube and everyone could see the update.

In PAfE, attempting the same sequence leaves you with RECALC_0_0_
Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?

HI Amin,

Thanks for the above. I added the setting and could see the Constrained Calc Scope option in my PAfE.
The F2, F9 now works.

What is curious is I can now remove that setting, set it to false and leave the Refresh data on writeback to None and F2, F9 does the evaluation, just like I needed it to.

On a colleague's machine, he did not need to make any changes, F2, F9 worked for him.
Main difference is likely that 2.0.92 is the first version of PAfE ever "installed" on his machine.

Will need to test further with the client who has come through 2.0.48 and is now on 2.0.92

Hi George,

You need to enable constrained calculation to reinforce that behavior in PAfE, if it isn't already enabled. You may do so by adding the below line in your tm1features.json file:

{ "r56_EnableManualConstrainedCalc": true }

Once you do that, you will notice an additional option (Constrained Calc Scope) showing under "Refresh data on writeback". Select that and you should be able to use F2+F9 to update the values in PAfE. 

Regards,

Hiyo George, my first question would be what is the current value on this setting?
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-refresh-data-writeback

for the use case you describe i'd expect it to probably to be worksheet (or constrained calc) for something to feel 'good' in the Perspectives compare.

This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

A client has a custom report that contains Sales related data for example.
The columns give them values for Actuals, Budget, Variance and another column for Commentary
The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
This is where things start going South.

In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
The text per the formula bar would then be sent back to the cube and everyone could see the update.

In PAfE, attempting the same sequence leaves you with RECALC_0_0_
Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?

Seems like my luck has run out. Today back to square one.

I have enabled the constrained calculation, set the option in PAfE options but what worked yesterday no longer works today, not for myself or my colleague.

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg

Original Message:
Sent: Mon January 29, 2024 03:37 AM
From: George Tonkin
Subject: Effective updating of String data via PAfE Custom Report

HI Amin,

Thanks for the above. I added the setting and could see the Constrained Calc Scope option in my PAfE.
The F2, F9 now works.

What is curious is I can now remove that setting, set it to false and leave the Refresh data on writeback to None and F2, F9 does the evaluation, just like I needed it to.

On a colleague's machine, he did not need to make any changes, F2, F9 worked for him.
Main difference is likely that 2.0.92 is the first version of PAfE ever "installed" on his machine.

Will need to test further with the client who has come through 2.0.48 and is now on 2.0.92

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg

Original Message:
Sent: Sun January 28, 2024 01:40 PM
From: Amin Mohammed
Subject: Effective updating of String data via PAfE Custom Report

Hi George,

You need to enable constrained calculation to reinforce that behavior in PAfE, if it isn't already enabled. You may do so by adding the below line in your tm1features.json file:

{ "r56_EnableManualConstrainedCalc": true }

Once you do that, you will notice an additional option (Constrained Calc Scope) showing under "Refresh data on writeback". Select that and you should be able to use F2+F9 to update the values in PAfE. 

Regards,

------------------------------
Amin Mohammed

Original Message:
Sent: Fri January 26, 2024 01:11 PM
From: Ted Phillips
Subject: Effective updating of String data via PAfE Custom Report

Hiyo George, my first question would be what is the current value on this setting?
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-refresh-data-writeback

for the use case you describe i'd expect it to probably to be worksheet (or constrained calc) for something to feel 'good' in the Perspectives compare.

------------------------------
Ted Phillips
IBM

Original Message:
Sent: Fri January 26, 2024 11:10 AM
From: George Tonkin
Subject: Effective updating of String data via PAfE Custom Report

This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

A client has a custom report that contains Sales related data for example.
The columns give them values for Actuals, Budget, Variance and another column for Commentary
The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
This is where things start going South.

In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
The text per the formula bar would then be sent back to the cube and everyone could see the update.

In PAfE, attempting the same sequence leaves you with RECALC_0_0_
Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
------------------------------

George,

How about a Universal Report? There are no DBRWs, just numbers or strings. You just have to remember to hit Commit... and be on the latest versions for the best experience.

------------------------------
Walter Coffen
Technology Manager
QueBIT Consulting, LLC

Original Message:
Sent: Tue January 30, 2024 08:55 AM
From: George Tonkin
Subject: Effective updating of String data via PAfE Custom Report

Seems like my luck has run out. Today back to square one.

I have enabled the constrained calculation, set the option in PAfE options but what worked yesterday no longer works today, not for myself or my colleague.

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg

Original Message:
Sent: Mon January 29, 2024 03:37 AM
From: George Tonkin
Subject: Effective updating of String data via PAfE Custom Report

HI Amin,

Thanks for the above. I added the setting and could see the Constrained Calc Scope option in my PAfE.
The F2, F9 now works.

What is curious is I can now remove that setting, set it to false and leave the Refresh data on writeback to None and F2, F9 does the evaluation, just like I needed it to.

On a colleague's machine, he did not need to make any changes, F2, F9 worked for him.
Main difference is likely that 2.0.92 is the first version of PAfE ever "installed" on his machine.

Will need to test further with the client who has come through 2.0.48 and is now on 2.0.92

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg

Original Message:
Sent: Sun January 28, 2024 01:40 PM
From: Amin Mohammed
Subject: Effective updating of String data via PAfE Custom Report

Hi George,

You need to enable constrained calculation to reinforce that behavior in PAfE, if it isn't already enabled. You may do so by adding the below line in your tm1features.json file:

{ "r56_EnableManualConstrainedCalc": true }

Once you do that, you will notice an additional option (Constrained Calc Scope) showing under "Refresh data on writeback". Select that and you should be able to use F2+F9 to update the values in PAfE. 

Regards,

------------------------------
Amin Mohammed

Original Message:
Sent: Fri January 26, 2024 01:11 PM
From: Ted Phillips
Subject: Effective updating of String data via PAfE Custom Report

Hiyo George, my first question would be what is the current value on this setting?
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-refresh-data-writeback

for the use case you describe i'd expect it to probably to be worksheet (or constrained calc) for something to feel 'good' in the Perspectives compare.

------------------------------
Ted Phillips
IBM

Original Message:
Sent: Fri January 26, 2024 11:10 AM
From: George Tonkin
Subject: Effective updating of String data via PAfE Custom Report

This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

A client has a custom report that contains Sales related data for example.
The columns give them values for Actuals, Budget, Variance and another column for Commentary
The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
This is where things start going South.

In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
The text per the formula bar would then be sent back to the cube and everyone could see the update.

In PAfE, attempting the same sequence leaves you with RECALC_0_0_
Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
------------------------------