0

I want to use the "split" function in a simple query on my SSRS 2008 report. However, I get an error "Query execution failed for dataset "SlsmRealNum". "Split" is not a recognized built-in function name". But it's listed as a common function (text) if I open up the Expression box on the query, so not sure why it's failing?

my simple select statement is: 

select slsm_num, slsm_msid from Salesman where slsm_msid = split(User.UserID,"\").GetValue(1)

right now to get the report to work, I have one parameter (SlsmnNum) that has the Split expression in it (to get the MSID of the user) and then a 2nd parameter that uses the above query in the Dataset Salesrepum using the @SlsmnNum parameter as the MSID. I'd like to not have to have 2 parapmeters if possible and just get the actualy salesrep # in just one. Any help is greatly appreciated!

Kim Jones
  • 123
  • 1
  • 6
  • 15

1 Answers1

0

Your select statement is executed as SQL so the error you are getting is actually from SQL server. This may be where you are getting confused.

There are two components to SSRS - SQL Statements and Report Expressions. Typically, SQL statements are used to generate datasets by querying the database. Report expressions are used to organize, aggregate, and filter the dataset once obtained from the SQL database. Since the SQL statement is executed IN the SQL database, only the functions that are in the database are available. The code you posted is a SQL statement not a Report Expression.

For example, you can't take a Report Expression and expect it to work in SSMS? No, because they are two different entities with wholly different syntax and purpose. When it comes to using built-in SSRS functions inside a SQL statement it will not work, the database has no concept of what the built in User.UserId is and as such you must use a parameter to transport the value over to the SQL query. This is definition and purpose of a parameter and why they exist.

Split is a function in SSRS which is why you see it in your expression reference, however, it is not a function in SQL. The code you posted is SQL syntax, so I am betting that this is the SQL statement that you are using to obtain your dataset. Therefore the query fails since the SQL DB does not have a Split Function.

You can add this split function to your database and the code is located here: Split String in SQL. You could also use something along the following in your where clause, the following is your updated SQL statement. 

    SELECT slsm_num, slsm_msid from Salesman where slsm_msid = SUBSTRING(@UserId, PATINDEX('%\%', @UserId), LEN(@UserId))

You would set the @UserId parameter's value to an expression of User!UserID rather than specifying it in your select statement.

The SSRS expression examples have a function similar to what your code is trying to accomplish if you were wanting the same thing in the report side. The function you are looking for is InStr(). On your report side you could use something along the lines of:

    =Parameters!User.Value.Substring(Parameters!User.Value.IndexOf("\")+1, Parameters!User.Value.Length-Parameters!User.Value.IndexOf("\")-1)

Expression examples can be found here: MSDN Expression examples.

Community
  • 1
  • 1
ShellNinja
  • 629
  • 8
  • 25
  • thanks for the reply :) but what you suggest is basically what I'm already doing. I have 1 parameter that gets the MSID value (using "=split(User!UserID,"\").GetValue(1)" as the expression value for default and then I use that parameter in another parameter to do the actual SELECT statement to the table. What I'm looking to do is get RID of the 1st parameter that gets the MSID and have it all in the 2nd parameter, usint the "split" within the SELECT statement and that is bombing out. Kim – Kim Jones Dec 13 '13 at 18:11
  • As I said in the answer, SQL does not have a split function therefore you cannot do it. Even if you fix the split issue by adding a user-defined function to your DB, you can't reference the Built-In fields directly in a SQL Query in SSRS - they are two separate independent things and as such, you use a parameter to pass the value. Once you add a UDF to fix the error you posted you would have a new one in which your User.UserId is seen as a table column and the query engine will fail. – ShellNinja Dec 13 '13 at 18:33
  • The SQL query won't have the slightest clue what User.UserId is... SELECT User.UserId and see what happens. – ShellNinja Dec 13 '13 at 18:46
  • Added an edit to explain the differences b/t SSRS Report Expressions and SQL statements based on your comment. – ShellNinja Dec 13 '13 at 19:00
  • hi @shellNinja, thanks for the help. I'll just leave it having the extra parameter as it's working. I was just hoping to simplify the report a tiny bit. Thanks for your help! – Kim Jones Dec 16 '13 at 23:50