[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SSRS Text Formatting Question

Posted on 2010-04-09
6
Medium Priority
?
460 Views
Last Modified: 2012-05-09
I have the following in SQL server to format an account number.

What is the syntax to do this in SSRS?


AcountNo = SUBSTRING(GLA.GLAccount,1,1) + '-' + SUBSTRING(GLA.GLAccount,2,7) + '-' + SUBSTRING(GLA.GLAccount,7,10)

Open in new window

0
Comment
Question by:ApexCo
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 30265596
It's very similar, but use double quotes instead of single quotes. Where are you going to use the formatted account number, in a derived column transformation?
0
 
LVL 9

Expert Comment

by:blandyuk
ID: 30343285
Not sure if Substring is in SSRS but you can also use:

Left([text], [num])
Right([text], [num])
Mid([text], [start], [len])
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 30343866
Yes, Substring is an SSIS function.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 30543657
There seems to be some confusion here.  Here's an attempt to set things straight.

Substring is not a function of SSRS.  But there is a function with a different name and exactly the same functionality: Mid()

The code snippet in attach is assuming that you're retrieving a field called GLAccount through your dataset.

Alternatively, you could produce this custom field in the query of your dataset.  In that case you can use exactly the same code as you've provided!

Regards,
Valentino.

=Mid(Fields!GLAccount.Value, 1, 1) + "-" + Mid(Fields!GLAccount.Value, 2, 7) + "-" + Mid(Fields!GLAccount.Value, 7, 10)

Open in new window

0
 
LVL 9

Expert Comment

by:blandyuk
ID: 30552739
I didn't think Substring was a function in SSRS, hence I provided the others above :)
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 30572866
It's not, I don't think. I misread the question.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question