0 Replies Latest reply: Feb 28, 2009 12:09 PM by Rusch Mauzy RSS

    Report Help

    Rusch Mauzy

      Class                                   Points       Sales                                      Net Real

      50     104     HG     50     114981     9.0102     2,512     $9,367.19      $7,093.52      $2,273.67      $3.73

      50     104     HG     50     114981     9.0108     3,130     $11,626.43      $8,849.20      $2,777.23      $3.71

      50     104     HG     50     114981     9.0112     2,057     $8,158.45      $5,897.27      $2,261.18      $3.97

      50     104     HG     50     114981     9.0115     2,445     $9,210.36      $6,858.22      $2,352.14      $3.77

      50     104     HG     50     114981     9.0119     1,850     $7,073.13      $5,237.09      $1,836.04      $3.82

      50     104     HG     50     114981     9.0122     2,574     $9,674.54      $7,292.33      $2,382.21      $3.76

      50     104     HG     50     114981     9.0123     125     $594.25      $420.52      $173.73      $4.75

      50     104     HG     50     114981     9.0126     2,307     $9,036.38      $6,615.17      $2,421.21      $3.92

      50     104     HG     50     114981     9.0129     2,606     $10,002.58      $7,366.18      $2,636.40      $3.84

      50     104     HG     50     114981     9.0131     2,014     $7,678.90      $5,665.63      $2,013.27      $3.81

       

                                                  21,620     $82,422.21                               $39.08

       

                                                           =     $3.81      

       

      I want to replace the number 50 above in this table with a name, like Miami.  I have a number of codes, similar to this, that I would prefer to replace with names in my summary.

      My second question is: I created a calculated field I named Net Real, (Sales divided by points). The measure in the summary sums the Net Real values, rather than recalculating at the summary level.

      Please help.

      Thank You,

       

      Rusch

        • Report Help
          Grant Perkins

          [code]Class                 Points     Sales      Net Real

          50    104    HG    50    114981    9.0102    2,512    $9,367.19     $7,093.52     $2,273.67     $3.73

          50    104    HG    50    114981    9.0108    3,130    $11,626.43     $8,849.20     $2,777.23     $3.71

          50    104    HG    50    114981    9.0112    2,057    $8,158.45     $5,897.27     $2,261.18     $3.97

          50    104    HG    50    114981    9.0115    2,445    $9,210.36     $6,858.22     $2,352.14     $3.77

          50    104    HG    50    114981    9.0119    1,850    $7,073.13     $5,237.09     $1,836.04     $3.82

          50    104    HG    50    114981    9.0122    2,574    $9,674.54     $7,292.33     $2,382.21     $3.76

          50    104    HG    50    114981    9.0123    125    $594.25     $420.52     $173.73     $4.75

          50    104    HG    50    114981    9.0126    2,307    $9,036.38     $6,615.17     $2,421.21     $3.92

          50    104    HG    50    114981    9.0129    2,606    $10,002.58     $7,366.18     $2,636.40     $3.84

          50    104    HG    50    114981    9.0131    2,014    $7,678.90     $5,665.63     $2,013.27     $3.81

           

                                   21,620    $82,422.21              $39.08

           

                                   = $3.81   

           

          /code

           

          /quote

           

          Hi Rusch,

           

          Whichever "50" it is you want to replace the answer is likely to be a lookup table. I'll guess that these numbers to names are fairly static so I would suggest an internal lookup table. In the Table view go to the Data menu and you will find what you need there. If there are a lot of codes you may find it worth your while preparing them elsewhere and then you can paste them into the table entry window.

           

          Once you have the table in one model, if you need it in others too, you can treat it as a "Linked Object" with a Strong Link so that you only have to maintain it in one place. You might want to consider creating a central reference Model for tables like that to make them easier to deploy in other models.

           

          If the table changes very frequently AND you have Monarch Pro, you might consider an EXTERNAL lookup to do the same thing but allow the table to be maintained externally - or perhaps even pick it up from a Host system table via an ODBC/OleDB link.

           

          For the calculation, set up another measure in the Summary and then change its properties and define the calculation you require. It doesn't matter what you start with it will adapt to your formula.

           

          HTH.

           

           

          Grant

            • Report Help
              Rusch Mauzy

              Grant,

               

              I will read and apply your suggestions. I will let you know how I do.

               

              Thank You,

               

              Rusch

                • Report Help
                  Rusch Mauzy

                  I was successful applying the lookup feature in Monarch to replace codes with names.  Thank you, that worked great.

                   

                  I am still struggling with the calculation.  I don't seem to get what you mean when you say "For the calculation, set up another measure in the Summary and then change its properties and define the calculation you require."  Please be more specific.

                   

                  Thank You,

                   

                  Rusch

                    • Report Help
                      Rusch Mauzy

                      I searched through other posts and was able to solve my reporting problem.

                       

                      Thank You,

                       

                      Rusch

                       

                      If someone could recommend a book or books on Monarch that would help me, I would appreciate the information.

                        • Report Help
                          Nick Osdale-Popa

                          The only documentation for Monarch is the Help system and the Monarch Learning Guide which is installed with V10.

                            • Report Help
                              Grant Perkins

                              The only documentation for Monarch is the Help system and the Monarch Learning Guide which is installed with V10.[/quote]

                               

                              And this forum ...?

                               

                               

                              Grant

                                • Report Help
                                  Data Kruncher

                                  Don't forget about the printed Learning Guide included in the box too!

                                   

                                  Oh, and there's a nice third party [URL is no longer valid] too.

                                   

                                  Shameless, I know...

                                    • Report Help
                                      Rusch Mauzy

                                      TIME 15:20:34                                     TO DATE WAREHOUSE AUDIT LISTING                                      PGM: RAP644                        

                                      LOCATION: 001    BRANCH:  51 MIAMI                 WAREHOUSE:  1 Milk COOLER          OPT: Y  FOR PERIOD: 02/01/09 THRU 02/28/09                         

                                      TRANSX     UNIT      EXTENDED   DOCUMENT VENDOR  BEGIN    PROD     PURCH  RETURN   ROUTE   WAREHSE RETURN  DAMAGED  OV/SH   ADJ'ED                       

                                        DATE      COST        COST      NUMBER  /ROUTE   INV     RECV      RECV   WHSE    LOADS   ISSUES   PROD                    ONHAND                       

                                      PRODUCT:    100 FF HOMO BOX     4/1GAL                                                                               

                                      02/02/09                         43826834 0148                                         336                                                                

                                      02/02/09                                  DAYTOT                                       336                              336                               

                                      02/03/09                                  DAYTOT                                                                        400                               

                                      02/04/09                         43827062 0104                                         240                                                                

                                      02/04/09                         43908919 0148                                          48                                                                

                                      02/04/09                         43877477 0148                                         240                                                                

                                      02/04/09                                  DAYTOT                                       528                              144                               

                                      02/05/09                         43908860 0148                                         240                                                                

                                      02/05/09                                  DAYTOT                                       240                              377                               

                                      02/06/09                                  DAYTOT                                                                        269                               

                                      02/07/09                         43932284 0104                                         192                                                                

                                      02/07/09                         43986581 0148                                          36                                                                

                                      02/07/09                         43955607 0148                                         192                                                                

                                      02/07/09                                  DAYTOT                                       420                                2-                              

                                      02/09/09                         43986689 0148                                         240                                                                

                                      02/09/09                                  DAYTOT                                       240                              480                               

                                      02/10/09                         44068609 0148                                          36                                                                

                                      02/10/09                                  DAYTOT                                        36                              192-                              

                                      02/11/09                         43987651 0104                                         288                                                                

                                      02/11/09                         44068683 0148                                          60                                                                

                                      02/11/09                         44037164 0148                                         336                                                                

                                      02/11/09                                  DAYTOT                                       684                              691                               

                                      02/12/09                         44068875 0148                                         288                                                                

                                      02/12/09                                  DAYTOT                                       288                              459                               

                                      02/13/09                                  DAYTOT                                                                        596                               

                                      02/14/09                         44068659 0104                                         240                                                                

                                      02/14/09                         44152332 0148                                          48                                                                

                                      02/14/09                         44121458 0148                                         432                                                                

                                      02/14/09                                  DAYTOT                                       720                               66-                              

                                      02/16/09                         44152496 0148                                         336                                                                

                                      02/16/09                                  DAYTOT                                       336                              336                               

                                      02/17/09                                  DAYTOT                                                                        688                               

                                      02/18/09                         44196657 0104                                         288                                                                

                                      02/18/09                         44228063 0148                                          48                                                                

                                      02/18/09                         44196585 0148                                         384                                                                

                                      02/18/09                                  DAYTOT                                       720                              120                               

                                      02/19/09                         44227979 0148                                         336                                                                

                                      02/19/09                                  DAYTOT                                       336                              302                               

                                      02/20/09                                  DAYTOT                                                                        640                               

                                      02/21/09                         44227940 0104                                         240                                                                

                                      02/21/09                         44280939 0148                                         384                                                                

                                      02/21/09                                  DAYTOT                                       624                                1                               

                                      02/23/09                         44311751 0148                                         240                                                                

                                      02/23/09                                  DAYTOT                                       240                              240                               

                                      02/24/09                         44387130 0148                                          48                                                                

                                      02/24/09                                  DAYTOT                                        48                              676                               

                                      02/25/09                         44312276 0104                                         240                                                                

                                      02/25/09                         44355756 0148                                         336                                                                

                                      02/25/09                                  DAYTOT                                       576                               26                               

                                      DATE 03/02/09                                           VELDA FARMS DAIRIES                                            PAGE    2                          

                                      TIME 15:20:34                                     TO DATE WAREHOUSE AUDIT LISTING                                      PGM: RAP644                        

                                      LOCATION: 001    BRANCH:  51 MIAMI                 WAREHOUSE:  1 Milk COOLER          OPT: Y  FOR PERIOD: 02/01/09 THRU 02/28/09                         

                                      TRANSX     UNIT      EXTENDED   DOCUMENT VENDOR  BEGIN    PROD     PURCH  RETURN   ROUTE   WAREHSE RETURN  DAMAGED  OV/SH   ADJ'ED                       

                                        DATE      COST        COST      NUMBER  /ROUTE   INV     RECV      RECV   WHSE    LOADS   ISSUES   PROD                    ONHAND                       

                                      02/26/09                         44387320 0148                                         336                                                                

                                      02/26/09                                  DAYTOT                                       336                              444                               

                                      02/27/09                                  DAYTOT                                                                        562                               

                                      02/28/09                         44410715 0104                                         240                                                                

                                      02/28/09                         44439989 0148                                         336                                                                

                                      02/28/09                                  DAYTOT                                       576                                                                               

                                      TOTALS   N/A                                7284                             7527   N/A                         

                                      PRODUCT:    101 FF HOMO         GAL                                                                               

                                      02/02/09                         43830781 052                                          120                                                                

                                      02/02/09                         43877434 054                                          104                                                                

                                      1) I don't know how to paste report info so it stays organized, I know it has something to do with Code brakets.

                                      2) I built a model from this report and can capture the detail line OK, but when it comes to capturing the DAYTOT AND TOTALS appeded information, I can't seem to get it right.

                                       

                                      Sorry for the leghty "paste", but I wnated to give you a flavor for the data structure.

                                       

                                      Please give me some leads.

                                       

                                      Thank you,

                                       

                                      Rusch

                                        • Report Help
                                          Data Kruncher

                                          [URL="http://www.monarchforums.com/showthread.php?t=2290"]Posting using CODE tags.[/URL]

                                           

                                          If you don't see the pound sign icon, go to your User CP (Control Panel) link at the top left of your screen. About 1/2 way down you'll see an Options link. Click it and scroll all the way to the end.

                                           

                                          Set the Message Editor Interface to Enhanced Interface - Full WYSIWYG Interface. Click the Save Changes button.

                                           

                                          Then please repost your sample. It'll then be possible for us to assist you.

                                            • Report Help
                                              Grant Perkins

                                              Rusch,

                                               

                                              As Kruncher identifies being able to see the correct format here looks like it will be very important and the tricks that sometimes work to reformat posts and regain the lost layout don't seem to be working here.

                                               

                                              In theory your DAYTOT lines look like Footers BUT sometimes they exist with no prior detail line of their own. That's not good for making life easy.

                                               

                                              I think the Total lines are probably footer but then would ask if you really need them anyway, other than as a check on what has been extracted assuming you are extracting and using all the detail lines that go to make up the total.

                                               

                                              I think that in addition to getting the format of the sample clearer (to avoid poor advice) it may be wise to provide an indication of what your resulting extraction needs to provide you with. Especially if the output has to fit with a user's specific requirement.

                                               

                                              HTH.

                                               

                                               

                                               

                                              Grant

                                              • Report Help
                                                Rusch Mauzy

                                                DATE 03/02/09                                           VELDA FARMS DAIRIES                                            PAGE    1  

                                                TIME 15:20:34                                     TO DATE WAREHOUSE AUDIT LISTING                                      PGM: RAP644

                                                LOCATION: 001    BRANCH:  51 MIAMI                 WAREHOUSE:  1 Milk COOLER          OPT: Y  FOR PERIOD: 02/01/09 THRU 02/28/09 

                                                TRANSX     UNIT      EXTENDED   DOCUMENT VENDOR  BEGIN    PROD     PURCH  RETURN   ROUTE   WAREHSE RETURN  DAMAGED  OV/SH   ADJ'ED

                                                  DATE      COST        COST      NUMBER  /ROUTE   INV     RECV      RECV   WHSE    LOADS   ISSUES   PROD                    ONHAND

                                                PRODUCT:    100 FF HOMO BOX     4/1GAL                                                                               

                                                02/02/09                         43826834 0148                                         336                                        

                                                02/02/09                                  DAYTOT                                       336                              336       

                                                02/03/09                                  DAYTOT                                                                        400       

                                                02/04/09                         43827062 0104                                         240                                        

                                                02/04/09                         43908919 0148                                          48                                        

                                                02/04/09                         43877477 0148                                         240                                        

                                                02/04/09                                  DAYTOT                                       528                              144       

                                                02/05/09                         43908860 0148                                         240                                        

                                                02/05/09                                  DAYTOT                                       240                              377       

                                                02/06/09                                  DAYTOT                                                                        269       

                                                02/07/09                         43932284 0104                                         192                                        

                                                02/07/09                         43986581 0148                                          36                                        

                                                02/07/09                         43955607 0148                                         192                                        

                                                02/07/09                                  DAYTOT                                       420                                2-      

                                                02/09/09                         43986689 0148                                         240                                        

                                                02/09/09                                  DAYTOT                                       240                              480       

                                                02/10/09                         44068609 0148                                          36                                        

                                                02/10/09                                  DAYTOT                                        36                              192-      

                                                02/11/09                         43987651 0104                                         288                                        

                                                02/11/09                         44068683 0148                                          60                                        

                                                02/11/09                         44037164 0148                                         336                                        

                                                02/11/09                                  DAYTOT                                       684                              691       

                                                02/12/09                         44068875 0148                                         288                                        

                                                02/12/09                                  DAYTOT                                       288                              459       

                                                02/13/09                                  DAYTOT                                                                        596       

                                                02/14/09                         44068659 0104                                         240                                        

                                                02/14/09                         44152332 0148                                          48                                        

                                                02/14/09                         44121458 0148                                         432                                        

                                                02/14/09                                  DAYTOT                                       720                               66-      

                                                02/16/09                         44152496 0148                                         336                                        

                                                02/16/09                                  DAYTOT                                       336                              336       

                                                02/17/09                                  DAYTOT                                                                        688       

                                                02/18/09                         44196657 0104                                         288                                        

                                                02/18/09                         44228063 0148                                          48                                        

                                                02/18/09                         44196585 0148                                         384                                        

                                                02/18/09                                  DAYTOT                                       720                              120       

                                                02/19/09                         44227979 0148                                         336                                        

                                                02/19/09                                  DAYTOT                                       336                              302       

                                                02/20/09                                  DAYTOT                                                                        640       

                                                02/21/09                         44227940 0104                                         240                                        

                                                02/21/09                         44280939 0148                                         384                                        

                                                02/21/09                                  DAYTOT                                       624                                1       

                                                02/23/09                         44311751 0148                                         240                                        

                                                02/23/09                                  DAYTOT                                       240                              240       

                                                02/24/09                         44387130 0148                                          48                                        

                                                02/24/09                                  DAYTOT                                        48                              676       

                                                02/25/09                         44312276 0104                                         240                                        

                                                02/25/09                         44355756 0148                                         336                                        

                                                02/25/09                                  DAYTOT                                       576                               26       

                                                DATE 03/02/09                                           VELDA FARMS DAIRIES                                            PAGE    2  

                                                TIME 15:20:34                                     TO DATE WAREHOUSE AUDIT LISTING                                      PGM: RAP644

                                                LOCATION: 001    BRANCH:  51 MIAMI                 WAREHOUSE:  1 Milk COOLER          OPT: Y  FOR PERIOD: 02/01/09 THRU 02/28/09 

                                                TRANSX     UNIT      EXTENDED   DOCUMENT VENDOR  BEGIN    PROD     PURCH  RETURN   ROUTE   WAREHSE RETURN  DAMAGED  OV/SH   ADJ'ED

                                                  DATE      COST        COST      NUMBER  /ROUTE   INV     RECV      RECV   WHSE    LOADS   ISSUES   PROD                    ONHAND

                                                02/26/09                         44387320 0148                                         336                                        

                                                02/26/09                                  DAYTOT                                       336                              444       

                                                02/27/09                                  DAYTOT                                                                        562       

                                                02/28/09                         44410715 0104                                         240                                        

                                                02/28/09                         44439989 0148                                         336                                        

                                                02/28/09                                  DAYTOT                                       576                                                                               

                                                TOTALS   N/A                                7284                             7527   N/A 

                                                PRODUCT:    101 FF HOMO         GAL                                                                               

                                                02/02/09                         43830781 052                                          120                                        

                                                02/02/09                         43877434 054                                          104                                        

                                                02/02/09                         43846671 0100                                          48                                        

                                                /CODE

                                                I built a model using this report. I am able to capture the detail line ok, but I am struggling with the appended information. DAYTOT and TOTALS.

                                                 

                                                Please give me some guidance.

                                                 

                                                Thank You,

                                                Rusch

                                                  • Report Help
                                                    Grant Perkins

                                                    Hi Rusch,

                                                     

                                                    Much better thanks.

                                                     

                                                    Following up on my previous observation, looks to me rather like the DAYTOT lines could be detail lines in some way since they exist on days when other lines do not. Also they are the only lines that record values in the OV/SH column.

                                                     

                                                    How do you see them?

                                                     

                                                     

                                                    Grant