• Replies 5 replies
  • Subscribers 336 subscribers
  • Views 53 views
  • Users 0 members are here
Related

ssrs problem

Jeff Stahel
$organization

Hello,

I am having some trouble with an SSRS report and wondered if anybody could steer me in the right direction.

The issue is this:

I have an optional multi-select  key_value parameter that appears to be passing an extra set of single quotes. It appears like this in profiler:

exec LRP_LIST_BY_ATTRIBUTE @keyword_no=N'563',@key_value=N'"Development"',@salutation_type=N'24'

If I remove the single quotes surrounding "Development" the procedure returns values, but as the parameters are being passed it returns nothing. The area of the stored procedure related to key_value is set up like this:

IF @key_value is not null
BEGIN
INSERT INTO #cust
(customer_no,
keyword_no,
key_value)
SELECT k.customer_no, k.keyword_no, k.key_value
FROM TX_CUST_KEYWORD k
WHERE keyword_no = @keyword_no AND key_value = @key_value
or k.keyword_no = @keyword_no and (CHARINDEX(',"' + k.key_value + '",',',"'+ @key_value + '",') > 0)

END
ELSE
BEGIN
INSERT INTO #cust
(customer_no,
keyword_no,
key_value)
SELECT k.customer_no, k.keyword_no, k.key_value
FROM TX_CUST_KEYWORD k
WHERE keyword_no = @keyword_no
END

The report works fine with no key_value parameter selected, but returns no value if one or more key_values are selected. The parameter in SSRS is not set to allow mulitple values.

I'm stuck as to how to get this ironed out. Any ideas?

Thanks in advance!

  • $organization

    Something like this might help you. Give a try. Just change the values. Let me know.

    declare @keyword_no int
    declare @key_value varchar (2000)

    set @keyword_no = 325
    set @key_value = 'Do Not Email,Do Not Mail (Brochure Ok)'


    SELECT k.customer_no, k.keyword_no, k.key_value
    FROM TX_CUST_KEYWORD k
    left join FT_SPLIT_LIST(@key_value ,',') z on k.key_value= z.Element 

    WHERE keyword_no = @keyword_no

    I forgot to mention  that I usually never have a problem with multi selects in ssrs when using ft split list.

     



    [edited by: Travis Armbuster at 2:11 PM (GMT -6) on 16 Oct 2013]
  • $organization in reply to Travis Armbuster

    Thanks for the help Travis,

    I got FT_SPLIT_LIST working, but am still having the issue with double quotes around each part of the parameter string. Still works great if I delete the double quotes and re-run the report, but doesn't pull any data before that. Have you figured out the syntax for ft_split_list so that it accounts for the quotation marks that Tessitura inserts?

  • Former Member
    Former Member $organization in reply to Jeff Stahel

    Jeff,

    I don't think this is an SSRS problem specifically.  It's been a long time since I've had this problem but I think there are a couple things to check.  In Report Setup, make sure that the data type for that parameter is correct and in T_Keyword check the data type and the input mask.  Compare it to other parameters/keywords that are behaving correctly.

    Hope this helps!

    - Levi

  • $organization in reply to Former Member

    Well I would check what Levi has suggested. But if you really wanted to remove the single quotes I guess you could certainly try a replace around the @key_value in the left join.

    example:

    left join FT_SPLIT_LIST(replace(@key_value,'''','') ,',') z on k.key_value= z.Element 

     

  • $organization in reply to Travis Armbuster

    Many thanks to you both!

    LEFT JOIN FT_SPLIT_LIST(replace(@key_value,'"',''),',') z on k.key_value = z.Element corrected the issue.

    Your help is greatly appreciated!

    Jeff