5 Replies Latest reply: May 15, 2014 10:04 AM by Data Kruncher RSS

    Runtime Parameters and Creating Filters from an External Source

    thecontroller _

      Hello,

       

      In one of my Monarch models I have a runtime parameter that prompts the user to enter a number (1-99) which represents the divisional unit. The prompt message informs the user what each digit means.  For example, 1= whole company; 2= travelling division; 3= product 85 division; 4= product 12 division; etc.

       

      Based on the value of the runtime parameter, a filter containing an IF statement will determine what the view of the records should be.  So, if ‘3’ was entered as the value for the runtime variable ‘Division’, then the appropriate divisional cost centres would be selected for the filter which is embedded in all my summary reports.

       

      Eg.Cost Centre Filter

      if(Division=1,[Cost centre] .In.('111222','333444','555666','444721'),

      if(Division=2,[Cost centre] .In.('123456','494974','262677','999721'),

      .In. ('999999')))

       

       

      What I don’t like:

       

      While the prompt message works it is all squished up and doesn’t look too appealing aesthetically.  I am used to using Visual Basic or VBA to create a nice looking dialog box with drop down list boxes, radio buttons and the like.  A little more visual real estate would make the prompt easier to read and lessen the chance for user error.

       

      There doesn’t seem to be a way to make the prompt message look nicely aligned.  It looks like a continuous word-wrapped message.  How do I insert a hard return into the runtime prompt?

       

      It would be really nice if I could create a drop down list box that had the 99 selections that a user could pick from instead of having the user type in a number.

       

      Does anyone know a more elegant way to do this?

       

      Also, if anyone knows a better way to create a filter based on the value of a runtime parameter—maybe by reading in cost centres from an external spreadsheet or Access database, my ears are open.  My computer science professor always told me “magic numbers’ in hard-coded filters are bad. 

       

      It is easier to have one cost centre database that I can update/add/delete instead of having to remember to change code all over the place in various parts of a Monarch model if cost centres are changed.

       

       

      Thanks,

       

      The Controller

        • Runtime Parameters and Creating Filters from an External Source
          Olly Bond

          Hello,

           

          If you need to maintain a table of codes and values - such as your cost centre database - then Monarch does let you do this within the model by using a type of calculated field: the lookup field.

           

          If this would be useful in multiple models, then Monarch v9 and above let you import entities (such as lookup fields, filters, templates etc etc) between models. A common deployment of this is to have a central master model that contains all the useful things you've built, that you can call on to select the right tool for the model you need. You can select strong, weak or no linking - this affects how the active model checks for updates from the source model.

           

          HTH

           

          Olly

          • Runtime Parameters and Creating Filters from an External Source
            Data Kruncher

            It sounds like it's time to scale this process up to a VBS script or better, an Excel VBA program.

             

            Then you could have better formatted and detailed input prompts, and could pass the inputs along to the model via the SetRuntimeParameter method.

             

            Using an Excel front end, you could import data from the cost center Access db, and use that as a validation source for user inputs.

             

            But when it comes down to it, since you can't create complex filters dynamically, you must define specific filters in advance in Monarch.

             

            That said, (as Olly said above) you could limit your overall efforts by employing a centralized strategy by creating a master Monarch model that defines the filters, both simple and compound, and then create strong links to those filters in your other models. See the object linking tutorial PDF file for more info.

             

            Your needs sound like it could be an interesting and very useful mini application. My Excel class module for Monarch would help you in developing your solution, regardless of your experience in automating Monarch.

             

            HTH,

            Kruncher

              • Runtime Parameters and Creating Filters from an External Source
                thecontroller _

                Olly and Kruncher,

                 

                Thank you for your replies. 

                 

                At least I have established that one can’t create filters dynamically in Monarch.

                 

                I actually tried to remember where strong, weak and no linking were mentioned in the Monarch Learning Guide.  Of course it wasn’t mentioned there at all.  When Kruncher mentioned the object linking tutorial PDF, I started looking for it.  I didn’t realize it was part of the launch menu in Monarch 10—I usually launch Monarch off the desktop icon.  I have started reading it and it looks good. My biggest beef with Monarch documentation is that it should consolidated in one big master document.  Materials about programming, functions, objects are scattered all over the place.  I use the command lines a lot in batch scripts, but the documentation is buried in 2 pages within Monarch Help—this assumes you read all of Monarch Help.  I only stumbled upon it accidentally.  I sometimes think that there is a page of documentation out there that could make my life easier if I only read it.

                 

                Someone should collate all the best tips and tricks of Monarch off the user forums and publish it (just a thought—I know this is a lot of work).

                 

                Kruncher, you mention an Excel class module for Monarch would help me.  I have no idea what this is about—can you direct me where I could find this “class module for Monarch” and how I could use it.  I am sure others would benefit too.

                 

                For me, a very small sample application to illustrate what you are talking about would help immensely.  Is there a book that you could recommend?

                 

                Thanks a lot,

                 

                The Controller

                v.10 Professional

                  • Runtime Parameters and Creating Filters from an External Source
                    Data Kruncher

                    I supply the [URL="http://********************/tips/monarch-programming-class"]Excel class module for Monarch[/URL] to those who elect to subscribe to my site via the free Feedburner service. The service sends you the new posts (which have been woefully non-existant of late , but that's a story in itself and I'm doing my best to rectify it) on the site direct to you via email.

                     

                    I'll try to post a sample of coding with the class module here later tonight. The main advantage of this approach is that you don't need to know all of the various flags and such for the Monarch object parameters. Instead, you're prompted with clear, user-friendly (hopefully ) parameters. You don't need to know the difference between 0, 1 values for a parameter.

                     

                    As I mention in my site post, my take on the Monarch Programmers Guide is that is was written for programmers by programmers. My class module is my attempt to bring the power that automating Monarch to the rest of us by hopefully making the task easier to do and understand.

                     

                    The tool includes not just the program code, but also full documentation and many, many samples.

                     

                    Again, I'll try to post a meaningful sample or two here later.

                     

                    Kruncher

                      • Runtime Parameters and Creating Filters from an External Source
                        Data Kruncher

                        As mentioned above, here are a couple of samples from my custom Monarch Excel class file. Class modules are a mechanism that Excel offers that allows for the creation of objects and can make programming easier to manage.

                         

                        This snippet would load a new report into Monarch and apply an existing model file.

                         

                        With objMonarch

                            bSuccess = .LoadReport("D:\Reports\SalesQ1.prn", mclAppendTypeNew)

                            If bSuccess Then

                               bSuccess = .ApplyModel("D:\Models\Sales.xmod")

                            End If

                        End With[/code]

                         

                        Here's a sample of my ExportSummaryEnhanced method, which offers the named parameters (sDestinationFile, sTableName, iAppend):

                         

                        With objMonarch

                           bSuccess = .LoadProject("D:\Projects\Inventory.xprj")

                           If bSuccess Then

                              .ActiveSummary = "Supplier Purchases"

                              If .ActiveSummary <> "Supplier Purchases" Then

                                 MsgBox "The Supplier Purchases summary does not exist in the model."

                                 bSuccess = False

                              Else

                                 'you would normally choose only one of the following three file handling methods

                         

                                  'create a new table (sheet) in the output file

                                  bSuccess = .ExportSummaryEnhanced("D:\Output Files\Supplier Purchases.xls", "Purchases", mclFileAppendTypeNewTable)

                         

                                  'add data to an existing table named Purchases

                                  bSuccess = .ExportSummaryEnhanced("D:\Output Files\Supplier Purchases.xls", "Purchases", mclFileAppendTypeAddData)

                         

                                   'overwrite the existing ouput file if it already exists

                                   bSuccess = .ExportSummaryEnhanced("D:\Output Files\Supplier Purchases.xls", "Purchases", mclFileAppendTypeOverwriteFile)

                               End If

                           End If

                           If bSuccess Then

                              MsgBox "Export complete!"

                           Else

                              MsgBox "Export failed!"

                           End If

                        End With

                        /CODE

                         

                        The file includes 51 worksheets used to describe each of the available properties and methods available to control Monarch, and a FAQ to answer task related questions like "How do I open a report?", "How do I export the table?", "How do I run a specific project export?" and many more. Two sheets summarize the available properties and methods and describe the purposes for each - what it means to you.

                         

                        The items that are for the use of Monarch Pro users only are clearly indicated with red icons.

                         

                        HTH,

                        Kruncher