firstDataSet has a simple query which returns a data that should be passed in the where clause of the secondDataSet. There are 2 tables in the layout (tied to each dataset), independent of each other. Below are the steps that I did,
First Data Set
- Declared a variable – variable1 with default value as 0
- SQL query defined in Open method.
- columns defined in Fetch method.
- onFetch method of the first dataset declared as – vars[“variable1”] = row[“column1”]
Second Data Set
- Defined the following line in beforeOpen – sqlText = this.sqlText.replace (“newclause”, Formatter.format(vars[“variable1″],”#” ));
- SQL query defined in Open method – select column2 from table1 where newclause
- Fetch method defined.
The SQL queries defined in both the data set is getting executed however the replace defined in the beforeOpen does not work. Please note that I cannot merge the dataset into 1 table in the layout ( this works but this does not inline with the requirement)
I guess that what you mean is a master-detail report, also called a sub-report in some tools.
For example, take the two tables PRODUCTLINES and PRODUCTS from the sample DB.
You want to show a kind of “product catalog”, grouped by product lines.
For each product line, you want to show the description and image for the productline, then list the products for this productline, and so on.
There are at least 3 different ways to achieve this, but one way which corresponds to what you describe is as follows.
You need two datasets, one for the PRODUCTLINES and one for PRODUCTS.
Name them accordingly.
The second one could look like this:
select * from PRODUCTS where PRODUCTLINE = ?
Note the question mark. This is called a bind variable.
Add a dataset parameter (input, type String) to this query which serves the purpose of supplying a concrete value for the bind variable when the query is executed. Give it a useful name, e.g. param_PRODUCTLINE.
In case of more than one bind variable: You need exactly as much data set parameters as there are question marks in the SQL.
In your layout, use a LIST item and bind it to the PRODUCTLINES.
Drag the PRODUCTLINE and the DESCRIPTION columns into the detail area of the list.
Next, drag the dataset “products” into the detail area.
This will create a TABLE item.
Name it accordingly, then open the “Binding” tab in the properties of the table. Click the button “Parameter Bindings”.
Here, edit the binding for the dataset parameter param_PRODUCTLINE and in the expression editor, enter row["PRODUCTLINE"] (you can also double-click it in the list of availale column bindings).
This way, for each PRODUCTLINE record, the value of its PRODUCTLINE column will be assigned to the bind variable param_PRODUCTLINE.
You should always prefer bind variables over manipulating SQL query text at run time, for security reasons (to avoid SQL injection!) and for performance reasons.
1