3 Replies Latest reply: May 15, 2014 10:10 AM by Olly Bond RSS

    Multi-column Detail Only Exists For Some Records

    jag11300 _

      Hello, Monarch Gurus!

       

      I’m running Monarch Pro 10.5 and have been using Monarch (in a basic manner) for around 7 years... so, I know just enough to be dangerous.  

       

      I require a bit of assistance with a very ugly report. Here’s my problem in a nutshell:

       

      My report has Primary Style Numbers (“AAAA” & “BBBB”), Division (“12” & “34”), Product Descriptions (“Category1” & “Category2,” etc…), and other information which has been omitted for the sake of time.  For only some Primary Styles, there is a Primary Style Description (“Assortment”), a Total for the Primary Style Dozens and Parts (“Total 0  6”), and the Component Style Numbers (“BB123” & “BB456”) with respective Dozens and Parts (“0   3” & “0   3”) that make up the Primary Style Total. There are a maximum of four columns and an unlimited number of rows for the Component Styles.

       

      I’ve attempted to capture the Component information using a multi-column region and the other information as append templates. However, I can’t seem to get it to work properly since the entire Component section is missing if the Primary Style contains no components. The table view in the model only shows the Primary Styles that contain Component Styles; I want all Primary Styles and the related Component Styles (where they exist).

      AAAA    

      -


         DIV       12    

       

      PROD DESCRIPTION-CATEGORY    -MAJOR GROUP  -MKT GROUP   -BRAND       -GARMENT TYP  -PIECE TYPE    -CONST.TYPE    -STYLE TYPE         

                        *CATEGORY1   *MAJOR GROUP1 *MKT GROUP1  *BRAND1      *GMT TYPE1  **  N/A  *       *CONST TYPE 1  *STYLE TYPE1                                                                               

      -


                                                                                       

      BBBB     

      -


         DIV       34 

      PROD DESCRIPTION-CATEGORY    -MAJOR GROUP  -MKT GROUP   -BRAND       -GARMENT TYP  -PIECE TYPE    -CONST.TYPE    -STYLE TYPE         

                        *CATEGORY2   *MAJOR GROUP2 *MKT GROUP2  *BRAND2      *GMT TYPE2    *PIECE TYPE2   *CONST TYPE 2  *STYLE TYPE2

                                                                                      *ASSORTMENT*        DOZ PTS     STYLE  XP SZ DOZ PTS  STYLE  XP SZ DOZ PTS  STYLE  XP SZ DOZ PTS  STYLE  XP SZ DOZ PTS                                                                               

      TOTAL     0   6     BB123  01      0   3  BB456  01      0   3                                                                          

      /CODE

       

       

      I’ve not had to build any remotely complex models before and there may be something very simple that I’m overlooking. Your help is greatly appreciated. 

       

      Regards,

      Juli

        • Multi-column Detail Only Exists For Some Records
          Olly Bond

          Hello Juli

           

          I think there are two issues going on here - the missing detail problem and then multiple columns.

           

          I think I'd need to see a bigger sample to help - would it be possible to email me something?

           

          Best wishes,

           

          Olly

            • Multi-column Detail Only Exists For Some Records
              Grant Perkins

              Juli,

               

              I would echo Olly's post.

               

              Leaving the MCR aside for now, the key to getting Monarch giving you what you need is to decide what is a DETAIL record and what is information to be appended to that record.

               

              Here is seems you sort of need 2 slightly different things at the same time so it's a matter of finding the best way to deliver that.

               

              Based on what you have posted as a sample it sound like you need to treat the Primary Style Numbers as Detail to get the full list.

               

              If there is then no way to process within the same model the secondary lines when they appear it would seem that a 2 step (or multiple step) approach would be required.

               

              If you have been able to supply Olly with the extended report samples I will leave it there and let Olly provide an answer for you. If you cannot supply a larger example (the exact format can be important here when trying to extract from tricky structures) let us know and I will write up some expended descriptions of potential approaches.

               

               

              Grant

                • Multi-column Detail Only Exists For Some Records
                  Olly Bond

                  Hello Grant,

                   

                  The sample was even more complicated than it first appeared, but there was quite a nice solution using the multi-column region.

                   

                  AAAA    

                  -


                     DIV       12    

                   

                  PROD DESCRIPTION-CATEGORY    -MAJOR GROUP  -MKT GROUP   -BRAND       -GARMENT TYP  -PIECE TYPE    -CONST.TYPE    -STYLE TYPE         

                                    *CATEGORY1   *MAJOR GROUP1 *MKT GROUP1  *BRAND1      *GMT TYPE1  **  N/A  *       *CONST TYPE 1  *STYLE TYPE1                                                                               

                  -


                                                                                                   

                  BBBB     

                  -


                     DIV       34 

                  PROD DESCRIPTION-CATEGORY    -MAJOR GROUP  -MKT GROUP   -BRAND       -GARMENT TYP  -PIECE TYPE    -CONST.TYPE    -STYLE TYPE         

                                    *CATEGORY2   *MAJOR GROUP2 *MKT GROUP2  *BRAND2      *GMT TYPE2    *PIECE TYPE2   *CONST TYPE 2  *STYLE TYPE2

                                                                                                  *ASSORTMENT*        DOZ PTS     STYLE  XP SZ DOZ PTS  STYLE  XP SZ DOZ PTS  STYLE  XP SZ DOZ PTS  STYLE  XP SZ DOZ PTS                                                                               

                  TOTAL     0   6     BB123  01      0   3  BB456  01      0   3  BB123  01      0   3  BB123  01      0   3 

                                                         BB123  01      0   3  BB123  01      0   3 

                  /CODE

                   

                  There was also a ten line Page Header region, and then some lines which weren't needed, but the challenge was essentially this - for each style, there should be at least one record, but for some styles, there are no totals, whereas for others, there can be anything from one total to 24, arranged in four columns, over multiple lines.

                   

                  The solution was to trap on a column heading that appeared in every style, as the detail, and use TOTAL as a preceding string to pull it in only where it appeared. The data next to TOTAL was defined as a multi-line memo field, with multi-column region active.

                   

                  The columns were defined as follows: 40 columns, 1 character wide, starting in column 150. From the Column() function, you can extract a Mod() calculation that cycles through 123412341234 - returning the vertical coordinate of the TOTAL required, and an Int() function that cycles through 111122223333... returning the horizontal slice of the memo field that you need.

                   

                  Then a simple Substr() on the relevant Textline() of the Memo field gives you the data for each TOTAL. An advanced filter is then required to eliminate the multiple blank entries and just leave you with real data.

                   

                  This approach works only for data sets with up to 40 possible points, as that's the limit of the MCR function in Monarch v10. You could get around that in a multi-pass solution but that might get a little hairy...

                   

                  Best wishes,

                   

                  Olly