Percentage Format in T-SQL

There is no function to present the percentage format in T-SQL, something like 17.25%. There are various ways to do this. However, for reporting purpose, the easiest way might be:

CONVERT(VARCHAR, Result_From_Calc)+'%'

Following is an example how to add Total row at bottom of the selected dataset as will as add percentage column for each of rows.

The original dataset is:

SELECT Leg_Id
, Statute_Miles
FROM dw_Legs
GO

The final script is as follows:

WITH Totaling
AS
(
SELECT SUM(Statute_Miles) AS Milage
FROM dw_Legs
)
SELECT Leg
, Milage
, CONVERT(VARCHAR, Milage*100/(SELECT Milage FROM Totaling))+'%' AS Percentage
FROM
(
SELECT CONVERT(VARCHAR, Leg_Id) AS Leg
, Statute_Miles AS Milage
FROM dw_Legs
UNION
SELECT 'Total' AS Leg
, Milage
FROM Totaling
) AS Dataset
GO

Explaination:

1. Total milage will be used more than once, so it has been in put into WITH clause.
2. The reason convert Leg_Id into VARCHAR is because word ‘Total’ is a VARHCAR. Else the UNION won’t work.
3. Make sure there is only one row being selected into WITH clause. That is where people usually create a bug.

Following is the result:

Clearing Obstacles for MS Excel Application Upgrading to 2007

,Recently an Excel application wrote in 2003 version needs to be converted into 2007 following the network infrastructure upgrade. It is noticed there are three parts needed to be attended.

In VBA micro of old application, value of attribute FileFormat for ActiveWorkbook.SaveAs was xlNormal. It is no longer allowed in Excel 2007. The FileFormat now needs to be explicitly expressed. Common values are:

51 = xlOpenXMLWorkbook (without macro's in 2007, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007, xls)

In this case, value of 52 is used. For more information, please refer to http://www.rondebruin.nl/saveas.htm.

File name is also an issue. This application automatically archives data everyday into a file, of which there is a space in file’s name. Now VBA in Excel 2007 converted the space to ‘%’ sign. Accordingly, the archiving file’s name needs to be changed, as well as the file type changed from .xls to .xlsm.

Originally archiving function involved large chunk of cells being copied from one worksheet to another. After conversion to Excel 2007, it is noticed the file’s size increase drastically without apparent reason. The educated guessing is somehow the application accumulated its clipboard during the automatic archiving process which involved copy-paste and save-as. However, nothing was found either in worksheets or clipboard. Web search shows no such error had been reported to Microsoft. After rewrote codes in this part to remove format as part of clipboard, the problem is resolved, although the reason is still not be found.