8 Replies Latest reply: May 15, 2014 10:08 AM by Joe Berry RSS

    Row Count Limit

    aelisalda _

      I need help trying to limit the amount of rows that I am seeing.  I am opening multiple reports and running them through one model.  So then in my table I am seeing all of the reports combined.  I'm sorting my table by location number and then by amount so the highest amounts show up at the top.  I want to be able to limit that amount of data to the first 100 rows per location.

       

      Any thoughs on how I might get at that?

       

      Thanks

      ~Angie

        • Row Count Limit
          Olly Bond

          Hello Angie,

           

          You can do this in a summary - but it needs two passes. Get the unfiltered data sorted as you would like in a table, export to an MDB file, then open this in another Monarch project.

           

          In one model it can't work because a summary can't depend directly or indirectly on the Rowno which can be affected by sorting, but by exporting you can freeze the sort order and use Recno to get the result you need.

           

          Best wishes,

           

          Olly

          • Row Count Limit
            Data Kruncher

            Hi Angie,

             

            I normally handle this exact type of work to make Top 20 tables in Excel, but of course it's done with Excel formulas.

             

            You can do this with Monarch v9, though it takes a bit more effort than you'd think, and unless I'm mistaken and have created a make-work project, probably more than it should!

             

            Here's my approach. You'll need to make three different Monarch projects to accomplish this.

             

            In the first project, build your sort by location (ascending) and then by amount (descending) as you've described. Now add a calculated RowNumber field using the RowNo() function. Define a project export to send the Table window to a file (use xlsx or Access database table). Run the project export and save the model and the project files, using "Pass 1" in the file names.

             

            Now add an external lookup to the Table, connecting the file that you created with the Pass 1 project, using the Location number as the common field, and importing the RowNumber field as LocationRow.

             

            Build another project export and export this table to an xlsx or Access file. Save the model and project with "Pass 2" in the name.

             

            Finally create a new Monarch project using the Pass 2 export as the data source. Build a filter, using:

            RowNumber-LocationRow < 100

             

            For extra flexibility, consider using a runtime parameter for the 100 value, and using that field in the filter.

             

            Save and close the Pass 3 model and project.

             

            Of course, you won't really want to handle all of these three project files yourself every time that you want to update your data. Build a batch file that you can run just by double-clicking "Top100.bat" on your desktop.

             

            @echo off

            START "Pass1" /WAIT "C:\Program Files\Monarch\Program\Monarch.exe" /prj:"Pass1.xprj" /pxall

            START "Pass2" /WAIT "C:\Program Files\Monarch\Program\Monarch.exe" /prj:"Pass2.xprj" /pxall

            "C:\Program Files\Monarch\Program\Monarch.exe" /prj:"Pass3.xprj"

            /codeI see that Olly mentioned the problem relating to the row number while I was building this solution.

             

            HTH,

            Kruncher

              • Row Count Limit
                aelisalda _

                Data Kruncher,

                 

                I'm having a hard time following the "pass 2".  I have pass 1 which is my table with an extra column for the row number.  I'm struggling on how to get the CustRow.

                  • Row Count Limit
                    Data Kruncher

                    Sorry, my mistake with field naming. Instead of a report with location information, I built my test with Customer info, thus the naming slip.

                     

                    Let me take a minute to edit that post.

                     

                    Oh, and you are using Monarch Pro aren't you (he asks hopefully)?

                      • Row Count Limit
                        aelisalda _

                        Yes Monarch Pro version 9.0

                          • Row Count Limit
                            Data Kruncher

                            Good! Then this is still possible!

                             

                            Assuming that the Pass 1 project exports to "Pass 1 Export.xlsx", then setup an external lookup using "Pass 1 Export.xlsx" as the data source. Use your location field as the linking columns. .You'll get a prompt about the field not forming a unique key. That's OK. You just want the value for the first instance of your location value. Click Yes to proceed and opt to import the RowNumber field.

                             

                            Edit: If you're not really familiar with external lookups then [URL="http://********************/sources/video-monarchs-external-lookups"]this video[/URL] might help.

                              • Row Count Limit
                                Olly Bond

                                Hello Angie,

                                 

                                I've mocked up a basic two project solution using Kruncher's approach and posted it on-line at:

                                 

                                www.greenbar.info/examples/angie/angie1.xmod[/url]

                                www.greenbar.info/examples/angie/angie1.xprj[/url]

                                www.greenbar.info/examples/angie/angie2.xmod[/url]

                                www.greenbar.info/examples/angie/angie2.xprj[/url]

                                www.greenbar.info/examples/angie/angie1out.mdb[/url]

                                www.greenbar.info/examples/angie/angie2out.xls[/url]

                                 

                                It's all based on the Classic example report files ClassJan.prn and ClassFeb.prn, giving an export of the ten biggest transactions each for January and February.

                                 

                                I've used the root of H:\ as a working folder - you may find Monarch Utility handy to fix the paths to your environment.

                                 

                                HTH,

                                 

                                Olly

                                  • Row Count Limit
                                    Joe Berry

                                    I do top 25 reports using a single project.  This would be work for you provided there are not too many locations.  Your sort is perfect, just create a filter for each location and go to the advanced tab on the filter.  You can enter the first ##  rows for your filter.  Create an export using that filter and you have a top ## report.

                                     

                                    This is not a good solution for a large number of locations; however, if there are just a few, it will work well.