Create a Report: Stock Threshold Info 2

Continuing from our previous discussions, you are now ready to add some data to our report. Considering that we’re calling this a “Stock Threshold” report, we probably want some core item information and the various thresholds. Some of this data is in MIITEM, the rest is in MIILOC.

Versions of Crystal vary, however, there should be a frame to the right that contains Database Fields and should have the two tables listed. Expand these nodes and you will see the fields that are available. Start by adding MIITEM.itemId and MIITEM.descr to the details of the report (don’t forget column headers if they don’t get automatically created). To do this, click on the field and then drag and drop it in the details section of the report. Now add the following fields from MIILOC- minLvl, ordLvl, maxLvel, ordQty.

Once you have done this, look at the Preview of the report. You should see a tight listing of items and their thresholds.

This report is a nice foundation. Next time, well discuss some filtering.

Visit us online at www.misysinc.com/support

Create a Report: Stock Threshold Info

Previously we talked about getting Crystal Reports connected to your MISys Manufacturing (no longer SBM!) database. Once that’s done, what can you do? While there are limitations on what can be done with custom fields and some of our more complicated reports that required processes, the whole database is otherwise available to you. In this simple example, we will demonstrate getting stock threshold information so that you can display them.

While you could just go to the Item Location file (MIILOC) and display the items and their thresholds, you may want to include other pieces of information from the Item record (description, unit of measure, etc.) So, we’ll start with MIITEM.

Create a new report in Crystal. Select the Data Source you created previously (using the required credentials and select the appropriate database name). You need to drill down into | dbo | Tables to get the table listing. Control-Click on MIILOC & MIITEM and bring it into the report and click Finish.

Now you can bring in the desired fields you want. Next time we’ll discuss a little more on this report concept.

For MISys Manufacturing technical support, visit us online at www.misysinc.com/support

Using the Source

At this point you should have a usable data source that Crystal can use as a conduit into the SBM database on your server. So what do you do with it?

1. Open Crystal Reports.

2. Click File | New and then select Standard Report.

3. Expand Create New Connection.

4. Expand ODBC (RDO).

5. Select your data source and click Next.

6. Enter your credentials (again, If you are using the instance of SQL provided with MISys SBM, contact technical support for this information).

7. Select the database name that you use- if you want to try the report on Sample Data, select SBMSAMPCO.

8. Click Finish.

What you see here might be different depending on which version of Crystal you’re using. Look for a node with your database name and expand it. Then expand dbo and then Tables. You now have all of the MISys tables exposed to the report.

It Starts With The Source

Start your exploration of direct access reporting by creating a data source. This is the heart of the whole thing. Crystal uses the data source as a conduit to the MISys SBM database, giving you access to the tables in the database.

To create a Data Source:

1. Click Start, point to Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC).
2. Click the System DSN tab, and then click Add.
3. Click on SQL Server and then click Finish.
4. Type the data source name (e.g. MISYSSBM). Remember what you choose. You will need to use it in Crystal.
5. Select the Server where MISys SBM is installed. If you are using the instance of SQL provided with MISys SBM, the you would be looking for \MISYSSBMSE, and then click Next.
6. Select With SQL Server Authentication for verifying Authenticity. Then use the User Name and Password of the SQL Server that will have access to the database. If you are using the instance of SQL provided with MISys SBM, contact technical support for this information.
7. Click Next through the next windows and then click Finish.

Now you have a data source to use in Crystal Reports to access the MISys tables directly.

Visit MISys Manufacturing Support at www.misysinc.com/support.php

Remember that you can use Crystal reports to report directly from your MISys SBM database.

It is a standard MS-SQL database, so all you need to do is make a System data source for SQL and then direct it to the database engine where the MISys SBM database resides. Then create a new report in Crystal using an ODBC (RDO) data source and select the data source you just made. Fill in the information and select the database to use and you’re off and running.

Oh, and If you are interested in our table layouts start SBM and go to Administration Housekeeping Print Data Tables.

For MISys Support, visit us online at misysinc.com/support.php