04-21-2023 04:08 PM - last edited on 05-24-2023 12:15 PM by JackLacava
Hello!
My brain is mush so I'm posting this for the bigger audience to please help me brainstorm or answer me directly if it's possible 🙂
Can I use a middle digit on an account name in a member filter? For example, the 4th digit of our cost centers represents location but I'm wondering if I can actually do anything with that. I want to list all CCs with a 4 in that spot but can't quite figure out a good member filter that works. I tried ? like the placeholders in transformation rules and * to no avail. Here's a sample member filter for reference (I'm using it in a QV):
A#RD_tot.base.where(name starts with 'CC_SAP_***4')
Any thoughts? I could add that fourth digit in a text field or otherwise create an alternate hierarchy but I'm wondering if there's a way to utilize it as is. Any thoughts are welcome!
Thanks & happy Friday!
04-21-2023 06:22 PM
Hi Nicole! Can you be more specific about where you're doing this member filter? Is it in a member formula? Or in a Cube View or Dashboard? Thanks.
~ Paula
(PS Congrats on the award at Splash!)
04-24-2023 07:58 AM
Thanks for the kind words! I'm using it in a quick view.
04-21-2023 08:54 PM
Hmmm, I don't think wild card works in member filter. I guess you could create a business rule and ask the use to pass the mid number and get those members which has the 4. I guess that will be the least effort one. Else like you said you can add that as a text attribute and do it that way as well.
04-22-2023 12:44 PM - edited 04-22-2023 12:47 PM
Hi Nicole
As mentioned by ckattokaran, you can use a custom (business rule based) list for this purpose, here you can use Linq queries, string operations, regular expressions and more to solve your problem. Custom lists can be used with QVs.
But, I do not believe, that this kind of queries against label names are a good solution. I prefer to use the text arguments for this purpose.
Cheers
Christian
04-24-2023 02:18 AM
Hi Nicole,
You can set a specific Text attribute say Text2 for all the CCs with a 4 in the required spot "XYZ" and use that in your member filter.
A#RD_tot.base.where(Text2 = XYZ)
This will be a maintenance task if your CCs change, but picking these using Text attribute would be easier.
Hope this helps.
04-24-2023 04:36 AM
If your fourth digit is the last and you always have 4 digits, you can combine Where clauses: A#RD_tot.Base.Where((Name StartsWith 'CC_SAP_') and (Name EndsWith 4))
If any of those conditions do not apply, though (i.e. more digits can appear after the fourth, and/or there is a variable number of digits), then you need Text properties (no coding required) or a Custom Member List or XFBR (coding required).
04-24-2023 08:00 AM
Hello!
The CCs are 10 digits long, after the CC_SAP_, so the 4th digit is in the middle. Thanks though!
04-24-2023 08:02 AM
Thanks all - I figured it'd be more work than just a member filter but was hoping I was wrong!! Appreciate all the responses!