Solved

SSRS Format Text Box value

Posted on 2013-02-06
7
1,615 Views
Last Modified: 2013-02-11
I have an if statement that if yes, provides a value. I set the number format to percentage in the text box properties. How can I through code,  if no, set the value to show N/A? I know I would need to change the format of the text box and provide "N/A" as a value.


So, if value > 0 then number%, else N/A.
0
Comment
Question by:LCNW
7 Comments
 
LVL 12

Expert Comment

by:Tony303
ID: 38862427
The expression for the value of the field should read somthing like this...

=IIF(Fields!YOURFIELDNAME.Value) > 0, Fields!YOURFIELDNAME.Value, "N/A")

Just try it and see if the number format for that field is going to be a pest.
0
 
LVL 9

Expert Comment

by:selva_kongu
ID: 38862778
select the text box and go to the expression (Right click on the text box and select from Menu)
RMC in txt boxit will open expression window there you can add the format expression
expression window
=IIF(Fields!scnnumber.Value > 0, (Fields!scnnumber.Value & "%") , "N/A")

Open in new window

here scnnumber is sample field which I Used.

replace your field name and test the report.

---- SelvaS---
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 38862947
In case you're still wondering how you can get your percentage formatting to work, have a look at the following expression:

=IIF(Fields!YourValue.Value <= 0, "N/A", Format(Fields!YourValue.Value/100, "P0"))

Open in new window

As you can see, the Format function can be used to achieve your requirement.  Now, how does that work?

I'm dividing the value by 100.  This may or may not be needed, depending on the content of YourValue.  If YourValue is a percentage representation in the zero to one range, with 1 meaning 100%, then the division is not needed.  However, if YourValue contains 100 to indicate 100%, then it is needed.
Through "P0" (that 0 is a zero) I'm telling the function that I want a percentage without any decimals.  Depending on your requirements, you may want to increase the zero to the number of decimal digits expected.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:LCNW
ID: 38865320
ValentinoV ,that's what I needed. Where can I get a list of all formatting options such as P0? Or, what is it called so I Google the correct terms. Searching for SSRS formatting gave me the wrong info.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38867171
Cool, glad to hear I was on the right track with my answer :)

The Format function is one of the Visual Basic string functions, so you'd have to refer to the doc for that function: Strings.Format Method
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38867182
Here's a trick that can be used in Google: copy the function description from the Expression builder and paste it into the search box, surrounded with double quotes.

Example:

"Returns a string formatted according to instructions contained in a format String expression"
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38867189
And as usual, MS has got too many pages that describe more or less the same functionality. It was really hard to find a reference to that number to specify precision following the P format specifier.  The best one is probably this one: Standard Numeric Format Strings
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

808 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