5 Replies Latest reply: May 15, 2014 10:03 AM by Dave Whybro RSS

    Column Headings

    Dave Whybro

      Hi All,

       

      First time poster but user of Monarch Pro for a few of years.

       

      I need help with a report that has changing column headings each month.

       

      Stock Budget Schedule                                        

       

      Prod    Order    Qty  Total Cost     MAR    APR    MAY    JUN

      -


       

      FAUX SUEDE MULTI PANEL OBL CUS                               

      FSMPO  R14368    350       $963      963                     

             R14504    200       $550                    550       

             Totals    550      $1513      963      0    550      0

      FS RUNNING STITCH CUSHION SS                                 

      FSRS   R14408    600      $1632     1632                     

             R14505    650      $1768                   1768       

             Totals   1250      $3400     1632      0   1768      0

      FAUX SILK TUSSAH 45CM CUSHION                                

      FST    R14463   1000      $2790                          2790

             Totals   1000      $2790        0      0      0   2790

      FAUX SUEDE WALLED DINING PAD                                 

      FSWDP  R14502    400      $1784                   1784       

             Totals    400      $1784        0      0   1784      0

      GANGES 45CM BRAIDED CUSHION                                  

      GAN    R14471    800      $2256                   2256       

             R14538    800      $2256                   2256       

             Totals   1600      $4512        0      0   4512      0

      /code

       

      I need to be able to extract the data but be able to pick up the column headings from the months (these change each month). Is there a way of doing this? I searched the forums and thought I found a solution (from a few years ago) but the post referred me to another post which I couldn't locate.

       

      Thanks for any help provided.

        • Column Headings
          Grant Perkins

          Hi Dave and welcome to the forum.

           

          Some of the old links will not work because the old forum was targeted by spammers a year or so ago and a new one was set up but the data carry over of course breaks the links.

           

          I think the most likely useful solution would be to use Multi Column Regions for your Month columns and pick the name of the month as an append field for the column. I would also advise creating a calculated field  to give you a 'conversion' to month number that you can use for sorting purposes - or some such solution.

           

          Now, have you used MCR before? If not you may have a few more questions along the way. It's not difficult but also people find it not entirely intuitive at first involvement and a little directional help can be useful once you have had a look at it.

           

          Basically if you get a mental image of each column really being a 'page', complete with whatever is to the LEFT of the first column,  in the way the Monarch parses the report you will be most of the way there.

           

          V10 includes a function for Column() that can be used with MCR to ID which column the data comes from and will help you to verify the extraction and the month. It's actually provided for other purposes but can be usefully used for verification as well.

           

          HTH.

           

          Be sure to let us know if you get stuck but especially if it all works out for you.

           

          Grant

            • Column Headings
              Dave Whybro

              Hi Grant,

               

              Thanks for the quick response and I'll try the solutions you suggested.  I have used MCR before so I will change my model to that to see how I go. However I cannot seem to find the Column() function you mentioned anywhere. Can you direct me please.

               

              Regards

              Dave

                • Column Headings
                  Dave Whybro

                  Hi Grant,

                   

                  Found the Column() function (didn't look closely enough the first time), but it doesn't appear in the Help section as a list of funtions.

                  • Column Headings
                    Grant Perkins

                    Hi Dave,

                     

                    I assume your profile is correct and you have V10 ...

                     

                    Table window, data menu, add a calculated field.

                     

                    Formula Tab.

                     

                    In the expression box enter

                     

                    Column()

                     

                    You should also be able to see the function listed under ALL functions or REPORT functions (first function displayed.)

                     

                    If not there .... then I'm puzzled.

                     

                     

                     

                    Grant

                     

                    Edit: Ah, you got there first!

                     

                    I seem to remember it not appearing in the help. Late addition as I recall, probably slipped through the documentation checks.