Processing
 

SSRS Format Numeric not summarizing in excel

26/08/2016 21:09#1

MelgoV

Member

Joined at: 4 months ago

Post: 1

Thank: 0

Thanked: 0

I need to set a format for a column when the column "KPI" has a value with '%' I need that the value has the format for a percentage otherwise the currency format, so I have something like this:

=IIF(InStr(Fields!KPI.Value,"%")>0,Format(Fields!DIA.Value, "P"),Format(Fields!DIA.Value, "C")) 

That expression works properly, but when I try to export that report to excel, that column is not summarizing when I select several columns as you will see in this picture:

SSRS Format Numeric not summarizing in excel

As you can see excel is recounting but not summarizing, I have tried formatting each field from SQL Server and with this formula:

=IIF(InStr(Fields!KPI.Value,"%")>0,Format(Fields!DIA.Value, "###.#%"),Format(Fields!DIA.Value, "###,###,###,###.##")) 

But the same happend when I export that report to excel, is there another way to handle this?

29/08/2016 13:29Top#2

bitnine

Member

Joined at: 4 months ago

Post: 1

Thank: 0

Thanked: 0

In order to make sure that Excel interprets a value as numeric, you can use the RenderFormat global variable to detect when the report is being rendered for Excel and not include the problematic formatting characters:

=IIF(InStr(Fields!KPI.Value,"%")>0,Format(Fields!DIA.Value, "###.#%"),IIF(OR(Globals!RenderFormat.Name="EXCELOPENXML",Gl‌​obals!RenderFormat.N‌​ame="EXCEL"),Fields!‌​DIA.Value,Format(Fie‌​lds!DIA.Value, "###,###,###,###.##"))) 

If its critical that you display a thousands separator in your generated output you can use the Language global variable to detect the user's region and use the appropriate character. That check would take the general form Globals.Lanuage="en-US", depending on what particular regions are involved.

Here's an example of using the RenderFormat.Name variable to check the renderer in VS2013:

When doing a preview of the report, the format will show that the render type is RPL:

Similar articles

Excel More conditions in one formula?

4 months ago - Reply: 2 - Views: 171

Get Workbook reference from Open File dialog

4 months ago - Reply: 2 - Views: 128

Add Calculated Field To Pivot Table

4 months ago - Reply: 0 - Views: 5

Nested IF Functions in Excel not working

4 months ago - Reply: 3 - Views: 163

Formulating Date in Excel 2013

4 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

5 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

5 months ago - Reply: 1 - Views: 19

excel search and copy in/from another file

5 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

5 months ago - Reply: 0 - Views: 4

Excel static conditional formatting area

5 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re