How to Split String in Report builder 3.0 query designer?

Question

I have created one report Named resource Projection and created 3 Parameters:

Technical Skills : Type : Text , Multiple Selected : True, Blank value : True, Default Value : one Row from Database(around 15 values).
Management Skills: Type : Text , Multiple Selected : True, Blank value : True, Default Value : one Row from Database(around 7 value).
Role : Type : Text , Multiple Selected : True, Blank value : True, Default Value : one Row from Database(around 30 value).

And my query is:

SELECT O.CustomerIdName,
    TC.New_ResourcesIdName,
    TC.New_number,
    TC.New_RoleIdName,
    TC.New_Skill_value,
    TC.New_mng_value,
    TC.New_year,
    convert(nvarchar(10),TC.New_StartDate,101) as New_StartDate,
    convert(nvarchar(10),TC.New_EndDate,101) as New_EndDate
FROM New_resource as TC
INNER JOIN Opportunity as O ON
    (TC.New_ResourcesId = O.OpportunityId)
WHERE TC.DeletionStateCode = '0' 
    and TC.New_ResourcesId is not null
    and ((TC.New_Skill_value in (@TechnicalSkills))or (TC.New_mng_value in (@ManagementSkills)))
    and TC.New_RoleIdName in (@Role)

but New_Skill_value and New_mng_value contains Coma Separated string like:

New_Skill_value = "Android,Java,PHP,SQL".
New_mng_value = "Communication Skill, Decision Making Skill".

Now when i run this report it only shows me the columns containing only one New_skill_value and New_Mng_value.

So how can I separate this 2 comma separated strings and show all records with comma separated String?

AnandPhadke AnandPhadke · Accepted Answer · 2012-08-22T10:42:11

As you are saying your New_Skill_value and New_mng_value are comma separated and the parameters you are passing is also comma separated.

So the query above will work only when there is exact match between table values and the passing variable values.

I guess this may not happen always,as the variables you are passing may contain the same set of values but in different order then this query may not return anything.

So what I would suggest here is if possible you can split all the comma seperated columns in your tables into separate tables like id,values and then also then join with temp tables to get the accurate results.

Here temp table is nothing but the values in your variables.

How to Split String in Report builder 3.0 query designer?

1 Answers