[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1223
  • Last Modified:

How do I convert an alphanumeric field in SSRS and export to excel to avoid the number stored as text error?

I have a report with 12 columns. 11 columns export fine to excel.  However,  I have one of the SSRS columns that is an numeric & alphanumeric, aaa1111....etc..  When I export to excel and I open the file to view I have the green error mark in the cells of that column that are just numbers.

If I change to  the expression =CDbl(Fields!your_numerical_column.Value) on the field all of my numbers only are corrected but the alphanumeric have errors.   Please assist.

0
rhservan
Asked:
rhservan
  • 10
  • 9
1 Solution
 
HainKurtSr. System AnalystCommented:
try wrapping it into " for that column

expression = '"' + ... + '"'
0
 
rhservanAuthor Commented:
Here is the expression that I placed on that field:

=""+Fields!TrailerNumber.Value+""

This worked only on the alphanumeric but not the numeric only.
0
 
HainKurtSr. System AnalystCommented:
try

=""""+Fields!TrailerNumber.Value+""""
or
="'"+Fields!TrailerNumber.Value+"'"

0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
rhservanAuthor Commented:

This worked to remove the errors in excel:
=""""+Fields!TrailerNumber.Value+""""
However, in both the report and excell it shows the double quote around the values.  Is there a way to hide them?
E.g. - "41244" or "JBHY3277"
Or perhaps another method.  I am testing the other expression now.
0
 
rhservanAuthor Commented:
Second expression results:
This worked to remove the errors in excel:
="'"+Fields!TrailerNumber.Value+"'"
However, in both the report and excell it shows the double quote around the values.  Is there a way to hide them?
E.g. - '41244' or 'JBHY3277'

This seems to be pretty much the same issue with the single quotes.   Waiting for further advisement.
0
 
HainKurtSr. System AnalystCommented:
what is your query?
0
 
rhservanAuthor Commented:
Is there a particular part of the query you need to view?
0
 
HainKurtSr. System AnalystCommented:
the part for "aaa1111"
0
 
rhservanAuthor Commented:
Here is the query.
EESample.txt
0
 
HainKurtSr. System AnalystCommented:
which column is creating issue?
0
 
rhservanAuthor Commented:
Trailernumber
0
 
HainKurtSr. System AnalystCommented:
also can you please post a sample excel file that shows the issue...
0
 
rhservanAuthor Commented:
Excel in .png format png file from snagit
0
 
HainKurtSr. System AnalystCommented:
can you please post a small sample of that excel file
0
 
HainKurtSr. System AnalystCommented:
as an excel file, attachement I mean
0
 
rhservanAuthor Commented:
Here you go.
EESamplexls.xls
0
 
HainKurtSr. System AnalystCommented:
when you select  A2:A15 and click the icon -> Ignore error it goes away
or go to error checking options and uncheck "enable background error checking"

what happens is you enter the heading and excel thinks that column is text
and when you enter a numeric value under that, excel warns you...

it is not a big deal... when a numeric data is entered "12345" excel puts it as text and adds that warning...
0
 
HainKurtSr. System AnalystCommented:
when you select a range make sure first cell has that green option... so you can ignore all warnings for that selection...
0
 
rhservanAuthor Commented:
Just to confirm, the solution is only in Excel and not in SSRS?
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now