1

I have a field like this in the column field in ssrs.

QUINIDINEssssssssssssssssssssssssssssssssssssssssssssssssssssss GL INJ 80MG/ML FLECAINIDEssssssssssssssssssssssssssssssssss TAB 50MG TAMBOCORsssssssssssssssssssssssssssssssssssssssssssss TAB 50MG

and how can i align align like this...

QUINIDINEssssssssssssssssssssssssssssssssssssssssssssssssssssss GL INJ 80MG/ML FLECAINIDEssssssssssssssssssssssssssssssssss(emptyspaceeeeeeeee)TAB 50MG TAMBOCORsssssssssssssssssssssssssssssssssssssssssssss(emptyspa)TAB 50MG

each part of string align properly...

Any help...

soundarrajan
  • 149
  • 1
  • 3
  • 13
  • I presume those "s" are spaces? Do you need to preserve the spaces between "TAB" and "50MG"? – Eric Hauenstein Jan 10 '14 at 12:43
  • hi eric,FOR FIRST ROW : 1st string = QUINIDINEssssssssssssssssssssssssssssssssssssssssssssssssssssss 2nd=GL 3rd=INJ 4th80MG/ML SECOND ROW : 1st string : FLECAINIDEssssssssssssssssssssssssssssssssss 2nd:TAB 3rd:50MG so..all 1st ,2nd and 3rd string align propoerly for all rows – soundarrajan Jan 10 '14 at 13:09

2 Answers2

1

I once used Switch to append spaces to a column field, like in the following example:

=Switch(LEN(Fields!FamilyMemberName.Value) = 1, " ",
LEN(Fields!FamilyMemberName.Value) = 2, "  ",
LEN(Fields!FamilyMemberName.Value) = 3, "   ")

But I didn't have to bother about the expression getting longer, since I know exactly how many spaces I should append and it wasn't more than 15.

You could use Switch in your case too or you could write a VB function in the Code tab of Report Properties window and pass the values, use some loops to generate the spaces you require.

Or it's much easier in MS SQL, all you got to do is append SPACE(NoOfSpacesInInteger) to the field.

kelsier
  • 4,050
  • 5
  • 34
  • 49
1

Well, there are two ways to do this that I worked out, and neither is particularly elegant so I'd be surprised if someone doesn't have a better solution.

"t.a" in the code below is your table and column.

The SQL way:

SUBSTRING(t.a,1,CHARINDEX('  ',t.a,1))+REPLICATE('_',20)+REVERSE(SUBSTRING(REVERSE(t.a),1,CHARINDEX('  ',REVERSE(t.a),1)))

Change the value being replicated above from '' to ' ' and you are in business. I left it as '' to illustrate what it's doing.

The SSRS way, which is better from a DB standpoint:

=Mid(Fields!a.Value,1,InStr(Fields!a.Value,"  "))+StrDup(20,"_")+StrReverse(Mid(StrReverse(Fields!a.Value),1,InStr(StrReverse(Fields!a.Value),"  ")))

This is precisely the same formula, executed by SSRS instead of by SQL Server.

Eric Hauenstein
  • 2,557
  • 6
  • 31
  • 41