Planning Analytics

 View Only
Expand all | Collapse all

PAFE tmrrptview with BzeroSuppression is aleatory

  • 1.  PAFE tmrrptview with BzeroSuppression is aleatory

    Posted Thu March 28, 2024 12:34 PM
    Hi,
     
    I use PAFE 2.0.94.7 and want to make a sample reporting :
     
    I use  tm1rptView with BZeroSuppression=1
     
    When I use DBRW(.....)  it work as wanted
     
    When I use DBRW(.....) / 1000   no data !!
     
    When I make other modifications, in a very random way, "BZeroSuppression" make that no line is carried over. (even if the have data)
     
    I've tried deleting TM1RPTTITLE but, again, it's very random.
     
    Once published, the behavior on this subject is not identical between PAFE and tm1Web.
     
    I need some help to understand :
     why BZeroSuppression supress all ligne, even with value
     why PAFE and tm1Web are different
    Which parameter I can use to securize the comportment.
     
    Best Regards,
    Philippe


    ------------------------------
    Philippe CHAMPLEBOUX
    ------------------------------


  • 2.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    Posted Mon April 01, 2024 10:23 AM

    It is hard to say much without really understanding the workload in detail, but I will say that Websheets and Perspectives both have some issues working when custom formulas are combined inline with other operations (compound formulas), PA for Excel works at function granularity, not formula, so it's behavior should most accurately model proper functional outcomes.  There is even more nuance with Dynamic Reports since PA for Excel will also run data-sensitive rowset outcomes in strict relation to the spatial geometry of the report. These characteristics are documented, albeit in summary, but it should absolutely be possible to get the outcome you're looking for from PA for Excel - may need to make some aspect of the report more explicit to achieve your goal.

    Best to engage with support where we can go over the specifics of your workload in depth.



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



  • 3.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    IBM Champion
    Posted Tue April 02, 2024 04:39 PM

    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 Tan
    agtan@tensorresearch.com
    ------------------------------



  • 4.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    IBM Champion
    Posted Tue April 02, 2024 07:10 PM
    Hi Ann-Grete,

    Did u try grouping the column instead as that normally works for me.






  • 5.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    IBM Champion
    Posted Tue April 02, 2024 07:54 PM

    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



    ------------------------------
    Ann-Grete Tan
    agtan@tensorresearch.com
    ------------------------------



  • 6.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    Posted Wed April 03, 2024 02:34 AM

    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



    ------------------------------
    Philippe CHAMPLEBOUX
    ------------------------------



  • 7.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    IBM Champion
    Posted Wed April 03, 2024 08:12 AM

    Phillippe:

    That's a nice way to deal with it, and thank you for submitting the support ticket! I look forward to hearing what you find.

    IMHO advanced Excel integration is one of the most powerful features of Planning Analytics. In this example there were several options to solve the problem, but there are other things we may want to do with a DBRW than simply divide by 1,000. Often the "best practice" approach is to build the calculation into the database (as @Luc Cusiel suggested and which I agree with) but there are times when this is not an option for a developer or report builder, so it is good to have other options. 

     Thank you,

    Ann-Grete



    ------------------------------
    Ann-Grete Tan
    agtan@tensorresearch.com
    ------------------------------



  • 8.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    IBM Champion
    Posted Wed April 03, 2024 03:07 AM

    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



    ------------------------------
    Luc Cusiel
    ------------------------------



  • 9.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    IBM Champion
    Posted Wed April 03, 2024 08:02 AM

    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



    ------------------------------
    Ann-Grete Tan
    agtan@tensorresearch.com
    ------------------------------



  • 10.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    Posted Fri April 05, 2024 07:52 AM

    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



    ------------------------------
    Philippe CHAMPLEBOUX
    ------------------------------



  • 11.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    Posted Fri April 05, 2024 01:38 PM

    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



    ------------------------------
    Philippe CHAMPLEBOUX
    ------------------------------



  • 12.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    IBM Champion
    Posted Fri April 05, 2024 03:43 PM

    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,



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



  • 13.  RE: PAFE tmrrptview with BzeroSuppression is aleatory

    IBM Champion
    Posted Sun April 14, 2024 06:56 PM

    George:

    Yes, in this case it is easily solved by setting the format! 

    But the more general case is where you may have a good reason to put some other Excel logic around your DBRW() rather than building the calculation into a rule or something.

    I just tried this with a newer version of PAW than I was referencing in my earlier post and it works fine in PAfE, but generates no rows in PAW. However (good news) with a more recent version of PAW there was no problem adding a column with a "clean" DBRW and then hiding it, which is a fine solution. 

    In the ancient (2+ years old) version I tried this with before column-hiding was not an option so I had to resort to "white on white" formatting to make it work.

    Anyway - it's been a good conversation, and a great example of how TM1 still provides many ways to making things work!

    Thanks,

    AG



    ------------------------------
    Ann-Grete Tan
    agtan@tensorresearch.com
    ------------------------------