Splitting tilda seperated text into columns

[Hogardy (Pierre Hogue)]

Hello,

Right now we have 3 plants, Soon we will add 4 others when we go live. Not all users have access to all the plants. So I am trying to create a BAQ I will use in my dashboards to allow to switch plants when viewing data. But I only want to show in the selection list, the plants allowed to the current user.

I found a function called STRING_SPLIT. I used it in MSSQL as this: which the result would be the list of the allowed plants to the user

How do I implement such functionality? I tried directly to use the function and it did not recongnized it.

Given the current user, and current company, only list the plants in PlantList field.

thank you
Pierre

[ckrusen (Calvin Krusen)]

Here’s a hack of a solution (maybe a better one further down in the topic)

String_split in BAQ

To add on to @jdtrent’s comment… Make a sub that returns the top 100 rows(or whatever the max number of fields you expect) of a table that has at least that many rows (parttran always has lots of rows). It only needs 2 fields: Company (just something to link it to the next subquery) Calc field: Name: RowNum Type integer Expression: ROW_NUMBER() OVER(ORDER BY PartTran.TranNum) Now make a subquery with the table containing the field to split , and the above subquery. The Calc field wil…