3 Replies Latest reply: May 15, 2014 9:56 AM by Data Kruncher RSS

    Access DB - Custom reports thru Monarch

    Carl P

      Hello All!

       

      I'm not sure if this is even possible, but here goes...

       

      I have an Access DB (with a DB password applied to it) that has, say, 3 tables:

       

      Table1:

      Field Names:

      EmpID (PK)

      Name

       

      Table2:

      Field Names:

      ShareID (PK)

      EmpID (FK)

      NumberOfShares

      Purchase Date

       

      Table3:

      Field Names:

      ContribID (PK)

      EmpID (FK)

      Amount

       

      I am trying to create VB code that would display detail results based on the tables they choose. For example, on a VB Form, the user would choose the Employees table and StockShares table. The user would then click a button that would launch Monarch. Monarch would bring these tables together and show detail on Name, NumberOfShares and PurchaseDate. At that point, since Monarch would be open, the user could create their own summaries, filters, etc.

       

      I have an excellent knowledge of VB programming and I have seen the code examples in the Monarch Programmers guide, which I can easily follow, but I'm not sure how to write the code to do what I'm attempting above. Is there a way to do it? Any pseudo code or advise would be most helpful.  :rolleyes:

        • Access DB - Custom reports thru Monarch
          Data Kruncher

          Hi Carl! Welcome to the forum.

           

          There's a [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000312;p=1#000002"]good example of keeping Monarch active[/url], so that the user can work with Monarch after the model loads.

           

          I would have models prebuilt and ready for any combination of tables chosen by the user. The you use the model needed for that request.

           

          Say they select the Stock Shares table. Because the Stock Shares could have multiple records for the same employee, I'd open that table first, then link in the employee name from the Employees table.

           

          Additionally, you could pass runtime parameters programmitically to send form variables to Monarch, like employee name for example.

           

          BTW, there's an [url="http://www.datawatch.com/support/downloads.asp"]upgrade available[/url] to 8.02.

           

          Does that help you progress with your project?

           

          Kruncher

          • Access DB - Custom reports thru Monarch
            Carl P

            Thanks for the quick reply, Kruncher.   

             

            I had a strange feeling that I would need to create a bunch of models for the different scenarios (that could get ugly in a larger database). Oh well.   

             

            I'm not clear on how those runtime parameters work or even what they specifically pertain to. Do you have any examples that you can send me on how they work?  smile.gif[/img]

            • Access DB - Custom reports thru Monarch
              Data Kruncher

              There may be another way other than having multiple models, and coincidentally (?) it may involve runtime parameters, but I've not tried it before... I'll think about it before rambling out loud. Nick may have more ideas on this. Nick?

               

              As to specific examples:

              [font="courier"]   openfile = MonarchObj.SetReportFile(Range("ReportFile").Value, False)

                 If openfile = True Then

                      With MonarchObj

                          SetRun = .SetRuntimeParameter("EmployeeID", Range("rngEmployeeID").Value)

                          SetRun = .SetRuntimeParameter("EmployeeName", Range("rngEmpName").Value)

                      End With

                      openmod = MonarchObj.SetModelFile(Range("ModelFile").Value)

                      If openmod = True Then

                         'process data

                      End If

                 End If /font[/quote]This requires the model file have pre-existing runtime parameter calculated fields defined named "EmployeeID" and "EmployeeName".

               

              Since any function or filter can make use of a runtime parameter field, the ways to improve automation like this (Access or Excel or w.h.y. can basically communicate dynamically with Monarch) give you a very powerful tool.

               

              HTH,

              Kruncher