Search code examples
reporting-servicesreplaceexpressionreporting

SSRS: "Contains an error: [BC302205] End of statement expected."

I have created a nested replace expression which will change the string in the field based on the current value. I almost have it working, however I am unsure what I need to add in to end the statement. Can anyone help?

 =Replace(
Replace(
REPLACE (Fields!DeviceType.Value, "1", "Desktop / Laptop / Server"),
"2", "Server"),
"3", "Printer"),
"4", "Firewall"),
"5", "Managed Switch"),
"6", "Switch"),
"7", "Access Point Controller"),
"8", "Access Point"),
"9", "Desk Phone"),
"10", "Modem"),
"11", "Mobile Phone"),
"12", "DVR"),
"13", "Camera"),
"14", "NAS / SAN"),
"15", "PBX"),
"16", "UPS"),
"17", "Router"),
"18", "Monitor"),
"19", "Docking Station")
Solution

  • The REPLACE function replaces one piece of text with another within a string so =REPLACE("Mobile Phone", "Mobile", "Cell") would return "Cell Phone". In your case the REPLACE function is not what you need.

    Ideally you would have a database table to join to and get the descriptions from there but I'll assume you can't do this for whatever reason.

    Therefore what you need is the SWITCH function.

    =SWITCH (
    Fields!DeviceType.Value = "1", "Desktop / Laptop / Server",
    Fields!DeviceType.Value = "2", "Server",
    Fields!DeviceType.Value = "3", "Printer",
    Fields!DeviceType.Value = "4", "Firewall",
    Fields!DeviceType.Value = "5", "Managed Switch",
    Fields!DeviceType.Value = "6", "Switch",
    Fields!DeviceType.Value = "7", "Access Point Controller",
    Fields!DeviceType.Value = "8", "Access Point",
    Fields!DeviceType.Value = "9", "Desk Phone",
    Fields!DeviceType.Value = "10", "Modem",
    Fields!DeviceType.Value = "11", "Mobile Phone",
    Fields!DeviceType.Value = "12", "DVR",
    Fields!DeviceType.Value = "13", "Camera",
    Fields!DeviceType.Value = "14", "NAS / SAN",
    Fields!DeviceType.Value = "15", "PBX",
    Fields!DeviceType.Value = "16", "UPS",
    Fields!DeviceType.Value = "17", "Router",
    Fields!DeviceType.Value = "18", "Monitor",
    True, "Unknown Device Type")

    The final expression acts like an ELSE in case there is some data you have not covered in your expression.

    If this does not work, check that there are no leading/trailing spaces. If there are then you will need to use TRIM (TRIM(Fields!DeviceType.Value) = ...)

    Also check that the field is text, if it's actually numeric, remove the quotes from around the numbers.