SSRS: how to extract only numeric values from a string in SSRS

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()

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

Result will be   01

But, mid() will give only the first value. It is working for single value. But I need to extract multiple values.

Please suggest.

Regards,

Julie

May 29th, 2015 1:35pm

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. 

Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 2:23pm

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) 

May 29th, 2015 7:39pm

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

Free Windows Admin Tool Kit Click here and download it now
June 1st, 2015 2:21am

Hi Patrick/ Ricardo/Vicky,

Thank you so much for your response. We have got a solution in different way. I think that is much easier. Please see the below exp.

=Split(System.Text.RegularExpressions.Regex.Replace(Join(Parameters!Grade.Label,","), "[^0-9,]", ""),",")

Regards,

Julie

June 1st, 2015 3:23am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics