1 Reply Latest reply: May 15, 2014 10:04 AM by Data Kruncher RSS

    Inserting space holders with zeros in Monarch V8

    JeanR _

      I am new to the forum and new to Monarch 8 and might not be able to word this message correctly, so bare with me. It took me two days to figure out how to post this message!

       

      My office uses Profitool to run PAR reports. The reports contain activity numbers and if a particular activity posts hours or dollars to "material", for instance, it lists those hours and dollars. If the activity doesn't contain material, it doesn't list anything.

       

      I am trying to create an excel spreadsheet from this profitool report using monarch, and all is well, except that I need a way to record zeros for the activites that don't contain "material" (for example) or "equipment"...

       

      When I create the monarch report for this, it magically inserts a space where there is no data, but duplicates the data from the closest cell. I am grateful for the space holder, but how do I get it to insert zeros where there is no value? The manual says you can fill empty cells, but it doesn't say how to keep them blank. Help?

        • Inserting space holders with zeros in Monarch V8
          Data Kruncher

          Hi Jean, and welcome to the forum.

           

          When Monarch extracts values from the report, and no value exists for a record for the fields you're after, it assigns what's referred to as a null value to the field.

           

          Under the View options, you can instruct Monarch to display whatever you want when it detects null values. This might be "(Null)', "No data found", " ", "0" or what have you.

           

          But what it exports to Excel or other file formats is different than what it displays in the Table or Summary windows, and this is important.

           

          Monarch will export the exact value of the field, null, to Excel because that really is the value of the cell (regardless of what is being displayed), and Monarch wants to be accurate in how it handles your data.

           

          So when you really want a zero value in your Excel file when nulls exist, you have to set things up just a little differently.

           

          First, you need to convert the nulls to zeros. You do this by creating a calculated field in the Table window. For your Hours fields, I'd start by naming it PreHours. This is my convention for knowing that I want to do something else with the field, and that any Pre fields shouldn't be included in any final output.

           

          Build a new Hours calculated field with this formula:

          If(IsNull(PreHours),0,PreHours))[/CODE]

           

          This will give you zeros where no hours data exists, and the proper hours when a value does exist.

           

          Do the same for the Dollars, and then hide PreHours and PreDollars.

           

          Your export to Excel will then contain the zero values that you want.

           

          HTH,

          Kruncher