March 12, 2019 at 3:10 pm
Hello,
I have an expression written for a textbox in a tablix. I'm getting an error that I can't seem to resolve. Here is the error:
The Value expression for the textrun ‘c_RegularAccountCode.Paragraphs[0].TextRuns[0]’ contains an error: [BC32017] Comma, ')', or a valid expression continuation expected.
Not real helpful... I have tried two methods in my expression to get the desired result. The SWITCH function is the cleaner and more desired method than nested IIf statements but the error is exactly the same. Here are the two different express
=Switch(Fields!c_AccountGroup.Value LIKE "SCP Group%" AND SUM(Fields!c_SCPCheck.Value, "c_AccountGroup") >= 1 AND InStr("CARAVAN, NRTHFLD, PETCO", Fields!ItemLocation.Value) = 0 AND
NOT(Fields!Location.Value LIKE "B%"), "01-4035-514-01",
Fields!c_AccountGroup.Value LIKE "SCP Group%" AND SUM(Fields!c_SCPCheck.Value, "c_AccountGroup") >= 1 AND InStr("CARAVAN, NRTHFLD, PETCO", Fields!ItemLocation.Value) = 0 AND
Fields!Location.Value LIKE "B%", "01-4035-514-02",
Fields!c_AccountGroup.Value LIKE "Fees & Disc%" AND SUM(Fields!c_SCPCheck.Value, "c_AccountGroup") >= 1 AND InStr("CARAVAN, NRTHFLD, PETCO", Fields!ItemLocation.Value) > 0 AND
NOT(Fields!Location.Value LIKE "B%"), "01-4035-514-03",
Fields!c_AccountGroup.Value LIKE "Hills%" AND NOT(Fields!Location.Value LIKE "B%"), "01-4040-519-01",
Fields!c_AccountGroup.Value LIKE "Hills%" AND Fields!Location.Value LIKE "B%", "01-4040-519-02",
Fields!c_AccountGroup.Value LIKE "Sales & Disc%" AND Fields!ManufacturerCode.Value = "ZUPREEM" AND NOT(Fields!Location.Value LIKE "B%"), "01-4040-519-01",
Fields!c_AccountGroup.Value LIKE "Sales & Disc%" AND Fields!ManufacturerCode.Value = "ZUPREEM" AND Fields!Location.Value LIKE "B%", "01-4040-519-02",
Fields!c_AccountGroup.Value LIKE "Development%" AND NOT(Fields!Location.Value LIKE "B%"), "01-4085-532-01",
Fields!c_AccountGroup.Value LIKE "Development%" AND Fields!Location.Value LIKE "B%", "01-4085-532-02",
Fields!c_AccountDiscountQuantityXPrice.Value = "5.00" AND NOT(Fields!Location.Value LIKE "B%"), "01-4040-519-01",
Fields!c_AccountDiscountQuantityXPrice.Value = "5.00" AND Fields!Location.Value LIKE "B%", "01-4040-519-02",
True, MAX(Fields!c_DetailRegularAccountCode.Value, "c_AccountGroup"))
AND
=IIf(Fields!c_AccountGroup.Value LIKE "SCP Group%" AND SUM(Fields!c_SCPCheck.Value, "c_AccountGroup") >= 1 AND InStr("CARAVAN, NRTHFLD, PETCO", Fields!ItemLocation.Value) = 0 AND
NOT(Fields!Location.Value LIKE "B%"), "01-4035-514-01",
IIf(Fields!c_AccountGroup.Value LIKE "SCP Group%" AND SUM(Fields!c_SCPCheck.Value, "c_AccountGroup") >= 1 AND InStr("CARAVAN, NRTHFLD, PETCO", Fields!ItemLocation.Value) = 0 AND
Fields!Location.Value LIKE "B%", "01-4035-514-02",
IIf(Fields!c_AccountGroup.Value LIKE "Fees & Disc%" AND SUM(Fields!c_SCPCheck.Value, "c_AccountGroup") >= 1 AND InStr("CARAVAN, NRTHFLD, PETCO", Fields!ItemLocation.Value) > 0 AND
NOT(Fields!Location.Value LIKE "B%"), "01-4035-514-03",
IIf(Fields!c_AccountGroup.Value LIKE "Hills%" AND NOT(Fields!Location.Value LIKE "B%"), "01-4040-519-01",
IIf(Fields!c_AccountGroup.Value LIKE "Hills%" AND Fields!Location.Value LIKE "B%", "01-4040-519-02",
IIf(Fields!c_AccountGroup.Value LIKE "Sales & Disc%" AND Fields!ManufacturerCode.Value = "ZUPREEM" AND NOT(Fields!Location.Value LIKE "B%"), "01-4040-519-01",
IIf(Fields!c_AccountGroup.Value LIKE "Sales & Disc%" AND Fields!ManufacturerCode.Value = "ZUPREEM" AND Fields!Location.Value LIKE "B%", "01-4040-519-02",
IIf(Fields!c_AccountGroup.Value LIKE "Development%" AND NOT(Fields!Location.Value LIKE "B%"), "01-4085-532-01",
IIf(Fields!c_AccountGroup.Value LIKE "Development%" AND Fields!Location.Value LIKE "B%", "01-4085-532-02",
IIf(Fields!c_AccountDiscountQuantityXPrice.Value = "5.00" AND NOT(Fields!Location.Value LIKE "B%"), "01-4040-519-01",
IIf(Fields!c_AccountDiscountQuantityXPrice.Value = "5.00" AND Fields!Location.Value LIKE "B%", "01-4040-519-02", MAX(Fields!c_DetailRegularAccountCode.Value, "c_AccountGroup")
)))))))))))
In both cases, when I take out conditions two and three, the report runs perfectly. I just can't seem to figure out where it thinks I'm missing a closing parenthesis or comma. I'm hoping an extra set of eyes will spot my error. If you need anything further to assist, please let me know. I didn't think a dataset would be useful in this case plus since it is financial, I don't have permission to share, sorry! I'm happy to provide the .RDL if that helps.
March 12, 2019 at 3:45 pm
Ok, I'm NOT answering my own question because this doesn't make sense to me. Something made me think I needed to include each criteria in parenthesis for the first three options and it runs. So, an explanation of why the following works (and the data is correct) but the above does not would be fabulous.
=Switch((Fields!c_AccountGroup.Value LIKE "SCP Group%") AND (SUM(Fields!c_SCPCheck.Value, "c_AccountGroup") >= 1) AND (InStr("CARAVAN, NRTHFLD, PETCO", Fields!ItemLocation.Value) = 0) AND
(NOT(Fields!Location.Value LIKE "B%")), "01-4035-514-01",
(Fields!c_AccountGroup.Value LIKE "SCP Group%") AND (SUM(Fields!c_SCPCheck.Value, "c_AccountGroup") >= 1) AND (InStr("CARAVAN, NRTHFLD, PETCO", Fields!ItemLocation.Value) = 0) AND
(Fields!Location.Value LIKE "B%"), "01-4035-514-02",
(Fields!c_AccountGroup.Value LIKE "Fees & Disc%") AND (SUM(Fields!c_SCPCheck.Value, "c_AccountGroup") >= 1) AND (InStr("CARAVAN, NRTHFLD, PETCO", Fields!ItemLocation.Value) > 0) AND
(NOT(Fields!Location.Value LIKE "B%")), "01-4035-514-03",
Fields!c_AccountGroup.Value LIKE "Hills%" AND NOT(Fields!Location.Value LIKE "B%"), "01-4040-519-01",
Fields!c_AccountGroup.Value LIKE "Hills%" AND Fields!Location.Value LIKE "B%", "01-4040-519-02",
Fields!c_AccountGroup.Value LIKE "Sales & Disc%" AND Fields!ManufacturerCode.Value = "ZUPREEM" AND NOT(Fields!Location.Value LIKE "B%"), "01-4040-519-01",
Fields!c_AccountGroup.Value LIKE "Sales & Disc%" AND Fields!ManufacturerCode.Value = "ZUPREEM" AND Fields!Location.Value LIKE "B%", "01-4040-519-02",
Fields!c_AccountGroup.Value LIKE "Development%" AND NOT(Fields!Location.Value LIKE "B%"), "01-4085-532-01",
Fields!c_AccountGroup.Value LIKE "Development%" AND Fields!Location.Value LIKE "B%", "01-4085-532-02",
Fields!c_AccountDiscountQuantityXPrice.Value = "5.00" AND NOT(Fields!Location.Value LIKE "B%"), "01-4040-519-01",
Fields!c_AccountDiscountQuantityXPrice.Value = "5.00" AND Fields!Location.Value LIKE "B%", "01-4040-519-02",
True, MAX(Fields!c_DetailRegularAccountCode.Value, "c_AccountGroup"))
March 13, 2019 at 8:15 am
Mu gut says the Switch function requires each "condition" to be either "singular", or that it be enclosed in parentheses. just using AND between the conditions needed is apparently not enough. Given what Switch has to do, I think that's a reasonable rule to ensure the parsing of the function happens correctly. It's just yet another example of something MS should have documented, but probably didn't.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply