Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » How to iterate over a dataset in script?
How to iterate over a dataset in script? [message #207749] Wed, 20 December 2006 13:30 Go to next message
Eclipse User
Originally posted by: clf.clf.com

I would like to iterate over my dataset to generate some statistics *before*
BIRT uses the dataset to render the report, so that I can use results from
those statistics as a computed column of the same dataset. An example of
this usage would be for displaying a linear regression series in a line
chart.
It seems like the place to do this would be in the afterOpen event of the
dataset, but I don't see any properties or methods in the dataset object
that allows me to do this.
The only option I see now is to use a scripted dataset. Any ideas?

I'm using BIRT 2.2.

Thanks
Re: How to iterate over a dataset in script? [message #208487 is a reply to message #207749] Fri, 29 December 2006 11:07 Go to previous messageGo to next message
Eclipse User
Originally posted by: eclipse.junga.com

You can not directly iterate over the dataset. Birt has hooks that you can
use to tap into its iteration(s). It makes multiple passes (iterations) as
needed to calculate the aggregate functions (that depend on the whole
dataset) and then the specific calculated columns. So the expression for a
calculated column can refer to the values of aggregate functions and birt
works out the dependencies.

You can create a calculated column in the dataset to contain the results of
your statistical analysis for each row. Invoke the expression builder for
the new calculated column and then look at "Birt Functions" -> sub catagory
"Total". There are various prebuilt statistical functions -- weighted
average, moving average, etc.... The provided Birt aggregation functions may
be enough to write your equation, or you may have to write your own
aggregation function(s) (there is an example on the birt web site for
writing your own aggregation function).

For a linear regression, I would think you would need an Aggregate function
that calculates the factors of the equation. Then in the calculated column
you could use the values from those Aggregate functions in an equation to
produce the value at that row. For example, one aggregate function would be
"slope" and a second would be "offset". Then the expression in the
calculated column would be "MySlope(row['x'],row['y']) * row['x'] +
MyOffset(row['x'],row['y'])". The values of MySlope and MyOffset are
calculated in an earlier pass. They need both the x and y values. Then is
the last pass, birt uses the values calculated for these aggregate functions
to plug into the expression and end up with the specific value for each row.

There may be existing functions to let you do the analysis you want (I would
think that linear regression would e common), but if not this is the Birt
way to "iterate" the dataset to do some arbitrary analysis. (well, I far as
my understanding is).

--BobG

"clf" <clf@clf.com> wrote in message news:embves$abi$1@utils.eclipse.org...
>I would like to iterate over my dataset to generate some statistics
>*before*
> BIRT uses the dataset to render the report, so that I can use results from
> those statistics as a computed column of the same dataset. An example of
> this usage would be for displaying a linear regression series in a line
> chart.
> It seems like the place to do this would be in the afterOpen event of the
> dataset, but I don't see any properties or methods in the dataset object
> that allows me to do this.
> The only option I see now is to use a scripted dataset. Any ideas?
>
> I'm using BIRT 2.2.
>
> Thanks
>
>
>
Re: How to iterate over a dataset in script? [message #208758 is a reply to message #208487] Tue, 02 January 2007 18:22 Go to previous messageGo to next message
Eclipse User
Originally posted by: clf.clf.com

Thanks Bob. Yeah, I agree it seems like this would be something common and
built-in. But so far you're the only person that has responded after all
this time. So it appears BIRT isn't too interested in this sort of thing :(
My solution for now is to use a scripted dataset. So I just do the calcs in
Java. I wish I didn't have to jump through this hoop, but I guess it's a
workaround for now.
You gave me a good idea on writing my own aggregate function for use in
BIRT, so I'll look for that example. Thanks again.

"Bobg" <eclipse@junga.com> wrote in message
news:en3eft$ja2$1@utils.eclipse.org...
> You can not directly iterate over the dataset. Birt has hooks that you can
> use to tap into its iteration(s). It makes multiple passes (iterations) as
> needed to calculate the aggregate functions (that depend on the whole
> dataset) and then the specific calculated columns. So the expression for a
> calculated column can refer to the values of aggregate functions and birt
> works out the dependencies.
>
> You can create a calculated column in the dataset to contain the results
of
> your statistical analysis for each row. Invoke the expression builder for
> the new calculated column and then look at "Birt Functions" -> sub
catagory
> "Total". There are various prebuilt statistical functions -- weighted
> average, moving average, etc.... The provided Birt aggregation functions
may
> be enough to write your equation, or you may have to write your own
> aggregation function(s) (there is an example on the birt web site for
> writing your own aggregation function).
>
> For a linear regression, I would think you would need an Aggregate
function
> that calculates the factors of the equation. Then in the calculated column
> you could use the values from those Aggregate functions in an equation to
> produce the value at that row. For example, one aggregate function would
be
> "slope" and a second would be "offset". Then the expression in the
> calculated column would be "MySlope(row['x'],row['y']) * row['x'] +
> MyOffset(row['x'],row['y'])". The values of MySlope and MyOffset are
> calculated in an earlier pass. They need both the x and y values. Then is
> the last pass, birt uses the values calculated for these aggregate
functions
> to plug into the expression and end up with the specific value for each
row.
>
> There may be existing functions to let you do the analysis you want (I
would
> think that linear regression would e common), but if not this is the Birt
> way to "iterate" the dataset to do some arbitrary analysis. (well, I far
as
> my understanding is).
>
> --BobG
Re: How to iterate over a dataset in script? [message #208821 is a reply to message #208758] Wed, 03 January 2007 00:41 Go to previous messageGo to next message
Eclipse User
Originally posted by: eclipse.junga.com

I think Birt is still kind of young so some things that will be of common
value just hav'nt been written yet.

I am curious about your experience with scripted data surces. With a
scripted data source, is all the work done out side of birt and then you
return the finished result set to birt? You have to replace the
functionality of the birt SQL datasets? Do you use a java class to access a
SQL db using JDBC and then iterate and calculate your vlaues?

I wanted to manipulate a result set to transpose some rows into columns --
i.e. the db table would have columns for person, month, and value, but I
wanted the report table to display one row per person with each month's
value in a column. I am not sure if a scripted data source would be
appropriate to do this. What I would like it to be is to be able to define a
new scripted dataset that takes a regular dataset as an input. It would do
some transformation on the dataset and produce a new dataset. But I don't
think that's what it is.

--BobG




"clf" <clf@clf.com> wrote in message news:enepf0$2mo$1@utils.eclipse.org...
> Thanks Bob. Yeah, I agree it seems like this would be something common
> and
> built-in. But so far you're the only person that has responded after all
> this time. So it appears BIRT isn't too interested in this sort of thing
> :(
> My solution for now is to use a scripted dataset. So I just do the calcs
> in
> Java. I wish I didn't have to jump through this hoop, but I guess it's a
> workaround for now.
> You gave me a good idea on writing my own aggregate function for use in
> BIRT, so I'll look for that example. Thanks again.
>
> "Bobg" <eclipse@junga.com> wrote in message
> news:en3eft$ja2$1@utils.eclipse.org...
>> You can not directly iterate over the dataset. Birt has hooks that you
>> can
>> use to tap into its iteration(s). It makes multiple passes (iterations)
>> as
>> needed to calculate the aggregate functions (that depend on the whole
>> dataset) and then the specific calculated columns. So the expression for
>> a
>> calculated column can refer to the values of aggregate functions and birt
>> works out the dependencies.
>>
>> You can create a calculated column in the dataset to contain the results
> of
>> your statistical analysis for each row. Invoke the expression builder for
>> the new calculated column and then look at "Birt Functions" -> sub
> catagory
>> "Total". There are various prebuilt statistical functions -- weighted
>> average, moving average, etc.... The provided Birt aggregation functions
> may
>> be enough to write your equation, or you may have to write your own
>> aggregation function(s) (there is an example on the birt web site for
>> writing your own aggregation function).
>>
>> For a linear regression, I would think you would need an Aggregate
> function
>> that calculates the factors of the equation. Then in the calculated
>> column
>> you could use the values from those Aggregate functions in an equation to
>> produce the value at that row. For example, one aggregate function would
> be
>> "slope" and a second would be "offset". Then the expression in the
>> calculated column would be "MySlope(row['x'],row['y']) * row['x'] +
>> MyOffset(row['x'],row['y'])". The values of MySlope and MyOffset are
>> calculated in an earlier pass. They need both the x and y values. Then is
>> the last pass, birt uses the values calculated for these aggregate
> functions
>> to plug into the expression and end up with the specific value for each
> row.
>>
>> There may be existing functions to let you do the analysis you want (I
> would
>> think that linear regression would e common), but if not this is the Birt
>> way to "iterate" the dataset to do some arbitrary analysis. (well, I far
> as
>> my understanding is).
>>
>> --BobG
>
>
Re: How to iterate over a dataset in script? [message #208851 is a reply to message #208821] Wed, 03 January 2007 01:37 Go to previous messageGo to next message
Eclipse User
Originally posted by: clf.clf.com

> With a
> scripted data source, is all the work done out side of birt and then you
> return the finished result set to birt?

As far as building the initial dataset is concerned, yes. Basically, what
it allows you to do, is use any datasource you wish, prepare it all in your
java code, then map the data from your java objects to BIRT datasets.

> You have to replace the
> functionality of the birt SQL datasets?

I wouldn't say that you completely replace it. It's more about where the
data comes from that you're replacing. So, once you've mapped your BIRT
dataset to properties/fields from your java objects, the BIRT dataset
behaves exactly as it would've had you done it the 'regular' way (i.e.
creating a JDBC/SQL dataset within the BIRT UI).

> Do you use a java class to access a
> SQL db using JDBC and then iterate and calculate your vlaues?

Yes. Technically, in my case, I'm using Hibernate/HQL... but yeah, it would
be the same procedure as using straight JDBC/SQL. Once I've retrieved my
data, I then iterate over the results to generate the statistics, then I
assign the property values in my POJO that I want to fields in a BIRT
dataset.

>
> I wanted to manipulate a result set to transpose some rows into columns --
> i.e. the db table would have columns for person, month, and value, but I
> wanted the report table to display one row per person with each month's
> value in a column. I am not sure if a scripted data source would be
> appropriate to do this.

The rows-into-columns thing, this makes sense, but can be difficult. First,
in case you don't already know, this dynamic crosstab functionality is
supposed to be available in the upcoming BIRT 2.2 (the extent of its
functionality seems to still be a mystery though). If you use SQL Server
2005, you can do pivot rows/columns on the fly in your SQL query and BIRT
doesn't even have to know about it (I assume Oracle can do the same?). That
helps in many cases, but doesn't help the fact that BIRT doesn't do dynamic
columns out-of-the-box.
That said, there are still a couple of options. If your db can't do the
pivot for you, then yeah, you could implement something in Java to do that,
then use a scripted dataset. If you also need dynamic columns, then you
could also use the BIRT Design Engine API via Java to manipulate the report
design on-the-fly to give you the columns you need at runtime.
There is a good example here...
http://wiki.eclipse.org/index.php/Dynamic_Table

> What I would like it to be is to be able to define a
> new scripted dataset that takes a regular dataset as an input. It would do
> some transformation on the dataset and produce a new dataset. But I don't
> think that's what it is.

If I understand you right, I would say that I've had a difficult time doing
anything out of the ordinary with a 'standard' BIRT dataset. As I mentioned
in my initial post, I couldn't even figure out how to iterate over the thing
using script or Java. It seems that once BIRT has got ahold of it, you're
pretty much shut out other than using built-in aggregate functions, etc.
Here's the thing... if you're just doing very standard SQL reporting or not
looking for tight integration with an app, then you can just stick to what's
available to you through the UI and BIRT script. But I've been much happier
with it once I decided to bite the bullet and spend some time setting up the
whole environment so that I could control the BIRT engine entirely from
code, render my own reports using the API, tap into the event adapters,
etc... Of course, that may be overkill for some people, but it all depends
on what your long term goals are.
Anyway, there are several ways you can tackle this. So far, I'm pretty
happy with my use of ScriptedDataSetEventAdapter for scripted datasets, but
you can also map your POJO properties to BIRT dataset fields directly in
BIRT's javascript... etc etc.

I've probably raised a lot more questions for you than answers as I was kind
of vague. But if we can narrow your requirements down, I might be able to
help you out with some code samples or more specific answers.
Re: How to iterate over a dataset in script? [message #209667 is a reply to message #207749] Sun, 07 January 2007 19:28 Go to previous messageGo to next message
Graeme Collis is currently offline Graeme Collis
Messages: 16
Registered: July 2009
Junior Member
We have built sales reports with crosstabs that needed to work out
percentages of sales. This required that we pass the data once to get the
total sales in the groups and then pass the data a second time using the
totals sales for a group to calculate the percentage.

Our strategy for this was to put two tables on the report using the same
dataset

Table 1.

The first table's detail row was set to invisible.

An expression was set for the fields to take the appropriate numeric value
and add it to an associative array (javascript) using the group field values
to build the key.

Table 2.

Table 2 then is normal and uses all the appropriate row fields and where
required uses the assaociative array and row field in an expression to
calculate the percentage of sales.

cheers, Graeme Collis

PS. We do this from an ODA datasource/resultset not a scripted datasource so
we still get all the features like display names vs field names, computed
columns, parameters, filters, etc

"clf" <clf@clf.com> wrote in message news:embves$abi$1@utils.eclipse.org...
>I would like to iterate over my dataset to generate some statistics
>*before*
> BIRT uses the dataset to render the report, so that I can use results from
> those statistics as a computed column of the same dataset. An example of
> this usage would be for displaying a linear regression series in a line
> chart.
> It seems like the place to do this would be in the afterOpen event of the
> dataset, but I don't see any properties or methods in the dataset object
> that allows me to do this.
> The only option I see now is to use a scripted dataset. Any ideas?
>
> I'm using BIRT 2.2.
>
> Thanks
>
>
>
Re: How to iterate over a dataset in script? [message #209675 is a reply to message #209667] Sun, 07 January 2007 20:09 Go to previous messageGo to next message
Eclipse User
Originally posted by: clf.clf.com

> PS. We do this from an ODA datasource/resultset not a scripted datasource
so
> we still get all the features like display names vs field names, computed
> columns, parameters, filters, etc

I never knew a scripted data source had these limitations! I looked at ODA
a long time ago, and it seemed like writing your own ODA datasource was a
huge effort (and maybe over my head). Was it difficult? Is there any
online resource you can point me to that was useful for you?

Thanks Graeme
Re: How to iterate over a dataset in script? [message #209682 is a reply to message #209675] Sun, 07 January 2007 20:15 Go to previous messageGo to next message
Graeme Collis is currently offline Graeme Collis
Messages: 16
Registered: July 2009
Junior Member
If the data you are trying to connect to already has :-
1. A "resultset" or "iterator" like interface
2. If it already has metdadata about columns(Name, data type)
3. If it can accept some sort of query (any text, not necessarily SQL, ours
isn't)
then writing the ODA layers is more just mapping(adapting) than anything
else.

cheers, Graeme

"clf" <clf@clf.com> wrote in message news:ens5hj$uqt$1@utils.eclipse.org...
>
>
>> PS. We do this from an ODA datasource/resultset not a scripted datasource
> so
>> we still get all the features like display names vs field names, computed
>> columns, parameters, filters, etc
>
> I never knew a scripted data source had these limitations! I looked at
> ODA
> a long time ago, and it seemed like writing your own ODA datasource was a
> huge effort (and maybe over my head). Was it difficult? Is there any
> online resource you can point me to that was useful for you?
>
> Thanks Graeme
>
>
Re: How to iterate over a dataset in script? [message #209690 is a reply to message #209682] Sun, 07 January 2007 20:56 Go to previous messageGo to next message
Eclipse User
Originally posted by: clf.clf.com

Thanks again. I'll look into it.

"Graeme Collis" <gcollis@iinet.net.au> wrote in message
news:ens678$daf$1@utils.eclipse.org...
> If the data you are trying to connect to already has :-
> 1. A "resultset" or "iterator" like interface
> 2. If it already has metdadata about columns(Name, data type)
> 3. If it can accept some sort of query (any text, not necessarily SQL,
ours
> isn't)
> then writing the ODA layers is more just mapping(adapting) than anything
> else.
Re: How to iterate over a dataset in script? [message #209698 is a reply to message #209690] Sun, 07 January 2007 21:36 Go to previous messageGo to next message
Graeme Collis is currently offline Graeme Collis
Messages: 16
Registered: July 2009
Junior Member
I forgot to mention Jason Weathersby book:-
"Integrating and Extending BIRT"
Chapter 19: Developing an ODA extension has two examples.

This book is a technical/programmer level, not an end-user level. It is
worth a look and considering Jason puts so much effort into BIRT and the
newsgroups it is worth purchasing.

cheers, Graeme

"clf" <clf@clf.com> wrote in message news:ens8a4$atu$1@utils.eclipse.org...
> Thanks again. I'll look into it.
>
> "Graeme Collis" <gcollis@iinet.net.au> wrote in message
> news:ens678$daf$1@utils.eclipse.org...
>> If the data you are trying to connect to already has :-
>> 1. A "resultset" or "iterator" like interface
>> 2. If it already has metdadata about columns(Name, data type)
>> 3. If it can accept some sort of query (any text, not necessarily SQL,
> ours
>> isn't)
>> then writing the ODA layers is more just mapping(adapting) than anything
>> else.
>
>
Re: How to iterate over a dataset in script? [message #209705 is a reply to message #209698] Sun, 07 January 2007 22:29 Go to previous messageGo to next message
Eclipse User
Originally posted by: clf.clf.com

Thanks for the tip. Definitely sounds like something I could use. Curious
though, those limitations on a scripted dataset you mentioned ("display
names vs field names, computed columns, parameters, filters, etc")... they
all seem to be available for scripted datasets in the UI, except computed
columns. Am I missing something?

"Graeme Collis" <gcollis@iinet.net.au> wrote in message
news:ensb01$7e7$1@utils.eclipse.org...
> I forgot to mention Jason Weathersby book:-
> "Integrating and Extending BIRT"
> Chapter 19: Developing an ODA extension has two examples.
>
> This book is a technical/programmer level, not an end-user level. It is
> worth a look and considering Jason puts so much effort into BIRT and the
> newsgroups it is worth purchasing.
Re: How to iterate over a dataset in script? [message #213276 is a reply to message #209705] Tue, 23 January 2007 08:35 Go to previous message
Eclipse User
Originally posted by: my_chimera.sms.at

About computed columns in Scripted Data Sources:

Instead of using computed columns you can calculate/map the values in the
fetch() method of the Scripted Data Set. Works, and is as easy as using a
computed column.
Previous Topic:PDF / embedded Truetype fonts on Solaris
Next Topic:Drop Group Header to Details - Will Not Line Up With Details
Goto Forum:
  


Current Time: Tue Sep 02 00:24:28 EDT 2014

Powered by FUDForum. Page generated in 0.01960 seconds