[maf]

Hi there,
I have just started out with using BIRT and I must say I am quite impressed, especially with the click'n'see approach which requires only a little bit of coding.
However I ran into a problem:
I created a DataSet and it features n columns. Now I would like to add a computed column which draws data from a second DataSet. The second DataSet exists in the same report. However, BIRT does not list it under "Available DataSets" in the Expression Builder.
I am looking for a "foreign key" type thing to link the two datasets togeher. Will that make the second DataSet available to the Expression Builder when building a column's expression for the first DataSet?
Best regards
Max

[rawesso]

Hi,

I would try the following:

Let's say I have two data sets 1 and 2. I create a union for the two data sets in a third one so that all fields will be available building a computed column in the third data set. I hope this helps.

Thanks!

[Tubal]

maf, on 22 June 2012 - 05:58 AM, said:

Hi there,
I have just started out with using BIRT and I must say I am quite impressed, especially with the click'n'see approach which requires only a little bit of coding.
However I ran into a problem:
I created a DataSet and it features n columns. Now I would like to add a computed column which draws data from a second DataSet. The second DataSet exists in the same report. However, BIRT does not list it under "Available DataSets" in the Expression Builder.
I am looking for a "foreign key" type thing to link the two datasets togeher. Will that make the second DataSet available to the Expression Builder when building a column's expression for the first DataSet?
Best regards
Max

It sounds like you are wanting a Joint data Set. Create a new dataset, and select New Joint Dataset. This will combine the two datasets you have into a 3rd dataset (based on a linked field from each). Then you can create additional computed columns in your 3rd dataset.

[maf]

I am not looking for a join (at least if they work similar to the ones used in MySQL). The task I am looking to accomplish is as follows:
Table "Customers", Fields: Id, Name, Country, Comment
Table "Orders", Fields: Id, Price, Date, CustomerId

My new column shall now list the total for all purchases.
In technical terms I am looking for the following columns:
Customers.Id, Customers.Name, Customers.Country, [SUM(Price) FROM Orders WHERE CustomerId=Customers.Id]

How can I accomplish that?

[rawesso]

Hi,

If tables "Customers" and "Orders" are database tables, I believe it should be possible to create the column in the data set's query instead of creating it as a computed column.

Thanks,

[maf]

Unfortunately they are not (or do not seem to be) database tables.
We use a third party program which offers a BIRT DataSource Type for the design of its reports.
We then create such a DataSource in BIRT and use it to create new DataSets. At no time do I get the chance to enter a query. Is there still a way to reference to the second DataSet in this DataSource without using a Joint DataSet?

Best regards
Max

[rawesso]

Hi,

I created an example attached to this message. It's created in Open Source BIRT. The data set called JoinedDS is created using the OS BIRT option "New Joint Data Set" you get right-clicking on Data Set slot. In the joined Data Set, you have access to both "customers" and "orders" Data Sets' fields.

Thanks

Attached File(s)

•  example.zip.txt (13.85K)
  Number of downloads: 5