Planning Analytics

 View Only
Expand all | Collapse all

Filtering on String Values in PAW Similar to Excel

  • 1.  Filtering on String Values in PAW Similar to Excel

    Posted 30 days ago
    Edited by Asgeir Thorgeirsson 30 days ago
    Hi all,
     
    I hope this message finds you well.
     
    I am currently working with a cube for some Master Data, where the measure dimension contains several string members. When using Planning Analytics for Excel (PAX) Custom Report, I find the Excel column filter option extremely helpful for filtering string values.

    However, I've encountered an issue when trying to replicate this functionality in PAW:
     
    If I publish an Excel workbook with the necessary filters to Websheets and then add this sheet to PAW, the filter option seems to be missing.
     
    Therefore, I have two questions for the community and the PAW development team:
     
    1. Is the Excel column filter option available in Websheets within PAW? 
    2. Is there a similar column filter option on the roadmap for PAW?
     
    Having this functionality would greatly enhance our ability to filter and analyze Master Data directly within PAW.
     
    Thanks in advance for your assistance!
     


    ------------------------------
    Asgeir Thorgeirsson
    ------------------------------



  • 2.  RE: Filtering on String Values in PAW Similar to Excel

    Posted 23 days ago

    Hi Asgeir,

    Good question!  A list of the Excel features that are supported in Websheet (doesn't matter if the Websheet is in TM1Web or Workspace) can be found here: https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=overview-inherited-excel-features-in-websheets.  This does not include the column filter function from your screenshot.  However, with Dynamic Reports (Active Forms if you are still using Perspectives) and Universal Reports it's possible to add in one or more filters that determine what rows are displayed.  This is typically done by parameterizing and MDX statement (with something the user types in or selects from a picklist) and the complete MDX statement is fed to the TM1RPTRow function.

    In Workspace the filtering you are asking for (matches or contains a string) is possible by updating the MDX statement used in the cube viewer tab.  We do have a filter feature on the cube viewer, but it doesn't current support matching string values.  This will change in the next few releases.  Here is a screenshot I grabbed from a development environment that shows the updated filter feature on the Workspace cube viewer.

    Still a bit of work to add support for filtering based on strings (contains and exact match) and support filtering on multiple rows and columns at a time.

    Note that this 'filter by value' feature is in addition to our current filter top or bottom feature, which is also getting an update to the user interface and support for optionally filtering both top and bottom at the same time.

    So, no very simple solution in the cube viewer today (you need to get your hand dirty with MDX) but we will have a simple to use filter feature within the next few releases.

     

      



    ------------------------------
    Stuart King
    IBM Planning Analytics Offering Manager
    ------------------------------

    Original Message