question

ZuluGupta-7007 avatar image
0 Votes"
ZuluGupta-7007 asked Joyzhao-MSFT edited

SSRS multi value array is returning only highest /last index value for all column of different arrays instead conditions are applied

=SWITCH(
Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some xyz Value") = "-1","NA",
Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some xyz Value") = "0", Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Created.Value,"Dataset1"), ","),",").GetValue(0)),
Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some xyz Value") = "1",
Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Created.Value,"Dataset1"), ","),",").GetValue(1),
Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some xyz Value") = "2",
Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Created.Value,"Dataset1"), ","),",").GetValue(2)
)





Below is my logic in this switch condition I am trying to implement below logic but each time only highest index value is returning for different arrays.

    Array used for below 1 point = []

     Array used for below 2 point  = ["1st value"]

    Array used for below  3 point  = ["1st value" , "2nd value"]

    Array used for below  4 point  = ["1st value" , "2nd value" , "3rd value"]

    1. If some value is not found in array then array index of is returning -1 and it should print NA as per logic in switch.

    2. If some value is found on array index 0 then array index of is returning 0 and it should fetch created date of index 0 (1st value)

    3. If some value is found on array index 1 then array index of is returning 1 and it should fetch created date on index 1 (2nd value)

    4.If some value is found on array index 1 then array index of is returning 2 and it should fetch created date on index 2 (3rd value)

    But currently it is returning 3rd value only for point 4 where array length is 3 but for others it is returning error where array length is 2, 1 or 0  because it is checking length 3 there as well don't know why Ideally it should work based on array index condition.


```

  `=SWITCH(
         
     Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some xyz Value") = -1,"NA",
         Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some xyz Value") = 0, Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Created.Value,"Dataset1"), ","),",").GetValue(0)),
         Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some xyz Value") = 1,Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Created.Value,"Dataset1"), ","),",").GetValue(1),
         Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some xyz Value") = 2,Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Created.Value,"Dataset1"), ","),",").GetValue(2)
         )`

```
Does anyone is having a solution or fix for this . Thanks in advance

sql-server-reporting-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ZuluGupta-7007
From the Switch expression, there is no error in the switch part. I am not sure if indexOf() is wrong. You could refer to the Array.IndexOf method for troubleshooting.
What is your report like? Did you add custom code to the report? Are you willing to share any data that can be used for testing?
Best Regards,
Joy

0 Votes 0 ·

1 Answer

ZuluGupta-7007 avatar image
0 Votes"
ZuluGupta-7007 answered Joyzhao-MSFT edited

Hi Joyzhao-MSFT ,

Thanks for your reply .

Array index of is working fine . I have checked with below formula which is returning values properly.
=SWITCH(
Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some value") = -1,"NA",
Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some value") = 0, "0",
Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some value") = 1, "1",
Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some value") = 2, "2"
Array.IndexOf(Split(Join(LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Team.Value,"Dataset1"), ","),","), "Some value") = 3, "3"
)


But when there is blank value or single string I am not able to fetch value inside switch although that formula return value if i use that separately..

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your feedback. Could you share some test data?

0 Votes 0 ·