Eclipse Community Forums: BIRT » computed field based on other dataset

Home » Archived » BIRT » computed field based on other dataset

computed field based on other dataset [message #114498]

Tue, 24 January 2006 12:57

Eclipse User

Originally posted by: vladperl.semanticprogrammer.org

I need computed field that use value from other dataset based on some value
from orher field in same dataset.
Example:
I have field "InsuranceTypeID" and I need name of that insurance type.
For some reasons I don't want put "InsuranceType" in same data query where
is locating "InsuranceTypeID" field.
But I can provide other dataset with "InsuranceType" field.
I need to know how to make computed field "InsuranceType" in first dataset
with values taken from second dataset.

Thank you,
Vladimir

Re: computed field based on other dataset [message #114756 is a reply to message #114498]

Tue, 24 January 2006 16:01

Eclipse User

A computed column cannot refer to fields another data set. So you can't
solve your problem this way with BIRT.

If you can write Java or Javascript code to read the TypeID->TypeName
mapping in memory, you can call up Javascript code to do the mapping in your
computed column expression. This is usually the most efficient solution if
the mapping table is small.

The alternative is to use nested queries. This is done by creating an
typeID->typeName look up data set (I'll name this data set TypeNameLookup)
that takes a data set parameter. The SQL query for that data set will look
something like this:
SELECT InsuranceType
FROM insurance
WHERE InsuranceTypeID = ?

Wherever in a table that you need to display an insurance type name, place a
Data element in the cell, and do the following two things:
(1) Edit the Data Binding of that data element so it is bound to the
TypeNameLookup data set (note that as a result, the Data cell will be bound
to a different data set than the table that contains it )
(2) Also in the Data Binding tab, edit the input parameter binding for
TypeNameLookup so that its lone parameter is bound to expression:
rows[0]["InsuranceTypeID"]. The column name InsuranceTypeID should be the
name of the column in the outer table's data set which contains the id.
Then set the data element expression to row["InsuranceType"] - you are done!

I have enclosed an sample report design that does something similar with the
Sample DB data set. The table displays a list of employees and the names of
their respective offices. This is normally done via joining the OFFICES and
the EMPLOYEES tables. But I have achieved this with two different data sets
without the join. A nested query is used instead.

--
Gary Xue
Actuate Corporation - Product Development
BIRT Committer

"Vladimir" <vladperl@semanticprogrammer.org> wrote in message
news:dr5pdu$o1f$1@utils.eclipse.org...
> I need computed field that use value from other dataset based on some
value
> from orher field in same dataset.
> Example:
> I have field "InsuranceTypeID" and I need name of that insurance type.
> For some reasons I don't want put "InsuranceType" in same data query where
> is locating "InsuranceTypeID" field.
> But I can provide other dataset with "InsuranceType" field.
> I need to know how to make computed field "InsuranceType" in first dataset
> with values taken from second dataset.
>
> Thank you,
> Vladimir
>
>

Attachment: sample.rptdesign
(Size: 4.83KB, Downloaded 120 times)

Re: computed field based on other dataset [message #114854 is a reply to message #114756]

Tue, 24 January 2006 17:07

Eclipse User

Originally posted by: vladperl.semanticprogrammer.org

"Gary Xue" <gxue@actuate.com> wrote in message
news:dr64gu$d3g$1@utils.eclipse.org...
>A computed column cannot refer to fields another data set. So you can't
> solve your problem this way with BIRT.
>
> If you can write Java or Javascript code to read the TypeID->TypeName
> mapping in memory, you can call up Javascript code to do the mapping in
> your
> computed column expression. This is usually the most efficient solution if
> the mapping table is small.
>
> The alternative is to use nested queries. This is done by creating an
Thank you very much Gary! Your explanation is clear and very useful for me.
That will be good for people to provide one more tutorial about using nested
queries on the birt website.
It's wonderful to have this kind of support for software product.
I will try to help from my side to make birt be even more better product.

Previous Topic:	No PDF Generation (URGENT)
Next Topic:	AttributeView not linked to editor

Goto Forum:

-=] Back to Top [=-

Current Time: Sun Jun 15 19:18:41 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter