Planning Analytics

Phillipe:

I have run into exactly this situation with Dynamic Reports and PAW, and the only way I have been able to resolve it is to create an extra "invisible" column with the pure DBRW() formula in it to drive the zero suppression. Then I make the column narrow and format the text to be the same color as the background. Unfortunately HIDING the column in Excel does not work: once the column is hidden it becomes invisible to TM1RPTVIEW and all its related functions.

If you find a better solution, please let me know!

Best regards,

Ann-Grete

Phillipe:

I have run into exactly this situation with Dynamic Reports and PAW, and the only way I have been able to resolve it is to create an extra "invisible" column with the pure DBRW() formula in it to drive the zero suppression. Then I make the column narrow and format the text to be the same color as the background. Unfortunately HIDING the column in Excel does not work: once the column is hidden it becomes invisible to TM1RPTVIEW and all its related functions.

If you find a better solution, please let me know!

Best regards,

Ann-Grete

Amin:

Thank you for your suggestion! 

However if by "grouping" you mean using the "Group"/"Ungroup" functionality under the Excel Data menu - then in my case hiding the column using a Group behaves the same way as simply hiding the column, and does not solve the problem of driving the zero-suppress behavior of TM1RPTVIEW/TM1RPTROW when *all* your DBRW() formulas are components of another expression e.g. DBRW()/1000. I have found that I need to have an unhidden column with "clean" DBRW() formulas pointing at the appropriate TM1RPTTITLE combinations to get the behavior I want. Fortunately white-on-white formatting does the trick.

Besides I am unfortunately stuck in an older version of PA and PAW at this particular client, and it is possible that the behavior is better in newer versions.

Best regards,

Ann-Grete

Phillipe:

I have run into exactly this situation with Dynamic Reports and PAW, and the only way I have been able to resolve it is to create an extra "invisible" column with the pure DBRW() formula in it to drive the zero suppression. Then I make the column narrow and format the text to be the same color as the background. Unfortunately HIDING the column in Excel does not work: once the column is hidden it becomes invisible to TM1RPTVIEW and all its related functions.

If you find a better solution, please let me know!

Best regards,

Ann-Grete

Hello Ann-Grete,

i use a special excel format with a space (it said to excel to divide by 1000) and it works .(perhaps a coma in english excel ?)

I have also a ticket with IBM support and will have a webex this April 5th.... perhaps solution ?

Regards,

Philippe

Amin:

Thank you for your suggestion! 

However if by "grouping" you mean using the "Group"/"Ungroup" functionality under the Excel Data menu - then in my case hiding the column using a Group behaves the same way as simply hiding the column, and does not solve the problem of driving the zero-suppress behavior of TM1RPTVIEW/TM1RPTROW when *all* your DBRW() formulas are components of another expression e.g. DBRW()/1000. I have found that I need to have an unhidden column with "clean" DBRW() formulas pointing at the appropriate TM1RPTTITLE combinations to get the behavior I want. Fortunately white-on-white formatting does the trick.

Besides I am unfortunately stuck in an older version of PA and PAW at this particular client, and it is possible that the behavior is better in newer versions.

Best regards,

Ann-Grete

Phillipe:

I have run into exactly this situation with Dynamic Reports and PAW, and the only way I have been able to resolve it is to create an extra "invisible" column with the pure DBRW() formula in it to drive the zero suppression. Then I make the column narrow and format the text to be the same color as the background. Unfortunately HIDING the column in Excel does not work: once the column is hidden it becomes invisible to TM1RPTVIEW and all its related functions.

If you find a better solution, please let me know!

Best regards,

Ann-Grete

Phillipe:

I have run into exactly this situation with Dynamic Reports and PAW, and the only way I have been able to resolve it is to create an extra "invisible" column with the pure DBRW() formula in it to drive the zero suppression. Then I make the column narrow and format the text to be the same color as the background. Unfortunately HIDING the column in Excel does not work: once the column is hidden it becomes invisible to TM1RPTVIEW and all its related functions.

If you find a better solution, please let me know!

Best regards,

Ann-Grete

Hi Ann-Grete, as a best practice I would try to keep the DBRW formulas as plain as possible. You could move the calculation for the k-value to the corresponding measure dimension by leveraging the weight property:

Kind regards,

Luc

Phillipe:

I have run into exactly this situation with Dynamic Reports and PAW, and the only way I have been able to resolve it is to create an extra "invisible" column with the pure DBRW() formula in it to drive the zero suppression. Then I make the column narrow and format the text to be the same color as the background. Unfortunately HIDING the column in Excel does not work: once the column is hidden it becomes invisible to TM1RPTVIEW and all its related functions.

If you find a better solution, please let me know!

Best regards,

Ann-Grete

Luc

That's a really nice and elegant solution for this specific situation - thank you for sharing it!

However I am afraid I refuse to accept that "keeping DBRW formulas pure" should be considered "best practice" :-) 

Let's acknowledge instead that it is a LIMITATION. There is no reason Dynamic Reports should not be able to parse formulas on any TM1RPTROW line for DBRWs. But as long as there is an acceptable workaround (and in this case there are several) there are other things I would prioritize.

Thank you,

Ann-Grete

Hi Ann-Grete, as a best practice I would try to keep the DBRW formulas as plain as possible. You could move the calculation for the k-value to the corresponding measure dimension by leveraging the weight property:

Kind regards,

Luc

Phillipe:

I have run into exactly this situation with Dynamic Reports and PAW, and the only way I have been able to resolve it is to create an extra "invisible" column with the pure DBRW() formula in it to drive the zero suppression. Then I make the column narrow and format the text to be the same color as the background. Unfortunately HIDING the column in Excel does not work: once the column is hidden it becomes invisible to TM1RPTVIEW and all its related functions.

If you find a better solution, please let me know!

Best regards,

Ann-Grete

Ann-Grete

I find an old (very old) IBM documentation  (03 February 2021) ... Compound DBRW formulas are not supported in TM1 Web (ibm.com)

Wich explain ..... we have no solution except Mine or Luc' solution.

I come back as soon I have talk with ibm support engineer

Regards,

Philippe

Luc

That's a really nice and elegant solution for this specific situation - thank you for sharing it!

However I am afraid I refuse to accept that "keeping DBRW formulas pure" should be considered "best practice" :-) 

Let's acknowledge instead that it is a LIMITATION. There is no reason Dynamic Reports should not be able to parse formulas on any TM1RPTROW line for DBRWs. But as long as there is an acceptable workaround (and in this case there are several) there are other things I would prioritize.

Thank you,

Ann-Grete

Hi Ann-Grete, as a best practice I would try to keep the DBRW formulas as plain as possible. You could move the calculation for the k-value to the corresponding measure dimension by leveraging the weight property:

Kind regards,

Luc

Phillipe:

I have run into exactly this situation with Dynamic Reports and PAW, and the only way I have been able to resolve it is to create an extra "invisible" column with the pure DBRW() formula in it to drive the zero suppression. Then I make the column narrow and format the text to be the same color as the background. Unfortunately HIDING the column in Excel does not work: once the column is hidden it becomes invisible to TM1RPTVIEW and all its related functions.

If you find a better solution, please let me know!

Best regards,

Ann-Grete

Hello,

Tested with Ibm support engineer.

Divide by 1000 is the explanation.

The support ask me to test https://tm1explorers.com/forums/topic/dbrw-not-working-in-tm1web.

I made an other thing :

   have a column with a correct and simple dbrw() formula .....  and Excel Hide this column

   have one or several column with "my" compound formula

 It work fine for me on tm1web

In conclusion :

    - Use excel format mask to show number divide by 1000

    - Use an other indicator as suggest Luke ( very nice and elegant, I think also)

    - Use a great and conform Column (Hide it in excel)    .... it work for me 

 And, above all, don't expect IBM to correct anyth  :-(

regards,

Philippe

Ann-Grete

I find an old (very old) IBM documentation  (03 February 2021) ... Compound DBRW formulas are not supported in TM1 Web (ibm.com)

Wich explain ..... we have no solution except Mine or Luc' solution.

I come back as soon I have talk with ibm support engineer

Regards,

Philippe

Luc

That's a really nice and elegant solution for this specific situation - thank you for sharing it!

However I am afraid I refuse to accept that "keeping DBRW formulas pure" should be considered "best practice" :-) 

Let's acknowledge instead that it is a LIMITATION. There is no reason Dynamic Reports should not be able to parse formulas on any TM1RPTROW line for DBRWs. But as long as there is an acceptable workaround (and in this case there are several) there are other things I would prioritize.

Thank you,

Ann-Grete

Hi Ann-Grete, as a best practice I would try to keep the DBRW formulas as plain as possible. You could move the calculation for the k-value to the corresponding measure dimension by leveraging the weight property:

Kind regards,

Luc

Phillipe:

I have run into exactly this situation with Dynamic Reports and PAW, and the only way I have been able to resolve it is to create an extra "invisible" column with the pure DBRW() formula in it to drive the zero suppression. Then I make the column narrow and format the text to be the same color as the background. Unfortunately HIDING the column in Excel does not work: once the column is hidden it becomes invisible to TM1RPTVIEW and all its related functions.

If you find a better solution, please let me know!

Best regards,

Ann-Grete

Coming in a bit late here and it is Friday but is this not as simple changing the format of the cells to display in thousands in Excel i.e.  #,##0.00,;-#,##0.00,

Hello,

Tested with Ibm support engineer.

Divide by 1000 is the explanation.

The support ask me to test https://tm1explorers.com/forums/topic/dbrw-not-working-in-tm1web.

I made an other thing :

   have a column with a correct and simple dbrw() formula .....  and Excel Hide this column

   have one or several column with "my" compound formula

 It work fine for me on tm1web

In conclusion :

    - Use excel format mask to show number divide by 1000

    - Use an other indicator as suggest Luke ( very nice and elegant, I think also)

    - Use a great and conform Column (Hide it in excel)    .... it work for me 

 And, above all, don't expect IBM to correct anyth  :-(

regards,

Philippe

Ann-Grete

I find an old (very old) IBM documentation  (03 February 2021) ... Compound DBRW formulas are not supported in TM1 Web (ibm.com)

Wich explain ..... we have no solution except Mine or Luc' solution.

I come back as soon I have talk with ibm support engineer

Regards,

Philippe

Luc

That's a really nice and elegant solution for this specific situation - thank you for sharing it!

However I am afraid I refuse to accept that "keeping DBRW formulas pure" should be considered "best practice" :-) 

Let's acknowledge instead that it is a LIMITATION. There is no reason Dynamic Reports should not be able to parse formulas on any TM1RPTROW line for DBRWs. But as long as there is an acceptable workaround (and in this case there are several) there are other things I would prioritize.

Thank you,

Ann-Grete

Hi Ann-Grete, as a best practice I would try to keep the DBRW formulas as plain as possible. You could move the calculation for the k-value to the corresponding measure dimension by leveraging the weight property:

Kind regards,

Luc

Phillipe:

I have run into exactly this situation with Dynamic Reports and PAW, and the only way I have been able to resolve it is to create an extra "invisible" column with the pure DBRW() formula in it to drive the zero suppression. Then I make the column narrow and format the text to be the same color as the background. Unfortunately HIDING the column in Excel does not work: once the column is hidden it becomes invisible to TM1RPTVIEW and all its related functions.

If you find a better solution, please let me know!

Best regards,

Ann-Grete