RE: [sqr-users] Using SYLK.sqc - writing currency to a spreadsheet so that the currency symbol appears on the left

[SandlerA@DFO-MPO.GC.CA]

Thank you to all for responding.  I discovered that the best solution to the
problem was to make a change to the SYLK.SQC file.  In its
SYLK-INITIALIZE-FILE section, I changed the section where it writes the
header information so that if the report is English, the format for 2
decimal number with '$' sign are P;P$0.00 and P;P$#,##0.00 while if the
report is French, the formatting is P;P0.00$ and P;P#,##0.00$.  I only need
to change the 2 decimal number with '$' sign because currency figures always
have 2 decimal places.  (This change substitution is made at compile time
since our users only run the compiled versions of the reports).

The only documentation I've ever seen on the SYLK file are the comments at
the beginning of the SYLK.SQC procedure and the comments in the
SYLK_DEMO.SQR file.

Thank you once gain for your help.

Aaron Sandler
(613) 991-4862 |facsimile/télécopieur (613) 993-4811
SandlerA@dfo-mpo.gc.ca 
MIMS Technical Team |Équipe technique SGIE
Canadian Coast Guard|Garde côtière canadienne
437 - 360 Laurier, Ottawa, ON  K1P 1C8
Government of Canada| Gouvernement du Canada



-----Original Message-----
From: Franck Masson [mailto:Franck_Masson@hyperion.com] 
Sent: May 24, 2006 5:34 PM
To: SandlerA@dfo-mpo.gc.ca
Subject: RE: [sqr-users] Using SYLK.sqc - writing currency to a spreadsheet
sothat the currency symbol appears on the left


Hi,

when i am face to such issue 
i use excell to find the solution
I mean open excell and format the cell as you would like to appear 
then save this document in sylk format 
and open the document.sylk in notepad and look to the sylk syntaxe and
reproduce it in sqr 

see sample in attachment 

you will have to define two format one for english and one for french in
your sylk header 

Franck Masson
Director, Business Intelligence
Mobile : +33 6 24 33 79 72


-----Original Message-----
From: sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org
[mailto:sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org]On
Behalf Of SandlerA@DFO-MPO.GC.CA
Sent: mercredi 24 mai 2006 21:43
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Using SYLK.sqc - writing currency to a spreadsheet
sothat the currency symbol appears on the left


Actually, no it isn't an Excel problem.  The feature is present in Excel.
Note that the format of the number is correct and although I didn't mention
it, the dates also appear correctly in French.

It is possible that this is a limitation of SYLK.  The function to format
the cell to a number allows for specifying the number of decimal places and
specifying whether the dollar sign should be present or not.  The feature is
present in Excel.  If I open the spreadsheet in Excel, then I go to one of
the cells that contains a dollar figure, select format cell, set the
Category to currency and the symbol to '$', if my Regional Settings are
French Canada, the dollar sign appears to the right of the number as it
should.  When SYLK procedure writes the number to the spreadsheet, it has a
category of custom and type of $# ##0,00.  This is correct except for the
position of the dollar sign.  If my regional settings are English Canada,
the number does appear correctly and is formatted as type Currency and
symbol '$'.  When I write the number to the spreadsheet in my SQR program, I
do change the format of the number so that it simply writes a number to the
spreadsheet.  (If I try to use the function sylk-write-number when the SQR
format is set to 999 999,99 an error occurs when opening the spreadsheet.
What should be passed is in the format 999,999.99 and the currency symbol
set to '$').  Either something odd is happening when the number is being
written to the file, or perhaps you are right and it is an Excel problem.

Aaron Sandler
(613) 991-4862 |facsimile/télécopieur (613) 993-4811 SandlerA@dfo-mpo.gc.ca 
MIMS Technical Team |Équipe technique SGIE
Canadian Coast Guard|Garde côtière canadienne
437 - 360 Laurier, Ottawa, ON  K1P 1C8
Government of Canada| Gouvernement du Canada



-----Original Message-----
From: sqr-users-bounces+sandlera=dfo-mpo.gc.ca@sqrug.org
[mailto:sqr-users-bounces+sandlera=dfo-mpo.gc.ca@sqrug.org] On Behalf Of
Knapp, Richard
Sent: May 24, 2006 3:16 PM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: RE: [sqr-users] Using SYLK.sqc - writing currency to a spreadsheet
sothat the cu rrency symbol appears on the left



Talk to Bill (Gates, that is).  This is an Excel problem, not something you
can control from SQR especially since the feature is not present in the
destination application, no?

Richard Knapp
AITS - Reporting
University of Missouri
Locust Street Building
Columbia, MO 65201
573-882-8856
knappr@umsystem.edu


-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
SandlerA@DFO-MPO.GC.CA
Sent: Wednesday, May 24, 2006 1:27 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Using SYLK.sqc - writing currency to a spreadsheet
sothat the cu rrency symbol appears on the left

Hi All,
 
I have many reports where I am writing both an SQR output and writing to a
spreadsheet.  I write to the spreadsheet using SYLK.SQC.  I need to produce
the output in both French and English (i.e. if and English user runs the
report the headings and formatting are in English while if a French user
runs the report, the headings and formatting are in French).
 
On many of the reports, I need to write dollar figures to the spreadsheet.
In English, the format should be $999,999.99.  This works without any
problem.  In French, the format should look like 999 999,99$.  On the SQR
output this works fine.  However, in writing to Excel, the dollar sign
always appears on the left side of the number not the right.  The spacing
and the commas on the spreadsheet do appear where they are supposed; just
the dollar sign does not.  The position of the currency symbol should be a
function of the Windows regional settings; no matter how I set these, the
currency symbol always appears on the left.
 
I do need to write these values to the spreadsheet as numbers (not as
characters, so I cannot set the format that way).  While the users know that
they can change the format on the spreadsheet, they would rather that it
appear from the report output as they want to see it.
 
We are in a Windows environment.  The reports are generated on a Metaframe
server running Windows 2000.  The users view the spreadsheet on their own
desktops.  Most of these are running Windows XP and the regional settings
are either English or French depending on the users preference.
 
The reports are created in SQR version 6.2 and are run as compiled reports
from Maximo 4.1.1.
 
Does anyone know how I can write to the spreadsheet so that the currency
symbol will appear to the right of the number on French reports?
 
Thank you.
Aaron Sandler
(613) 991-4862 |facsimile/télécopieur (613) 993-4811

SandlerA@dfo-mpo.gc.ca <mailto:SandlerA@dfo-mpo.gc.ca>  

MIMS Technical Team |Équipe technique SGIE

Canadian Coast Guard|Garde côtière canadienne

437 - 360 Laurier, Ottawa, ON  K1P 1C8

Government of Canada| Gouvernement du Canada

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users


If you have received this e-mail in error, please delete it and notify the
sender as soon as possible. The contents of this e-mail may be confidential
and the unauthorized use, copying, or dissemination of it and any
attachments to it, is prohibited.

Internet communications are not secure and Hyperion does not, therefore,
accept legal responsibility for the contents of this message nor for any
damage caused by viruses. The views expressed here do not necessarily
represent those of Hyperion.

For more information about Hyperion, please visit our Web site at
www.hyperion.com


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users