How to merge parameters in Crystal Reports?

[nikhil_sabnis2]

Many of us are using more than one data source in Crystal Reports. And many of the data sources require same parameter values but have different names for the parameters. Have you ever needed to merge these parameters?

Well, this document explains how you can merge the parameters of 2 or more data sources to make it as a single set and cascade the selected values to the individual data source.

1. Let's consider that we have a list of companies maintained in SQL Server database and their turnover in Oracle database.

2. A procedure is written in both SQL Server and in Oracle, which will provide the list of companies (SQL Server) and the their turnover (Oracle).

3. Let us consider we have a SQL Server procedure and an Oracle procedure which takes in the below parameters (Both are of string data type):

SQL Server:

@Cust_ID

Oracle:

Customer_ID

4. Add SQL Server procedure first to the report.

5. Go to "Field Explorer" and under "Parameters", rename the "@Cust_ID" parameter to "Customer_ID" (Parameter Name identical to the parameter name in Oracle procedure).

6. Now add the Oracle procedure to the report. Once you are finished add the oracle procedure you will find that there is only "Customer_ID" parameter under "Parameters".

7. If you refresh the report, the "Customer_ID" parameter will be prompted only once and the value entered for this parameter will be cascaded to SQL Server and to Oracle procedure as well.

When we use this method to merge the parameters, we should decide which data source should be added first to the report. In this example I have chosen SQL Server because in SQL Server procedures that parameter names always start with "@" and in oracle the parameter name can not start with "@".

Hope this information helps you!