Hi,

I encountered strange behaviour by the DBRA formula:

I use the TM1RPTROW function to import some Data into Excel. This works fine:


I then want to add some attributes through the DBRA function:

This works fine for the first row:

All other rows show the #VALUE error. The strange thing is: When i click into the formula bar and press enter the correct data is retrieved:





I can do this with other cells as well. If I press F9 to update everything all rows except the first show the #VALUE error again.

What is happening here? 



​​

------------------------------
Sylwester Przybyla
------------------------------

#PlanningAnalyticswithWatson

Hi,
I have a simple question.  What are the results if you go to the Planning Analytics tab and click on the "Rebuild Worksheet" and then the "Refresh Worksheet"?

------------------------------
Scott Brown
------------------------------

Hi Scott,

Here are the results:

After Rebuild Worksheet: DBRA formulas show #VALUE




After Refresh Worksheet: Only first Row shows data


​

------------------------------
Sylwester Przybyla
------------------------------

Original Message:
Sent: Fri May 06, 2022 12:30 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,
I have a simple question.  What are the results if you go to the Planning Analytics tab and click on the "Rebuild Worksheet" and then the "Refresh Worksheet"?

------------------------------
Scott Brown
------------------------------

Hi,
If you delete the attribute formulas from row 24, and rebuild the sheet, are they replaced?  With the new version of Excel, you often see "@" at the start of formulas.  Have you tried that with the DBRA formulas?

------------------------------
Scott Brown
------------------------------

Original Message:
Sent: Fri May 06, 2022 12:39 PM
From: Sylwester Przybyla
Subject: DBRA function shows #value

Hi Scott,

Here are the results:

After Rebuild Worksheet: DBRA formulas show #VALUE




After Refresh Worksheet: Only first Row shows data


​

------------------------------
Sylwester Przybyla
------------------------------


Original Message:
Sent: Fri May 06, 2022 12:30 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,
I have a simple question.  What are the results if you go to the Planning Analytics tab and click on the "Rebuild Worksheet" and then the "Refresh Worksheet"?

------------------------------
Scott Brown

It get´s wierder and wierder:

I deleted the formulas below 23 and rebuild:

The deleted formulas do not get replaced but the formulas from row 20-24 show data:

But when I fill in the rest of the rows the 'Value error comes up again:


Rebuilding and refreshing doesn´t help after that: Only first row shows data again:


I also tried out the @DBRA formula. It had no effect.

​

------------------------------
Sylwester Przybyla
------------------------------

Original Message:
Sent: Fri May 06, 2022 01:14 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,
If you delete the attribute formulas from row 24, and rebuild the sheet, are they replaced?  With the new version of Excel, you often see "@" at the start of formulas.  Have you tried that with the DBRA formulas?

------------------------------
Scott Brown
------------------------------


Original Message:
Sent: Fri May 06, 2022 12:39 PM
From: Sylwester Przybyla
Subject: DBRA function shows #value

Hi Scott,

Here are the results:

After Rebuild Worksheet: DBRA formulas show #VALUE




After Refresh Worksheet: Only first Row shows data


​

------------------------------
Sylwester Przybyla


Original Message:
Sent: Fri May 06, 2022 12:30 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,
I have a simple question.  What are the results if you go to the Planning Analytics tab and click on the "Rebuild Worksheet" and then the "Refresh Worksheet"?

------------------------------
Scott Brown

Difficult to say what may be going wrong for you. Similar DR works fine for me on version 2.0.75.2 of PAfE.
Could be references e.g. row 20 is not using absolute references for the TM1RptView or other dependencies.

What you do need to be aware of is when changes on the 1st row are propogated to other rows.
Changing a formula or formatting does not always trigger a complete rebuild as there is caching that happens.

Typically I also delete from row 2 of the DR onwards then click Rebuild Sheet to be sure that rows 2 onwards are added correctly.
This holds true whether I develop on Manual calculation or Automatic calculation mode within Excel.


------------------------------
George Tonkin
------------------------------

Original Message:
Sent: Sat May 07, 2022 02:54 AM
From: Sylwester Przybyla
Subject: DBRA function shows #value

It get´s wierder and wierder:

I deleted the formulas below 23 and rebuild:

The deleted formulas do not get replaced but the formulas from row 20-24 show data:

But when I fill in the rest of the rows the 'Value error comes up again:


Rebuilding and refreshing doesn´t help after that: Only first row shows data again:


I also tried out the @DBRA formula. It had no effect.

​

------------------------------
Sylwester Przybyla
------------------------------


Original Message:
Sent: Fri May 06, 2022 01:14 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,
If you delete the attribute formulas from row 24, and rebuild the sheet, are they replaced?  With the new version of Excel, you often see "@" at the start of formulas.  Have you tried that with the DBRA formulas?

------------------------------
Scott Brown


Original Message:
Sent: Fri May 06, 2022 12:39 PM
From: Sylwester Przybyla
Subject: DBRA function shows #value

Hi Scott,

Here are the results:

After Rebuild Worksheet: DBRA formulas show #VALUE




After Refresh Worksheet: Only first Row shows data


​

------------------------------
Sylwester Przybyla


Original Message:
Sent: Fri May 06, 2022 12:30 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,
I have a simple question.  What are the results if you go to the Planning Analytics tab and click on the "Rebuild Worksheet" and then the "Refresh Worksheet"?

------------------------------
Scott Brown

I've seen the #VALUE appear under various conditions.  A couple of things to check:

1) Try switching the DBRA to a DBRW formula against the relevant }ElementAttribute cube.
2) Check the workbook for errors, specifically circular references which I've seen cause cells to not calculate properly and return #VALUE instead
3) This one is probably a bug... I've seen a DBRA in a Dynamic Report fail to retrieve the attribute when the row element being referenced is set to an alias/caption.  If you're using an alias as your main row element, try removing it from the TM1RPTROW formula and rebuild.​

Good luck :)

------------------------------
Brian Simpson
------------------------------

Original Message:
Sent: Sat May 07, 2022 02:54 AM
From: Sylwester Przybyla
Subject: DBRA function shows #value

It get´s wierder and wierder:

I deleted the formulas below 23 and rebuild:

The deleted formulas do not get replaced but the formulas from row 20-24 show data:

But when I fill in the rest of the rows the 'Value error comes up again:


Rebuilding and refreshing doesn´t help after that: Only first row shows data again:


I also tried out the @DBRA formula. It had no effect.

​

------------------------------
Sylwester Przybyla
------------------------------


Original Message:
Sent: Fri May 06, 2022 01:14 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,
If you delete the attribute formulas from row 24, and rebuild the sheet, are they replaced?  With the new version of Excel, you often see "@" at the start of formulas.  Have you tried that with the DBRA formulas?

------------------------------
Scott Brown


Original Message:
Sent: Fri May 06, 2022 12:39 PM
From: Sylwester Przybyla
Subject: DBRA function shows #value

Hi Scott,

Here are the results:

After Rebuild Worksheet: DBRA formulas show #VALUE




After Refresh Worksheet: Only first Row shows data


​

------------------------------
Sylwester Przybyla


Original Message:
Sent: Fri May 06, 2022 12:30 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,
I have a simple question.  What are the results if you go to the Planning Analytics tab and click on the "Rebuild Worksheet" and then the "Refresh Worksheet"?

------------------------------
Scott Brown

Hey,

thanks for your support. Unfortunately I had no luck:

1) Try switching the DBRA to a DBRW formula against the relevant }ElementAttribute cube.
---> Not working

2) Check the workbook for errors, specifically circular references which I've seen cause cells to not calculate properly and return #VALUE instead
--> No other errors in workbook. The file is small and nothing else in this file. It´s just one sheet. 

3) This one is probably a bug... I've seen a DBRA in a Dynamic Report fail to retrieve the attribute when the row element being referenced is set to an alias/caption.  If you're using an alias as your main row element, try removing it from the TM1RPTROW formula and rebuild.​
--> No Aliases used


------------------------------
Sylwester Przybyla
------------------------------

Original Message:
Sent: Mon May 09, 2022 09:35 AM
From: Brian Simpson
Subject: DBRA function shows #value

I've seen the #VALUE appear under various conditions.  A couple of things to check:

1) Try switching the DBRA to a DBRW formula against the relevant }ElementAttribute cube.
2) Check the workbook for errors, specifically circular references which I've seen cause cells to not calculate properly and return #VALUE instead
3) This one is probably a bug... I've seen a DBRA in a Dynamic Report fail to retrieve the attribute when the row element being referenced is set to an alias/caption.  If you're using an alias as your main row element, try removing it from the TM1RPTROW formula and rebuild.​

Good luck :)

------------------------------
Brian Simpson
------------------------------


Original Message:
Sent: Sat May 07, 2022 02:54 AM
From: Sylwester Przybyla
Subject: DBRA function shows #value

It get´s wierder and wierder:

I deleted the formulas below 23 and rebuild:

The deleted formulas do not get replaced but the formulas from row 20-24 show data:

But when I fill in the rest of the rows the 'Value error comes up again:


Rebuilding and refreshing doesn´t help after that: Only first row shows data again:


I also tried out the @DBRA formula. It had no effect.

​

------------------------------
Sylwester Przybyla


Original Message:
Sent: Fri May 06, 2022 01:14 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,
If you delete the attribute formulas from row 24, and rebuild the sheet, are they replaced?  With the new version of Excel, you often see "@" at the start of formulas.  Have you tried that with the DBRA formulas?

------------------------------
Scott Brown


Original Message:
Sent: Fri May 06, 2022 12:39 PM
From: Sylwester Przybyla
Subject: DBRA function shows #value

Hi Scott,

Here are the results:

After Rebuild Worksheet: DBRA formulas show #VALUE




After Refresh Worksheet: Only first Row shows data


​

------------------------------
Sylwester Przybyla


Original Message:
Sent: Fri May 06, 2022 12:30 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,
I have a simple question.  What are the results if you go to the Planning Analytics tab and click on the "Rebuild Worksheet" and then the "Refresh Worksheet"?

------------------------------
Scott Brown

Hi,
I have an issue open with IBM in this space at the moment. (TS009273622)
I have found that any DBRA in any sheet will fail if there is a dynamic report open in any workbook that is failing or corrupted in some way.  In my case I found that a user had been duplicating dynamic reports and then sliced a new DR into the same sheet creating a confused state and repeated sets of named ranges.
The TM1RptRow was formula was stuck in a pending state.
So I suggest you check all the workbooks that are open when the DBRA is failing and look for any DR that are failing wth Pafe errors (rather than Excel errors) and generally have a good clean up.
It seems as though the calculation refresh is being blocked somehow when the recalc of the corrupted DR fails.
HTH helps in tracking your issue

------------------------------
Steven Rowe - Technical Director - InfoCat
------------------------------

Thanks for your support. 

Unfortunytely I have no other workbooks open and the workbook I use is very clean. It´s just this one report on one sheet. Nothing else. So this can´t be the issue here.

------------------------------
Sylwester Przybyla
------------------------------

Original Message:
Sent: Mon May 09, 2022 04:01 AM
From: Steven Rowe
Subject: DBRA function shows #value

Hi,
I have an issue open with IBM in this space at the moment. (TS009273622)
I have found that any DBRA in any sheet will fail if there is a dynamic report open in any workbook that is failing or corrupted in some way.  In my case I found that a user had been duplicating dynamic reports and then sliced a new DR into the same sheet creating a confused state and repeated sets of named ranges.
The TM1RptRow was formula was stuck in a pending state.
So I suggest you check all the workbooks that are open when the DBRA is failing and look for any DR that are failing wth Pafe errors (rather than Excel errors) and generally have a good clean up.
It seems as though the calculation refresh is being blocked somehow when the recalc of the corrupted DR fails.
HTH helps in tracking your issue

------------------------------
Steven Rowe - Technical Director - InfoCat
------------------------------

Nice share, i am beginner here, i want to improve my knowledge,

------------------------------
Suryono 666
Sukasejati Garden City
------------------------------

Hi,

I am working from memory, so things might be slightly off.  The other formulas did not get replicated down because they are not in your report area.  If you go to the Excel Formulas tab, and then open name manger you should have a few named ranges.  I believe one is for rows.  The other one should be for columns/report area.  Make sure that it includes the columns that you added to the dynamic report.  If not, then increase the area to include those columns.  Then go ahead and delete the attribute formulas in the rows below 24 and click rebuilt.  It it is set correctly, then it will replicate the formulas down to the other rows.
You want this set correctly so that if you row list changes and you get more rows, you do not end up with rows that do not have the formulas, or the rows shrink and you end up with formulas no matching data values.

Good luck and let me know if that helps!

------------------------------
Scott Brown
------------------------------

Original Message:
Sent: Sat May 07, 2022 03:52 AM
From: Suryono 666
Subject: DBRA function shows #value

Nice share, i am beginner here, i want to improve my knowledge,

------------------------------
Suryono 666
Sukasejati Garden City
------------------------------

Thanks for your support. 

I checked the name manager. It looks clean to me. Seems that only one range exists for the hole data range. The others are for formatting etc. 


I have decided to give up on this. It looks like a bug to me. I have realized that I can update 20 rows at a time. When i update 21 rows it breaks down. Luckily it´s only 300 rows of data I need. I´ll probably just write a macro to do the updating for me. This is ridicoulous. But as long as it works....

------------------------------
Sylwester Przybyla
------------------------------

Original Message:
Sent: Mon May 09, 2022 02:49 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,

I am working from memory, so things might be slightly off.  The other formulas did not get replicated down because they are not in your report area.  If you go to the Excel Formulas tab, and then open name manger you should have a few named ranges.  I believe one is for rows.  The other one should be for columns/report area.  Make sure that it includes the columns that you added to the dynamic report.  If not, then increase the area to include those columns.  Then go ahead and delete the attribute formulas in the rows below 24 and click rebuilt.  It it is set correctly, then it will replicate the formulas down to the other rows.
You want this set correctly so that if you row list changes and you get more rows, you do not end up with rows that do not have the formulas, or the rows shrink and you end up with formulas no matching data values.

Good luck and let me know if that helps!

------------------------------
Scott Brown
------------------------------


Original Message:
Sent: Sat May 07, 2022 03:52 AM
From: Suryono 666
Subject: DBRA function shows #value

Nice share, i am beginner here, i want to improve my knowledge,

------------------------------
Suryono 666
Sukasejati Garden City

HI,
The formatting is what you should expand.  The one that stop at $D$10 is the one that sets the columns that are considered part of the report.

Good luck with this, and if you have not, please open a case with IBM.  That way they can either get this fixed, or update the documentation.


------------------------------
Scott Brown
------------------------------

Original Message:
Sent: Fri May 27, 2022 08:10 AM
From: Sylwester Przybyla
Subject: DBRA function shows #value

Thanks for your support. 

I checked the name manager. It looks clean to me. Seems that only one range exists for the hole data range. The others are for formatting etc. 


I have decided to give up on this. It looks like a bug to me. I have realized that I can update 20 rows at a time. When i update 21 rows it breaks down. Luckily it´s only 300 rows of data I need. I´ll probably just write a macro to do the updating for me. This is ridicoulous. But as long as it works....

------------------------------
Sylwester Przybyla
------------------------------


Original Message:
Sent: Mon May 09, 2022 02:49 PM
From: Scott Brown
Subject: DBRA function shows #value

Hi,

I am working from memory, so things might be slightly off.  The other formulas did not get replicated down because they are not in your report area.  If you go to the Excel Formulas tab, and then open name manger you should have a few named ranges.  I believe one is for rows.  The other one should be for columns/report area.  Make sure that it includes the columns that you added to the dynamic report.  If not, then increase the area to include those columns.  Then go ahead and delete the attribute formulas in the rows below 24 and click rebuilt.  It it is set correctly, then it will replicate the formulas down to the other rows.
You want this set correctly so that if you row list changes and you get more rows, you do not end up with rows that do not have the formulas, or the rows shrink and you end up with formulas no matching data values.

Good luck and let me know if that helps!

------------------------------
Scott Brown


Original Message:
Sent: Sat May 07, 2022 03:52 AM
From: Suryono 666
Subject: DBRA function shows #value

Nice share, i am beginner here, i want to improve my knowledge,

------------------------------
Suryono 666
Sukasejati Garden City