{"id":146,"date":"2007-12-23T06:41:00","date_gmt":"2007-12-23T06:41:00","guid":{"rendered":"\/lisa\/post\/2007\/12\/23\/A-Pedestrian-Piece-of-Code-Goes-Walkabout.aspx"},"modified":"2007-12-23T06:41:00","modified_gmt":"2007-12-23T06:41:00","slug":"a-pedestrian-piece-of-code-goes-walkabout","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2007\/12\/23\/a-pedestrian-piece-of-code-goes-walkabout\/","title":{"rendered":"A Pedestrian Piece of Code Goes Walkabout"},"content":{"rendered":"<p>\n<a href=\"http:\/\/forums.microsoft.com\/MSDN\/ShowPost.aspx?PostID=2580712&amp;SiteID=1\" target=\"_blank\" rel=\"noopener\">Somebody asked<\/a> how to convert numbers to words on one of the Reporting Services forums today, and I was interested enough to look at a bunch of VB functions, pick one out, and adapt it for reports.&nbsp; This is something that people often need for checkprinting, so it seemed worthwhile to look into it.\n<\/p>\n<p>\nThere are two callable functions in my version of this code: one that you can use with Integers and one for currency, which depends on the Integer functionality for its work.&nbsp; You can change the regular expression pattern for a different number of decimal places, or pass a different separator than the default &quot;.&quot;, if your currency is different.\n<\/p>\n<p>\nThe original code (cited in the comment in my version, below) is not great, and my version probably isn&#39;t great either, although I did fix numerous spacing issues and so on that had nothing to do with updating for .NET.&nbsp; But I think this adaptation serves to illustrate a number of general principles that tend to trip people up when they&#39;re bringing old VB functions into RS in particular and .NET in general.&nbsp;\n<\/p>\n<p>\nThe general .NET points are obvious : use honest-to-god RETURN statements in your functions, explicitly type your variables,&nbsp; use StringBuilder instead of concatenating strings, and so on.&nbsp; The RS-specific items are a little more subtle, and I decided to post this note to call out two in particular:\n<\/p>\n<ul>\n<li>You notice that I am using&nbsp;some System.Text classes (StringBuilder and Regular Expression handling), and that they are explicitly qualified. People get confused by the fact that you can&#39;t add <strong>Imports<\/strong> statements into an RDL&#39;s custom code. It&#39;s possible to add Assembly references&nbsp;to the RDL (check the Report Properties dialog, there is a tab for this), but I personally find it a pain in the neck.&nbsp; If the classes will be found at run time (especially if, as here, they are part of the base .NET Framework set) and if you explicitly qualify your class names as I have done here, you don&#39;t need to worry about assembly references and&nbsp;you don&#39;t feel the lack of an <strong>Imports<\/strong> statement capability in custom code.\n<\/li>\n<li>If you compare my version withi the original &#8212; it&#39;s almost unrecognizable, actually &#8212; one glaring difference is that I&#39;ve pulled the lists of words representing numeric parts (such as &quot;Billion&quot;) into SHARED arrays of strings, declared at the top, rather setting them up within&nbsp;any function body.&nbsp; There is absolutely no reason to recreate these lists for every&nbsp;invocation of&nbsp;the two callable functions during a report run.<\/li>\n<\/ul>\n<p>\nHumdrum as this little bit of code is &#8212; and you will probably be able to make it better &#8212; these are still principles worth living by in an RS-code universe.\n<\/p>\n<p class=\"NB\">\nSpeaking of RS-code universes, I&#39;m getting ready to dive back&nbsp;into one.&nbsp; C and I have pulled up stakes and started new work; you&#39;ll see a bit of difference here on our site, and I will probably be publishing some articles under the auspices of <a href=\"http:\/\/ecwise.com\" class=\"ecwLogoRef\"><span class=\"ecwLogoOrange\">EC<\/span><span class=\"ecwLogoGray\">|<\/span><span class=\"ecwLogoBlack\">Wise<\/span><\/a> as well as here on Spacefold.&nbsp; But my general interests will continue to be BI- and Reporting- related, and my posts here will continue.&nbsp; I&#39;ll also get back to the forums, now that we&#39;re (almost) out of boxes.\n<\/p>\n<p>\nHappy holidays, everyone.\n<\/p>\n<div class=\"code\">\n&#39; adapted from <br \/>\n&#39; <a href=\"http:\/\/cc.msnscache.com\/cache.aspx?q=72465960679242&amp;mkt=en-US&amp;lang=en-US&amp;w=577f5001&amp;FORM=CVRE8\">http:\/\/cc.msnscache.com\/cache.aspx?q=72465960679242&amp;mkt=en-<br \/>\n&nbsp; US&amp;lang=en-US&amp;w=577f5001&amp;FORM=CVRE8<\/a><br \/>\n&#39; drastically updated for .NET by LSN<\/p>\n<\/div>\n<div class=\"code\">\nSHARED&nbsp; suffixes AS String() = _<br \/>\n{&quot;Thousand &quot;, &quot;Million &quot;, &quot;Billion &quot;, &quot;Trillion &quot;, _<br \/>\n&nbsp; &quot;Quadrillion &quot;, &quot;Quintillion &quot;, &quot;Sextillion &quot;}\n<\/div>\n<div class=\"code\">\nSHARED units AS String() =&nbsp; _<br \/>\n{&quot;&quot;,&quot;One &quot;, &quot;Two &quot;, &quot;Three &quot;, &quot;Four &quot;, &quot;Five &quot;, _ <br \/>\n&nbsp; &quot;Six &quot;, &quot;Seven &quot;, &quot;Eight &quot;, &quot;Nine &quot;}\n<\/div>\n<div class=\"code\">\nSHARED tens AS String() = _<br \/>\n{&quot;Twenty &quot;, &quot;Thirty &quot;, &quot;Forty &quot;, &quot;Fifty &quot;, &quot;Sixty &quot;, _<br \/>\n&nbsp; &quot;Seventy &quot;, &quot;Eighty &quot;, &quot;Ninety &quot;}\n<\/div>\n<div class=\"code\">\nSHARED digits AS String() = _<br \/>\n&nbsp;{&quot;Ten &quot;,&quot;Eleven &quot;, &quot;Twelve &quot;, &quot;Thirteen &quot;, &quot;Fourteen &quot;, _<br \/>\n&nbsp; &quot;Fifteen &quot;, &quot;Sixteen &quot;, &quot;Seventeen &quot;, &quot;Eighteen &quot;, &quot;Nineteen&quot;}\n<\/div>\n<div class=\"code\">\nSHARED expr AS&nbsp; NEW&nbsp; _<br \/>\n&nbsp; System.Text.RegularExpressions.Regex(&quot;^-?\\d+(\\.\\d{2})?$&quot;,&nbsp;&nbsp; _<br \/>\n&nbsp; System.Text.RegularExpressions.RegexOptions.None)\n<\/div>\n<div class=\"code\">\n&nbsp;PUBLIC Function ExpandPrice(pPrice AS String, _<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Optional pSeparator AS String = &quot;.&quot;) _<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AS String&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br \/>\n&nbsp;&nbsp;&nbsp; Dim temp AS New System.Text.StringBuilder()<br \/>\n&nbsp;&nbsp;&nbsp; If&nbsp; Not expr.IsMatch(pPrice) Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39; temp.Append(pPrice) or whatever you want to do here<br \/>\n&nbsp;&nbsp;&nbsp; Else<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim parts AS String() = pPrice.Split(pSeparator)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim dollars AS String = parts(0)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim cents AS String = parts(1)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If CDbl(dollars) &gt; 1 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp.Append(ExpandIntegerNumber(dollars) &amp; &quot;Dollars &quot;)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If CInt(cents) &gt; 0 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp.Append(&quot;And &quot;)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ElseIf&nbsp;&nbsp;&nbsp; CDbl(dollars) = 0 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp.Append(ExpandIntegerNumber(dollars) &amp; &quot;Zero Dollars &quot;)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If CInt(cents) &gt;= 0 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp.Append(&quot;And &quot;)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ElseIf&nbsp;&nbsp;&nbsp; CDbl(dollars) = 1 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp.Append(ExpandIntegerNumber(dollars) &amp; &quot;Dollar &quot; )<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If CDbl(cents) &gt; 1 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp.Append(ExpandIntegerNumber(cents) &amp; &quot;Cents&quot;)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ElseIf&nbsp;&nbsp;&nbsp; CDbl(cents) = 0 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp.Append(ExpandIntegerNumber(cents) &amp; &quot;Zero Cents &quot;)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ElseIf&nbsp;&nbsp;&nbsp; CDbl(cents) = 1 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp.Append(ExpandIntegerNumber(cents) &amp; &quot;Cent &quot;&nbsp; )<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br \/>\n&nbsp;&nbsp;&nbsp; End If<br \/>\n&nbsp;&nbsp;&nbsp; RETURN temp.ToString()<br \/>\n&nbsp;End Function<br \/>\n&nbsp;&nbsp;&nbsp; <br \/>\n&nbsp;PUBLIC Function ExpandIntegerNumber(pNumberStr AS String) AS String<br \/>\n&nbsp;&nbsp;&nbsp; Dim temp2&nbsp; AS New System.Text.StringBuilder()<br \/>\n&nbsp;&nbsp;&nbsp; Dim number AS String = _<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; StrDup(3 &#8211; Len(pNumberStr) Mod 3, &quot;0&quot;) &amp; pNumberStr<br \/>\n&nbsp;&nbsp;&nbsp; Dim i AS Integer, j AS Integer = -1<br \/>\n&nbsp;&nbsp;&nbsp; Dim numPart AS String<br \/>\n&nbsp;&nbsp;&nbsp; For i = Len(number) &#8211; 2 To 1 Step -3<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; numPart = Mid(number, i, 3) <br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If Clng(numPart &gt; 0) Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If j &gt; -1 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp2.Insert(0,suffixes(j),1)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp;&nbsp; <br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp2.Insert(0,GetNumberUnder1000Str(numPart),1)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; j&nbsp; += 1<br \/>\n&nbsp;&nbsp;&nbsp; Next<br \/>\n&nbsp;&nbsp;&nbsp; RETURN temp2.ToString()<br \/>\n&nbsp;End Function<br \/>\n&nbsp;&nbsp;&nbsp; <br \/>\n&nbsp;Function GetNumberUnder1000Str(pNumber AS String) AS String<br \/>\n&nbsp;&nbsp;&nbsp; Dim temp1 AS New System.Text.StringBuilder()<br \/>\n&nbsp;&nbsp;&nbsp; If Len(pNumber) = 3 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If CLng(Left(pNumber, 1)) &gt; 0 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp1.Append( _<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GetNumberUnder100Str(Left(pNumber, 1)) &amp; &quot;Hundred &quot;)<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp;&nbsp; <br \/>\n&nbsp;&nbsp;&nbsp; End If<br \/>\n&nbsp;&nbsp;&nbsp; temp1.Append(GetNumberUnder100Str(Right(&quot;0&quot; &amp; pNumber, 2)))<br \/>\n&nbsp;&nbsp;&nbsp; RETURN temp1.ToString()<br \/>\n&nbsp;End Function<br \/>\n&nbsp;&nbsp;&nbsp; <br \/>\n&nbsp;Function GetNumberUnder100Str(pNumber AS String) AS String<br \/>\n&nbsp;&nbsp;&nbsp; If pNumber &gt; 19 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN tens(Left(pNumber, 1) &#8211; 2) &amp; units(Right(pNumber, 1))<br \/>\n&nbsp;&nbsp;&nbsp; ElseIF pNumber &gt;= 10 and pNumber &lt;= 19 Then<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN digits(Right(pNumber, 1)) <br \/>\n&nbsp;&nbsp;&nbsp; Else<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN units(Right(pNumber, 1))<br \/>\n&nbsp;&nbsp;&nbsp; End If<br \/>\n&nbsp;End Function\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Somebody asked how to convert numbers to words on one of the Reporting Services forums today, and I was interested enough to look at a bunch of VB functions, pick one out, and adapt it for reports.&nbsp; This is something that people often need for checkprinting, so it seemed worthwhile to look into it. There<a class=\"more-link\" href=\"https:\/\/spacefold.com\/lisa\/2007\/12\/23\/a-pedestrian-piece-of-code-goes-walkabout\/\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,4,5],"tags":[],"class_list":["post-146","post","type-post","status-publish","format-standard","hentry","category-asp-net","category-life","category-reporting"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/146","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=146"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/146\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=146"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=146"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=146"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}