SSRS expression not working from tester tool for SQL Server 2008

Question

I have expression like this:

 (""" & Join(Parameters!Projects.Value, """,""")  & """)

When I'm trying to evaluate this using "Expression tester tool for SQL Server 2008"

Missing part of required element such as parenthesis. What is wrong with this?

Here is my original string (working good for report):

=(IIf(Parameters!Projects.Count > 0, 
 "(project in (""" & 
    Join(Parameters!Projects.Value, """,""") 
 & """)", "(")
 &
  IIf(Parameters!Statuses.Count > 0,
 IIf(Parameters!Projects.Count > 0, " and ", "")
 &
 "status in ("'" & 
    Join(Parameters!Statuses.Value, "'",""")
 & """)", "")
  &
  IIf(Parameters!Issue_Types.Count > 0,
 IIf(Parameters!Projects.Count > 0 Or Parameters!Statuses.Count > 0, " and ", "") 
 &
 "issueType in ("'" &
    Join(Parameters!Issue_Types.Value, "'",""")
 & """))", ")")) & " or issueKey=""GRL-1"" order by Created asc"

I changed it like this:

=(IIf(Parameters!Projects.Count > 0, 
 "(project in ("'" & 
    Join(Parameters!Projects.Value, "'","'") 
 & "'")", "(")
  &
  IIf(Parameters!Statuses.Count > 0,
     IIf(Parameters!Projects.Count > 0, " and ", "")
     &
 "status in ("'" & 
    Join(Parameters!Statuses.Value, "'","'")
 & "'")", "")
  &
      IIf(Parameters!Issue_Types.Count > 0,
     IIf(Parameters!Projects.Count > 0 Or Parameters!Statuses.Count > 0, " and    ", "") 
 &
     "issueType in ("'" &
        Join(Parameters!Issue_Types.Value, "'","'")
     & "'"))", ")")) & " or issueKey=""GRL-1"" order by Created asc"

to get my expression evaluted like :

{"jql": "(project in ('Change Instructions')) and status in ('Open') and  issueType in ('Fees') or issueKey ='GR L-1' order by Created asc" }

But, it shows an error:

Argument not specified for paramter 'FalsePart' of public function IFF(Expression As Boolean,TruePart as Object, FalsePart as Object) As Object.

How can I get desired string ?Is there any other tool available to evaluate expression for SSRS?

Hannover Fist Hannover Fist · Accepted Answer · 2015-08-12T17:08:48

The issue (or at least one) is in your JOIN statement.

The original JOIN uses the Projects field with "," as the delimiter (double quote comma double quote)

Join(Parameters!Projects.Value, " "","" ")

I added the extra spaces to show how it interprets it. The consecutive double quotes resolve to one double quote.

I don't think your second and third JOINs in the original should not work due to an extra argument after the single quote delimiter. Not sure why you're not getting an error.

Join(Parameters!Statuses.Value, "'",""")

JOIN description

I think this is what you need:

=IIf(Parameters!Projects.Count > 0, 
    "(project in ('" & Join(Parameters!Projects.Value, "','") & "')", 
    "(")
&
IIf(Parameters!Statuses.Count > 0,
    If(Parameters!Projects.Count > 0, " and ", "")
        & "status in ('" & Join(Parameters!Statuses.Value, "','")& "')",
    "")
&
IIf(Parameters!Issue_Types.Count > 0,
    If(Parameters!Projects.Count > 0 Or Parameters!Statuses.Count > 0, " and ", "") 
        & "issueType in ('" & Join(Parameters!Issue_Types.Value, "','") & "'))", 
    ")") & " or issueKey = 'GRL-1' ORDER BY Created ASC"

SSRS expression not working from tester tool for SQL Server 2008

1 Answers