Avatar of thedeltacompanies
Flag 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  
Microsoft SQL ServerSSRS

Avatar of undefined
Last Comment

8/22/2022 - Mon

It is not possible to SSRS Export intoExcel ,

Soln:Create Seperate package and Do your formatting.


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...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

@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.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.