5 Replies Latest reply: May 15, 2014 9:52 AM by Hobbes _ RSS

    need run-time parameter formula

    Hobbes _

      I have a fairly complex database that runs Monarch over a 17.6 GB report with a batch file and imports the results into an Access table.  Currently I have Monarch set up to filter for every transaction a particular account has and create a spreadsheet with the information.  After the spreadsheet is created and imported into the database, I filter it further with date criteria. 

      The problem I'm having is that some accounts have more than 65,000 transactions, so when Monarch encounters this, it stops running.  I tried setting Monarch up to create a .txt file instead of a .xls file, but it won't import properly into Access. 

      I can reduce the information Monarch pulls if I am able to include my date criteria in Monarch instead of Access.  The problem is that my date criteria requires looking at two columns of information, comparing them, and choosing the appropriate date from one OR the other.  I successfully do this in Access, but can't figure out how to make this work in Monarch.  I was trying to create a run-time parameter, but I couldn't include all of the date comparison criteria.  Any suggestions???

       

      Thanks!!!  [img]tongue.gif[/img]

        • need run-time parameter formula
          Hobbes _

          I have a fairly complex database that runs Monarch over a 17.6 GB report with a batch file and imports the results into an Access table.  Currently I have Monarch set up to filter for every transaction a particular account has and create a spreadsheet with the information.  After the spreadsheet is created and imported into the database, I filter it further with date criteria. 

          The problem I'm having is that some accounts have more than 65,000 transactions, so when Monarch encounters this, it stops running.  I tried setting Monarch up to create a .txt file instead of a .xls file, but it won't import properly into Access. 

          I can reduce the information Monarch pulls if I am able to include my date criteria in Monarch instead of Access.  The problem is that my date criteria requires looking at two columns of information, comparing them, and choosing the appropriate date from one OR the other.  I successfully do this in Access, but can't figure out how to make this work in Monarch.  I was trying to create a run-time parameter, but I couldn't include all of the date comparison criteria.  Any suggestions???

           

          Thanks!!!  [img]tongue.gif[/img]

          • need run-time parameter formula
            Grant Perkins

            Firstly, is there anything stopping you looking at an export directly to Access from Monarch rather than Excel? Or is that what you are doing and the Excel part relates to something else? (I assume I am corerect in thinking that you are mining a report and not going directly into the database and filtering from there?)

             

            As far as extending the filter is concerned, how many date criteria are there?

             

            My expectation, possibly wrong in this case, would be that you might have a start and end date which was of interest overall but perhaps some complex rules of comparison between the two dates on the report.

             

            If so your need 2 runtime fields and a formula in another 'virtual' (i.e. hidden?) field that does the comparisons for you, using the report derived dates and the runtime entries.

             

            Perhaps your requirement is more complex than that but in principle that is where I would imagine you need to start.

             

            If you can write out the comparison rules I would be happy to have a go at an experiment but it will be a couple of days before I will have time to look at it.

             

            Grant

             

             

            Originally posted by Hobbes:

            I have a fairly complex database that runs Monarch over a 17.6 GB report with a batch file and imports the results into an Access table.  Currently I have Monarch set up to filter for every transaction a particular account has and create a spreadsheet with the information.  After the spreadsheet is created and imported into the database, I filter it further with date criteria. 

            The problem I'm having is that some accounts have more than 65,000 transactions, so when Monarch encounters this, it stops running.  I tried setting Monarch up to create a .txt file instead of a .xls file, but it won't import properly into Access. 

            I can reduce the information Monarch pulls if I am able to include my date criteria in Monarch instead of Access.  The problem is that my date criteria requires looking at two columns of information, comparing them, and choosing the appropriate date from one OR the other.  I successfully do this in Access, but can't figure out how to make this work in Monarch.  I was trying to create a run-time parameter, but I couldn't include all of the date comparison criteria.  Any suggestions???

             

            Thanks!!!   [img]tongue.gif[/img]  /b[/quote]

            • need run-time parameter formula
              Grant Perkins

              Firstly, is there anything stopping you looking at an export directly to Access from Monarch rather than Excel? Or is that what you are doing and the Excel part relates to something else? (I assume I am corerect in thinking that you are mining a report and not going directly into the database and filtering from there?)

               

              As far as extending the filter is concerned, how many date criteria are there?

               

              My expectation, possibly wrong in this case, would be that you might have a start and end date which was of interest overall but perhaps some complex rules of comparison between the two dates on the report.

               

              If so your need 2 runtime fields and a formula in another 'virtual' (i.e. hidden?) field that does the comparisons for you, using the report derived dates and the runtime entries.

               

              Perhaps your requirement is more complex than that but in principle that is where I would imagine you need to start.

               

              If you can write out the comparison rules I would be happy to have a go at an experiment but it will be a couple of days before I will have time to look at it.

               

              Grant

               

               

              Originally posted by Hobbes:

              I have a fairly complex database that runs Monarch over a 17.6 GB report with a batch file and imports the results into an Access table.  Currently I have Monarch set up to filter for every transaction a particular account has and create a spreadsheet with the information.  After the spreadsheet is created and imported into the database, I filter it further with date criteria. 

              The problem I'm having is that some accounts have more than 65,000 transactions, so when Monarch encounters this, it stops running.  I tried setting Monarch up to create a .txt file instead of a .xls file, but it won't import properly into Access. 

              I can reduce the information Monarch pulls if I am able to include my date criteria in Monarch instead of Access.  The problem is that my date criteria requires looking at two columns of information, comparing them, and choosing the appropriate date from one OR the other.  I successfully do this in Access, but can't figure out how to make this work in Monarch.  I was trying to create a run-time parameter, but I couldn't include all of the date comparison criteria.  Any suggestions???

               

              Thanks!!!   [img]tongue.gif[/img]  /b[/quote]

              • need run-time parameter formula
                Hobbes _

                Thanks for your help Grant!  I ended up taking a slightly easier route.  I changed the file extension to .tab and that solved my problems so that I didn't have to mess around with my Monarch model.  I've done small tests on this change and so far so good!

                • need run-time parameter formula
                  Hobbes _

                  Thanks for your help Grant!  I ended up taking a slightly easier route.  I changed the file extension to .tab and that solved my problems so that I didn't have to mess around with my Monarch model.  I've done small tests on this change and so far so good!