QV10 Numeric fields exported as text

[john_duffy]

Hello All.

We are in the process of migrating from QV8.5 to QV10 SR2. 

One issue I am having is that when objects are sent to Excel in QV10, numeric fields are formatted as text in the spreadsheet.  The cells also have the green triangle in the upper left corner indicating an error in the formula in the cell.  When the cell is selected, Trace Error indicates that the number in the cell is formatted as text or is preceeded by an apostrophe.

The same objects send to Excel in QV8.5 have numeric fields formatted as numeric in the spreadsheet.

I can get around this issue by changing the number format setting for each field to Number.  If this is done, the fields export in numeric format. 

Can someone explain why this is happening in QV10?  Is there any other work around than changing each fields number format setting?

I have attached a very simple sample application to illustrate the issue.

Thanks,

John.

[derekjones]

Hi John

I have exactly the same problem and I agree as you suggest, the only way to correct is by manually selecting the format in the object.

But for my report this does not work as each field the formatting is done in the expression as the expression shows different formatting dependent on which type is chosen i.e. £ for Revenues, Numeric for Traded Accounts and % for return ratios....

If([Field] ='Gross Profit', num(SUM(LINE_PROFIT),'£#,##0;-£#,##0'),

If([Field] ='Revenue', num(SUM(LINE_VALUE),'£#,##0.00'),

If([Field] ='Margin', num(SUM(LINE_PROFIT)/SUM(LINE_VALUE),'#,##0.0%'),

If([Field] ='Returns', num(fabs((sum(if(left(LINE_TYPE_NAME,6)='MP-RTN',LINE_VALUE))/sum(LINE_VALUE))),'#,##0.0%'),

If([Field] ='Units', num(SUM(QUANTITY),'#,##0'),

If([Field] ='Freight', num(sum({<LINE_TYPE={"Freight Lines"}>} LINE_VALUE)/(sum({<LINE_TYPE={"Freight Lines"}>} LINE_COST)),'#,##0.0%'),

If([Field] ='Traded Accounts', num(count({<CUSTOMER_CODE={"MC*","MX*"},LINE_TYPE_NAME={"MP-SAL-Standard Line"}>} distinct CUSTOMER_CODE),'#,##0'))))))))

I'm afraid I'm at a dead-end too, wrapping your calculation in num() should have sorted it (as QV9 worked). So I think it's a bug that needs correcting in QV10 unless I've missed something too.

Does anyone know how to formally report bugs in QV10?

Thanks

Derek

[derekjones]

Actually, strangely enough, for yours I've managed to get num() to work (see attached).

After investigation, mine is caused by the IF statement, somehow when you use an if statement with more than one format in QV10 it exports as text. I'll create my own thread to solve so you can close yours down.

Anyway, either you can do the num() in the calculation as in the attached or you can do it in your script on the load.

Good luck...

[derekjones]

For anyone interested, I've attached in this reply my example of it failing with IF's. The first column of data contains an expression with an IF which sets the data either to numeric or £ and the second is a straight expression to £. On export, the first column comes out as text and second column loses the £ but is numeric. Bonkers!