locked
SQL Reporting Services RRS feed

  • Question

  •  

    Hi there,

     

    I have a report with currency symbols ("R") "South African Rands" in the report textboxes. When the report is exported to Excel, It exports the textbox values as strings and not numeric values.

     

    Is there a way to set SSRS, so that when exporting to excel, that it does not export as strings, but as numeric values, where the currency symbols ("R") "South African Rands" have been removed?

     

    Regards

    BloodDiamond.

    Sunday, October 19, 2008 3:30 PM

Answers

  •  

    Hi Karlo,

     

    I finnaly found a solution to my problem. The casting in SQL did not work though. What i did is that in SSRS itself, for the expressions, I removed the following from the expressions :

     

    =format( . . . . . . . . . . "R #,##0")

     

    And in the format preoperty of the textboxe, i added "R #,##0" and this solved my exporting problem...!

     

     

     

    Thanks for you help.

    Wednesday, October 22, 2008 1:42 PM

All replies

  • HI BD,

    You would need to cast the value at the SQL level when creating in SRS. Either way when exporting to Excel, the value is numeric, just that it's formatting is set to currency. You should still be able to perform calculations against those fields.

    Cheers,

    Karlo
    Sunday, October 19, 2008 4:18 PM
  • Hi Karlo,

     

    I tried a conversion on on of my fields and it resulted in an "Error" in the report viewer.

     

    Here is what am trying to convert to a decimal / Double.

     

    =format(iif(Parameters!RevenueType.Value=0,Code.getMonthData(First(Fields!StartMonth.Value, "FinancialYearStartMonth"),1,Fields!GPJan.Value,Fields!GPFeb.Value,Fields!GPMar.Value,Fields!GPApr.Value,Fields!GPMay.Value,Fields!GPJun.Value,Fields!GPJul.Value,Fields!GPAug.Value,Fields!GPSep.Value,Fields!GPOct.Value,Fields!GPNov.Value,Fields!GPDec.Value),

    Code.getMonthData(First(Fields!StartMonth.Value, "FinancialYearStartMonth"),1,Fields!RevJan.Value,Fields!RevFeb.Value,Fields!RevMar.Value,Fields!RevApr.Value,Fields!RevMay.Value,Fields!RevJun.Value,Fields!RevJul.Value,Fields!RevAug.Value,Fields!RevSep.Value,Fields!RevOct.Value,Fields!RevNov.Value,Fields!RevDec.Value)),"R #,##0")

     

    So, I did the following :

     

     

    =CDec(format(iif(Parameters!RevenueType.Value=0,Code.getMonthData(First(Fields!StartMonth.Value, "FinancialYearStartMonth"),1,Fields!GPJan.Value,Fields!GPFeb.Value,Fields!GPMar.Value,Fields!GPApr.Value,Fields!GPMay.Value,Fields!GPJun.Value,Fields!GPJul.Value,Fields!GPAug.Value,Fields!GPSep.Value,Fields!GPOct.Value,Fields!GPNov.Value,Fields!GPDec.Value),

    Code.getMonthData(First(Fields!StartMonth.Value, "FinancialYearStartMonth"),1,Fields!RevJan.Value,Fields!RevFeb.Value,Fields!RevMar.Value,Fields!RevApr.Value,Fields!RevMay.Value,Fields!RevJun.Value,Fields!RevJul.Value,Fields!RevAug.Value,Fields!RevSep.Value,Fields!RevOct.Value,Fields!RevNov.Value,Fields!RevDec.Value)),"R #,##0"))

     

    And this results in an ERROR.

     

    Can you assist?

     

    Thanks.

    BD

    Monday, October 20, 2008 8:41 AM
  • Hi BD,

    I meant you would need to convert in your actual SQL e.g. CAST(amount as DECIMAL(2)) ... that should remove the currency field you're receiving.

    CHeers,

    Karlo
    Monday, October 20, 2008 10:08 PM
  •  

    Hi Karlo,

     

    I finnaly found a solution to my problem. The casting in SQL did not work though. What i did is that in SSRS itself, for the expressions, I removed the following from the expressions :

     

    =format( . . . . . . . . . . "R #,##0")

     

    And in the format preoperty of the textboxe, i added "R #,##0" and this solved my exporting problem...!

     

     

     

    Thanks for you help.

    Wednesday, October 22, 2008 1:42 PM