4 Replies Latest reply: May 15, 2014 9:57 AM by JanBH _ RSS

    Multiple models vs Combined columns

    JanBH _

      We’ve recently installed Monarch Pro. I am a beginner and would appreciate some advice on best practice for setting up the following scenario for extracting data across 6 reports

      So far, I have created a single model, single  export which means we then have to split the data to get the final requirement in the correct format.

      Or alternatively 3 models resulting in 3 exports which is a pain!

       

      I am sure that there must be a way of combining this data in an easier method.

       

      All reports are in basically same format, and this can be simplified as follows:

       

      H1     H2     

      D1     D2          V1     V2     V3

       

      Where:

      H1 = Header record 1

      H2 = Header record 2

      D1 = Detail Record 1

      D2 = Detail Record 2

      V1 = Value 1

      V2 = Value 2

      V3 = Value 3

       

      Each detail line has 3 values recorded against it

       

      Each Value has a number of lookups assigned to them based on H1 and D1 combinations. These appear in the table output in the following format (each represents a col heading)

       

      H1   H2   D1     D2     V1     V1(n1)       V1(n2)   V2     V2(n1)          V2(n2)……etc…

       

      Note : the Lookups labels n1, n2 etc are the same for each of Values columns, but I cannot create same name lookups in the same model.

      (this is the primary reason I have had to create 3 models to extract the data across V1, V2 and V3)

       

      Ideally what I would like is

       

      H1    H2     D1    D2    V1   V1(n1)       V1(n2)

      H1    H2     D1    D2    V2   V2(n1)       V2(n2)

      H1    H2     D1    D2    V3   V3(n1)       V3(n3)

       

      Therefore all of the Values are represented in the same column as are the analysis columns represented by n1 and n2.

       

      Do I need to use “And” function to combine all Values?

      How do I ensure that I capture the associated analysis in the same colums – I tried linking fields but it didn’t work…

       

      Any clues would be much appreciated as I seem to be going round in circles a bit

        • Multiple models vs Combined columns
          Grant Perkins

          Jan, welcome to the forum.

           

          Can we step back here a moment and look at the large picture as I am have read you very detailed description several times but I'm not sure I have a grasp of the requirement yet.

           

          Here's how I understand it.

           

          You have six reports from which you need to extract information.

           

          You can extract from all the reports using one model (as far as the report format is concerned) but this does not entirely satisfy your data need.

           

          You mention two detail records but of course Monarch expects just one so I'm not sure how you have addressed that or if we simply have a terminology understanding to cover.

           

          Also I am not sure why you need same name lookups or why the naming convention of the lookup is important.

           

          Similarly if you eventually need to separate the data what do you see as the major drawbacks between a single (possibly more complex?) model and a number of separate models all based on a common master model?

           

          Have you any examples of the reports that you could post to the forum (with any confidential data disguised of course) so that we can get a full impression of what you are setting out to do?

           

          Normally reports can be cut and pasted into the forum from a text version of the report. If you se the CODE button below the message entry window and paste between the code start and end labels the format of the report should be retained correctly.

           

          For backgound information as you mention you are new to Monarch have you had the opportunity to work through relevant parts of the Training Guide yet? Or maybe to attend a training course? Just trying to gauge where you are at in the "new user" process - basically which side the 'eureka' moment of understanding you currently reside!       

           

          In my experience the sort of thing you seek to do could result in either a single model or use of multiple models. the best practice is what get results most effectively and efficiently for now and looking ahead to the future in so far as the future requirements can be predicted. The fundamental rule, if there is no clear answer to the future, is 'keep it simple' whenever possible.

           

          Grant

          • Multiple models vs Combined columns
            JanBH _

            Hi Grant, Can quite appreciate that my ramble caused confusion...I feel as though I am very close to that eureka moment but having tried two or three different approaches, it still escapes me!! I have worked throught he majority of the Learning Guide, and feel as though I've a reasonable understanding of the basics, but am now trying to apply that !!!

            I'll adress your points as best I can.

            1.  You have six reports from which you need to extract information.[/b] Correct.

            2.[b]You can extract from all the reports using one model (as far as the report format is concerned) but this does not entirely satisfy your data need.[/b] - Correct the data is in the table but not in an onward useable layout.

            3. I have identified a single DETAIL line - in my example.  D1,D2,V1,V2,V3 are either string(D's) or (V's) Value fields within the detail linepicked out by the template. V's represent Gross, Vat, Net. D's represent Location code and description.

            4. Reference Lookups - For each of the values, I need to attach Account, Dept, Vat and product analysis. I am doing this via lookups which is fine. Account is dependant on Value type, hence I am getting 3 Account columns against the values. (I was then trying to consolidate these account columns together so that I have a single column showing the Account code and another showing ALL of the V (values)).

            I can attach the lookup for the other analysis on one of the D fields instead to avoid the duplication. Hence I am then cutting and pasting the output in excel to combine the values and accounts.

            5. The 3 x simpler models provide the data in a consistent format, however I am running 3 x exports to output the data(append) to a single excel worksheet... I cannot see how to  combine models in a single output which I would prefer for ease of the end user. - again if I can understand how to do this - it would solve my problem !!!

            See below example of initial report and final output - using 3 exports - I would just like to make the bit in between more efficient- ideally single click once set up!!

             

              [font="courier"]     Trading Statistics Report                                                                               

            Location          Description                                                     Sales            Fin Sales           Fin Sales      GP%  Fin GP%                  Tax              Tax Inc   Tax Inc                                                                               

            Var                                                       Sales       GP%                                                                               

            Show Financial Sales for each category in the BAR department at ALL LOCATIONS location(s) between xx/xx/2007 and xx/xx/2007. The Financial Sales information                                                                   

                will be shown for each location and will be sorted by , highest to lowest.                                                                               

            OxxxxF            OxxxxY RxxxxL                                                 £936.42                £0.00             £936.42     73.0      0.0              £163.78            £1,100.20      62.1                        

                 OxxxxE            OxxxxY STEAM                                                   £17.02                £0.00              £17.02     70.9      0.0                £2.98               £20.00      60.3                        

                 HxxxxD            HI SxxxD                                                        £0.00                £0.00               £0.00      0.0      0.0                £0.00                £0.00       0.0                        

                 OxxxxR            OSxxxxBAR  1861                                            £16,727.12                £0.00          £16,727.12     74.1      0.0            £2,930.00           £19,657.12      63.0                        

                 OSPSTOCK          Oxxxx STOCK HOLDING                                             £0.00                £0.00               £0.00      0.0      0.0                £0.00                £0.00       0.0                        

                 WAREHOUSE         WAREHOUSE                                                       £0.00                £0.00               £0.00      0.0      0.0                £0.00                £0.00       0.0                        

                 xxxxx            xxxx xxxxx STEAM                                                 £0.00                £0.00               £0.00      0.0      0.0                £0.00                £0.00       0.0                        

                 TxxxF             TExxxxAL 1                                                    £623.82                £0.00             £623.82     75.8      0.0              £109.08              £732.90      64.5                        

                 TxxxF             TxxxxxxL 2                                                    £640.53                £0.00             £640.53     93.5      0.0              £112.07              £752.60      79.6                        

                 ExxxxE            ExxxxE STEAM                                                   £11.92                £0.00              £11.92     74.5      0.0                £2.08               £14.00      63.5                        

                 EAGSTOCK          ExxxE STOCK HOLDING                                             £0.00                £0.00               £0.00      0.0      0.0                £0.00                £0.00       0.0                        

                 EAGBAR            ExxxE BAR 1861                                             £12,934.21                £0.00          £12,934.21     74.3      0.0            £2,265.99           £15,200.20      63.2                        

                 ExxxEF            ExxxE REFUEL                                                   £45.21                £0.00              £45.21     73.6      0.0                £7.89               £53.10      62.6                        

                 ExxxOP            ExxxE TOP DECK BAR                                          £3,012.95                £0.00           £3,012.95     74.5      0.0              £527.95            £3,540.90      63.4                        

                 FxxxxE            FxxxxN STEAM                                                   £31.48                £0.00              £31.48     70.7      0.0                £5.52               £37.00      60.2                        

                 FxxxxOCK          FxxxxN STOCK HOLDING                                            £0.00                £0.00               £0.00      0.0      0.0                £0.00                £0.00       0.0                        

                 FALBAR            FxxxxN BAR 1861                                            £15,731.67                £0.00          £15,731.67     74.3      0.0            £2,757.13           £18,488.80      63.3                        

                 FALREF            FxxxxN REFUEL                                               £1,037.27                £0.00           £1,037.27     73.0      0.0              £181.63            £1,218.90      62.1                                                                               

            TOTAL                                                                        £51,749.62                £0.00          £51,749.62     74.4      0.0            £9,066.10           £60,815.72      63.3                                                                               

            Output in Excel

            [font="courier"] Type     Reference     END DATE     LOCATION     DESCRIPTION     Account_Code     T1_ANAL     COST CENTRE     DIVISION     Final Value

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1201     DFANB     £3,120.66

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1205     DFANB     £102.96

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     H1000     DFANB     £0.00

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1202     DFANB     £812.35

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     H1000     DFANB     £0.00

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     H1000     DFANB     £0.00

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     S4005     DFANB     £162.52

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     S1101     DFANB     £7,484.08

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     S1201     DFANB     £3,658.73

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1305     DFANB     £64.85

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     H1000     DFANB     £0.00

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1302     DFANB     £607.60

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1301     DFANB     £3,523.37

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1303     DFANB     £213.00

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1105     DFANB     £33.35

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     H1000     DFANB     £0.00

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1102     DFANB     £690.20

            OTHER dep     OTHR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1101     DFANB     £3,452.48

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1201     DFANB     £1,100.20

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1205     DFANB     £20.00

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     H1000     DFANB     £0.00

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1202     DFANB     £19,657.12

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     H1000     DFANB     £0.00

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     H1000     DFANB     £0.00

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     S4005     DFANB     £0.00

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     S1101     DFANB     £732.90

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     S1201     DFANB     £752.60

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1305     DFANB     £14.00

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     H1000     DFANB     £0.00

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1302     DFANB     £15,200.20

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1301     DFANB     £53.10

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1303     DFANB     £3,540.90

            BAR depar     BAR     31/08/2007     xxxx     Oxxx Oxxxxxx     55850     S     F1105     DFANB     £37.00

            /font[/font][/quote]My columns have shifted a bit here but this is basically the required output...I need a single Account column and a single Value column for each location with the appropriate analysis.....

             

            Hope this clarifies a bit !!!

             

            Best regards

            J                                                                               

                • END OF REPORT ***                                                                               

            /QUOTE

            • Multiple models vs Combined columns
              Grant Perkins

              Hi Jan,

               

              Thanks for the information. I'll start by summarizing my understanding.

               

              The source report looks quite straightforward and you have an obvious detail row to start with and then on or two (whichever works best, probably 2) Append templates to pick the DEPARTMENT (and Location? Seems redundant if it is is ALL for example) and the DATE(S) from the data selection description text.

               

              So far so good.

               

              For the lookup requirement I am assuming you have that in hand as well, using either internal or external lookups.

               

              The result should be a single record in a row  with all the required information fields for the analysis you require.

               

              The final format involves splitting the single row into 3 rows to give a value for each account code for each location - hence the three codes per location will result in 3 rows per location in the table to be exported.

               

              So I imagine your 3 model approach picks each of the Value columns (Net Value, VAT (Tax) and Gross Value) as solo values in the extraction and results in simple table (x3) to be exported.

               

              To run that as a single process (I assume that the 6 reports are used to separate the Departments which are not otherwise identified clearly on the report) you could set up a batch file with the required command lines to process the 6 files for each model in turn and export the result to the Excel worksheet.

               

              The export process would probably be most easily managed as a Project Export which would be referenced in the batch file. (See Chapter 9 in the Help for details).

               

              You could also consider writing some VBA code to make a more sophisticated batch control process if you have VBA skills  - I don't (shamefully) so if you want to do that I will leave it to others to offer assistance although there are several posts in the forum with excellent examples of code available.

               

              One option for the exports is to use a separate worksheet for each VALUE group (NET, VAT, GROSS) and then in the Workbook provide a consolidating Worksheet to pick its values from the others keeping the display screen separate from the data.

               

               

              The one model 'solution' seems to me to be a similar concept since you still need to split 1 record into 3.

               

              I would suggest creating 3 SUMMARIES one for each of the values you need to report as undividual rows. The Value field will be the Measure you set for the summary.

               

              You can then create a Project Export to export all three summaries to the Excel workbook either into their individual worksheets with a master sheet (as suggested above) or by using the Append feature to include all of the rows from the individual exports into a single sheet providing there is a way to match column names between Monarch and Excel for the export to Excel.

               

              My thoughts so far. Have a look at the Summaries and the Command Line information while I have a play to see if any other ideas pop up.

               

              HTH.

               

               

              Grant

              • Multiple models vs Combined columns
                JanBH _

                Hi Grant thanks for your comprehensive response, I think your understanding of the position so far is correct.

                My approach is going to be to look at the "batch file" export option first as I think I have missed this first time round and I did suspect that I may be able to consilidate the data using Summaries so failing batches I will try that and get back to you - prob early next week with the results... VBA skills are v limited too so will try these options first !!!

                Thanks again in the meantime

                Jan