May 23, 2018 at 10:34 am
In my report i have a column which is of varchar type but currently it is storing int value and in the excel export that column shows up with warning Number stored as Text.
i tried to use Cint but it displays #VALUE for blank cells. Can someone help with this . Thanks
May 24, 2018 at 11:56 am
I think what you would want is something like in this webpage:
https://jwcooney.com/2015/03/02/ssrs-simple-code-examples-of-handling-null-or-blank-date-fields/
or this page:
https://www.experts-exchange.com/questions/20862294/cint-error-when-field-is-null.html
basically, you want to check if the value is blank or null and if so, give it a value or leave it null. If the value is not blank or null, then you will want to do the CINT on it. The only issue I can see on this though is if your varchar column ends up holding values that are not integers. Offhand, I do not remember what SSRS does if you try to CINT a string like "bob".
Although this does feel like a table design decision gone wrong. If a column contains only int values, why would you make it a varchar?
Another option would be to do all the heavy lifting inside SQL - create a VIEW on the table(s) and cast it as an INT in there. An empty varchar or a varchar with only spaces cast as an INT will give you 0. You will get issues in the event you have non-integer values in there, but you will get issues with your report using CINT on a string.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 29, 2018 at 1:58 pm
bmg002 - Thursday, May 24, 2018 11:56 AMI think what you would want is something like in this webpage:
https://jwcooney.com/2015/03/02/ssrs-simple-code-examples-of-handling-null-or-blank-date-fields/
or this page:
https://www.experts-exchange.com/questions/20862294/cint-error-when-field-is-null.htmlbasically, you want to check if the value is blank or null and if so, give it a value or leave it null. If the value is not blank or null, then you will want to do the CINT on it. The only issue I can see on this though is if your varchar column ends up holding values that are not integers. Offhand, I do not remember what SSRS does if you try to CINT a string like "bob".
Although this does feel like a table design decision gone wrong. If a column contains only int values, why would you make it a varchar?
Another option would be to do all the heavy lifting inside SQL - create a VIEW on the table(s) and cast it as an INT in there. An empty varchar or a varchar with only spaces cast as an INT will give you 0. You will get issues in the event you have non-integer values in there, but you will get issues with your report using CINT on a string.
That column can contain string value as well .its of nvarchar type.Is there any way to remove the warning-"number stored as text" from the excel export?
May 29, 2018 at 2:40 pm
Pretty sure the only good way to do that is to cast it to a numeric type in the report or casting it as something else inside excel.
But you'd need to do something to ensure that all of the values that are in the report are numeric otherwise your cast will fail.
CINT would work, but you need to make sure you are only doing CINT on values that can be numeric. Doing CINT on a non-numeric value (such as blank, null, single or multiple characters, etc) will give you bad results. You could do something like (this is Excel syntax as I don't remember fully how to do it in SSRS, but it should be similar):
=IF(ISERROR(CINT(value)),'',CINT(value))
where "value" above is the cell you want to do the formula on. In SSRS, I am pretty sure you'd just need to change "value" to be the parameter you are looking at. What the above will do is if it is unable to convert value to an integer, it will put in a blank. If it can convert it to an integer, it will put in the integer value.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 29, 2018 at 2:58 pm
bmg002 - Tuesday, May 29, 2018 2:40 PMPretty sure the only good way to do that is to cast it to a numeric type in the report or casting it as something else inside excel.
But you'd need to do something to ensure that all of the values that are in the report are numeric otherwise your cast will fail.CINT would work, but you need to make sure you are only doing CINT on values that can be numeric. Doing CINT on a non-numeric value (such as blank, null, single or multiple characters, etc) will give you bad results. You could do something like (this is Excel syntax as I don't remember fully how to do it in SSRS, but it should be similar):
=IF(ISERROR(CINT(value)),'',CINT(value))where "value" above is the cell you want to do the formula on. In SSRS, I am pretty sure you'd just need to change "value" to be the parameter you are looking at. What the above will do is if it is unable to convert value to an integer, it will put in a blank. If it can convert it to an integer, it will put in the integer value.
I used above and it shows #VALUE when there is a blank.
May 30, 2018 at 1:40 pm
Papil - Tuesday, May 29, 2018 2:58 PMbmg002 - Tuesday, May 29, 2018 2:40 PMPretty sure the only good way to do that is to cast it to a numeric type in the report or casting it as something else inside excel.
But you'd need to do something to ensure that all of the values that are in the report are numeric otherwise your cast will fail.CINT would work, but you need to make sure you are only doing CINT on values that can be numeric. Doing CINT on a non-numeric value (such as blank, null, single or multiple characters, etc) will give you bad results. You could do something like (this is Excel syntax as I don't remember fully how to do it in SSRS, but it should be similar):
=IF(ISERROR(CINT(value)),'',CINT(value))where "value" above is the cell you want to do the formula on. In SSRS, I am pretty sure you'd just need to change "value" to be the parameter you are looking at. What the above will do is if it is unable to convert value to an integer, it will put in a blank. If it can convert it to an integer, it will put in the integer value.
I used above and it shows #VALUE when there is a blank.
Bottom line is that if you expect Excel to handle this well, that's where your problem is. You are trying to use the flexibility of data type insensitivity of Excel with strongly typed data and then expect to mix data types within a column. That's not a solvable problem in the way your hoping to solve it. Excel exports from SSRS are going to use the data type that comes from the first x number of rows, and it's never a decision in favor of "both". That's just not how Excel works. There's really no alternative that doesn't involve after the fact formatting. That's not to say you might not be able to add a code module to the spreadsheet and then run the code to do the formatting, and then remove the module after running the code, but that seems like a lot of work to me... Most people that work with Excel usually end up knowing about this issue fairly quickly after first encountering Excel, assuming that they work with it on a regular basis. Best bet is probably user education.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply