8 Replies Latest reply: May 15, 2014 10:12 AM by KeyserSoze _ RSS

    Column Headings in Table View

    toyug _

      I’m fairly new to Monarch but have setup several models and reports. I am currently working on a new report where the column headings roll (change) each month. This month the column headings would be: Nov Dec Jan Feb  etc…; next month the column headings would be: Dec Jan Feb Mar  etc…. The columns are all fixed width with numeric data, just the name of the column changes. I need to have the rolling heading be in my table as the column heading. Is there an easy way to extract this information and use it as a column heading?

        • Column Headings in Table View
          KeyserSoze _

          Hi toyug,

           

          Using the [URL="http://www.google.com/cse/home?cx=017979669301925061066:c4v8ksw-llg"]Custom Monarch Forum search page[/URL], I was able to find at least two posts with similar challenges here.

           

          The results of the search went back to the early days of this forum. The [URL="http://www.monarchforums.com/showthread.php?1605-Field-Names&"]first post[/URL] discusses the techniques that will still work now, and in the [URL="http://www.monarchforums.com/showthread.php?1541-Capturing-Period-Titles"]second post[/URL] Grant Perkins references the first post, I believe. It is hard to tell as the URL of the forum changed and the links to old forum posts are broken.

           

          I would expect that the answer for your challenge lies in these posts.

           

          If you still are having problems building a model, please post a sample of your report here and we will work with your sample.

            • Column Headings in Table View
              Grant Perkins

              Gosh, this goes back a way!

               

              toyug,

               

              Assuming you are using a fairly recent version of Monarch you are likely to have a few more tools available to make the task easier - so long as the report is playing ball. 95% probably will work fine but there MAY be a few catches to watch for. We can come back to those later if necessary.

               

              Somewhere between 2002 and now the Multi-Column Region (MCR) concept was added to Monarch functionality. All being well with the design of your report you should be able to set up the columns for the detail data and make the column heading be part of the MCR definition with the heading fields (Called, for example "Month_Name") being an append field.

               

              Once you have the month name for each detail record you can arrange the resulting output as you need it and combine results for 'same months' in aggregation resports should that be a requirement. (You may also need to deal with the order of the months by including a month number or some such, either in the name text (eg 01-Jan) or as a hidden calculated field used to sort output at a later stage. We can come back to the output part when you have successfully extracted the data you need to a table.)

               

              If you have not yet used MCR it would probably be a god idea to check the section of the User Guide and Tutorial that descibes it as a starting point. It's not difficult to use but I think an initial 'orientation' helps people get into it quickly.

               

              Let us know how you get on. If for some reason your report proves problematic there are other approaches.

               

              KeyserSoze ... thanks for the search tip and unearthing a long forgotten post.

               

               

               

              Grant

                • Column Headings in Table View
                  toyug _

                  Grant, thanks for the heads up on the MCR!! We are on Version 10.5 so it should be there. I'll give it a shot and see what happens. I may be back for additional questions...

                   

                  KeyserSoze, thanks for finding the old post. I was trying to figure out how to do it that way, but didn't have much success. Here's hoping the MCR works for me!!

                   

                  Jim

                    • Column Headings in Table View
                      Grant Perkins

                      MCR will be there.

                       

                      As a basic understanding think of each column as a page or visualise a potentially very long page with a single column that has been 'folded' to make multiple columns.

                       

                      The design mode works with a single column. It may seem disconcerting but just accept it as it is and things will unfold.

                       

                       

                      Grant

                        • Column Headings in Table View
                          toyug _

                          Grant, I'm making progress with the MCR. Here is a sample of the report I'm working on:[CODE]

                                                                           FORECAST REQUIREMENT - NET                                                    PAGE   1    

                                                           As Of Fisc.Period - 12/3 Prod.Class - JKT  Batch # - 6                                                                               

                          STL/CLR      Nov      Dec     Jan      Feb      Mar      Apr      May      Jun      Jul      Aug      Sep      Oct        TOTAL      FWD                                                                               

                          15EH02          7      413      782        0        0        0        1      145      379        0    1,069        6      2,802         55                                                                               

                          15EH05          0      866      765       73      212      409      845      946      748    2,212    1,796      622      9,494        187                                                                               

                          15EH31          0        0       24        0      198        0      138      316        0      721        0      254      1,651         32                                                                               

                          15EI31          0      688       26       22      434      767      105        0      722    2,883      788        0      6,435        127                                                                               

                          150602          0        0    8,513    4,436   17,212      355    1,341      308    1,840    1,231    3,859      527     39,622        783                                                                               

                          150605          0   10,608   10,797    6,277   10,687   12,880    6,769   18,096   10,963    5,789   22,805    9,297    124,968      2,469                                                                               

                          150612      3,880        0        0        0      583    1,538    2,077    2,284    2,532      633    4,082    1,008     18,617        367                                                                               

                          150613        630      367      493        0       66      931      359      796    1,197        0      770      403      6,012        118                                                                               

                          150631        831    3,802    1,448    2,039    3,092    4,862    3,684    1,533    4,842    1,534    6,963    1,515     36,145        714 

                           

                          /CODE

                           

                          The issue I'm having is how do I get the Month Columns to go horizontal instead of vertical in my table view? The "month column" was created using MCR as a "append" template and it is only showing the last month's name (Oct). In the report view, all of the "month names" are highlighted so I think I did it correctly. I thought I would have seen all of them in the table, but only the last one shows up. The first four colums in the table were created as a Header template, the body of the report as a detail template. Wish I could have posted some pics to explain myself better, but it appears it is not allowed.

                          Here is the table view:

                          NET      12/3     JKT     6     Oct      15EH02     7     413     782     0     0     0     1     145     379     0     1069     6     2802     55

                          NET      12/3     JKT     6     Oct      15EH05     0     866     765     73     212     409     845     946     748     2212     1796     622     9494     187

                          NET      12/3     JKT     6     Oct      15EH31     0     0     24     0     198     0     138     316     0     721     0     254     1651     32

                          NET      12/3     JKT     6     Oct      15EI31     0     688     26     22     434     767     105     0     722     2883     788     0     6435     127

                          NET      12/3     JKT     6     Oct      150602     0     0     8513     4436     17212     355     1341     308     1840     1231     3859     527     39622     783

                          NET      12/3     JKT     6     Oct      150605     0     10608     10797     6277     10687     12880     6769     18096     10963     5789     22805     9297     124968     2469

                          NET      12/3     JKT     6     Oct      150612     3880     0     0     0     583     1538     2077     2284     2532     633     4082     1008     18617     367

                          NET      12/3     JKT     6     Oct      150613     630     367     493     0     66     931     359     796     1197     0     770     403     6012     118

                          NET      12/3     JKT     6     Oct      150631     831     3802     1448     2039     3092     4862     3684     1533     4842     1534     6963     1515     36145     714

                          NET      12/3     JKT     6     Oct      150702     0     43     124     12     158     238     384     1     1     361     486     204     2012     39

                          NET      12/3     JKT     6     Oct      150705     0     1451     317     23     492     1072     755     3374     969     73     2686     0     11212     221

                          NET      12/3     JKT     6     Oct      150713     0     0     0     0     0     0     0     0     614     0     0     0     614     12

                          NET      12/3     JKT     6     Oct      150731     0     0     35     0     194     543     0     1529     0     0     486     0     2787     55

                          NET      12/3     JKT     6     Oct      152702     0     1188     617     65     453     1099     138     551     262     736     1795     0     6904     136

                          NET      12/3     JKT     6     Oct      152705     0     6973     5219     2767     4521     5931     3832     8030     5084     2870     13179     2903     61309     1211

                          NET      12/3     JKT     6     Oct      152713     269     108     281     0     246     456     11     400     238     0     1099     0     3108     61

                          NET      12/3     JKT     6     Oct      152731     1244     3252     1377     1277     3750     2163     1793     7290     0     1323     4669     164     28302     559

                          NET      12/3     JKT     6     Oct      154002     0     0     0     0     0     0     0     0     0     0     0     0     0     0

                          NET      12/3     JKT     6     Oct      154005     130     789     0     0     139     78     716     350     2122     0     0     171     4495     88

                          NET      12/3     JKT     6     Oct      154013     431     0     206     0     0     0     0     0     0     0     0     0     637     12

                          NET      12/3     JKT     6     Oct      154031     274     297     0     418     0     161     520     0     216     67     1373     0     3326     65

                          NET      12/3     JKT     6     Oct      180807     0     0     0     439     0     0     0     0     0     1239     0     0     1678     33

                          NET      12/3     JKT     6     Oct      NET TOT     7696     30845     31024     17848     42437     33483     23468     45949     32729     21672     67905     17074     372130     

                           

                          /CODE

                           

                          Thanks for any help you can give me.

                          Jim

                            • Column Headings in Table View
                              Olly Bond

                              Hello Jim,

                               

                              It looks like the MCR is only active from after the Append template to the bottom of the page. If you are in the Report window, go to Templates, MCR, and choose Top of Page as the Start option for the region.

                               

                              HTH,

                               

                              Olly

                                • Column Headings in Table View
                                  toyug _

                                  Olly, that did give me the rest of the months, but..... not what I was hoping for. Data came out all in one column. They want it to look like the original report with the months going across and the data below each month

                                  NET      12/3     JKT     6      Nov      15EH02     7                                                                 

                                  NET      12/3     JKT     6      Nov      15EH05     0                                                                 

                                  NET      12/3     JKT     6      Nov      15EH31     0                                                                 

                                  NET      12/3     JKT     6      Nov      15EI31     0                                                                 

                                  NET      12/3     JKT     6      Nov      150602     0                                                                 

                                  NET      12/3     JKT     6      Nov      150605     0                                                                 

                                  NET      12/3     JKT     6      Nov      150612     3880                                                                 

                                  NET      12/3     JKT     6      Nov      150613     630                                                                 

                                  NET      12/3     JKT     6      Nov      150631     831                                                                 

                                  NET      12/3     JKT     6      Nov      150702     0                                                                 

                                  NET      12/3     JKT     6      Nov      150705     0                                                                 

                                  NET      12/3     JKT     6      Nov      150713     0                                                                 

                                  NET      12/3     JKT     6      Nov      150731     0                                                                 

                                  NET      12/3     JKT     6      Nov      152702     0                                                                 

                                  NET      12/3     JKT     6      Nov      152705     0                                                                 

                                  NET      12/3     JKT     6      Nov      152713     269                                                                 

                                  NET      12/3     JKT     6      Nov      152731     1244                                                                 

                                  NET      12/3     JKT     6      Nov      154002     0                                                                 

                                  NET      12/3     JKT     6      Nov      154005     130                                                                 

                                  NET      12/3     JKT     6      Nov      154013     431                                                                 

                                  NET      12/3     JKT     6      Nov      154031     274                                                                 

                                  NET      12/3     JKT     6      Nov      180807     0                                                                 

                                  NET      12/3     JKT     6      Nov      NET TOT     7696                                                                 

                                  NET      12/3     JKT     6      Dec      15EH02     413                                                                 

                                  NET      12/3     JKT     6      Dec      15EH05     866                                                                 

                                  NET      12/3     JKT     6      Dec      15EH31     0                                                                 

                                  NET      12/3     JKT     6      Dec      15EI31     688                                                                 

                                  NET      12/3     JKT     6      Dec      150602     0                                                            

                                  /CODE

                                  Original report...................

                                                                                   FORECAST REQUIREMENT - NET                                                    PAGE   1    

                                                                   As Of Fisc.Period - 12/3 Prod.Class - JKT  Batch # - 6                                                                               

                                  STL/CLR      Nov      Dec     Jan      Feb      Mar      Apr      May      Jun      Jul      Aug      Sep      Oct        TOTAL      FWD                                                                               

                                  15EH02          7      413      782        0        0        0        1      145      379        0    1,069        6      2,802         55                                                                               

                                  15EH05          0      866      765       73      212      409      845      946      748    2,212    1,796      622      9,494        187                                                                               

                                  15EH31          0        0       24        0      198        0      138      316        0      721        0      254      1,651         32                                                                               

                                  15EI31          0      688       26       22      434      767      105        0      722    2,883      788        0      6,435        127                                                                               

                                  150602          0        0    8,513    4,436   17,212      355    1,341      308    1,840    1,231    3,859      527     39,622        783                                                                               

                                  150605          0   10,608   10,797    6,277   10,687   12,880    6,769   18,096   10,963    5,789   22,805    9,297    124,968      2,469 

                                   

                                  /CODE

                                   

                                  If I do the MCR as a Detail Template and include the month's names, it comes out looking OK, but all of the data is in Character format, not Numeric format. Any other suggestions?

                                  Jim

                                    • Column Headings in Table View
                                      KeyserSoze _

                                      Now you can add a summary to your model to get the end result that you need.

                                       

                                      Add the STL_CLR field as a key, going down. Then add the Period field as another key, going across. Finally add the Amount field as a Measure field, and you're done.

                                       

                                      Edit: I overlooked your comment about the amounts being Character. Depending upon how you've captured/acquired the field, you can either change the field type to Numeric, or add a Val() function to convert the amount from Character to Numeric.