Present Accounting Data

[JrgMyr]

When analyzing accounting or production-related data it is common practice to show larger integer numbers and monetary amounts with separators after every three digits. In an application based on the SQLite library this would be the duty of the application to present data nicely.

I understand that SQLiteStudio is a frontend and not a business apllication. Fortunately, there is the function editor. I have created two funtions to present large integers (frmInt) and monetary amounts (frmAmt) which are shown in two further posts.

I have to say I have difficulties using named arguments. Could somebody please show me a working example of named argument function? Then the width of the presentation field could be supplied and varying.

When using frmInt() and frmAmt() it is useful to change the character font for database results to a fixed width font such as "Lucida Sans Typewriter" or similar. A query such as "SELECT Company, Year, frmInt(COUNT(*)) AS LineItems, frmAmt(SUM(amount)) AS TotalAmount FROM datatable GROUP BY Company, Year;" can look like this:

Do you like that?

Best regards, Jörg

[pawelsalawa]

Currently named arguments are not visible under their names in the function implementation code. There is ehancement planned to maka it work this way: #3272

But before that happens you need to handle parameters the usual way. For QtScript it's arguments[] array, for Tcl it's argv and argc variables, for SQL implementation lang it's $1, $2, and so on.

[pawelsalawa]

Functions are now added to the list on https://github.com/pawelsalawa/sqlitestudio/wiki/Scripts_repository

Thanks!

Please let me know if decriptions are okay.

[JrgMyr]

Great. Thanks. I have two remarks:

1. The description of frmAmt() says: "Formats numbers like ... or 23434,68 to 23.434,68". Actually it is "23434.68 to 23.434,68". The source number is a real number with a decimal dot. The presentation style is German DIN format with a decimal comma. Of course, it is easy to adjust the scripts to have apostrophes or commas or whatever as separator. Even Indian style such as "1,23,456.78" with lakhs is easily possible.

2. You might want to repeat the sentence about using fixed width fonts on the wiki page. They improve readability of numbers and are necessary for right-aligned presentation.

[pawelsalawa]

Updated. Also added your example SQL query as examples to both functions.