On my previous project, I needed to select a number stored in the database, such as 36749384 or 934834.23 which were monetary values, and display them as 36,749,384.00 and 934,834.23. Basically, in monetary terms.
I was thinking of using string manipulation but that would only work in my .net development, so if you need these kinds of format in other applications, you can do it in the SQL statement like so:
SELECT convert(varchar(50), convert(money, Actual), 1) AS Actual from table
And even though I realised after that in ActiveReports you could actually just apply a mask to the textfield or label (D’oh!), in properties > OutputFormat > #,##0
I thought this SQL statement was a pretty nifty way of getting the desired output i wanted without doing any programming on the data.
3 Comments
February 2, 2008 at 5:15 pm
Actually, the ActiveReports way is better because it is better to assign formatting responsibility to the interface rendering module, which in this case is ActiveReports. This is a technique known as “Separation of Concerns”. It keeps your code clean.
Besides, using a mask is not “programming”, it’s configuration.
Use SQL to retrieve data only (your SQL code looks ok now, but try doing that to result sets of 250K rows) as performance counts.
And do not embed business rules in SQL. It’s very hard to debug business rules in SQL.
February 2, 2008 at 7:44 pm
Hi ivan
wow mister programmer, yepyep i definitely used the mask in the end. The currency way was a very cumbersome way and made the SQL too complicated, especially when you needed multiple fields like this. But thought i’d post what i found in case there would ever be a scenario someone would want something like this
June 19, 2008 at 2:31 pm
Somehow i missed the point. Probably lost in translation
Anyway … nice blog to visit.
cheers, Geothermic.