How to split the data in ssrs

Question

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

if the numeric portion is fixed length (always four characters), use the Left function e.g.

Left("1234-abc", 4)

yields "1234"

If the numeric portion is variable length, add a call to InStr:

=Left("1234-abc",InStr("1234-abc","-")-1)

mcfarlandparkway SSCertifiable Points: 7701 More actions · Answer 2

I understood,but I am using column name as Systemname

From system name I am getting values ad 1184-abc

Now I want in ssrs report only 1184 how can I write the expression is there any function in ssrs

I tried split but not working..

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

gbritton1 (5/1/2014)
If the numeric portion is variable length, add a call to InStr:
=Left("1234-abc",InStr("1234-abc","-")-1)

That's your answer, just replace the hard-coded text with your Field name.

Spiff SSCertifiable Points: 7826 More actions · Answer 4

Neat way to do it is use the Val() function.

This will return all the numbers on the left of a string until it hits a non-digit value.

e.g. Val("12345-Parkway") returns 12345.

Only gotchas are it returns 0 if there is no numeric part and something like "1 2 3-Parkway" would return 123.

Remember to convert to a string if you are concatenating the value.

i.e. Cstr(Val(Fields!systemname.value))

mcfarlandparkway SSCertifiable Points: 7701 More actions · Answer 5

Thank You for the reply

I have a question here -

If I select parameter system as 'ALL' in the data base I have a system name column showing data as ALL Systems

If I select parameter as 648

In the database I have a data 648-County

The one which you told me to use val is working fine for only 648 - County

I used expression as =Cstr(val(fields!systemname.value))

When I select ALL and I preview the report its showing as 0

How to solve this issue

I want to show this message in header

Spiff SSCertifiable Points: 7826 More actions · Answer 6

I'd probably just do something like the following:

=Iif(Parameters!Systemname.Value = "ALL", Fields!Systemname.Value, Cstr(Val(Fields!Systemname.Value)))

mcfarlandparkway SSCertifiable Points: 7701 More actions · Answer 7

I tried in this way

=IIF((Parameters!SystemId.Value) = "ALL", "ALL Systems DATA",Parameters!SystemId.Value+" " +"DATA")

It got worked;Thank you so much..

Cookies on SQLServerCentral