6 Replies Latest reply: Feb 12, 2016 5:30 AM by Dave-G _ RSS

    Column Headings with changing years.

    Dave-G _

      I am using Modeler v12 with Automator. Upgraded from Monarch 10.5 with DataPump. My report has 3 columns with the same year in it's heading and the report has 5 years. So, I have 15 columns to create a way to make the years dynamic when a new 5 year report is created. I was able to use the Summary option with the Across checked box to correctly format my column headings. For each on the columns, I create a separate table. I have to do that, because I think the Summary Across only will work with one Key Field and One Measure. Is that correct? My main question centers around the External Lookup. When I bring in a new table with updated years, I get a "Modeler-Error" saying

      "No value given for one or more required parameters." The Main file shows the previous years column headings with no data. It looks like the Select column - check box is not checked when I bring in the new files. Are tables used in External Lookup, need to retain the same column names? Any thoughts on how I can update the years?

       

      Header AHeader BHeader CHeader D
      ACCOUNT2015 FIELD A2015 FIELD B2015 FIELD C
      A101560
      B202570
      C303580
      D404590
      E5055100
        • Re: Column Headings with changing years.
          Chris Porthouse

          Just to answer the question about external lookups, when you create the external lookup, you map/select the columns you want to use.  If you change the name of the column, then you will get an error because Monarch can no longer find the column name you specified.

          • Re: Column Headings with changing years.
            Olly Bond

            Hello Dave,

             

            It's hard to be sure of the best approach without seeing the report and the external lookup. Are you exporting this summary and using that as the source of the lookup? Or trying to join another data set on to this summary?

             

            Best wishes,

             

            Olly

              • Re: Column Headings with changing years.
                Dave-G _

                Hi Olly,

                 

                I create 3 summary files from the same model and project. I will call the 3 files A, B & C. I use A as the Main source and B & C as External Lookups. The report is very much like the example I posted.

                 

                HTH,

                 

                Dave

                  • Re: Column Headings with changing years.
                    Olly Bond

                    Hello Dave,

                     

                    Aha - in order to end up with fifteen columns - now I get what you're trying to do. In v12 you can do this without the extra pass - there are three techniques to choose from.

                     

                    If you have nicely separate columns for field A, field B and field C already, let's call them [Apples], [Bananas] and [Cherries] and assume they have numeric values, then just add these as measures and in the Summary display options set the Active Measure to "All, by Measure", which will give you two header rows:

                     

                    Customer >>> 2015 >>> 2016 >>> ...

                    ... > Field A > Field B > Field C > Field A > field B > Field C ...

                    then your data.

                     

                    If you don't have a column called Apples, another called Bananas, another called Cherries for A B and C, but just have a column called "Fruit" with different text values appearing in each row, then you might find that making a calculated field combining str(Year)+[Fruit] gives you a new field you can use as a key, with a single measure.

                     

                    If things are a little hairier, then as long as you are working from a text or PDF report, then you can solve this nicely with a Multi Column Region trick. Let me know if you need to explore that route.

                     

                    Best wishes,

                     

                    Olly