Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to split the data in ssrs Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 9:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 1:06 PM
Points: 55, Visits: 109
Hello,
I have a textbox in the header where I am trying to split the field

The column data in database looks like

1185-Peachtree County

But in my ssrs report I need only 1185 (I need to show header as Report System-1185 DATA)

I written the expression as

="FNS Report System-" + VBCRLF + Fields!SystemName.Value+" "+"DATA"

How to split it any help?
Post #1566751
Posted Thursday, May 1, 2014 10:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 325, Visits: 719
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)

Post #1566765
Posted Thursday, May 1, 2014 11:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 1:06 PM
Points: 55, Visits: 109
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..
Post #1566794
Posted Thursday, May 1, 2014 11:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:23 PM
Points: 11,247, Visits: 13,009
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.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566799
Posted Friday, May 2, 2014 4:22 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:49 AM
Points: 83, Visits: 690
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))
Post #1566976
Posted Friday, May 2, 2014 6:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 1:06 PM
Points: 55, Visits: 109
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

Post #1567014
Posted Friday, May 2, 2014 7:02 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:49 AM
Points: 83, Visits: 690
I'd probably just do something like the following:

=Iif(Parameters!Systemname.Value = "ALL", Fields!Systemname.Value, Cstr(Val(Fields!Systemname.Value)))
Post #1567016
Posted Friday, May 2, 2014 7:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 1:06 PM
Points: 55, Visits: 109
I tried in this way

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

It got worked;Thank you so much..
Post #1567024
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse