thedeltacompanies

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

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

ASKER

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

Users have the option to export any report to excel,

ExportExample.docx

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

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

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

@thedeltacompanies, try this expression ...

=format(YourFieldorExpress

ASKER

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.

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

Soln:Create Seperate package and Do your formatting.