Exclude multiple texts prefix from the Text Field

Amzad · July 3, 2024, 1:36pm

Hello Team,

We are having field Deliverable Doc ID, which is a text week trying to exclude Text which stated from ZOQT-1234 and N/A.

I have tried multiple ways using this formula is excluding the text from the filter, but still I am able to see the issues with the same values, please let me know if I am doing anything wrong

I want to exclude the text which starts with ZOQT & N/ in the below report

Aggregate(
Filter(
[Deliverable Doc ID].Members,
[Deliverable Doc ID].CurrentHierarchyMember.Name
NOT MATCHES “.ZOQU.|.N/.”
)
)

Report Definition
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Business Steps Testing Indicator Overview”,
“folder_name”: “LDC - Build - Testing (OQT/PQT)”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Issues created]”],“members”:},{“name”:“R2.0 Testing Indicator”,“selected_set”:[“[R2.0 Testing Indicator].[All R2.0 Testing Indicators]”],“members”:[{“depth”:0,“name”:“All R2.0 Testing Indicators”,“full_name”:“[R2.0 Testing Indicator].[All R2.0 Testing Indicators]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:1,“name”:“(none)”,“full_name”:“[R2.0 Testing Indicator].[(none)]”,“removed”:true,“parent_full_name”:“[R2.0 Testing Indicator].[All R2.0 Testing Indicators]”},{“depth”:1,“name”:“Out of Scope”,“full_name”:“[R2.0 Testing Indicator].[Out of Scope]”,“removed”:true,“parent_full_name”:“[R2.0 Testing Indicator].[All R2.0 Testing Indicators]”},{“depth”:1,“name”:“Whitelist (No Retest Required)”,“full_name”:“[R2.0 Testing Indicator].[Whitelist (No Retest Required)]”,“removed”:true,“parent_full_name”:“[R2.0 Testing Indicator].[All R2.0 Testing Indicators]”}],“bookmarked_members”:}]},“rows”:{“dimensions”:[{“name”:“Workstream”,“selected_set”:[“[Workstream].[LDC - Workstreams]”],“members”:[{“depth”:0,“name”:“LDC - Workstreams”,“full_name”:“[Workstream].[LDC - Workstreams]”,“calculated”:true,“drillable”:true,“drilled_into”:false,“removed”:false,“expanded”:true}],“bookmarked_members”:}]},“pages”:{“dimensions”:[{“name”:“Time”,“selected_set”:[“[Time].[Created After Mar 1st, 2024]”],“members”:[{“depth”:0,“dimension”:“Time”,“name”:“Created After Mar 1st, 2024”,“formula”:“Aggregate(\n[Time].[Month].DateMembersBetween(‘Mar 01 2024’, ‘today’)\n)\n”,“format_string”:“”,“full_name”:“[Time].[Created After Mar 1st, 2024]”,“drillable”:true,“annotations”:null,“calculated”:true}],“bookmarked_members”:,“current_page_members”:[“[Time].[Created After Mar 1st, 2024]”]},{“name”:“Issue Type”,“selected_set”:[“[Issue Type].[All Issue Types]”],“members”:[{“depth”:0,“name”:“All Issue Types”,“full_name”:“[Issue Type].[All Issue Types]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Business Step”,“full_name”:“[Issue Type].[Business Step]”,“parent_full_name”:“[Issue Type].[All Issue Types]”}],“bookmarked_members”:,“current_page_members”:[“[Issue Type].[Business Step]”]},{“name”:“Workstream”,“duplicate”:true,“selected_set”:[“[Workstream].[LDC - Workstreams]”],“members”:[{“depth”:0,“name”:“LDC - Workstreams”,“full_name”:“[Workstream].[LDC - Workstreams]”,“calculated”:true,“drillable”:true,“expanded”:false,“drilled_into”:false,“removed”:false}],“bookmarked_members”:,“current_page_members”:[“[Workstream].[LDC - Workstreams]”]},{“name”:“Testing Event”,“selected_set”:[“[Testing Event].[All Testing Events]”],“members”:[{“depth”:0,“name”:“All Testing Events”,“full_name”:“[Testing Event].[All Testing Events]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“(none)”,“full_name”:“[Testing Event].[(none)]”,“parent_full_name”:“[Testing Event].[All Testing Events]”}],“bookmarked_members”:,“current_page_members”:[“[Testing Event].[(none)]”]},{“name”:“Status”,“selected_set”:[“[Status].[All but cancelled / rejected]”],“members”:[{“depth”:0,“name”:“All but cancelled / rejected”,“full_name”:“[Status].[All but cancelled / rejected]”,“calculated”:true,“drillable”:true,“dimension”:“Status”,“formula”:“Aggregate(\n Except(\n [Status].[Status].Members,\n { \n [Status].[Cancelled],\n [Status].[Rejected]\n }\n )\n)”,“format_string”:“”,“annotations”:null,“dependent_calculated_members_count”:0,“dependent_report_ids”:[1197164,1210017,2528683,2528616,2638441,1574859,1606503,1768131,1793543,1768128,1768127,1767920,1561556,1591978,1577002,1664941,1665062,2128714,1685220,1896792,1471977,1626072,1015680,1198716,2393814,2393876,1250648,2147693,2674532,2414551,2103377,2095599,1103908,2426388,2083775,2146463,2119569,2426390,2095563,2159868,2144867,2482532,2482522,2159911,2137141,2099764,1634733,855622,1680780,1195755,2099526,956066,898558,1177766,1183959,2526720,2526282,1080536,1086600,1086598,1086601,1086599,1086602,1086603,1241893,1241896,1236900,1086695,1090193,1086671,1090198,1090196,1236892,1090205,1090204,1175516,1090194,1090191,966458,955255,1605272,957900,2393911,1597400,1704881,2393897,2393812,1173001,1634634,1597348,2641880,1301213,1305727,1181889,2430069,2430085,1688567,1912289,2073293,1903747,1709420,1912311,1688570,1709404,1912305,2377919,2508502,1688569,1709370,1912304,1893145,1683525,1709424,1709407,1912323,2146476,2139821,1709405,1912307,2159873,2139819,2144895,1688571,2159939,2139820,1080490,1634710,2289426,1283178,2159833,1283177,1634708,2137226,1606504,1242124,1242182,1894700,2368156,1894684,2528790,2358918,1374353,1519268,1471592,1709275,1314906,1314863,1314883,1314907,1314882,2146493,2137228,1495920,1495919,1640599,1640595,1634282,1634301,1608810,2105860,2412392,2480999,1636376,2518716,2159895,2163571,2518718,2145817,2139743,1253530,1187213,1213902,1215546,1236898,1086693,956062,1236888,1241892,1241895,1938815,2139714,2518726,2159944,2163572,2429594,2426347,2426514,2391771,2402906,2426423]}],“bookmarked_members”:,“current_page_members”:[“[Status].[All but cancelled / rejected]”]},{“name”:“Release_Version”,“selected_set”:[“[Release_Version].[All Release_Versions]”],“members”:[{“depth”:0,“name”:“All Release_Versions”,“full_name”:“[Release_Version].[All Release_Versions]”,“drillable”:true,“type”:“all”}],“bookmarked_members”:,“current_page_members”:[“[Release_Version].[All Release_Versions]”]},{“name”:“Deliverable Doc ID”,“selected_set”:[“[Deliverable Doc ID].[All but N/A]”,“[Deliverable Doc ID].[All But ZOQT]”,“[Deliverable Doc ID].[All Scripted/Unscripted Test]”,“[Deliverable Doc ID].[ALL tEST 2]”],“members”:[{“depth”:0,“dimension”:“Deliverable Doc ID”,“name”:“ALL tEST 2”,“formula”:“Aggregate({\n[Deliverable Doc ID].[All But ZOQT],\n[Deliverable Doc ID].[All but N/A]\n})”,“format_string”:“”,“full_name”:“[Deliverable Doc ID].[ALL tEST 2]”,“drillable”:true,“annotations”:null}],“bookmarked_members”:,“current_page_members”:[“[Deliverable Doc ID].[ALL tEST 2]”]}]},“options”:{“nonempty”:true,“total”:true},“view”:{“current”:“table”,“maximized”:false,“table”:{“row_dimension_headers”:{“Workstream”:true}}},“calculated_members”:}
} ],
“calculated_members”: [{“name”:“All but cancelled / rejected”,“dimension”:“Status”,“formula”:“Aggregate(\n Except(\n [Status].[Status].Members,\n { \n [Status].[Cancelled],\n [Status].[Rejected],\n [Status].[On Hold]\n }\n )\n)”,“format_string”:“”},{“name”:“Commercial”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Commercial],\n[Workstream].[COM-Customer 360],\n[Workstream].[COM-Evidence-to-Value],\n[Workstream].[COM-Lead-to-Quote],\n[Workstream].[COM-Order-to-Release],\n[Workstream].[COM-Quote-to-Contract],\n[Workstream].[COM-Release-to-Invoice],\n[Workstream].[COM-Strategy-to-Demand],\n[Workstream].[COMP-Pillar: Commercial],\n[Workstream].[DAT-Commercial]\n})”,“format_string”:“”},{“name”:“Finance”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Finance \u0026 Procurement],\n[Workstream].[DAT-Finance \u0026 Controlling],\n[Workstream].[DAT-Customer],\n[Workstream].[FIN-Asset Accounting and Projects],\n[Workstream].[FIN-FC\u0026C (incl. Analytics)],\n[Workstream].[FIN-General Ledger and Period End],\n[Workstream].[FIN-I2C - Invoice 2 Cash],\n[Workstream].[FIN-Operations Finance],\n[Workstream].[FIN-Intercompany],\n[Workstream].[FIN-Revenue Management],\n[Workstream].[FIN-RDF],\n[Workstream].[FIN-Tax],\n[Workstream].[FIN-Treasury and Cash-Management],\n[Workstream].[FINP-Pillar: Finance],\n[Workstream].[DAT-Customer \u0026 Commercial],\n[Workstream].[FIN-RDF and intercompany],\n[Workstream].[FIN-BPA]\n})”,“format_string”:“”},{“name”:“Procurement”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[DAT-Supplier \u0026 Procurement],\n[Workstream].[PUR-Accounts Payable and Supplier Experience (SLP)],\n[Workstream].[PUR-CE and Transactional Direct Procurement],\n[Workstream].[PUR-CE and Transactional Indirect Procurement and Front door],\n[Workstream].[PUR-Strategic Procurement and S2C],\n[Workstream].[PUR-S2P],\n[Workstream].[SEP-Supplier Exp - Supplier Information Mgmt],\n[Workstream].[PURP-Pillar: Procurement],\n[Workstream].[SEP-Supplier Exp- Supplier Data Quality]\n})”,“format_string”:“”},{“name”:“Operations”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Operations],\n[Workstream].[DAT-Material \u0026 Operations],\n[Workstream].[DAT-Product \u0026 Brand],\n[Workstream].[OPS-Manufacturing - Asset Management],\n[Workstream].[OPS-Manufacturing - Manufacturing],\n[Workstream].[OPS-SCM - Demand Planning],\n[Workstream].[OPS-SCM - External Manufacturing],\n[Workstream].[OPS-SCM - Intercompany Order Management / Customs and Trade Compliance],\n[Workstream].[OPS-SCM - Lifecycle Management and Implementation],\n[Workstream].[OPS-SCM - Logistics/Warehousing],\n[Workstream].[OPS-SCM - Long Term, Demand and Supply Planning],\n[Workstream].[OPS-SCM - Production planning],\n[Workstream].[OPS-SCM - Supply Planning],\n[Workstream].[OPS-TRD - Clinical Supply],\n[Workstream].[OPS-TRD - Embedded],\n[Workstream].[SCM Supply Chain Management],\n[Workstream].[OPS-Quality (incl. OBBR)],\n[Workstream].[OPS-SCM - Ariba SCC],\n[Workstream].[OPSP-Pillar: Manufacturing],\n[Workstream].[OPSP-Pillar: Quality],\n[Workstream].[OPSP-Pillar: Supply Chain],\n[Workstream].[MOM - Masters of Material],\n[Workstream].[OPSP-Pillar: TRD]\n})”,“format_string”:“”},{“name”:“LDC - Workstreams”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[Commercial],\n[Workstream].[Finance],\n[Workstream].[Procurement],\n[Workstream].[Operations],\n[Workstream].[Solutions],\n[Workstream].[DAT-Migration],\n[Workstream].[N/A],\n[Workstream].[Unmatch],\n[Workstream].[(none)]\n})”,“format_string”:“”},{“name”:“Solutions”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Integration],\n[Workstream].[DAT-Integration],\n[Workstream].[SOL-BPM],\n[Workstream].[SOL-Build Management],\n[Workstream].[SOL-Data],\n[Workstream].[SOL-Design Management],\n[Workstream].[SOL-Learning],\n[Workstream].[SOL-Platform Management],\n[Workstream].[SOL-Platform Management Basis],\n[Workstream].[SOL-Platform Management Integration],\n[Workstream].[SOL-R\u0026A],\n[Workstream].[SOL-Security \u0026 Authorization],\n[Workstream].[SOL-Solution Core \u0026 Integration],\n[Workstream].[SOL-Solution Methodology],\n[Workstream].[SOL-Technology \u0026 Architecture],\n[Workstream].[SOL-BITC-NFCM]\n})”,“format_string”:“”},{“name”:“ZOQU-Unscripted Test”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate({\nFilter(\n[Deliverable Doc ID].Members,\n[Deliverable Doc ID].CurrenthierarchyMember.Name MATCHES (".ZOQU.")\n)\n})”,“format_string”:“”},{“name”:“ZOQT-Scripted Test”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate({\nFilter(\n[Deliverable Doc ID].Members,\n[Deliverable Doc ID].CurrenthierarchyMember.Name MATCHES (".ZOQT.")\n)\n})”,“format_string”:“”},{“name”:“All Scripted/Unscripted Test”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate({\n[Deliverable Doc ID].[ZOQU-Unscripted Test],\n[Deliverable Doc ID].[ZOQT-Scripted Test]\n})”,“format_string”:“#,##0”},{“name”:“Created After Mar 1st, 2024”,“dimension”:“Time”,“formula”:“Aggregate(\n[Time].[Month].DateMembersBetween(‘Mar 01 2024’, ‘today’)\n)\n”,“format_string”:“”},{“name”:“All But ZOQT”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate(\n Filter(\n [Deliverable Doc ID].Members,\n [Deliverable Doc ID].CurrentHierarchyMember.Name \n NOT MATCHES ".ZOQU.|.N/.|.n/."\n )\n)”,“format_string”:“”},{“name”:“All but N/A”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate({\nFilter(\n[Deliverable Doc ID].Members,\n[Deliverable Doc ID].CurrenthierarchyMember.Name NOT MATCHES (".N/.")\n)\n})”,“format_string”:“”},{“name”:“ALL tEST 2”,“dimension”:“Deliverable Doc ID”,“formula”:“Aggregate({\n[Deliverable Doc ID].[All But ZOQT],\n[Deliverable Doc ID].[All but N/A]\n})”,“format_string”:“”}]
}

_P3 · July 5, 2024, 9:53am

selecting filter rows on the measure, not matches, then ZOQU.|.N/.
You can the remove the measure field if you don’t want it (Don’t know if you selected this in your rows.

Amzad · July 10, 2024, 7:48am

This does not clear issues even after doing this I was not able to exclude the values in text field.

When I have tried to create below calculated member in the dimension as below
Aggregate({
Filter(
[Deliverable Doc ID].Members,
[Deliverable Doc ID].CurrenthierarchyMember.Name NOT MATCHES (“.ZOQT.”)
)
})

This Worked, but when I add the or condition in the above measure as below
Aggregate(
Filter(
[Deliverable Doc ID].Members,
[Deliverable Doc ID].CurrentHierarchyMember.Name
NOT MATCHES “.ZOQU. |.N/. ”
)
)
This is not working

when I try to combine both the calculated members as below and select this giving me the error.
Aggregate({
[Deliverable Doc ID].[All But ZOQT],
[Deliverable Doc ID].[All but N/A]
})

nauris.malitis · July 12, 2024, 7:22am

Hi @Amzad

You’re on the right path, however, in the Filter function you should specify which level members do you want to iterate through.

Also, the regular expression that you use for matching, needs to be improved to accommodate the forward slash, which is a special symbol in regex.

Please try the following formula:

Aggregate(
  Filter(
    [Deliverable Doc ID].[Deliverable Doc ID].Members,
    [Deliverable Doc ID].CurrentHierarchyMember.Name NOT MATCHES "ZOQU.*|N\/.*"
  )
)

For the other formula this should work as expected:

Aggregate(
  Filter(
  [Deliverable Doc ID].[Deliverable Doc ID].Members,
  [Deliverable Doc ID].CurrentHierarchyMember.Name NOT MATCHES "ZOQT.*"
  )
)

Let me know if you have any additional questions on this!
Best regards,
Nauris

Amzad · July 19, 2024, 12:34pm

Aggregate(
Filter(
[Deliverable Doc ID].[Deliverable Doc ID].Members,
[Deliverable Doc ID].CurrentHierarchyMember.Name NOT MATCHES “ZOQT.|N/.”
)
)

I have tried using the above measure, but this is giving the error as below

Please let me know what is causing this error

_P3 · September 20, 2024, 7:34am

I think you have to put the multiplication (star) behind the dot

nauris.malitis · September 23, 2024, 10:37am

Thanks @_P3 for replying, I totally missed this.

@Amzad, there is a limitation of 1000 members that the Aggregate function can aggregate.

If you have more than a thousand “Deliverable Doc ID” members, then this approach will not work.
You can, however, define a new calculated JavaScript field to check the names during the import process and assign the necessary group accordingly. You can then import this field as a dimension in eazyBI.

Let me know if you need assistance with this!
Best regards,
Nauris

Adon_Biju · September 24, 2024, 11:58am

@nauris.malitis @Amzad @_P3

I wanted to check if there is any Eazibi (jira) API available that supports adding users to an Project with roles such as dashboard viewer,admin etc… Could you kindly provide any relevant documentation or guidance on how this can be achieved?

nauris.malitis · September 24, 2024, 12:17pm

Hi @Adon_Biju

Unfortunately, there are no APIs to configure any settings in eazyBI.
The users or user groups can only be manually added to the eazyBI accounts.

We only provide one API and that is to export report results: Report results export API

Best regards,
Nauris

Adon_Biju · September 27, 2024, 3:53am

@nauris.malitis Thank you for your response!