Problem with Percentages in EXL2K

October 06, 2008, 04:09 AM

We upgraded to 7.6.2 in april of this year and before that we were on 5.3.3

In 5.3.3 when reports were exported to excel in the exl2k format ,

columns with percentages became percentage column's in excel

now they become a custom column that doesn't act as a percentage column?

ex.

  

 TABLE FILE CAR
 SUM
 COMPUTE PERC/F7.2%=DEALER_COST/SALES*100;
 BY COUNTRY
 ON TABLE PCHOLD FORMAT EXL2K
 END

If you look in excel at the output of the example above, you(ll find that the
format of the cell is #,00\% instead of #,00%

Any clues , how to resolve this issue?

Thanks,

P.

October 06, 2008, 05:04 AM

<JG>

Pete

quote:
doesn't act as a percentage column?

Don't under stand what you mean.

When I run your code in 7.6.6

it displays as

ENGLAND 315.44%
etc.

Yes the format is custom #,00\% but that does not appear to cause any problems
I can sum the columns, I can copy and paste special, values and they appear fine to me.

October 06, 2008, 05:18 AM

Pete

Hi JG,

I know that it is a bit confusing

Here is a result with or without / :

COUNTRY PERC (with / in mask) PERC without / Sales Sales * PERC1 Sales * Perc2
JAPAN 7,06% 7,06% 200 1412 14,12

7,06% of 200 = 14,12 not 1412

October 06, 2008, 07:53 AM

<JG>

Pete,

Yes I see exactly what you mean.

That is a change in basic functionaltity and should be reported as a bug to support.

There is absolutely no reason why they should change that.

The only possible excuse would be, that accomodation has had to be made for Microsoft Excel
functionality change, as has happened with templates in office 2007.

That is not the case in this situation.

Even if that was the situation it should be accomadated in a diffenent way as that means
every single IB customer has to upgrade to Office 2007 before they can upgrade WebFocus.

The only work around that I can see at the moment is to use an Excel template and pass
the value without the *100 and have the template format the column and not do it in WebFocus.

October 06, 2008, 10:26 AM

j.gross

IBI blew it when they originally introduced %. (It should have worked, like Excel percentage formats, as a directive to rescale the result and add a "%" suffix; instead they made % just a decoration. True, we would have had to rework computations to take advantage of the % format option -- but we could do so at our own pace or not at all)

I think the decision to change the behavior from 5.x to 7.x may be related to the EXL2K FORMULA hold option.

In general (I gather) under 5.3 WF did you the service of dividing your "percent" field by 100 when holding for Excel, so it could apply a true "Percentage" Excel format to the cell and let nature take its course. Thus your computed 12.0 which WF prints as 12.0% would be held in the cell as 0.12 to display correctly as 12.0%, and you could add columns with formulae referencing the percentage cell after the download).

But for a COMPUTEd percentage, with the FORMULA option, they would have to modify your computational formula to rescale the result; and then they would have to find an appropriate way to modify the formula stored for any other COMPUTE columns that are based on your computed percent, so that you would see the same thing in Excel as in WF's HTML or PDF report output. I'm not sure they had much choice.

BUT, if so, they still should have consulted the user community on how holding as Exl2k (with and without Formula) should behave, to try to come up with a solution that preserves the (desirable) behavior in 5.x ... So they blew it again.

October 07, 2008, 02:07 AM

Pete

jgross,

nail on the head , i opened a case , and your answer is pretty much what they gave me in response :

quote:

HEllo Peter,

I did some more research and it seems indeed that the way the percentage is
represented in excel has changed. Several customers had the same issue, but
programming looked into it and will not change the behaviour, since it was not
correct before and now it is.
This is the official statement I found:
The change for the Excel format now includes an escape character that
prevents Excel from calculating the percent (\%). This change creates
consistency across the WF output options in that the percent is not
calculated when using the percent display option.

In the early 53x series there was an error in EXL2K with % formats.
Use of the % format together with EXL2K would give values that looked
correct in individual Excel cells, but were off by a factor of 100 when
the cell value was used in an Excel formula such as SUM() or AVERAGE().
For example:
TABLE FILE CAR
SUM DEALER_COST RETAIL_COST
COMPUTE RATIO /D9.2 = DEALER_COST /RETAIL_COST *100;
COMPUTE PRCNT /D9.2% = DEALER_COST /RETAIL_COST *100;
BY COUNTRY BY CAR
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END
would produce the following output:
RATIO PRCNT
83.24 8,324.47%
83.70 8,369.75%

Having Excel format a Percent value causes Excel to multiply it by 100.
The value for PRCNT should display as 83.24% not 8,324%. Therefore the
way we ask EXL2K to format the output was changed to only append a '%'
character to the end of the cell, and not look at the value in the cell
as a Percent. This allows the value displayed to have a correct internal
value, and still match WebFocus formatting.

This change in behavior is a correction rather than a problem.
You can avoid this behavior if you take the value and use FTOA to
convert it to an alpha string and concatenate the % symbol at the end of
it.

What's the point of turning it in an alphanumeric field ????

Besides i can't use exl2K formula in 99% of our reports , we use OVER alot and it wont let us use exl2K formula

P.

October 07, 2008, 08:57 AM

linus

I've run into the same problem. I solved it by converting my percent field to alpha using the PTOA subroutine. This will bring the '%' symbol into the value of the field. See my code example:

  
GTNSPCT01/P14.2% = ((CAMT01 - PAMT01)/PAMT01)*100;
    AGTNSPCT01/A21 = PTOA(GTNSPCT01, '(P14.2%)', AGTNSPCT01);

I then use the AGTNSPCT01 field in my final report output.