Crystal report with oracle query in sage X3

In Sage X3, there are various modules, each equipped with standard reports for almost every screen, and these reports are developed using Crystal Reports. Additionally, we have the capability to create customized reports to meet specific client needs, especially in the context of Indian legislation. For these specialized reports, we utilize SQL as the data source.
Recently, we encountered a situation where a client was using an Oracle database. To ensure the seamless generation of Indian legislation reports for this client, we developed Crystal Reports that are based on the Oracle database server. Crystal Reports, being a popular Windows-based report writer solution, allowed us to create reports with ease by connecting to various data sources, including Oracle databases, with minimal coding requirements. Here are the general steps we followed to create Crystal Reports with Oracle queries in Sage X3:

Step1: Create an Oracle Query:

• To begin, create a custom Oracle query that retrieves the necessary data for your report.
• Utilize parameters to filter and customize the data displayed in the report.
• It’s important to note that the syntax for passing parameters in an Oracle query differs from that in SQL.
• When working with parameters in Oracle queries, use curly braces with a question mark, as shown below:
• {?FOLDER}
• If you have multiple parameters to include in your WHERE condition, you can specify them in your query as follows: {?StartDate}’ and ‘ {?EndDate}’

New stuff:- Error ‘@X3.TRTGLOBIENS$adx(1427) Error 56 Date Error Incorrect’ prompted while opening FA screen in Sage X3

Figure1 – Sample Query

Step 2: Adding the Query and Mapping Parameters in Crystal Reports:

1. Adding the Query:
   -Start by adding the previously created Oracle query to your Crystal Report.
   -Ensure to mention the names of parameters used in the query in the parameter list, as shown in Figure 2 for reference.
2. Parameter Values:
   -Crystal Reports will prompt you for parameter values before executing the report.
   -You can enter the desired values for these parameters or link them to other fields or formulas within Crystal Reports.
3. Adding Parameters to the Report:
   -Following this, you should add the parameters to the Crystal Report in the parameter fields under the field explorer, as illustrated in Figures 3A and 3B.

Figure2 – Parameter List

Figure 3A – New parameter addition

Figure 3B – Parameters

This is how we add the oracle query in crystal report.