Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Problem with Percentages in EXL2K
Go
New
Search
Notify
Tools
Reply
  
Problem with Percentages in EXL2K
 Login/Join
 
Platinum Member
posted
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.


D: WF 7.6.2 P. : WF 7.6.2 on W2003
------------------------------------------------------------------
I see myself as an intelligent, sensitive human, with the soul of a clown which forces me to blow it at the most important moments.

-Jim Morrison-

 
Posts: 206 | Registered: February 25, 2005Reply With QuoteReport This Post
<JG>
posted
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.
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


D: WF 7.6.2 P. : WF 7.6.2 on W2003
------------------------------------------------------------------
I see myself as an intelligent, sensitive human, with the soul of a clown which forces me to blow it at the most important moments.

-Jim Morrison-

 
Posts: 206 | Registered: February 25, 2005Reply With QuoteReport This Post
<JG>
posted
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.
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


D: WF 7.6.2 P. : WF 7.6.2 on W2003
------------------------------------------------------------------
I see myself as an intelligent, sensitive human, with the soul of a clown which forces me to blow it at the most important moments.

-Jim Morrison-

 
Posts: 206 | Registered: February 25, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Linus,

By doing that you are turning the percent column into a general mask column , instead of an percentage mask column

if your output was 7 and you multipled it with 200 it would equal 1400 instead of 14 Percent


D: WF 7.6.2 P. : WF 7.6.2 on W2003
------------------------------------------------------------------
I see myself as an intelligent, sensitive human, with the soul of a clown which forces me to blow it at the most important moments.

-Jim Morrison-

 
Posts: 206 | Registered: February 25, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
It's strictly an alpha field for display purposes only at this point. I only had problems with my subfoots not carrying the '%' into Excel. The data fields within the body of the report did carry the '%' into the spreadsheet.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Problem with Percentages in EXL2K

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.