sql - How to create BIRT report based on multiple data sets

Question

I need help in creating a BIRT report; the situation is that I have multiple queries but the report all columns should be grouped by One column for example (Operator) Like :

Operator | Expr1 | Expr2 | Expr3 | Expr4 |

op1 | ## | ## | ## | ## |

op2 | ## | ## | ## | ## |

op3 | ## | ## | ## | ## |

---

How can i accomplish that ? I 'm trying to make the Operator separate data set and put it in the first column and add a parameter in all quires i.e (where (operator = ?)) to get the Operator from the current row of operators column..But I can not attach the parameter value in each dataset to a the Operator dataset data set. Is this strategy right ? If yes .. how can I do that. If no, what is the correct strategy? Million thanks in advance,

Answer 1

If your multiple datasets are coming from the same datasource, I recommend combining the queries.

If the multiple datasets are coming from different datasources, you can link them together using the Joint Data Set functionality in BIRT.

To link two separate datasets together:

1. Right-click on Data Sets in the Data Explorer pane within the BIRT Report Designer, and select New Joint Data Set.
2. On the New Joint Data Set dialog, select the data set and field (Operator, here) to be joined from on the left-hand list.
3. Select the join type (Inner, Left Outer, Right Outer, Full Outer) from the options between the two lists.
4. Select the data set and field (Operator again, here) to be joined to on the right-hand list.
5. Give the resultant new dataset a meaningful name, in the option at the bottom of the dialog.
6. Click Finish.
7. Edit the resultant dataset to produce output as required, from the Edit Dataset dialog.

You can only produce joint datasets from no more than two existing datasets. However, joint datasets can themselves be included as input datasets for other joint datasets - so if you need to join three datasets together, this can be achieved by linking two of them into one joint dataset, then creating a new joint dataset based on the first joint dataset and the third original dataset.

It is not necessary to paramterise any of the existing queries to join datasets. A parameter condition on Operator in the query should only be included if the intention is to restrict the Operators returned in that dataset (eg. to return operators 100-121 only).