Data Conversion of many fields

[#1]

Hi,

I am new to crystal.

I have a table with a bunch of fields that need to be converted from string to number.

I feel like I need to have some kind of looping statement that will convert each field. But It seems like I need to create the same number of formulas as I have fields.

Attached is screen shot of what I have to work with.

Each of those fields is for lack of a better term, is the content for each cell of a grid that I've built in my table.

I however need to do calculations on them and can't while they are in string format.

Thanks,

Wade

[abhilash_kumar · Accepted solution]

Hi Wade,

Unfortunately, yes, you'll need to create a formula for each field if you're trying to convert the datatype.

Instead, use a stored proc or a sql query as the datasource for the report and handle the conversion back at the database side.

P.S: You can also use a 'SQL Expression' - but that again means creating one for each field.

-Abhilash

[Former Member · Accepted solution]

Thanks, I was afraid of that. 

[JWiseman · Accepted solution]

to save you from this pain you may wish to talk to your dba about altering the column types from text to the proper type. here's how to do this in sap hana, not sure what db you're using but the syntax will be database specific and may vary from these commands. this would save you lots of work if you have to do convert statements for each field for each report.

or if your dba is unwilling to make the changes and you're using an enterprise product (cr server or businessobjects enterprise) create a universe or business view (depending on what version you're using) so that this is done only once.

[Former Member · Accepted solution]

We're using MSSQL and the DBA won't change the data type.

I don't think I'm using an enterprise product.

THanks

Wade

[JWiseman · Accepted solution]

that's too bad. you can save yourself some future pains though by creating all of your reports off of a command object as mentioned by Abhilash. if you've never used a command before, create a new report off of your existing ms sql connection and press the Add Command link instead of using tables.

now you can hand type your sql and use the proper cast statements in your command object. commands are database specific so you'll need to use syntax appropriate to that db and version.

once you've created that command object with all of the conversions then save your report to use it as a template for future reports as you can then reuse that syntax. so you don't really need an enterprise product to do this.

[JWiseman]

hi Wade,

you can use the Val() function in a formula against those fields in crystal reports. i.e. create a new formula for each field that you need to convert.

or you can use a SQL Expression field to Convert the fields. you'll need to check your database manual for the proper function (could be a cast statement as an example) as sql expressions are in your database syntax.

-jamie