Number formatting in ssrs 2008 R2

thedeltacompanies
thedeltacompanies used Ask the Experts™
on
A report that is being exported to excel has a number formatting issue.

when the value in a specific cell contains a decimal place, I need to return the value with the decimal....if the number returned in the same cell is a whole number, the export to excel does not need to have the decimal place. (The report does not show the decimal place for the whole number until exported to excel).

Currently the number formatting is #,#.#;-#,#.#.

Is there some kind of conditional statement that would eliminate the decimal if my result is a whole number  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It is not possible to SSRS Export intoExcel ,

Soln:Create Seperate package and Do your formatting.

Author

Commented:
Not sure I understand your statement "not possible to SSRS Export into Excel"

Users have the option to export any report to excel,
 ExportExample.docx

Commented:
I think in the code below i am missing something small...

declare @decimal_value decimal (18,3)
declare @decimal_v int
declare @front_number int
set @decimal_value = 75.000
set @decimal_v = (select PARSENAME(@decimal_value,1))
set @front_number = ( select (left(@decimal_value, Charindex('.', @decimal_value, 1) - 1 )))


--select @decimal_v
--select @decimal_value
--select @front_number

select case when @decimal_v > 0
            then (select @decimal_value)
            else convert (int, (select @front_number))
end as [Number]

everything works fine when I do select @front_number i get 75 which is OK.
but when I use @front_number in CASE WHEN it returns me decimal, I tried to CAST/CONVERT to int but again it shows me 75.000.

more or less this is the logic I used for this case...

I ask other experts to modify this and maybe you can get what you need...
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
@anandarajpandian and @barlet I think you are not quite understanding the problem,


@thedeltacompanies, try this expression ...

=format(YourFieldorExpression,"#,#.#;-#,#.#")

Author

Commented:
edlunad, that is what I have done.....looks great on report, but when my user exports to excel he sees

"27." instead of the "27" shown on report.  And as you know...users don't want to fix formatting problems.

Is there a way to do something like

 iif(YourFieldorExpression is a whole number," #,#;-#,#" , "#,#.# ;-#,#.#")  

and if there is, how do I determine that my result is a whole number.
Commented:
Actually first I just put your mask format in the Format property of the field and it did exported wrong like you said, with the "27."  But when I add a new column based on my expression, exported correctly as "27"

Did you tried that already? I mean, adding a new column with just my expression, nothing on the Format property.

Author

Commented:
I had not removed the format property and done on only the expression.  That will work.  You're awesome.  Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial