Produmex Manufacturing offers and easy solution for report customization with Crystal Reports.
Please note: This document describes the custom report creation procedure in MSSQL.
In our example we will include the foreign name of the operation on the Job Requirements report under the operation name.
Open the report. On the ‘Select Report Layout’ form select a base layout on the grid then click on the ‘New’ button to create a new layout.
In the example we will open the Job Requirements Report.
Add the ‘Layout Name’ and the ‘Filename’ then click on the ‘Add’ button. The base layout will be copied to the new layout.
After adding the custom layout, select it on the grid. Click on the ‘Edit Layout’ button and open the layout with Crystal Reports.
After the layout has been opened in Crystal Reports, setup the ODBC connection between the report and the database. On the Field Explorer right click on ‘Database Fields’ and select ‘Database Expert’ option.
On the opening ‘Database Expert’ form open the ‘Create New Connection’ folder and click on the ‘OLE DB’ folder. On the OLE DB form chose ‘Microsoft OLE DB Provider for SQL Server’ as the Provider. Click on ‘Next’ to proceed.
On the next screen add the connection information. Select the local server as the ‘Server’ and enter the User ID and Password. Then select the database from the dropdown menu and click on ‘Next’.
Please note: It is possible the use the custom report in a different company as well, because the Produmex Manufacturing add-on will always set the Server and the Database name to the currently connected Company database.
On the next screen click on ‘Finish’.
The database is added to the OLE DB folder.
Then select the database table that contains the field you would like to add and click on the right arrow to add it to the ‘Selected Tables’.
In our example we would like to add the foreign name of the operation to the report. In order to do this, we have to add the OITM table (Item Master Data).
Define the file path to the BX PPS Data Set Model by clicking on the ‘Links’ tab. Browse the BxPPSDataSetModel.xsd file after clicking on the ‘…’ button on the ‘ADO.NET form. Then click on ‘Finish’.
The default file path is: C:\Program Files\SAP\SAP Business One\AddOns\BXP\Produmex Manufacturing\BxPPSDataSetModel.xsd
On the Links tab the links between the table records are shown. These links are created automatically and might not be correct for the customization goals. Correct the links by deleting the wrong ones and creating new links. Click on the ‘OK’ button.
In the example the ‘Item Name’ field form the OITM table was linked automatically to the WOR1 table. First we delete this link then we create the new one by connecting the Operation Code from the BXPPSPRODORDEROPER table to the Item Code on the OITM table.
A database warning will pop up. Click on the ‘OK’ button to acknowledge the message.
Select the field to add to the report on the ‘Field Explorer’ module and simply drag and drop it to the report.
After the formatting is ready, save the report. Make sure that you do not save it under SAP\SAP Business One\AddOns\ because in that case the file will be overwritten by a new Produmex add-on installation. It is also recommended to save the custom report to a central location where every client will be able to see it.
When you print the custom report created in the example, the foreign name will be added under the operation name.