Answered by:
Showing only last four characters of returned string . . .

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.- Proposed as answer by Rhys SchmidtkeMicrosoft employee Tuesday, June 28, 2016 2:46 PM
- Marked as answer by Qiuyun YuMicrosoft contingent staff Saturday, July 9, 2016 7:22 AM
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.- Proposed as answer by Rhys SchmidtkeMicrosoft employee Tuesday, June 28, 2016 2:46 PM
- Marked as answer by Qiuyun YuMicrosoft contingent staff Saturday, July 9, 2016 7:22 AM
Monday, June 27, 2016 8:30 PM -
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