Link to home
Start Free TrialLog in
Avatar of thedeltacompanies
thedeltacompaniesFlag for United States of America

asked on

Number formatting in ssrs 2008 R2

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  
Avatar of anandarajpandian
Flag of India image

It is not possible to SSRS Export intoExcel ,

Soln:Create Seperate package and Do your formatting.

Avatar of thedeltacompanies


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

Users have the option to export any report to excel,
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...
@anandarajpandian and @barlet I think you are not quite understanding the problem,

@thedeltacompanies, try this expression ...

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.
Avatar of edlunad
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I had not removed the format property and done on only the expression.  That will work.  You're awesome.  Thanks.