PAW How to make View Filters work together

[JDLove]

As a example I have a simple PAW Book with 1 View,

I have dragged out 2 dimensions for user to filter the view.

1. Dimension is "Container Size"
2. is "Destination"

I want the Destination subset to only show the destinations which have data for that Container Size.


I'm reduced to using a Excel Websheet at the moment, my Excel active form just uses the SUBNM(Destination,40FRDG)
I have a subsets for each Container Size, using MDX in the subset editor for each.

Dimension:Destination:Subset "40RFDG" MDX as below example.

{
FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Destination])}, 0)},
[Freight].(
[Container Size].[40RFRDG],
[Version].[Forecast],
[Period].[Current Reporting Year],
[Freight FCL Measure].[Cost(NZD)]) <> 0)
}

Thanks
James

[burnstripe]

Check out the link below. You could create a cube with two dimensions, one with the }clients dim, the other with elements container size and destination.

The container size would have a picklist showing available sizes. The user would make, store their chosen selection (against their userid tm1user function can be used to determine this) in this cube.

Example of picklist subset
{
StrToMember("[destination]." + [User Selections].(
StrToMember("[}Clients].["+USERNAME+"]"),
[M user selections].[container size])+"]"
)
}

You'd then create a subset in the destination dimension which is similar to the one you provided but looks up to the new cube to determine the container size. Note: I've not tested this so there may be a typo

Code: Select all

{
FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Destination])}, 0)},
[Freight].(
+
{
StrToMember("[container size].[" + [User Selections].(
    StrToMember("[}Clients].["+USERNAME+"]"), 
        [M user selections].[container size])+"]"
     )
}
+
, 
[Version].[Forecast],
[Period].[Current Reporting Year],
[Freight FCL Measure].[Cost(NZD)]) <> 0)
}

From a usability stand point you may also wish to consider changing this tm1web property Recalconpicklistchange to true

https://www.linkedin.com/pulse/infocat- ... =share_via

[JDLove]

Thanks for the reply, another colleague advised me of this approach but I had hoped for something cleaner and able to use the native PAW Dimension selectors/filters in the book.

I think I had this demonstrated to me a few years back but I didn't get the full details from them at the time.

If not what I have is much simpler for now.

Sometimes simple is best .

[JohnO]

As a example I have a simple PAW Book with 1 View,

I have dragged out 2 dimensions for user to filter the view.

1. Dimension is "Container Size"
2. is "Destination"

I want the Destination subset to only show the destinations which have data for that Container Size.


I'm reduced to using a Excel Websheet at the moment, my Excel active form just uses the SUBNM(Destination,40FRDG)
I have a subsets for each Container Size, using MDX in the subset editor for each.

Dimension:Destination:Subset "40RFDG" MDX as below example.

{
FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Destination])}, 0)},
[Freight].(
[Container Size].[40RFRDG],
[Version].[Forecast],
[Period].[Current Reporting Year],
[Freight FCL Measure].[Cost(NZD)]) <> 0)
}

Thanks
James

What you are looking for is something IBM has talked about doing for a long time, but I don't see it on the roadmap.

https://bigblue.aha.io/published/30f7b7 ... 10d?page=4

Basically it would be an extension of synchronisation where the target set MDX is dynamically driven by the selection in the source. If that existed it would make PAW a far more useful thing.

[Wim Gielis]

What you are looking for is something IBM has talked about doing for a long time, but I don't see it on the roadmap.

https://bigblue.aha.io/published/30f7b7 ... 10d?page=4

Basically it would be an extension of synchronisation where the target set MDX is dynamically driven by the selection in the source. If that existed it would make PAW a far more useful thing.

Correct.

And as to the roadmap, this link is a bit easier: http://ibm.biz/pa-roadmap

There is also http://ibm.biz/ca-roadmap
for Cognos Analytics.

[Mark RMBC]

Hi,

I ran into this issue in 2021, as per this link on the IBM forum:

https://community.ibm.com/community/use ... -cube-view

I was hoping for a synchronisation solution by now!

having said that when you say,

Sometimes simple is best

That more often than not should mean, simple for the user. And to be fair the solution Burnstripe described was pretty much the solution we use, and for the User, it is pretty simple.

Actually it is probably simpler than your excel solution!

regards,

Mark

[JDLove]

Thanks for the replies,

Given the support for burnstripe I simply must trial this

Very interesting links again thank you all for those.
Hope this helps others who come across this seemingly simple need in PAW.

[JDLove]

Thanks, this approach works as expected, took me some time to follow the MDX but worth the effort to reuse. I found the link provided and detailed example and workings needed for my understanding. It was a good complete example quite close to what I was wanting.

by burnstripe » Sat Oct 21, 2023 5:21 am Check out the link below. You could create a cube with two dimensions, one with the }clients dim, the other with elements container size and destination.

The container size would have a picklist showing available sizes. The user would make, store their chosen selection (against their userid tm1user function can be used to determine this) in this cube.

CubeName = User Selection, 2 dimensional, Dims[ {Clients, Measure]
Measures:[Container Selection, Country Selection, Port Selection]

Picklist for Measures
Container Selection ( Just used a basic Subset to LEVEL0 of Dimension )
Country Selection (See below MDX used for Picklist Subset)
Port Selection (See Below MDX used for Picklist Subset)

The Filter needs refining as its quite slow when a Container picklist member is changed.(working on this).

Used Arc MDX Lab to work my way through the steps and see results, was quite usefull.

Code: Select all

--Returns User Element
{StrToMember("[}Clients].[" + USERNAME + "]")}

--Container Selection
{StrToMember("[Container Size].[Container Size].[" + [User Selection].(
              StrToMember("[}Clients].[" + USERNAME + "]"),
              [User Selection Measure].[Container Selection]
            ) + "]")}
            
--Country Selection
{StrToMember("[Destination].[Destination].[" + [User Selection].(
              StrToMember("[}Clients].[" + USERNAME + "]"),
              [User Selection Measure].[Country Selection]
            ) + "]")}
            
--Port Selection
{StrToMember("[Destination].[Destination].[" + [User Selection].(
              StrToMember("[}Clients].[" + USERNAME + "]"),
              [User Selection Measure].[Port Selection]
            ) + "]")}

-- Picklist SUBSET Country Selection
{FILTER(
      {EXCEPT({TM1DRILLDOWNMEMBER({[Destination].[Destination].[All Countries]}, ALL)},
              {[Destination].[Destination].[All Countries],[Destination].[Destination].[Unspec Destination]})
      },
        [Freight FCL].(
        StrToMember("[Container Size].[Container Size].[" + [User Selection].(StrToMember("[}Clients].[" + USERNAME + "]"),[User Selection Measure].[Container Selection]) + "]"),
        [Version].[Version].[Working Forecast],
        [Period].[Period].[Current Reporting Year],
        [Freight FCL Measure].[Freight FCL Measure].[JDE Cost(NZD)]  
        ) <> 0)
}

-- Picklist SUBSET Port Selection
{FILTER(
        StrToMember("[Destination].[Destination].[" + [User Selection].(StrToMember("[}Clients].[" + USERNAME + "]"),[User Selection Measure].[Country Selection]) + "]").Children,
        [Freight FCL].(
        StrToMember("[Container Size].[Container Size].[" + [User Selection].(StrToMember("[}Clients].[" + USERNAME + "]"),[User Selection Measure].[Container Selection]) + "]"),
        [Version].[Version].[Working Forecast],
        [Period].[Period].[Current Reporting Year],
        [Freight FCL Measure].[Freight FCL Measure].[JDE Cost(NZD)]  
        ) <> 0)
}

Thanks

[JohnO]

What you are looking for is something IBM has talked about doing for a long time, but I don't see it on the roadmap.

https://bigblue.aha.io/published/30f7b7 ... 10d?page=4

Basically it would be an extension of synchronisation where the target set MDX is dynamically driven by the selection in the source. If that existed it would make PAW a far more useful thing.

Correct.

And as to the roadmap, this link is a bit easier: http://ibm.biz/pa-roadmap

Just noticed this under "Analyst Asset Authoring" for 2024 Q4

"Set synchronization method between widget | Determine the method used to set members in synchronized widgets when selecting a member. Options may include 'Expand to....children, parents, descendants' and 'Replace with....children, parents, descendants'"

This is good but I was hoping it would be possible to set your own MDX with a replaceable parameter to match that in the synchronisation source.

[JDLove]

An update, I ended up reverting to Active Forms as the MDX was taking too long to run.
Good exercise and will benefit in other use cases but for this report/calculator it was terribly slow taking about 30 seconds to refresh.