locked
Showing only last four characters of returned string . . . RRS feed

  • Question

  • Hello, Novice SSRS person,

    I need to show only the last four characters and a number/letter string returned on the report for security reasons. How can I do this? It is in a Matrix. Can seem to find the exact info I need.  0345667M should show only 667M . . .

    Thanks for your assistance, Becca

    Monday, June 27, 2016 7:21 PM

Answers

  • You'd be best off returning this from the database.

    In your query, find the column in question, and do something like this:

    DECLARE @table TABLE (myColumn VARCHAR(40))
    INSERT INTO @table (mycolumn) VALUES
    ('dsfdfsfdsfdsfdsfdsf'),('121231f5gfg')
    
    SELECT REPLICATE('*',LEN(mycolumn)-4)+RIGHT(mycolumn,4), 
           REPLICATE('*',6)+RIGHT(myColumn,4)
      FROM @table
    The first example retains the original length of the string, the second converts it to a fixed 10 character string.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, June 27, 2016 8:30 PM

All replies

  • Hi ,

      Try an expression as below

    =  MID(Fields!ColumnName.Value,IIF(Len(Fields!ColumnName.Value) > 4,Len(Fields!ColumnName.Value),5)  - 4,4)


    Best Regards Sorna

    Monday, June 27, 2016 8:08 PM
  • You'd be best off returning this from the database.

    In your query, find the column in question, and do something like this:

    DECLARE @table TABLE (myColumn VARCHAR(40))
    INSERT INTO @table (mycolumn) VALUES
    ('dsfdfsfdsfdsfdsfdsf'),('121231f5gfg')
    
    SELECT REPLICATE('*',LEN(mycolumn)-4)+RIGHT(mycolumn,4), 
           REPLICATE('*',6)+RIGHT(myColumn,4)
      FROM @table
    The first example retains the original length of the string, the second converts it to a fixed 10 character string.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, June 27, 2016 8:30 PM
  • Hi Becca Blankenship,

    As you said  for security reason you need this.

    Then follow Patrick Hurst suggestion, however you create view at your database.

    View should have the derived column not main column. So that user will never get the details of original column.

    As RDL can be downloaded and code can be exposed.

    Only provide data though view and dont provide the orginial column.

    Thanks

    Prasad


    Tuesday, June 28, 2016 2:12 AM