|
Create A SQL Server ODBC Connection
You must create a SQL Server ODBC connection to point to your database, which is in this case your ActiveM database. For this example we will name it LocalGGS.
Select the server available to your system then select the database, usually it will be called ActiveM.
Keep clicking Next until you get to the end of the process. When you have an opportunity to test the data source, do so to ensure setup has been completed correctly and that you have sufficient rights to the SQL server.
Create the View (SQL Query)
The views can be found within the SQL Server Management Studio (SQL Enterprise Manager) in the view section of your Database. Here you can design the query with will act as the data source for your report.
Once done, save it to your SQL Server. Remember the name you give it.
Create your Crystal Report
From within Crystal Reports, you need to set the data source location.
Go to the “DATABASE” menu option and choose “Database Expert”
Move down to the “Create New Connection” option and expand the “OLE DB (ADO) option and choose to create a new connection.
Scroll down through the list and chose the “Microsoft OLD DB Provider for ODBC Drivers” option. Choose your ODBC DSN that you would like to connect to (typically ActiveM).
For SQL versions of the software you will need to provide your login information for the database (contact your SQL administrator for assistance).
Click the finish button.
At this point you should now have a connection to your ActiveM database within Crystal. This now allows you to add your custom SQL View to the report.
Layout your report as you would do any normal report. Once the all the database fields have been placed and you the report is completed, you need to save the “.rpt” file in your master sxRelease folder\templates.
Storing the rpt file in templates subfolder allows the rpt to be sent to the client computers on autoupdate and prevents unnecessary network traffic whereas clients are running the file from a network share location.
Maintenance of the Datasource
In the event you need to add, remove or just alter the data source or report, you will have to re-verify the report in Crystal Reports.
To do this perform the following actions
| 1. | Open your view in either SQL or Access. |
| 2. | Modify the view as you see fit. |
| 3. | Obtain the SQL statement that represents your modified view (save the information to a text file temporarily). |
| 4. | Open the Crystal Reports file. |
| 5. | Under the “Database” menu option, choose the “verify database” option. Note, on reports that contain sub-reports, the main database is verified first followed by the sub reports. |
Now you are free to add database fields.
Linking the Report to OrderStream
The link between Crystal and OrderStream is the reports database. It can be located by checking the Reports ODBC connection on your machine.
This database holds all the information needed to link to OrderStream and you must set up your crystal reports in it as well
The database contains the following tables which are needed to be updated to set up your report
Reports Database Table Reference
|
|
|
Modules
|
System Table that contains all the Seradex System modules and their ID’s.
|
ModuleType
|
Not Used
|
OwnerTypes
|
Not Used
|
Printers
|
Contains all printers available on the network
|
ReportDestinations
|
Links Reports, Users, Printers and computers
|
ReportDisplay
|
Controls Display of Reports by User
|
Reports
|
Main Table used to setup a custom report
|
ReportsDialogSetup
|
Table used to drive Report Filtering Form
|
ReportSettings
|
Stores Printer Bins and Tray Information if needed
|
ReportsSubReports
|
Table used to drive sub reports associated with a main report
|
ReportsSubReportsSetup
|
Links Report Filtering to Sub Reports - Optional
|
Version
|
System Table to record version of Custom Reports database
|
Reports Table Setup
The Reports table is the master table in which you set up the report.
Reports Table Field Reference
|
|
|
|
ReportID
|
The Unique Report Identifier used to link information to the other report tables
|
1
|
ModuleID
|
This is the module ID from the Module Table that is also in the reports database, it determines which section of the system it prints
|
2
|
Name
|
Report Name – Developer’s Name for this Report
|
My Custom Sales Report
|
ModuleTypeID
|
Not Used
|
|
CompanyName
|
Your Company Name
|
Seradex
|
ReportDescription
|
Report Description
|
My Report Description
|
ReportTitle
|
Title of Report (Name that the user sees on the Switch Board)
|
Seradex Sales Order
|
HeaderAddressStyle
|
Not Used
|
|
PrinterID
|
Links to the printers table, holds printer info
|
|
DateCreated
|
Date Report was Created
|
10/20/2007 12:59 AM
|
DateModified
|
Date Report was Modified
|
10/20/2007 12:59 AM
|
UserModified
|
User Who Modified this Transaction
|
|
UseDialog
|
Use a dialog box? (True in most cases) Indicates if a dialog box should be presented for the user to interact with at runtime.
|
v
|
MainQuery
|
Contains the SQL query that represents your view (SQL) or your query (access). This SQL statement in this field must match what your report was designed to at all times.
SQL – open the Management Studio/Enterprise Manager, locate the view your crystal report was designed to, right click and choose modify. From there, obtain the SQL statement, copy and paste it into this field.
Access – open your ActiveM database and switch over to the queries panel. Locate the query your report was designed to. Right click on the query and choose design view. From there, obtain the SQL statement, copy and paste it into this field.
|
SELECT SalesOrder.SalesOrderNo, SalesOrder.SalesRepID, SalesOrder.DueDate FROM SalesOrder
|
DefaultType
|
GRD = Yes SDD = No
GRD – Generic Report Dialog – This is the most common filtering form utilized by Standard Reports and should be your default selection
SDD – Select Document Dialog – A Filtering Form that enables users to select multiple transactions not in sequence as well as advanced search and filter. Refer to the section of SDD reporting for further information.
|
GRD
|
MainQueryField
|
The field from your MainQuery that limits the report in the form of “table.fieldname”.
The value entered her must be in your SELECT statement.
Example – on a sales order report, you may use the SalesOrder.SalesOrderNo
|
SalesOrder.SalesOrderNo
|
SddQueryField
|
SDD Query field for link to main query
Refer to the SDD reporting section for further details.
|
|
SDDQuery
|
Select Document Dialog Query used to load the SDD filter form
Refer to the SDD reporting section for further details.
|
|
Owner
|
Your Company Name or Seradex – whoever is responsible to maintain the report
|
|
DeveloperComment
|
Developer Comment
|
|
ReportTitleShort
|
Not Used
|
|
FieldShading
|
Not Used – You can this functionality directly to Crystal Reports
|
|
CompanyHeader
|
Indicates that the report contains crCompanyHeader, False if your report does not use the Crystal Report crCompanyHeader Subreport. Alternately you can have that flag checked and If your crystal report contains a formula called “sxCompanyName” that formula will be filled with the Company Name at RunTime.
If you do not have either the crCompanyHeader sub report in your main report or the formula sxCompanyName, you must turn this flag off.
This saves time when creating reports.
|
|
CompanyFooter
|
Indicates that the report contains the Crystal Report crCompanyFooter
If your report does not use the sub report crCompanyFooter, you must turn this flag off.
|
|
ReportPath
|
This is the path on the local machine where the crystal report resides. It must be a path that all Seradex Users have access to or the report will NOT work for them.
Example – c:\program files\activeerp\templates\myreport.rpt
Note: the report file name is case sensitive!
|
C:\Program Files\ActiveERP\Templates\myreport.rpt
|
Show
|
True to have the report show up in the Reports drop down menu of the module indicated in the ModuleID field
|
v
|
SDDCopiesField
|
Field number in main SDD Query to base number of copies on
|
|
QueryName
|
Not Used
|
|
PrinterName
|
Not Used
|
|
PrinterDriver
|
Not Used
|
|
PrinterPort
|
Not Used
|
|
Display
|
Whether or not a report is visible on the reports switchboard (Reports section of the OrderStream profile bar)
|
v
|
ReportCaption
|
Alternate Title for the report. If this is filled in this is what the report title on the print out will be. If blank then the field Report Title will be used. If you have a formula in your report called sxReportTitle that formula will contain this or the report title at RunTime.
|
|
IDSection
|
The section within the report the ID field is located. See Adding Reprint Functionality to Custom Reports for setup instructions.
|
|
ReprintFlagField
|
The field within the transaction database table to save the print status of a transaction when a dedicated Reprint field is not available. For example, there is no Reprint field in the WorkOrder table, thus a different field must be used to save the print status for custom work order reports. See Adding Reprint Functionality to Custom Reports for setup instructions.
|
|
Reports Dialog Setup
This table sets up the form that allows users to filter records for reports. A sample report dialog form is shown below. In this the user can filter by customer, Sales Order, Territory, Item or Order Date.
This form is driven by data in the ReportsDialogSetup table as shown below:
ReportsDialogSetup Table Field Reference
|
|
|
|
ReportID
|
Report ID that the dialog is for
|
1
|
ControlOrder
|
Order the selections appear on the dialog form
|
0
|
MainQueryField
|
This is the query field in the Report Main query that the dialog selection is assigned to, it MUST be a field in the query
|
SalesOrder.SalesOrderNo
|
Width
|
Combo/Text width (use 2500 for Combos and 0 for dates.
|
2500
|
Label
|
Label of the selection (ie Item Number)
|
Sales Order #
|
IsRange
|
Is this a from/to option (true of false)
|
True
|
DisplayType
|
Type of option (1 = Combo, 2= Date)
|
1
|
BoundColumn
|
Which column of the dialog is used in the report? For instance if the Combo is SalesOrderNO and Customer Name and you want to use Customer Name then use 1, 0 is the default
|
1
|
DisplayColumn
|
Not Used
|
|
ColumnHeaders
|
Whether or not to use column headers
|
-1
|
ColumnCount
|
Number of Columns in the dialog, 0 assumes default
|
0
|
ColumnWidths
|
Not Used
|
|
IsNumeric
|
Whether or not the dialog links to a numeric value
|
0
|
DefaultDateFrame
|
Not Used
|
|
DateDefault
|
Date Default:
|
|
DateFromDate
|
Date From Date:
|
|
DateToDate
|
Date To Date:
|
|
SddField
|
Field to link back to the report from the SDD
|
|
Query-Data
|
SQL Statement to Fill the combo (ie SELECT SalesOrder.SalesOrderNO FROM SalesOrder)
|
SELECT SalesOrder.SalesOrderNo FROM SalesOrder ORDER BY SalesOrder.SalesOrderNo ASC
|
RowSourceType
|
Not Used
|
|
Adding Sub Reports
Crystal Report Sub-Reports are supported as custom reports. It is not the intent of this document to explain how to create sub reports within the Crystal Reports application. Rather, we will explain how to integrate these reports into OrderStream.
The sub report table is found within your Reports Access database (.mdb).
ReportsSubReports Table Field Reference
|
|
|
|
ReportsSubReportID
|
Autoumber - do not type anything here
|
16
|
ReportID
|
The report ID to link back to.
Since this is a sub report, it is part of a main report. You must specify the main report to link back to. This can be found by opening the Reports table and looking at the reportsid column.
|
37
|
Name
|
Name of the crystal sub report.
NOTE: The sub report must be in the same folder as the main report.
NOTE: The name is case sensitive!
|
crTaxSubReport
|
SortOrder
|
Enter 0 if there is only one sub report in the main report. If there are additional sub reports, you must increment the value by 1 (0,1,2,3,4…)
|
0
|
SubQuery
|
Enter the query used to develop the sub report.
SQL – open the Management Studio/Enterprise Manager, locate the view your crystal report was designed to, right click and choose modify. From there, obtain the SQL statement, copy and paste it into this field.
Access – open your ActiveM database and switch over to the queries panel. Locate the query your report was designed to. Right click on the query and choose design view. From there, obtain the SQL statement, copy and paste it into this field.
|
SELECT EstimateDetails.RuleDesc, EstimateDetails.EstimateID, EstimateDetails.EstimateDetailID FROM Customers RIGHT JOIN (Estimate INNER JOIN EstimateDetails ON Estimate.EstimateID = EstimateDetails.EstimateID) ON Customers.CustomerID = Estimate.CustomerID WHERE (((Estimate.TotalTaxes)<>0)) ORDER BY EstimateDetails.EstimateID
|
SubReportQueryFieldSDD
|
Only used if the SDD option was chosen.
|
|
DeveloperComment
|
Used to hold a comment about the sub-report.
|
|
Adding Reprint Indicator to Custom Reports
It is possible to display "Reprint" on your custom reports when the report is printed more that once for a specific transaction. Follow the steps listed below to incorporate this functionality.
1) Check the Report Query
The transaction ID must be within the main report query. For example, if you are working on a work order report, WorkOrder.WorkOrderID must be within the main report query.
Also, the Reprint field of the transaction's header table must be within the main report query if it is available. For example if you are working on a sales order report, SalesOrder.Reprint must be within the main report query. The reprint field is not available in all transaction header tables, including WorkOrder and Estimate. In this case an unused field within the transaction header table must be designated as the field in which the print status of the transaction is stored. This is explained in detail later in this topic.
If the above fields are not currently within the main report query, you must add the field(s) to the report view on your SQL Server then verify the the report against the view to add the field to the report.
2) Setup the Report
Open your report in the report designer and add the transaction ID field to a section of the report. Right click on the field and select Format Field. The field can be suppressed to prevent it from being displayed. Enter ID into the Object Name field as shown in the following image.
You must obtain the section number of the report section you placed the transaction ID field into. Do this by right clicking on the group list and selecting Section Expert.
 Transaction ID field indicated by red arrow. Right click anywhere in grey area listing report sections to open the Section Expert
In the sample image above note that the transaction ID field has been added to Group Header #3. Using the Section Expert, determine the section number by counting the sections from top to bottom.
The Section Expert shows all of the sections within the report. You will see that the transaction ID field is located in section 8 for the sample report.
The final step in the report setup is to create a formula to display "Reprint" when the value in the Reprint field (or the field designated as the reprint field when it is not available) is equal to 1. A sample formula is as below:
 Sample Crystal formula to determine when the Reprint text is shown
Add the formula to the appropriate location on your report to complete the report setup.
3) Setup the Reports Database
Open your reports Microsoft Access database then open the Reports table. Locate the record for the report you are working on then scroll to the right until you see the field named "IDSection". Enter the section number corresponding to the section you added the transaction ID field to, so for the sample report we have been working with we will enter 8 into the IDSection field.
If the IDSection field does not exist in the Reports table of your reports database you may add it by switching to table Design View. Use the setup shown below.
If your report is reporting on a module which contains a field named "Reprint" in its transaction header table, you are finished. The transaction header tables which contain the Reprint field include:
1) SalesOrder
2) Invoice
3) PO
However, if your report is reporting on a module which does not contain the Reprint field in its transaction header table, such as Estimate or Work Order, you must specify the field in which you wish to store the print status of the transaction. Use a text field that is not being utilized during your order processing within the relevant module; for example, if in work order order you are not making use of the Revision field you can use WorkOrder.Revision. In Estimate, if you are not using the Tech Buyer field (located on the Sales Tracker tab) you could use Estimate.TechBuyer.
Since the sample report we have been working on is a work order report, we will use WorkOrder.Revision. In the Reports table of your reports database, locate the record of the report you are working on until you see the field named "ReprintFlagField". Enter the name of the field (minus the table name) into the ReprintFlagField field. Thus, for the sample report Revision must be entered into the ReprintFlagField field.
If the ReprintFlagField field is not present in your reports database you can add it using the following field setup:
Make sure the length of the text field is sufficient to store the enter name of any field designated as the reprint field.
Note:
The Reprint field (or its designated field) for a transaction is not updated until the report has actually been sent to the printer. The field is not updated when the report is previewed.
|
