21 Replies Latest reply: May 15, 2014 9:52 AM by Grant Perkins RSS

    Excel WorkSheet and Records Problem

    atma _

      I am using Monarch Pro V7. I have report which has accounting data for multiple customers and the number of rows in the report can be in several thousands. I need to generate a excel report out of it with each customer in a seperate worksheet and if the number of records > 65500(approx. excel limitation), it has to generate a new worksheet for that customer.

       

      I have two problems here.

      1. How do I generate individual worksheet for each customer without hardcoding the customer name in the filter?

      2. How do I make sure a new worksheet gets generated when the record count is more 65500 when I dont know how many records I get in the report?

       

      Is there is solution with Monarch to generate this kind of report?   :confused:

        • Excel WorkSheet and Records Problem
          atma _

          I am using Monarch Pro V7. I have report which has accounting data for multiple customers and the number of rows in the report can be in several thousands. I need to generate a excel report out of it with each customer in a seperate worksheet and if the number of records > 65500(approx. excel limitation), it has to generate a new worksheet for that customer.

           

          I have two problems here.

          1. How do I generate individual worksheet for each customer without hardcoding the customer name in the filter?

          2. How do I make sure a new worksheet gets generated when the record count is more 65500 when I dont know how many records I get in the report?

           

          Is there is solution with Monarch to generate this kind of report?   :confused:

          • Excel WorkSheet and Records Problem
            Monarch_Guru _

            Originally posted by atma:

            I am using Monarch Pro V7. I have report which has accounting data for multiple customers and the number of rows in the report can be in several thousands. I need to generate a excel report out of it with each customer in a seperate worksheet and if the number of records > 65500(approx. excel limitation), it has to generate a new worksheet for that customer.

             

            I have two problems here.

            1. How do I generate individual worksheet for each customer without hardcoding the customer name in the filter?

            2. How do I make sure a new worksheet gets generated when the record count is more 65500 when I dont know how many records I get in the report?

             

            Is there is solution with Monarch to generate this kind of report?    :confused:  /b[/quote]

            • Excel WorkSheet and Records Problem
              Monarch_Guru _

              Originally posted by atma:

              I am using Monarch Pro V7. I have report which has accounting data for multiple customers and the number of rows in the report can be in several thousands. I need to generate a excel report out of it with each customer in a seperate worksheet and if the number of records > 65500(approx. excel limitation), it has to generate a new worksheet for that customer.

               

              I have two problems here.

              1. How do I generate individual worksheet for each customer without hardcoding the customer name in the filter?

              2. How do I make sure a new worksheet gets generated when the record count is more 65500 when I dont know how many records I get in the report?

               

              Is there is solution with Monarch to generate this kind of report?    :confused:  /b[/quote]

              • Excel WorkSheet and Records Problem
                Monarch_Guru _

                Originally posted by atma:

                I am using Monarch Pro V7. I have report which has accounting data for multiple customers and the number of rows in the report can be in several thousands. I need to generate a excel report out of it with each customer in a seperate worksheet and if the number of records > 65500(approx. excel limitation), it has to generate a new worksheet for that customer.

                 

                I have two problems here.

                1. How do I generate individual worksheet for each customer without hardcoding the customer name in the filter?

                2. How do I make sure a new worksheet gets generated when the record count is more 65500 when I dont know how many records I get in the report?

                 

                Is there is solution with Monarch to generate this kind of report?    :confused:  /b[/quote]First, I am sorry for the previous reply.  I accidentally hit reply before adding a message.

                 

                Here are the steps that you can take to export multiple sheets when you have records that exceed the Excel limit:

                 

                1.     Create a calculated field called Record No using the Recno function, "Recno".  That will create a record number for each record in the table.

                2.     Create a filter or add a formula to your existing filter using, "Recno<65000" in the first filter, "Recno>=65000 .and. Recno<130000" in the second filter and so on, if you have more than 130000 records in your table.

                 

                I hope that helps.

                 

                Note: Whenever you see "Recno" in this message, add open and close Parentheses.  This message board would not allow me to include actual parentheses.

                • Excel WorkSheet and Records Problem
                  Monarch_Guru _

                  Originally posted by atma:

                  I am using Monarch Pro V7. I have report which has accounting data for multiple customers and the number of rows in the report can be in several thousands. I need to generate a excel report out of it with each customer in a seperate worksheet and if the number of records > 65500(approx. excel limitation), it has to generate a new worksheet for that customer.

                   

                  I have two problems here.

                  1. How do I generate individual worksheet for each customer without hardcoding the customer name in the filter?

                  2. How do I make sure a new worksheet gets generated when the record count is more 65500 when I dont know how many records I get in the report?

                   

                  Is there is solution with Monarch to generate this kind of report?    :confused:  /b[/quote]First, I am sorry for the previous reply.  I accidentally hit reply before adding a message.

                   

                  Here are the steps that you can take to export multiple sheets when you have records that exceed the Excel limit:

                   

                  1.     Create a calculated field called Record No using the Recno function, "Recno".  That will create a record number for each record in the table.

                  2.     Create a filter or add a formula to your existing filter using, "Recno<65000" in the first filter, "Recno>=65000 .and. Recno<130000" in the second filter and so on, if you have more than 130000 records in your table.

                   

                  I hope that helps.

                   

                  Note: Whenever you see "Recno" in this message, add open and close Parentheses.  This message board would not allow me to include actual parentheses.

                  • Excel WorkSheet and Records Problem
                    Nick Osdale-Popa

                    You can reference the Recno() function directly in the Filter, rather than using a seperate calculated field.

                     

                    Also note, that you will lose your sort order if any sorting is applied.

                    • Excel WorkSheet and Records Problem
                      Nick Osdale-Popa

                      You can reference the Recno() function directly in the Filter, rather than using a seperate calculated field.

                       

                      Also note, that you will lose your sort order if any sorting is applied.

                      • Excel WorkSheet and Records Problem
                        atma _

                        Thanks for the replies. I tried this option earlier and it works fine for some extent. My problem is I dont know how many records will be there in the report. It can be 70000, it can be 100000, 200000, 300000 etc., In such a situation i don't know if i should create 10 filters and hope that number of records fall within the filter limit.

                        Is there a way to tell monarch something like

                         

                        if Recno()/65000 = 0

                        create a worksheet

                         

                        Maybe I am expecting too much out of the tool.    :confused:

                         

                        [size="1"][ September 23, 2004, 02:56 AM: Message edited by: atma ][/size]

                        • Excel WorkSheet and Records Problem
                          atma _

                          Thanks for the replies. I tried this option earlier and it works fine for some extent. My problem is I dont know how many records will be there in the report. It can be 70000, it can be 100000, 200000, 300000 etc., In such a situation i don't know if i should create 10 filters and hope that number of records fall within the filter limit.

                          Is there a way to tell monarch something like

                           

                          if Recno()/65000 = 0

                          create a worksheet

                           

                          Maybe I am expecting too much out of the tool.    :confused:

                           

                          [size="1"][ September 23, 2004, 02:56 AM: Message edited by: atma ][/size]

                          • Excel WorkSheet and Records Problem
                            Monarch_Guru _

                            What you can do it you can create, let's say, 10 filters (65,000 records for each filter).  Then create 10 Summary sheets/tabs and apply those 10 filters in each summary sheet.  10 filters should cover 650,000 records.  Also, everytime, when you run your report, check the number of records in the Table section.  If you have more than 650,000 records, you can then create more filters.  If less, you don't have to worry about it. 

                             

                              :cool:   Originally posted by atma:

                            Thanks for the replies. I tried this option earlier and it works fine for some extent. My problem is I dont know how many records will be there in the report. It can be 70000, it can be 100000, 200000, 300000 etc., In such a situation i don't know if i should create 10 filters and hope that number of records fall within the filter limit.

                            Is there a way to tell monarch something like

                             

                            if Recno()/65000 = 0

                            create a worksheet

                             

                            Maybe I am expecting too much out of the tool.     :confused:  /b[/quote]

                            • Excel WorkSheet and Records Problem
                              Monarch_Guru _

                              What you can do it you can create, let's say, 10 filters (65,000 records for each filter).  Then create 10 Summary sheets/tabs and apply those 10 filters in each summary sheet.  10 filters should cover 650,000 records.  Also, everytime, when you run your report, check the number of records in the Table section.  If you have more than 650,000 records, you can then create more filters.  If less, you don't have to worry about it. 

                               

                                :cool:   Originally posted by atma:

                              Thanks for the replies. I tried this option earlier and it works fine for some extent. My problem is I dont know how many records will be there in the report. It can be 70000, it can be 100000, 200000, 300000 etc., In such a situation i don't know if i should create 10 filters and hope that number of records fall within the filter limit.

                              Is there a way to tell monarch something like

                               

                              if Recno()/65000 = 0

                              create a worksheet

                               

                              Maybe I am expecting too much out of the tool.     :confused:  /b[/quote]

                              • Excel WorkSheet and Records Problem
                                atma _

                                Looks like thats what I have to do. Thanks for the reply!

                                • Excel WorkSheet and Records Problem
                                  atma _

                                  Looks like thats what I have to do. Thanks for the reply!

                                  • Excel WorkSheet and Records Problem
                                    Grant Perkins

                                    This does sort of sound like one of those 'it may never happen but just in case it does' situations which make life far more complicated than necessary much of the time.

                                     

                                    Have you estimated how often this is likely to occur?

                                     

                                    If it is a lot - should someone be considering whether Excel is really suitable for the process being undertaken?

                                     

                                    For example, could the required analysis be better handled in Monarch?

                                     

                                    I have no idea about the answers but have on occasion in the past found it useful to raise such questions.

                                     

                                     

                                    Grant

                                     

                                     

                                    Originally posted by atma:

                                    Looks like thats what I have to do. Thanks for the reply! [/b][/quote]

                                    • Excel WorkSheet and Records Problem
                                      Grant Perkins

                                      This does sort of sound like one of those 'it may never happen but just in case it does' situations which make life far more complicated than necessary much of the time.

                                       

                                      Have you estimated how often this is likely to occur?

                                       

                                      If it is a lot - should someone be considering whether Excel is really suitable for the process being undertaken?

                                       

                                      For example, could the required analysis be better handled in Monarch?

                                       

                                      I have no idea about the answers but have on occasion in the past found it useful to raise such questions.

                                       

                                       

                                      Grant

                                       

                                       

                                      Originally posted by atma:

                                      Looks like thats what I have to do. Thanks for the reply! [/b][/quote]

                                      • Excel WorkSheet and Records Problem
                                        Monarch_Guru _

                                        One other option that you have is to export those records into Access, instead of Excel.  Access can handle more records than Excel.  You can also create a report for each customer in Access.

                                        • Excel WorkSheet and Records Problem
                                          Monarch_Guru _

                                          One other option that you have is to export those records into Access, instead of Excel.  Access can handle more records than Excel.  You can also create a report for each customer in Access.

                                          • Excel WorkSheet and Records Problem
                                            atma _

                                            Well .. the user wants it only in Excel as he is comfortable with it. Regarding the chances of it happening .. even the user is not sure.

                                            This is a crucial accounting report that we cannot take chances .. I may have to look at different solution(excel macros etc.,) to achieve it.

                                             

                                            [size="1"][ September 24, 2004, 11:15 PM: Message edited by: atma ][/size]

                                            • Excel WorkSheet and Records Problem
                                              atma _

                                              Well .. the user wants it only in Excel as he is comfortable with it. Regarding the chances of it happening .. even the user is not sure.

                                              This is a crucial accounting report that we cannot take chances .. I may have to look at different solution(excel macros etc.,) to achieve it.

                                               

                                              [size="1"][ September 24, 2004, 11:15 PM: Message edited by: atma ][/size]

                                              • Excel WorkSheet and Records Problem
                                                Grant Perkins

                                                I'm sure you know the figures for the percentage rate of software developments that ever get used. Around 80 percent as I recall nd even then many that get through are not used as intended.

                                                 

                                                FWIW if this were my developement, taking into account complexity of future maintenance and that even the user has no idea if it will be a problem (and being 'comfortable' with something that doesn't work for you is not really such a good reason for using it for a critical report is it? Can they successfully deal with records split across 2 worksheets?) then I would:

                                                 

                                                1.  Get a few examples of typical reports and run them against the model and see what happens. Are there any times when there are likely to be particulalry high numbersof transactions? If so run the reports for those times.

                                                 

                                                2. Suggest that, assuming no obvious problem turn up in the tests (you can always get a summary to give you a record count by whatever grouping you require.) you will put a check in and a warning if the number of records is very high. The Export routine provide a warning anyway if you exceed the number of rows for an excel worksheet.

                                                 

                                                As far as the filter is concerned, are there a known set of customers or might this be variable according to the report selections?

                                                 

                                                Im wondering if it might be possible to do a pre-process to extract a summary record for each identifiable customer, complete with a record count perhaps, and then feed this back in to create a dynamic filter somehow. Probably needs a bit of vba code to be effective.

                                                 

                                                Of course if you were to set up a Portable Report Export with the customer number as the first key you might more easily see the nature of the problem and have a way of presenting your findings to the worried user. Just a thought.

                                                 

                                                Grant

                                                 

                                                Originally posted by atma:

                                                Well .. the user wants it only in Excel as he is comfortable with it. Regarding the chances of it happening .. even the user is not sure.

                                                This is a crucial accounting report that we cannot take chances .. I may have to look at different solution(excel macros etc.,) to achieve it. /b[/quote]

                                                • Excel WorkSheet and Records Problem
                                                  Grant Perkins

                                                  I'm sure you know the figures for the percentage rate of software developments that ever get used. Around 80 percent as I recall nd even then many that get through are not used as intended.

                                                   

                                                  FWIW if this were my developement, taking into account complexity of future maintenance and that even the user has no idea if it will be a problem (and being 'comfortable' with something that doesn't work for you is not really such a good reason for using it for a critical report is it? Can they successfully deal with records split across 2 worksheets?) then I would:

                                                   

                                                  1.  Get a few examples of typical reports and run them against the model and see what happens. Are there any times when there are likely to be particulalry high numbersof transactions? If so run the reports for those times.

                                                   

                                                  2. Suggest that, assuming no obvious problem turn up in the tests (you can always get a summary to give you a record count by whatever grouping you require.) you will put a check in and a warning if the number of records is very high. The Export routine provide a warning anyway if you exceed the number of rows for an excel worksheet.

                                                   

                                                  As far as the filter is concerned, are there a known set of customers or might this be variable according to the report selections?

                                                   

                                                  Im wondering if it might be possible to do a pre-process to extract a summary record for each identifiable customer, complete with a record count perhaps, and then feed this back in to create a dynamic filter somehow. Probably needs a bit of vba code to be effective.

                                                   

                                                  Of course if you were to set up a Portable Report Export with the customer number as the first key you might more easily see the nature of the problem and have a way of presenting your findings to the worried user. Just a thought.

                                                   

                                                  Grant

                                                   

                                                  Originally posted by atma:

                                                  Well .. the user wants it only in Excel as he is comfortable with it. Regarding the chances of it happening .. even the user is not sure.

                                                  This is a crucial accounting report that we cannot take chances .. I may have to look at different solution(excel macros etc.,) to achieve it. /b[/quote]