4 Replies Latest reply: May 15, 2014 10:14 AM by JLain _ RSS

    How to replace fields with zero with a blank cell in the report function

    JLain _

      So, I have a db that I'm working with. It is an AR aging with aging buckets as calculated fields. In the report view ...and export for that matter, I would like to return a blank or empty cell rather than the current 0's

       

      So, the below invoice let's say is from Sept with30 day terms..It would be aged in the 1-30 day bucket.

       

      Inv Amt , Current, 1- 30, 31-60

      1,000, 0, 1,000, 0, 0, etc.....

        • How to replace fields with zero with a blank cell in the report function
          Olly Bond

          Hello JLain,

           

          Is the data only available as a database? Or is it a text report? If it's a delimited file, like csv, it might be easier than if it's in .mdb or .xls.

           

          Do you have any records with multiple values, e.g.

           

          Inv Amt, Current, 1-30, 31-60...

          1000, 500, 0, 500...

           

          and if so, how do you want to handle them?

           

          Best wishes,

           

          Olly

            • How to replace fields with zero with a blank cell in the report function
              JLain _

              The db is not text as the source record is converted to excel as it exports from SAP. I dont think I have much control on the output but I can see.

               

              Once the AR drops into the appropriate aging bucket it only does so one time. So in the example, the $1000 will only be represented once in the aging categories which are calc fields..not from the source excel db.

               

              Tks for the response

                • How to replace fields with zero with a blank cell in the report function
                  Olly Bond

                  Hello Jlain,

                   

                  To get the data from SAP as text, not Excel, the user just has to select "unconverted" as the format. But in this case, you're just getting, as far as I can tell, an amount and a date, from which the age of the debt is being calculated in Monarch. You can stop showing the zero values by going to Options>View. This will show blank cells in Monarch, but if you export to Excel the true zero value will still appear. If that's a problem, let me know and we can go through a summary trick to get around it.

                   

                  If you want the cells to have the value of null, rather than zero, edit the if() statement so that they take the value 1/0 rather than 0.

                   

                  Hope this helps,

                   

                  Olly