Solved

SSRS Format Text Box value

Posted on 2013-02-06
7
1,578 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now