6 Replies Latest reply: May 15, 2014 9:55 AM by Grant Perkins RSS

    Summary export

    Bruce _

      I am trying to export a summary to Excel. I have dates going accross the page, and would like to show/ export a column EVEN if there is no data in the column.

       

      ie now it exports.

       

      Jan, Feb, (no March data so nothing), April etc. I need to see March column with zeros.

       

      Ant suggestions?

       

      Thank you

      Bruce  smile.gif[/img]

        • Summary export
          Bruce _

          Forgot to add I'm using Monarch 7.01, Excel 2002 (XP), Win XP Pro.

           

          Sorry

          BR

          • Summary export
            Grant Perkins

            Bruce,

             

            As I understand it you are reading some values into a field (making the content dynamic) and then using the field to display ACROSS in the summary to provide various values. Logically your field can only contain the information for different months that appear in the report (or possibly blank as well?) but you really need to create artificial columns for any months which have no data extracted.

             

            If that is right then we need to add any missing data values somewhere in the report so that the relevant columns become available even if with zero values.

             

            One way might be to spoof the process by always including some 'dummy' records that would ensure that every month existed at least once in the data table. A 'standard' file with 12 records (one for each month if youe are expecting to report 12 month periods) and formatted to be the same as the real file could, possibly, be concatenated with the real file to create a working file for the model to analyse.

             

            An alternative would be to set up a calcuated field for each month in each record and simply populate the 'active' month but leave the others as zero value. However the 'display field across' option in the summary would no longer apply since there would be multiple fields to display. That said you may be able to get the output format you need from the Table alone.

             

            Taking that idea a stage further you could consider a 2 stage process where stage 1 introduces the required array of all months in the table. Then export the table to a file to be used as a report and work with that as all months will now have a value to be picked, even if zero value.

             

            If you have Monarch Pro you could consider simply exporting to a .csv file, Excel file or similar and running that back into Monarch or just deal with it in Excel.

             

            There may very well be some other options which have not occurred to me and others on the forum may already have a solution to this puzzle.

             

            I would be quite happy to play with this some more but I would prefer to make it as specific as possible by working with samples of, or from, your input files (and possibly your current model?) to make it the effort more relevant. I find I tend to get more inspiration that way and spend less time wondering if any concepts developed using other fabricated layouts are going to be invalidated by the real layout which is providing the problem!

             

            If you feel that it would be acceptable to go down that route send me a Private Message and we can sort out how to go about it.

             

            Best regards.

             

            Grant

             

             

            Originally posted by Bruce:

            I am trying to export a summary to Excel. I have dates going accross the page, and would like to show/ export a column EVEN if there is no data in the column.

             

            ie now it exports.

             

            Jan, Feb, (no March data so nothing), April etc. I need to see March column with zeros.

             

            Ant suggestions?

             

            Thank you

            Bruce   smile.gif[/img]  /b[/quote]

            • Summary export
              Bruce _

              Grant

               

              Thank you for your response. I use Monarch to take 4 seperate reports out of the system, export into Access, use access to canatate these 4 files into one big one. I then go back into Monarch & use the access table as input. So the model file is not complicated at all.

               

              The data set is forecasts, bookings, sales and returns. Normally everything works fine, but if there is no data for a particular month, the month will not show up.

               

              Right now I fix up the "missing" columns in Excel, but there are 85 seperate files (one for each customer), this is a bit of a pain.

               

              I have struggled to add "fictional" data, but as each customer is differnt, I would have to look up what product they use, find out what months are missing, and then add (into Access?). Each month I would have to do this step.

               

              Thanks again

              Bruce  smile.gif[/img]

              • Summary export
                Grant Perkins

                Bruce,

                 

                Wow, interesting process. I'm glad I asked since it puts things in a brighter light!

                 

                Have you looked at doing all the concatenation using Monarch? (Based on V7 Pro allowing up to 9 linked external files.)

                 

                My thinking is that if you could get a full report across all customers each month must surely have at least one entry.

                 

                If you then set up filters by customer and apply them to a standard summary then use the Project Export to produce the separate outputs by customer I would guess you should get columns for all the months though some of them may not be populated for a given customer.

                 

                Alternatively export to Access with all columns in place and then read back into Monarch or summarization if required followed by mass report file output.

                 

                I have been using a 3 file (TAB delimited files) input which requires some 'massaging' and the addition of many lookups (from internal tables mostly). I needed a multi-stage process to make it work and in part I had to create dummy records - i.e. records not contained in the file that held the lowest level data - to complete the loop. However that was slightly different to what you describe since I was effectively creating DETAIL records to pad out incomplete references.

                 

                This may be something to consider. Your master database may work as you want it to if it has a a value array for each product (by customer) for every[/i] month with a zero value if nothing to report. That way you may get a column for each month (some with zero values) under all circumstances (unless you filter that field for values >0 in which case a summary would remove any columns with only zero values of course.)

                 

                A crude idea might be to have a 'dummy' customer called, say, "Control Record" where a full range of months with zero values are defined and the file format matches your complete concatenated file format. Control can be another file linked in. All filters by Customer would also filter in Control and therefore columns for each month would be created.

                 

                I feel sure there is a more refined way to achieve this reliably but it may need to some gestation time to uncover it!

                 

                If I can make some time I will try to simulate soemthing along the lines you described and see what I can come up with.

                 

                All the best,

                 

                Grant

                 

                 

                Originally posted by Bruce:

                Grant

                 

                Thank you for your response. I use Monarch to take 4 seperate reports out of the system, export into Access, use access to canatate these 4 files into one big one. I then go back into Monarch & use the access table as input. So the model file is not complicated at all.

                 

                The data set is forecasts, bookings, sales and returns. Normally everything works fine, but if there is no data for a particular month, the month will not show up.

                 

                Right now I fix up the "missing" columns in Excel, but there are 85 seperate files (one for each customer), this is a bit of a pain.

                 

                I have struggled to add "fictional" data, but as each customer is differnt, I would have to look up what product they use, find out what months are missing, and then add (into Access?). Each month I would have to do this step.

                 

                Thanks again

                Bruce   smile.gif[/img]  /b[/quote]

                • Summary export
                  Bruce _

                  Grant

                   

                  Your 1st comments gave me an idea. I added a dummy sales list with zero quantaties sold, for each customer, for each month in my planning period. It now works great!!

                   

                  Your 2nd comments about using Monarch to join tables does not work. I thought it would be great, but monarch only JOINS tables, and not append to existing tables.

                   

                  Thanks for all the help

                  Bruce

                   

                  PS I used to use Monarch ver 4, 5, but have not used for a few years untill I landed my new position. I talked them into getting ver 7.01 & now I'm back "in the swing"

                   

                    :cool:

                  • Summary export
                    Grant Perkins

                    Excellent news.

                     

                    That sounds like one of those tips that Mike Urbonas might like to include in the newsletter. A universal potential solution which can be applied in several different ways.

                     

                    Re. your JOIN comment - does that mean you are normally processing and then appending to an existing Access database? In particular the same database you are currently reading from?

                     

                    To be honest I am not sure that I have tried that in my experiments - probably not. Frankly by the time I have created my 'Master' file and then joined the 3 original files to it I am dealing with a large record set and I just haven't found the time or will to experiment a lot with a 970Mb file! Export would allow an append but I'm not sure if it would allow it to itself or that it wouold give the results expected. I guess, from what you are saying, that it does not. Apart from that may data is not dynamic so there is no need to investigate the append to the current database.

                     

                    May try to find the time to play around with that in the next few weeks. It could be useful to experience what is and is not feasible currently.

                     

                    Thanks for the feedback. I usually find that comments stimulate some further things to investigate and they all add to my personal 'knowledge base' so are much appreciated.

                     

                    Regards,

                     

                    Grant

                     

                    Originally posted by Bruce:

                    Grant

                     

                    Your 1st comments gave me an idea. I added a dummy sales list with zero quantaties sold, for each customer, for each month in my planning period. It now works great!!

                     

                    Your 2nd comments about using Monarch to join tables does not work. I thought it would be great, but monarch only JOINS tables, and not append to existing tables.

                     

                    Thanks for all the help

                    Bruce

                     

                    PS I used to use Monarch ver 4, 5, but have not used for a few years untill I landed my new position. I talked them into getting ver 7.01 & now I'm back "in the swing"

                     

                       :cool:  /b[/quote]