Post reply

SSRS Defaulting parameter to Empty String?

  • February 8, 2017 at 8:37 pm

    #327088

    Hello, 

    I have a report I've created that is a multi-value. I have it flagged as such as well as the "Allow blank value("")". I have in the dataset for the parameter

    UNION ALL

    SELECT
    '12/31/2099' AS Job_Due_Date,
    0 AS Job_Key,
    '' AS Job_No

    The problem is that the user doesn't want to have to select a value for this parameter all the time and yet if I leave it selecting ALL values, the report never finishes. So I've been trying to default this multi-value parameter to an Empty String but it's not working. I've tried in the Default Values tab selecting the "Specify values" and entering the following but none of them seem to work: 

    =""
    =''
    =NOTHING

    My sproc is handing when the parameter being passed is NULL or Empty String, but not sure how to pass these with a Multi-Value. So then I decided to look at the sproc and see where exactly the issue is, and found that it is in where I'm trying to break up the CSV being passed in from the parameter. I created a table variable for this and am trying to populate it using the following to break the string up:

     SELECT DISTINCT
            @PCN,
            J.Job_Key
        FROM dbo.Job_History AS J
        WHERE J.PCN = @pcn
            AND CHARINDEX(CONVERT(VARCHAR(15), J.Job_Key), @Job_Key_List, 0) > 0;
            --AND J.Job_Key IN @Job_Key_List;

    When using the CHARINDEX line, it never returns before I kill the spid. I found somewhere in my digging a post that said to use IN, but when using that last line with the IN it errors saying incorrect syntax which I kind of expected. (The @Job_Key_List is VARCHAR(MAX)).  

    So I'm looking for 1 of 2 answers - either is fine at this point LOL 
    1. Is there a way to set a Multi-Value parameter in SSRS to have default of an Empty String? 
    2. Is there a better way to split up the CSV that gets passed from the Multi-Value parameter with out having to create a specific function just for this? 

    Any ideas would be great! TIA!

  • February 8, 2017 at 8:47 pm

    #1927433

    So I really shouldn't post anything so late at night LOL As soon as I clicked the Post button I realized I was trying to pass in an empty string but should be trying to pass in a zero. The zero works. However, just in case I need to know for future, if anyone knows the answers I would appreciate it. Thanks!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply