Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.
Originally posted by: ejonesThanks for this second question, it helped me realize what the best answer is for the first question.
The best thing to do for the best looking reports is to use software that has formatting beautiful reports as its purpose. LAE is very good for pulling the data together for one of these other reporting systems.
So for Excel, the best thing to do is learn how to use the "Output Excel" node. On the Optional tab there is a parameter named TemplateInputFile. This is where you put the name of a spreadsheet that is already formatted the way you want the report to be formatted. In that spreadsheet you preformat the columns to have dollar signs and commas as you want them.
I'm also familiar with Tableau and I'm sure other similar packages have the same feature where you configure the formatting of the data for the reports being produced from it. Using LAE you pull the data together and do your analysis, then feed the results to a reporting tool and let it format it.
But, I have done the formatting in BRAINscript and it isn't easy. You have to write many lines of code and make certain assumptions about what the format will be and code it that way. Note that it is generally good practice to keep your formatting code separate from the logic that pulls the data together and does analysis because this way when you are working on the analysis the reporting isn't being touched. And when adjusting the reporting, you are staying away from the analysis logic.
In the following code, the number that is to be formatted is in a field named 'f'. The values always more than -1 billiion and less than 1 billion. There will always be two digits after the decimal which is indicated by a period and the thousands separator is indicated by a comma. There is a large part of this world that tends to flip the meaning of the comma and period and if this is done, an additional step would need to be done to replace the period produced by the format() function with a comma.
The code begins by initializing s and then builds it in pieces. It then outputs the result in a string field named s. I'm sure this can be improved.
if 'f' < 0
then s = "-"
else s = ""
w = abs('f').floor().long()
p = abs('f') - w
if w > 1000000 then {
s = s + w.div(1000000).str() + ","
w = w.mod(1000000)
}
if w > 1000 then {
s = s + w.div(1000).str() + ","
w = w.mod(1000)
}
s = s + w.str() + format("%.02f",p).substr(1)
emit s