5 Replies Latest reply on Jul 24, 2013 9:01 AM by philmodjunk

    ExecuteSQL drops zeros after decimal when extracting price information...

    productionQC

      Title

      ExecuteSQL drops zeros after decimal when extracting price information...

      Post

            

           Hello -

           I am using the following calcuation to extract pricing information and display in columns.

           ExecuteSQL ( "SELECT Category, Sum ( Cost ) FROM Renovation GROUP BY Category ORDER BY 2 DESC" ; Char (9) ; "" )

            My problem is that SQL drops any zero's after pricing.  Thus they will not line up properly.  Does anyone know how to keep the zeros?

           Please see attached photo which illustrates the problem.  Notice that the top price has no ".00" after it causing it not to line up...

           Thanks

            

      Voila_Capture44.png

        • 1. Re: ExecuteSQL drops zeros after decimal when extracting price information...
          philmodjunk

               A good question for which I do not have an answer. But I do know of a way to list these sub totals in a portal...

          • 2. Re: ExecuteSQL drops zeros after decimal when extracting price information...
            productionQC

                 Hi Phil - Ok, so let's try the portal route, how do I go about doing that?

                 Much Thanks

                  

            • 3. Re: ExecuteSQL drops zeros after decimal when extracting price information...
              productionQC

                   My only hesitation in using a portal would be that I need these numbers accessible for further calculations, like comparisons to budget numbers to see if they are over or under...etc...  I have used one line portals in the past, but as you know they trap the data and can not be used in further formulas.  I like the SQL route, but boy is it finicky...  My end result on this project will be to have a dashboard that will show each category of items, there totals from both cost and budget with another column showing the difference (Budget - Cost sort of thing).

              • 5. Re: ExecuteSQL drops zeros after decimal when extracting price information...
                philmodjunk

                     I generally limit myself to one response per thread per day. Otherwise, volunterring in the forum consumes way to much of my day.

                     An alternative approach comes to mind that could make the ExecuteSQL work, though the performance hit involved might not be acceptible. A recursive custom function could be crafted to take the results returned by ExecuteSQL and reformat the number column to supply trailing zeroes after the decimal and even, if desired a leading currency symbol.

                     Using the portal method that I referred to ealier:

                     You'd need relationships similar to this:

                     DashBoardTO-------<Categories-------<Expenses

                     DashBoardTO::anyField X Categories::anyField
                     Categories::CategoryFIeld = Expenses::CategoryField.

                     Then you can place a portal to Categories on your DashBoard layout with the Category field and a summary field from Expenses can be placed in your portal row. A calculation field defined in Categories can also compute these sub totals using the sum function to sum a field from expenses.

                     And yes, this totals all expenses in each category. There's an extremely good chance that what you really need are subtotals for a sub set of all the records in Expenses such as all the expense data for a specified month, quarter or year. Adding additional match fields in the Categories to Expenses relationship can accomplish that result. You can even use a global field or Fields defined in Categories so that the user can edit them on teh DashBoard layout to get different sets of sub totals.