Creating a Relational Report Source

Before you can report on data in MITS Report, you must first have a Report Source that has been populated with data.  In this article, we will cover how to create a relational Report Source using the new source wizard.

Relational Report Sources are those that populate their data from a relational database such a Progress or SQL.  Check with MITS Support or your ERP provider if you're not sure which type of database your ERP uses.

From the Sources tab, click on the New button in the menu and select Relational Source.  

This will open the wizard, where you'll be stepped through the process of creating a new source.  Click Next after each step to advance to the next page of the wizard.  

Provide a title and description for your Report Source, and specify the connection to the database account where your desired source data resides.  This is a connection that would have been previously set up by your MITS Administrator or MITS Support.  It is possible to have multiple connections to multiple databases, so be sure you select the right one.

From here the wizard forks into two different paths.   If you already have an SQL query for the data you need, or have the ability to write one, follow the steps below in the section SQL QUERY.  If you prefer not to write or use an SQL query but know the table and fields for the data you need, follow the steps below in the section TABLE and FIELDS.

TABLE and FIELDS

• After entering the name of the source and selecting the connection, click Next.
  
  
• MITS Report will query the database and return a list of all the available tables.  Select the table you want from the list and click Next.
  
  
• Once more MITS Report will query the database, this time limiting the query to just that table and return all of the available fields.  Place a checkmark next to each of the fields you want to include in your Report Source.  Keep in mind the more fields you add to a source, the longer it will take to update.  Use caution when selecting all of the tables.  You may end up creating a source that is unmanageable.  Click Next to advance the wizard.
  
  
• Select any columns you want to have enhanced capabilities. Enhanced column capabilities include sorting, filtering, and adding a line break when the value in the column changes.  The more columns you select, the longer it will take for MITS Report to update the source.  We suggest only enabling this feature on the columns you know for sure are going to need it.  This can also be enabled or disabled for any column at a later time in a different part of MITS Report. 
  
  
• Click Finish to complete the creation of the Report Source.  This will take you to the Sources tab where your new source will be listed.  It will be flagged as Update Required since it doesn't yet have any data.
  
  
• Perform a Test Update to populate your new Report Source with a small amount of data. On the Sources tab, click Data under the Actions column of the new Report Source and select Test Update.  MITS Report will attempt to add 100 records to the new Report Source.  If the test update was successful, you can view the data that was added to Report Source by clicking Data under the Actions column of the Report Source and selecting View All Data.

SQL QUERY

• After entering the name of the source and selecting the connection, mark the checkbox to use an SQL query, then click Next.
  
  
• On the next screen will be a large entry box for you to write or paste in your SQL query.  In the upper right corner is a link to Execute the query.  This allows you to test and make sure your SQL is returning the expected results.  The output from the query will display under the entry box.
  
  After you have the query entered and tested, click Next to advance the wizard.
  
  
• Select any columns you want to have enhanced capabilities. Enhanced column capabilities include sorting, filtering, and adding a line break when the value in the column changes.  The more columns you select, the longer it will take for MITS Report to update the source.  We suggest only enabling this feature on the columns you know for sure are going to need it.  This can also be enabled or disabled for any column at a later time in a different part of MITS Report. 
  
  
• Click Finish to complete the creation of the Report Source.  This will take you to the Sources tab where your new source will be listed.  It will be flagged as Update Required since it doesn't yet have any data.
  
  
• Perform a Test Update to populate your new Report Source with a small amount of data. On the Sources tab, click Data under the Actions column of the new Report Source and select Test Update.  MITS Report will attempt to add 100 records to the new Report Source.  If the test update was successful, you can view the data that was added to Report Source by clicking Data under the Actions column of the Report Source and selecting View All Data.
   

Once a Report Source has been created and contains data, it will become available to report against in the Reports tab.