SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to split the data in ssrs


How to split the data in ssrs

Author
Message
mcfarlandparkway
mcfarlandparkway
SSC Eights!
SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)

Group: General Forum Members
Points: 829 Visits: 729
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?
gbritton1
gbritton1
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 864
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
mcfarlandparkway
SSC Eights!
SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)

Group: General Forum Members
Points: 829 Visits: 729
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
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19376 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Spiff
Spiff
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 2230
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
mcfarlandparkway
SSC Eights!
SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)

Group: General Forum Members
Points: 829 Visits: 729
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
Spiff
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 2230
I'd probably just do something like the following:

=Iif(Parameters!Systemname.Value = "ALL", Fields!Systemname.Value, Cstr(Val(Fields!Systemname.Value)))
mcfarlandparkway
mcfarlandparkway
SSC Eights!
SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)

Group: General Forum Members
Points: 829 Visits: 729
I tried in this way

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

It got worked;Thank you so much..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search