How to get percentage to display correctly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

How to get percentage to display correctly

Author

Topic

mlawton
Starting Member

35 Posts

Posted - 2012-03-27 : 11:43:50

When the field value = 0.00, I want to display as 0%, all other times display as the normal percentage. I also, lose the type when the report is exported to Excel. How can I format the field so that the percentage will display correctly?

Sample of the output:

Reporting Services
Value Percentage
972 -52.27%
0 Infinity
0 NaN
152 -1.31%
1,353 -29.27%
0 NaN
64 -74.00%
1,255 -28.69%

Excel
Value Percentage
972 -52.27%
0 #NUM!
0 #NUM!
152 -1.31%
1,353 -29.27%
0 #NUM!
64 -74.00%
1,255 -28.69%

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 12:29:47

use an expression for format in cell properties like

=IIF(Me.Value=0, "0%","##.#0%")

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mlawton
Starting Member

35 Posts

Posted - 2012-03-27 : 14:38:04

I used the following:

=iif((Sum(Fields!ActualSales.Value) - Sum(Fields!PrevSales.Value))/Sum(Fields!PrevSales.Value)= 0.00, "0%","##.#0%")

I put it in the format textbox properties in the format code section.

Also, I have =(Sum(Fields!ActualSales.Value) - Sum(Fields!PrevSales.Value))/Sum(Fields!PrevSales.Value) in the textbox.

I still get the same results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 14:49:08

whats the datatype of ActualSales and PrevSales?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mlawton
Starting Member

35 Posts

Posted - 2012-03-27 : 14:53:52

The datatype is float

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 14:56:30

can you use val inside and see if that makes a difference?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mlawton
Starting Member

35 Posts

Posted - 2012-03-27 : 15:03:09

I don't understand what you mean by val inside.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 15:10:37

=iif((Sum(val(Fields!ActualSales.Value)) - Sum(val(Fields!PrevSales.Value)))= 0.00, "0%","##.#0%")

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mlawton
Starting Member

35 Posts

Posted - 2012-03-27 : 16:34:49

Thanks. This got rid of the infinity. I still get the NaN.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 16:52:00

=iif((Sum(val(Fields!ActualSales.Value)) - Sum(val(Fields!PrevSales.Value)))= 0.00 Or Sum(val(Fields!PrevSales.Value))=0.00, "0%","##.#0%")

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mlawton
Starting Member

35 Posts

Posted - 2012-03-28 : 12:42:51

Thanks!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 12:37:25

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources