Custom Filter

Hi Experts,


I have a requirement.i am confusing how to resolve this kindly help on this.


In One module       Row Axis - Product and Customer lists
                              Column axis - Lime Item and time lists
                               Page Axis - Measure list

There are 5 line items. For each line item i want to saw different time periods that also based on user selection.


For suppose user selects 2018 year
Line item 1 to 3: Show all years
Line Item 4 : Show 2018 year only (Its possible by using filter but when i applying filter it shows only 2018 year and line item 4 only)

But as per my requirement i want show line item 1 to 3 all years and line item 4 specific selection period.


I Line item 4 period based on user selection.






Is it possible. Kindly suggest me on this

Best Answer

  • DavidSmith
    Answer ✓


    As you have found, filtering on nested dimensions is tricky.

    However, if you look at your problem a different way, you are wanting to display a different single time value depending on the line item. So you don't actually need a timescale in the report

    Lets assume I have the following (I've used random numbers!!):



    1. Create a Line Item subset using the module above


    2. Create a staging module based on the module from above, but include the Line Item Subset from 1. and a single line item with COLLECT() as the formula


    Remember to turn the summaries off for the lists (you have to leave Time summaries on) 

    3. Create a second module using the Line Item Subset with a single Boolean line item. This is where you define the line items that will reference the user choice from 4. below


    4. Create a module to define the Time period for the dynamic selection.  A single line item formatted as Year


     5. Create a reporting module based on 1. but without the timescale



     The formula is IF NOT Line Items Selection.Dynamic? THEN KPI Staging.Value[SELECT: TIME.All Periods] ELSE KPI Staging.Value[LOOKUP: Filter Selection.Year]

    We put the most common occurrence first as a Best Practice






    A couple of extra tips:

    1. As @usman.zia  mentioned, if you need the report and filter setting to vary by user, then you will need to ensure that the modules created in 4 and 5 have the user list within them

    2. If you needed the second selection to be dynamic, you could set up a second line item in the module from 4 to hold the second variable and adjust the formula in 5 accordingly.

    I hope that helps



  • Hi Uma, 


    Is the 4th line item dimensioned by user?

    Is the filter that applies to the 4th line item dimensioned by user? 

    Does the formula of the 4th line item reference the user filter? 

    I would suggest creating these and multiplying the line item by the flag or by using an if statement. 


    I hope that this helps @UMAMAHESH 





  • Hi Usman, Could you please explain in brief. Regards, Mahesh