Filtering using a substring

[Chamopak]

Hi I have a set created showing only the leaf members(GLs):
e.g.

GLName:
filter( members ( [xxxxzxxx].[xxxxxxxxx].[Account Detail] ),
        count(1 within set children( currentMember(
         [xxxxxxx].[xxxxxxxxx].[Account Detail] )  )  ) = 0 )

WORKS

I created a simple way to just display the GL numbers:
substring(caption([GLName]),1,7)
WORKS when displayed via a list but only need to use this as a filter.

The idea behind this is to have to use just the GL numbers (GLNR) to filter out unwanted GLs (or keep wanted GLS)

e.g. GLNR in (100000 , 200000,  400000, 700000, 900000)

or GLNR NOT in (300000, 500000, 600000, 800000)

and not need to go for GLName in ('100000 - Ham Cheese bacon', '200000 - Lettice on the side', '400000  - Just dessert', '700000 - Beer please' , '900000 - I am vegan')


Can someone please help me in the right direction?

[hellokimy310]

Can you keep it in the list but just hide the column? Then you can filter it in the query....does that make sense? Or if you click on the list properties, under data --> properties, you can click the corresponding column and it should work without the data item in the list.

[Chamopak]

Can you keep it in the list but just hide the column? Then you can filter it in the query....does that make sense? Or if you click on the list properties, under data --> properties, you can click the corresponding column and it should work without the data item in the list.

Thank you, the problem is that I need to then put it in a crosstab.
In the list it works with no problem at all but as soon as a Put it in a crosstab I get "No Data Available"   :'( :'( :'( :'( :'( :'( :'( :'( :'(

[hellokimy310]

Would a filter on the GLNR that looks like the following work?

GLNR like ('100000 - %')
OR
GLNR like ('200000 - %')
OR
GLNR like ('400000 - %')
etc etc

[Chamopak]

IT works but it times out at the amount of GL I truly have to process
>300

thanks for the help though.