Ian Stallings: web log

official chicken™

SQL Server Reporting Services - Multi-Value Params - part 2

OK, so in my last post I talked about how SQL server reporting services does not handle multi-value parameters and I would need to work around it. So I thought I'd share my work around for those that might need to do the same.

Basically I ended up writing a User Defined Function and compiling it on the SQL server I was pulling the reporting data from. This function will parse a string using the supplied delimiter and then return a table with an index and a bigint. I needed to return a recordset of bigints for a comparison but you may need another data type.

<code>

CREATE  FUNCTION StringToBigIntTable (
    @TargetString varchar(8000),
    @SearchChar varchar(1)
    )
    RETURNS @Set TABLE (
        idPos smallint not null identity,
        ExtractedInt bigint null
        )
AS
BEGIN

    SET @TargetString = COALESCE(@TargetString,'')
    DECLARE @SearchCharPos smallint,
            @LastSearchCharPos smallint
    SET @SearchCharPos = 0
    WHILE 1=1
    BEGIN
        SET @LastSearchCharPos = @SearchCharPos
        SET @SearchCharPos = CHARINDEX( @SearchChar, @TargetString, @SearchCharPos + 1 )
        IF @SearchCharPos = 0
        BEGIN
            INSERT @Set( ExtractedInt ) VALUES ( Convert(bigint, SUBSTRING( @TargetString, @LastSearchCharPos + 1, DATALENGTH( @TargetString ) )) )
            BREAK
        END
        ELSE
            INSERT @Set( ExtractedInt ) VALUES ( Convert(bigint, SUBSTRING( @TargetString, @LastSearchCharPos + 1, @SearchCharPos - @LastSearchCharPos - 1 )) )
    END
    RETURN
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

</code>


Then in my data view for my report (the RDL file) I use the following sql statement , calling my function to parse the parameter and return a recordset of bigints:


<code>

SELECT  *
FROM vwExpectedReport
WHERE ProgramID IN (select ExtractedInt from dbo.StringToBigIntTable(@ProgramIDs, ','))
</code>


I hope this helps someone down the road.






Comments

Marcelo Marim said:

Hey Ian

Thanks so much for your help.  

This worked just great for my code.

Keep the good work

# August 17, 2007 9:43 AM

Anna Catarina said:

Thank you. It was really helpful.

# October 18, 2007 7:38 AM

Guy R said:

Thanks Ian.  The implementation of these multi-value parameters is abominable.  You've been helpful in confirming that yet again code is going to have to written around a "half feature".

# December 4, 2007 10:02 AM

Dave Diehl said:

Nice simple solution.

...and I was about to implement an XML parameter.  Jeeze!  :)

# January 2, 2008 4:47 PM

Gabriel said:

Ever tried this?

=split(join(Parameters!YourMultiValueParamter.Value, ","), ",")

Silly I know...

# January 17, 2008 2:05 PM

kevin_cio said:

I agree - this feature should have been included COMPLETELY or removed COMPLETELY.  

It is just another example of how code is required to make RS work properly.

(my own sarcasm:  Do you think MS developers ever used Crystal Reports?)

# April 8, 2008 8:52 AM

ryan said:

works for me, thanks for posting the code.

# May 5, 2008 2:01 PM

Para said:

Hey this code was helpful, thanks.

But this seems to be working for me only if I have the exact code of the RDL in a Stored Procedure. If I add the exact code to the RDL it says too many arguments for the function dbo.StringToBigIntTable..... Do you have any clue as in why it says so? It just doesn't make any sense to me as to why the same code that works on the SP doesn't work on the RDL....

Cheers,

Para

# May 12, 2008 1:28 AM

Moodi said:

Thank  you very much...

# January 25, 2009 8:51 AM

TweetiBird said:

I thought this was it

SELECT  *

FROM vwExpectedReport

WHERE ProgramID IN (@ProgramID'))

# March 20, 2009 10:19 AM

Maxx said:

I get an error:

function "StringToBigIntTable" has too many arguments specified

When i select only 1 value it works OK, but i need multi value

I use you UDF and query is:

SELECT proj_id, proj_short_name FROM Project

WHERE proj_id IN

(

select ExtractedInt from dbo.StringToBigIntTable(@ProjectIds, ',')

)

# March 27, 2009 6:11 AM

Maxx said:

Quote:

"

I thought this was it

SELECT  *

FROM vwExpectedReport

WHERE ProgramID IN (@ProgramID')

"

Worked for me too

# March 27, 2009 8:54 AM

ipad app downloads said:

Conceit is the quicksand of success.

-----------------------------------

# December 18, 2010 12:19 PM

best ipad accessories said:

-----------------------------------------------------------

yea great Perform

# January 7, 2011 5:46 PM

best ipad application said:

-----------------------------------------------------------

"That’s As well wonderful, when it is available in india desire it might produce a Rocking place for youngster..<br> hope that appear true."

# January 11, 2011 5:01 PM

cordless phones reviews said:

"Dental packages in Costa Rica include all costs like X-ray, prosthesis, and so on., at the clinic, and also the dentist's fees, and nevertheless they are 50-70% cheaper than that which you  will come across inside the US, UK, Canada."

# February 10, 2011 5:48 PM