locked
How to split an integer in SSRS RRS feed

  • Question

  • so I have my value 1234 from my dataset, now I need to split that into separate text boxex, ie 1 and 2 and 3 and 4 

    my expr is = split((Sum(Fields!AdherencePercent.Value, "DataSet1"))).GetValue(0) but that only ever returns 1234

    = split((Sum(Fields!AdherencePercent.Value, "DataSet1"))).GetValue(1) returns #Error

    Saturday, August 27, 2016 12:56 PM

Answers

  • Variable length strings could be a problem but there are a few ways to deal with it.

    One solution would be to pad the string out to a maximum value with spaces, (e.g. 10 as Int can not store integers above 2147483647),  you report would then be able to handle a variable number of characters up till the length of the padding. The query for the 10 text boxes would look something like

    =MID(RIGHT(StrDup(10," ") & formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0),10) ,1,1)

    =MID(RIGHT(StrDup(10," ") & formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0),10) ,2,1)

    =MID(RIGHT(StrDup(10," ") & formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0),10) ,3,1)...

    =MID(RIGHT(StrDup(10," ") & formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0),10) ,10,1)

    • Proposed as answer by Yuliana Gu Monday, August 29, 2016 11:10 AM
    • Marked as answer by Charlie Liao Friday, September 9, 2016 11:01 AM
    Saturday, August 27, 2016 3:44 PM

All replies

  • Hi

    Split would not be the right function as there are no delimiters in you number/text

    The function you would want to use is MID, this is the equivalent of SUBSTRING in SQL.

    Your code for each text box would look something like.

    =MID(formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0) ,1,1)

    =MID(formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0)   ,2,1)

    =MID(formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0)   ,3,1)

    =MID(formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0)   ,4,1)

    #Note be aware of the number of digits in the string and how those are mapped to the text boxes. 

    Saturday, August 27, 2016 1:19 PM
  • Thanks for this, if the string was to be an  unidentified length, ie could be 12, then 1234 or 123 would the mid still work?

    I am gonna give it a go.


    charlie snedden

    Saturday, August 27, 2016 2:27 PM
  • Variable length strings could be a problem but there are a few ways to deal with it.

    One solution would be to pad the string out to a maximum value with spaces, (e.g. 10 as Int can not store integers above 2147483647),  you report would then be able to handle a variable number of characters up till the length of the padding. The query for the 10 text boxes would look something like

    =MID(RIGHT(StrDup(10," ") & formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0),10) ,1,1)

    =MID(RIGHT(StrDup(10," ") & formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0),10) ,2,1)

    =MID(RIGHT(StrDup(10," ") & formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0),10) ,3,1)...

    =MID(RIGHT(StrDup(10," ") & formatnumber(Sum(Fields!AdherencePercent.Value, "DataSet1"),0),10) ,10,1)

    • Proposed as answer by Yuliana Gu Monday, August 29, 2016 11:10 AM
    • Marked as answer by Charlie Liao Friday, September 9, 2016 11:01 AM
    Saturday, August 27, 2016 3:44 PM