Custom Filter

UMAMAHESH

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
Then
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.

 

 

Regards,

Uma

 

Is it possible. Kindly suggest me on this

DavidSmith

@UMAMAHESH 

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

6.

 

 

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

David