3 Replies Latest reply: May 15, 2014 9:51 AM by Grant Perkins RSS

    Trapping Problems

    donh _

      I am having lots of problems getting a report by department to work and was hoping someone could potentially provide some insight.

       

      I can capture the detail but cannot get the category to drop in - - - defining category with the " *** " as book ends (see below).

       

      In the table I need (going across):

       

      dept#/category/account# desc/actual/budget/YTD

       

      Writing formulas in monarch for ranges will not work as the account numbers duplicate between different categories(expenses/manhours) plus 2 of the categories do not have account numbers (statistics, subtotal).

       

      Can it be done on Monarch Pro 7 ???

       

      Would appreciate any help - - -

       

      Report example for many departments:

       

      [font="courier"]ABC COMPANY               Page 5

      MONTHLY FINANCIAL REPORT               14-APR-2006 1:34 PM

                     Report table: HBO.RPH

                     Data bases: dmdy2006.dbh

                     fix06bud.dbh

      OCT - FEB               dmdy2005.dbh

                      

           CURRENT     CURRENT     LAST YEAR

           ACTUAL     BUDGET     YTD

                     ACTUAL

                      

                      

      61510 SUR               

                      

      **REVENUE**               

      8 R C     239225     237,038     1391511

      8 R C     14787     27,677     173896

      802 R C     8528     11,456     80032

      803 R C     8200     6,753     42981

      804 R C     12136     14,785     100735

      805 R C     3147     3,700     22632

      807 R C     56482     73,646     464003

      818 O M     14991     2,402     40327

      819 O M     1636     268     9183

      820 O B     1136     128     5152

      821 O C     813     251     6436

      822 O S     1344     309     5844

      823 O O     1191     117     6220

      824 O P     9285     1,837     50361

      830 O     0     40     481

                      

      GROSS REVENUE     372901     380,407     2399794

                      

      **EXPENSES**               

      1000 S     5845     5,952     48243

      2000 R     92961     90,019     586926

      3000 L     30197     43,101     252013

      4000 N     21742     22,828     126120

      6000 P     6383     0     2051

      7000 C     11384     15,449     99904

      10000 E     3773     0     -26020

      11700 F     12353     13,568     82442

      31000 M     2207     2,501     13626

      40000 O     1074     1,309     6570

      43000 E     313     246     1399

      45000 G     482     1,062     7000

      47000 T     57     80     485

      54000 P     620     990     5779

      59000 S     0     36     247

      60000 M     165     161     348

      70000 T     0     198     1545

                      

      DIRECT EXPENSE     189556     197,500     1208678

                      

      GROSS MARGIN     183345     182,907     1191116

                      

      **UNITS**               

      2 PAT.DAY-I     1017     1,017     6992

                      

                      

      ABC COMPANY               Page 6

      MONTHLY FINANCIAL REPORT               14-APR-2006 1:34 PM

                     Report table: HBO.RPH

                     Data bases: dmdy2006.dbh

                     fix06bud.dbh

      OCT - FEB               dmdy2005.dbh

                      

           CURRENT     CURRENT     LAST YEAR

           ACTUAL     BUDGET     YTD

                     ACTUAL

                      

                      

      61510 SUR               

                      

      **UNITS**               

      102 OBS-O     133     133     431

                      

      TOTAL UNITS     1150     1,150     7423

                      

      **MANHOURS**               

      1000 S     160     166     1383

      2000 R     4121     3,771     24683

      3000 L     1880     2,537     16703

      4000 N     2100     2,244     13768

      6000 P     238     0     80

      7000 C     1149     1,529     9697

                      

      TOTAL MANHOURS     9648     10,247     66314

                      

      **STATISTICS**               

                      

      UNITS I     1017     1,017     6992

      UNITS O     133     133     431

      UNITS T     1150     1,150     7423

      M P     9648     10,247     66314

      F P     60.3     64.04     76.87

      F C     160     160.00     862.7

      P M     8.39     8.91     8.93

      A H R     17.47     17.31     16.82

      I R/I U     336.78     368.79     325.48

      O R/O U     228.54     39.94     286.6

      T R/T U     324.26     330.79     323.29

      S U     146.53     154.22     150.24

      N S U     18.3     17.52     12.59

      T E U     164.83     171.74     162.83

      G M U     159.43     159.05     160.46

                      

      **SUBTOTALS**               

                      

      I R     342505     375,055     2275790

      O R     30396     5,312     123523

      O R     0     40     481

      G R     372901     380,407     2399794

      P S     168512     177,349     1115257

      S     3763     4,872     27196

      O E     17281     15,279     66225

      D E     189556     197,500     1208678[/font][/quote]

       

      [size="1"][ June 23, 2006, 11:50 PM: Message edited by: donh ][/size]

        • Trapping Problems
          Grant Perkins

          Hi Don,

           

          Some thoughts.

           

          If you extract all the lines of details (presumably those lines that start with numerics under each category heading? Is it reasonable to assume that the format of the report is fixed width columns or does it come out just as you have posted?) and append the category headings (bounded by the ***'s) you can filter out the unwanted categories  - statistics and subtotals - in the resulting table. Easier than trying to use the trap for filtering as well as trapping in this case.

           

          If you make the category an append template you should get the data you require but may need to re-arrange the table columns to get them into the order you require.

           

          I am not sure where you are coming from when you mention writing formulas for ranges and the duplicate account numbers issue. Is account number the same as department number???

           

          In your example of the required output the account number does not feature - is that intentional or simply an omission?

           

          What piece of data (or pieces of data?) identifies the dept# you want in the first column of your output?

           

          Sorry to answer a question with a question but it seems important in this case.

           

          Grant

          • Trapping Problems
            donh _

            Grant:

             

            Thanks for the reply - - -

             

            I can trap all the detail with no problems - - - but I also need the categories with the respective detail.

             

            I will have to read up on the append template - unfortunately I am somewhat new to monarch.

             

            I had thought that I could write formulas to get the categories populated - - - account number < 1000, "**Revenues**", . . . - - - but with duplicate account numbers I would not be able to differentiate between expenses and manhours.

             

            I accidentally omitted the account number & description - - - good catch.

             

            The department number was not very clear - - - it would be in this instance 61510 Sur - - - the department number would be in the same location except it does show up on succedding pages of the report until that department is finished

             

            Regarding the report - - - it is actually 8 columns across but I only put in 4 columns.

             

            When I get back to work on Monday I will include what the finished product needs to look like.  I thought I might be able to access from home but cannot.

            • Trapping Problems
              Grant Perkins

              Originally posted by donh:

              I can trap all the detail with no problems - - - but I also need the categories with the respective detail.[/b][/quote]Excellent.

               

               

              Originally posted by donh:

              I will have to read up on the append template - unfortunately I am somewhat new to monarch.[/b][/quote]Very easy to do. Create a new template using one of he category lines starting with ***. If, as I think you are indicating, the *** actually appears in the line that would be your trap for the template. Make the field as long as you need and don;t worry about the *** at the end being in  a variable position. We can discard it later if necessary though I suspect that what you will really want to do is interpret the category heading to a shorter code for presenting the output.

               

               

              Originally posted by donh:

              I had thought that I could write formulas to get the categories populated - - - account number < 1000, "**Revenues**", . . . - - - but with duplicate account numbers I would not be able to differentiate between expenses and manhours.

              /b[/quote]Once you have the detail record fields with the append field included in the record you should  have a means of separating and grouping the data even when the account numbers are duplicated.

               

              Originally posted by donh:

              I accidentally omitted the account number & description - - - good catch.

               

              The department number was not very clear - - - it would be in this instance 61510 Sur - - - the department number would be in the same location except it does show up on succedding pages of the report until that department is finished[/b][/quote]I sort of guessed that but have learned never to assume ...    

               

              The Department number is simply another append field. The repetition should not make any difference one way or another for your purposes I think.

               

              It is just possible that trapping it cleanly might require some creative thinking since there seems to be no obvious unique 'key' to identifying the line and field(s) directly. Plenty of tricks available to play with though.

               

               

              Originally posted by donh:

              Regarding the report - - - it is actually 8 columns across but I only put in 4 columns.

               

              When I get back to work on Monday I will include what the finished product needs to look like.  I thought I might be able to access from home but cannot. /b[/quote]I'm going to guess that this will end up as filtering exercise mostly. The report looks like it has several components (mostly summary sections) which fit the format of the detail lines well enough that they will be picked up as details and append fields. So the easiest way to deal with them is likely to be to extract them and then filter them out later. (Or maybe filter them IN and use them stand alone - but then Monarch can provide the summaries for you anyway ...)

               

              Once you have the Department and Category fields in the table records the whole picture will become much clearer.

               

              HTH.

               

              Grant