How to create a multi-select parameter with a uniqueidentifier?

Question

I am developing an SSRS 2008 R2 RDL file. Now, I am trying to add a report parameter which should be a multi-select. Previously I have worked with multi-select parameters but they have always been CHARACTER data types before. So in my main dataset my code was:

;WITH Cte AS 
( 
    SELECT 
        1 as id, 
        CAST('<M>' + REPLACE( (select @IncidentType),  ',' , '</M><M>') + '</M>' AS XML) AS Names 
) 
SELECT 
    id, 
    Split.a.value('.', 'VARCHAR(MAX)') AS Names
INTO #incident_types     
FROM Cte 
CROSS APPLY Names.nodes('/M') Split(a)

(And in my RDL file this IncidentType report parameter allows multiselect)

But now IncidentType is a UNIQUEIDENTIFIER datatype. I have tried the SSRS JOIN function but this is still giving me same error in SSRS when I run this:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'Incidents'. (rsErrorExecutingCommand)
Conversion failed when converting from a character string to uniqueidentifier. 

How do I code for this by passing in multiple uniqueidentifiers?

I tried the solution below that you posted:

CAST(Split.a.value('.', 'VARCHAR(MAX)') AS UNIQUEIDENTIFIER) AS Names

But this gave me this error now:

Msg 8169, Level 16, State 2, Line 62
Conversion failed when converting from a character string to uniqueidentifier.

At the top of my sproc I have:

declare
    @FacilityID varchar(MAX) = null,
    @ProgramID uniqueidentifier = null,
    @ServiceDateStart smalldatetime = null,
    @ServiceDateEnd smalldatetime = null,
    @IncidentType varchar(MAX) = null

SET @IncidentType = '864EA130-F36B-4958-B51F-EE9DBD35D804,671A8EB3-8298-40DB-BD66-93309904E463,ACA1EB55-3D66-467B-8388-CC42FCFB00F3
SET @FacilityID = '83B465B8-28DD-4F37-9F2D-A4D5E38EE7FB,3EC657F7-655F-43FB-8424-2A8914E7C725,B2064474-5C9B-4884-B1D7-4FCE1C804DF7'

But the line that causes this error is:

   AND (@IncidentType is NULL OR event_definition_rv.event_definition_id in (SELECT Names FROM #incident_types))

cause I can run these lines without errors:

select * from #incident_types
select * from #facilities

Kythera

Answer 1

Try casting the unsplit values as uniqueidentifier:

    ;WITH Cte AS 
( 
    SELECT 
        1 as id, 
        CAST('<M>' + REPLACE( (select @IncidentType),  ',' , '</M><M>') + '</M>' AS XML) AS Names 
) 
SELECT 
    id, 
    CAST(Split.a.value('.', 'VARCHAR(MAX)') AS UNIQUEIDENTIFIER) AS Names
INTO #incident_types
FROM Cte 
CROSS APPLY Names.nodes('/M') Split(a)

Encyclopedic

Paul, can u have another look please? – Kythera

Can you show your entire code? Running EXEC tempdb..sp_help '#incident_types' shows that the #incident_types table is of type GUID. And I had no trouble using a WHERE x in (SELECT Names from #incident_types) clause, so I think maybe the problem lies elsewhere. – Encyclopedic

Thanks, I must have misentered the IncidentType cause now my sproc is working with your logic! Thanks! – Kythera

Answer 2

But the line that causes this error is:

AND (@IncidentType is NULL OR event_definition_rv.event_definition_id in (SELECT Names FROM #incident_types))

Since @IncidentType is a multivalued report parameter, you can't use @IncidentType Is NULL in your report's Dataset Query because it messes up the sql code and it become unpredictable.

Also "A multi-value parameter cannot include null values" so it's useless to check if the parameter Is Null. You could maybe use a possible workaround like this:

 AND ('NULL_FLAG' IN (@IncidentType) OR event_definition_rv.event_definition_id IN (@IncidentType))

See learn.microsoft.com where it says:

• The query must use an IN clause to specify the parameter.

Note:

The report server rewrites queries for data sources that cannot process parameters as an array. Rewriting the query is necessary to produce the intended result. A query rewrite is triggered when a parameter is defined as multivalued and the query uses an IN statement to specify the parameter. If you build a query that does not include the IN statement, be aware that you are circumventing the logic the report server provides to support multivalued parameters.

Abridgment