December 18, 2013 at 4:01 pm
Hello Everyone,
I am new to SSRS. I have a report that has a column (Loan_Count) that is defined as a decimal field. I would like the report to display whole number (no decimal point) if the value is greater than 1 and display the decimal digit if the value is less than or equal to 1. I tried to right click the field in the report Design tab. But I couldn't figure out what to do next. Do I need to enter some kind of IIF command somewhere? If yes, where would I enter the commands and what would the commands be?
Thank you very much for any input !
December 18, 2013 at 4:25 pm
Hi,
You can use an expression on the "Format" property of the text box:
= IIf(Fields!Proposal.Value>1,"F0","F2")
The numeric part of the format code is how many decimal places to include.
See here for more information : http://msdn.microsoft.com/en-us/library/dwhawy9k(v=vs.90).aspx
Also see attached sample report (works in the designer without access to any database) - click on the "Proposal" text box and look at the "Format" property.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);December 19, 2013 at 1:06 am
mister.magoo (12/18/2013)
Also see attached sample report (works in the designer without access to any database) - click on the "Proposal" text box and look at the "Format" property.
Nice touch 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 19, 2013 at 9:30 am
mister.magoo (12/18/2013)
Hi,You can use an expression on the "Format" property of the text box:
= IIf(Fields!Proposal.Value>1,"F0","F2")The numeric part of the format code is how many decimal places to include.
See here for more information : http://msdn.microsoft.com/en-us/library/dwhawy9k(v=vs.90).aspx
Also see attached sample report (works in the designer without access to any database) - click on the "Proposal" text box and look at the "Format" property.
Thank you very much mister.magoo! Your suggestion worked beautifully.
However, after further consideration, I need to display the values equal to or less than 1 as percentages with a %. (i.e. 0.022 will be displayed as 2.2%). The values greater than 1 will be displayed as whole number. Sorry about this change. Can this be done?
Thank you again!
December 19, 2013 at 9:41 am
I think I just figured it out: = IIf(Fields!loan_count.Value>1,"F0","P1")
Thank you all for your help. I really appreciate it! 🙂
December 19, 2013 at 1:50 pm
Hello again,
I thought I had resolved this issue. But a new one came up. How can I apply the 1000 (,) separator to my expression in the "Number" under "Text Box properites" ?
Here is my current expression
= IIf(Fields!loan_count.Value>1,"F0","P1")
I really appreciate any input!
December 19, 2013 at 1:53 pm
kcha2004 (12/19/2013)
Hello again,I thought I had resolved this issue. But a new one came up. How can I apply the 1000 (,) separator to my expression in the "Number" under "Text Box properites" ?
Here is my current expression
= IIf(Fields!loan_count.Value>1,"F0","P1")
I really appreciate any input!
Here's a great article on formatting numbers in SSRS[/url].
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 19, 2013 at 3:05 pm
Koen Verbeeck (12/19/2013)
kcha2004 (12/19/2013)
Hello again,I thought I had resolved this issue. But a new one came up. How can I apply the 1000 (,) separator to my expression in the "Number" under "Text Box properites" ?
Here is my current expression
= IIf(Fields!loan_count.Value>1,"F0","P1")
I really appreciate any input!
Thank you Koen for the great link. It has great information. However, I couldn't find anything that shows me how to keep the conditional formating and add the thousand separator (,) to the same column. Am I missing something obvious?
Thanks.
December 19, 2013 at 3:10 pm
kcha2004 (12/19/2013)
Koen Verbeeck (12/19/2013)
kcha2004 (12/19/2013)
Hello again,I thought I had resolved this issue. But a new one came up. How can I apply the 1000 (,) separator to my expression in the "Number" under "Text Box properites" ?
Here is my current expression
= IIf(Fields!loan_count.Value>1,"F0","P1")
I really appreciate any input!
Thank you Koen for the great link. It has great information. However, I couldn't find anything that shows me how to keep the conditional formating and add the thousand separator (,) to the same column. Am I missing something obvious?
Thanks.
I don't know all the different formats by hard.
What I normally do is go to the textbox properties, format the number the way I need it and then check the expression it generated for the format property.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 19, 2013 at 3:25 pm
Thanks Koen.
I think i figured it out. I replaced the F0 with N0. So my expression looks like this: = IIf(Fields!full_date.Value=1,"N0","P1")
I am learning alot here. Thank you all! 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply