Hi All,

I have a report which is redirecting to a subreport. The main report is having multi value parameter. I need to pass these  multi values to sub report. Passing parameters from MDX report to T-sql report. So, I'm using the below exp.

=SPLIT(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),",   ",","),",")

The value will look like this

01-Manger

02-Senior Mange

21-Associate

25-Associate Trainee

This is working for me in all the cases except one. In all other cases, the parameter's Label and Value field has same data in the sub report. But, in a specific parameter I'm getting Label and Value data are different. I'm getting an alpha numeric string value from MDX report , but I need to pass only the numeric values to the sub report since its value field contains only numeric value. The numeric value is coming at the starting of the string data. So I have used Mid()

Julie

This is a kludge, and I don't much like it, but it will work.

Create a dataset, and set the query to an expression. In that expression, use:

="
SELECT MyParam, LEFT(MyParam,CHARINDEX('-',MyParam)-1) AS MyNumber FROM (
SELECT '" 
+
 Join(Parameters!ReportParameter1.Value, "' AS MyParam UNION ALL SELECT '")
+
"' AS MyParam) a"

Where ReportParameter1 is the name of your parameter.

Set the fields MyParam and MyNumber in the fields tab (it won't do this for you).

You can then use the fields returned from this dataset as your values to pass to the subreport. 

Hi Julie Eliza,

See the expression below:

=IIF(IsNumeric(SPLIT(Mid(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),",   ",","),1,2),",")) = 1, SPLIT(Mid(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),",   ",","),1,2),","), 0) 

Hi Julie,

Per my understanding that you are want to numeric part value from the multiple value parameter, right?

I have tested on my local environment and if what you want is to always get the first two numeric character of the values in the multiple parameter, you can add an new dataset in the subreport (T-sql) which will return a field with the values from this main report's parameter label, then you can add an calculated field (NewGrade)using the expression "=Mid(Fields!Grade.Value,1,2)" to get the  numeric part from this field, finally, create an new parameter to get the value from the new dataset.

Details information below for your reference:

1. Create an new function named Split to split the string(join all the values of the multiple value parameter label) into rows:

CREATE FUNCTION [Split](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (Grade varchar(8000))     
as     
begin     
    declare @idx int     
    declare @slice varchar(8000)         
    select @idx = 1     
        if len(@String)<1 or @String is null  return         
    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@String)     
        if @idx!=0     
            set @slice = left(@String,@idx - 1)     
        else     
            set @slice = @String          
        if(len(@slice)>0)
            insert into @temptable(Grade) values(@slice)     
        set @String = right(@String,len(@String) - @idx)     
        if len(@String) = 0 break     
    end 
return     
end

2. Create an new dataset2 in the subreport (T-sql) and use the expression below in the query:
="Select * from Split('"+join(Parameters!Grade.Label,",")+"',',')"

3. Right click the dataset2 to add an calculated field(New Grade) using the expression below"=Mid(Fields!Grade.Value,1,2)":

4. Create an new parameter "NewGrade" in the subreport which values get from the dataset2 and then hide the original parameter "Grade" in the Subreport:

If you still have any problem, please feel free to ask.

Regards,
Vic