Home » Archived » BIRT » transform SQLs built in JSPs to BIRT data sets
| transform SQLs built in JSPs to BIRT data sets [message #194134] |
Wed, 04 October 2006 15:39  |
Eclipse User |
|
|
|
Originally posted by: mhanganu.tremend.ro
Hello,
I am trying to migrate some pages of an existing reporting application
built with JSPs and SQL queries executed in the page (using <sql:query>
tag).
I just convinced our client BIRT is a good solution but I found myself
faced with a major problem that I do not know how to solve:
1. I need the report to receive several parameters (no problem here)
2. Based on those parameters, the old rep. app. build a query. For example:
if(someVar == "userX") {
sql += " and table1.col1 > 3";
} else {
sql += " and table1.col1 in (select col1 from table1 where id between 2
and 4)";
}
As you can see, the SQL queries are built on the fly.
How can I transform this kind of constructions to BIRT?
Thanks,
Marius Hanganu
|
|
|
| Re: transform SQLs built in JSPs to BIRT data sets [message #194175 is a reply to message #194134] |
Wed, 04 October 2006 16:58  |
|
As long as you do not modify the returned list of columns this can be done
in scripting or property binding.
If you need to modify the returned list of columns you will need to use the
Design Engine API.
Take a look at:
http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp
Data Set Events for an example of how to modify the query based on
parameters.
Jason
"Marius Hanganu" <mhanganu@tremend.ro> wrote in message
news:b2b765a41eb2e3200efbae434393efef$1@www.eclipse.org...
> Hello,
>
> I am trying to migrate some pages of an existing reporting application
> built with JSPs and SQL queries executed in the page (using <sql:query>
> tag).
>
> I just convinced our client BIRT is a good solution but I found myself
> faced with a major problem that I do not know how to solve:
> 1. I need the report to receive several parameters (no problem here)
> 2. Based on those parameters, the old rep. app. build a query. For
> example:
> if(someVar == "userX") {
> sql += " and table1.col1 > 3";
> } else {
> sql += " and table1.col1 in (select col1 from table1 where id between 2
> and 4)";
> }
>
> As you can see, the SQL queries are built on the fly.
>
> How can I transform this kind of constructions to BIRT?
>
> Thanks,
> Marius Hanganu
>
|
|
|
| Re: transform SQLs built in JSPs to BIRT data sets [message #194182 is a reply to message #194175] |
Wed, 04 October 2006 17:11 |
| Eclipse User |
|
|
|
Originally posted by: mhanganu.tremend.ro
Thanks Jason for the response. I will analyze the resources and
hopefully it will work out for me.
I have another question though: the result sets retrieved in the JSP
pages are iterated and transform. For example, a result set looking like:
name value date
'Dan' 1 10 sep 2006
'Mark' 2 10 sep 2006
'Dan' 1 11 sep 2006
'Mark' 3 11 sep 2006
'Dan' 5 12 sep 2006
'Mark' 1 12 sep 2006
is transformed to
Name 10sep2006 11sep2006 12sep2006
Dan 1 1 5
Mark 2 3 1
Is this kind of manipulation possible with BIRT? I guess the problem is
to generate a table with a variable no of columns.
If so, perhaps you can help me with some indications.
Thanks,
Marius Hanganu
Jason Weathersby wrote:
Jason Weathersby wrote:
> As long as you do not modify the returned list of columns this can be done
> in scripting or property binding.
> If you need to modify the returned list of columns you will need to use the
> Design Engine API.
>
> Take a look at:
> http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp
> Data Set Events for an example of how to modify the query based on
> parameters.
>
> Jason
>
> "Marius Hanganu" <mhanganu@tremend.ro> wrote in message
> news:b2b765a41eb2e3200efbae434393efef$1@www.eclipse.org...
>> Hello,
>>
>> I am trying to migrate some pages of an existing reporting application
>> built with JSPs and SQL queries executed in the page (using <sql:query>
>> tag).
>>
>> I just convinced our client BIRT is a good solution but I found myself
>> faced with a major problem that I do not know how to solve:
>> 1. I need the report to receive several parameters (no problem here)
>> 2. Based on those parameters, the old rep. app. build a query. For
>> example:
>> if(someVar == "userX") {
>> sql += " and table1.col1 > 3";
>> } else {
>> sql += " and table1.col1 in (select col1 from table1 where id between 2
>> and 4)";
>> }
>>
>> As you can see, the SQL queries are built on the fly.
>>
>> How can I transform this kind of constructions to BIRT?
>>
>> Thanks,
>> Marius Hanganu
>>
>
>
> As long as you do not modify the returned list of columns this can be done
> in scripting or property binding.
> If you need to modify the returned list of columns you will need to use the
> Design Engine API.
>
> Take a look at:
> http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp
> Data Set Events for an example of how to modify the query based on
> parameters.
>
> Jason
>
> "Marius Hanganu" <mhanganu@tremend.ro> wrote in message
> news:b2b765a41eb2e3200efbae434393efef$1@www.eclipse.org...
>> Hello,
>>
>> I am trying to migrate some pages of an existing reporting application
>> built with JSPs and SQL queries executed in the page (using <sql:query>
>> tag).
>>
>> I just convinced our client BIRT is a good solution but I found myself
>> faced with a major problem that I do not know how to solve:
>> 1. I need the report to receive several parameters (no problem here)
>> 2. Based on those parameters, the old rep. app. build a query. For
>> example:
>> if(someVar == "userX") {
>> sql += " and table1.col1 > 3";
>> } else {
>> sql += " and table1.col1 in (select col1 from table1 where id between 2
>> and 4)";
>> }
>>
>> As you can see, the SQL queries are built on the fly.
>>
>> How can I transform this kind of constructions to BIRT?
>>
>> Thanks,
>> Marius Hanganu
>>
>
>
|
|
|
| Re: transform SQLs built in JSPs to BIRT data sets [message #194191 is a reply to message #194182] |
Wed, 04 October 2006 17:34 |
|
Does this have to scroll to the right?
You can do the aggregate piece of this easy just by grouping on name with a
sub group of date.
Jason
"Marius Hanganu" <mhanganu@tremend.ro> wrote in message
news:eg0pvk$u38$1@utils.eclipse.org...
> Thanks Jason for the response. I will analyze the resources and hopefully
> it will work out for me.
>
> I have another question though: the result sets retrieved in the JSP pages
> are iterated and transform. For example, a result set looking like:
> name value date
> 'Dan' 1 10 sep 2006
> 'Mark' 2 10 sep 2006
> 'Dan' 1 11 sep 2006
> 'Mark' 3 11 sep 2006
> 'Dan' 5 12 sep 2006
> 'Mark' 1 12 sep 2006
>
> is transformed to
>
> Name 10sep2006 11sep2006 12sep2006
> Dan 1 1 5
> Mark 2 3 1
>
> Is this kind of manipulation possible with BIRT? I guess the problem is to
> generate a table with a variable no of columns.
>
> If so, perhaps you can help me with some indications.
>
> Thanks,
> Marius Hanganu
>
> Jason Weathersby wrote:
>
>
> Jason Weathersby wrote:
>> As long as you do not modify the returned list of columns this can be
>> done in scripting or property binding.
>> If you need to modify the returned list of columns you will need to use
>> the Design Engine API.
>>
>> Take a look at:
>> http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp
>> Data Set Events for an example of how to modify the query based on
>> parameters.
>>
>> Jason
>>
>> "Marius Hanganu" <mhanganu@tremend.ro> wrote in message
>> news:b2b765a41eb2e3200efbae434393efef$1@www.eclipse.org...
>>> Hello,
>>>
>>> I am trying to migrate some pages of an existing reporting application
>>> built with JSPs and SQL queries executed in the page (using <sql:query>
>>> tag).
>>>
>>> I just convinced our client BIRT is a good solution but I found myself
>>> faced with a major problem that I do not know how to solve:
>>> 1. I need the report to receive several parameters (no problem here)
>>> 2. Based on those parameters, the old rep. app. build a query. For
>>> example:
>>> if(someVar == "userX") {
>>> sql += " and table1.col1 > 3";
>>> } else {
>>> sql += " and table1.col1 in (select col1 from table1 where id between
>>> 2 and 4)";
>>> }
>>>
>>> As you can see, the SQL queries are built on the fly.
>>>
>>> How can I transform this kind of constructions to BIRT?
>>>
>>> Thanks,
>>> Marius Hanganu
>>>
>>
>>
>> As long as you do not modify the returned list of columns this can be
>> done in scripting or property binding.
>> If you need to modify the returned list of columns you will need to use
>> the Design Engine API.
>>
>> Take a look at:
>> http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp
>> Data Set Events for an example of how to modify the query based on
>> parameters.
>>
>> Jason
>>
>> "Marius Hanganu" <mhanganu@tremend.ro> wrote in message
>> news:b2b765a41eb2e3200efbae434393efef$1@www.eclipse.org...
>>> Hello,
>>>
>>> I am trying to migrate some pages of an existing reporting application
>>> built with JSPs and SQL queries executed in the page (using <sql:query>
>>> tag).
>>>
>>> I just convinced our client BIRT is a good solution but I found myself
>>> faced with a major problem that I do not know how to solve:
>>> 1. I need the report to receive several parameters (no problem here)
>>> 2. Based on those parameters, the old rep. app. build a query. For
>>> example:
>>> if(someVar == "userX") {
>>> sql += " and table1.col1 > 3";
>>> } else {
>>> sql += " and table1.col1 in (select col1 from table1 where id between
>>> 2 and 4)";
>>> }
>>>
>>> As you can see, the SQL queries are built on the fly.
>>>
>>> How can I transform this kind of constructions to BIRT?
>>>
>>> Thanks,
>>> Marius Hanganu
>>>
>>
|
|
|
| Re: transform SQLs built in JSPs to BIRT data sets [message #194199 is a reply to message #194191] |
Wed, 04 October 2006 17:48 |
| Eclipse User |
|
|
|
Originally posted by: mhanganu.tremend.ro
Yes, it should scroll to the right. Is this a problem?
Thanks,
Marius Hanganu
Jason Weathersby wrote:
> Does this have to scroll to the right?
> You can do the aggregate piece of this easy just by grouping on name with a
> sub group of date.
>
> Jason
>
> "Marius Hanganu" <mhanganu@tremend.ro> wrote in message
> news:eg0pvk$u38$1@utils.eclipse.org...
>> Thanks Jason for the response. I will analyze the resources and hopefully
>> it will work out for me.
>>
>> I have another question though: the result sets retrieved in the JSP pages
>> are iterated and transform. For example, a result set looking like:
>> name value date
>> 'Dan' 1 10 sep 2006
>> 'Mark' 2 10 sep 2006
>> 'Dan' 1 11 sep 2006
>> 'Mark' 3 11 sep 2006
>> 'Dan' 5 12 sep 2006
>> 'Mark' 1 12 sep 2006
>>
>> is transformed to
>>
>> Name 10sep2006 11sep2006 12sep2006
>> Dan 1 1 5
>> Mark 2 3 1
>>
>> Is this kind of manipulation possible with BIRT? I guess the problem is to
>> generate a table with a variable no of columns.
>>
>> If so, perhaps you can help me with some indications.
>>
>> Thanks,
>> Marius Hanganu
>>
>> Jason Weathersby wrote:
>>
>>
>> Jason Weathersby wrote:
>>> As long as you do not modify the returned list of columns this can be
>>> done in scripting or property binding.
>>> If you need to modify the returned list of columns you will need to use
>>> the Design Engine API.
>>>
>>> Take a look at:
>>> http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp
>>> Data Set Events for an example of how to modify the query based on
>>> parameters.
>>>
>>> Jason
>>>
>>> "Marius Hanganu" <mhanganu@tremend.ro> wrote in message
>>> news:b2b765a41eb2e3200efbae434393efef$1@www.eclipse.org...
>>>> Hello,
>>>>
>>>> I am trying to migrate some pages of an existing reporting application
>>>> built with JSPs and SQL queries executed in the page (using <sql:query>
>>>> tag).
>>>>
>>>> I just convinced our client BIRT is a good solution but I found myself
>>>> faced with a major problem that I do not know how to solve:
>>>> 1. I need the report to receive several parameters (no problem here)
>>>> 2. Based on those parameters, the old rep. app. build a query. For
>>>> example:
>>>> if(someVar == "userX") {
>>>> sql += " and table1.col1 > 3";
>>>> } else {
>>>> sql += " and table1.col1 in (select col1 from table1 where id between
>>>> 2 and 4)";
>>>> }
>>>>
>>>> As you can see, the SQL queries are built on the fly.
>>>>
>>>> How can I transform this kind of constructions to BIRT?
>>>>
>>>> Thanks,
>>>> Marius Hanganu
>>>>
>>>
>>> As long as you do not modify the returned list of columns this can be
>>> done in scripting or property binding.
>>> If you need to modify the returned list of columns you will need to use
>>> the Design Engine API.
>>>
>>> Take a look at:
>>> http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp
>>> Data Set Events for an example of how to modify the query based on
>>> parameters.
>>>
>>> Jason
>>>
>>> "Marius Hanganu" <mhanganu@tremend.ro> wrote in message
>>> news:b2b765a41eb2e3200efbae434393efef$1@www.eclipse.org...
>>>> Hello,
>>>>
>>>> I am trying to migrate some pages of an existing reporting application
>>>> built with JSPs and SQL queries executed in the page (using <sql:query>
>>>> tag).
>>>>
>>>> I just convinced our client BIRT is a good solution but I found myself
>>>> faced with a major problem that I do not know how to solve:
>>>> 1. I need the report to receive several parameters (no problem here)
>>>> 2. Based on those parameters, the old rep. app. build a query. For
>>>> example:
>>>> if(someVar == "userX") {
>>>> sql += " and table1.col1 > 3";
>>>> } else {
>>>> sql += " and table1.col1 in (select col1 from table1 where id between
>>>> 2 and 4)";
>>>> }
>>>>
>>>> As you can see, the SQL queries are built on the fly.
>>>>
>>>> How can I transform this kind of constructions to BIRT?
>>>>
>>>> Thanks,
>>>> Marius Hanganu
>>>>
>
|
|
| |
| Re: transform SQLs built in JSPs to BIRT data sets [message #194222 is a reply to message #194205] |
Wed, 04 October 2006 18:17 |
| Eclipse User |
|
|
|
Originally posted by: mhanganu.tremend.ro
Hi Cristian,
The JSP report I'm trying to convert to BIRT uses actually dates as
column headers. Those dates are retrieved from a filter page and date
range could vary from 1 day to 3 years ( 1000+ days).
If that's the case, I'll have to display 10-1000 columns. Is this
something that can be done with BIRT?
Do you know what is the maximum number of columns?
Thanks,
Marius
Cristian wrote:
> Hi Marius,
>
> from what I know a table in birt is defined with a maximum number of
> columns (which is a fix number). My previous solution to this problem is
> to define a maximum number of columns and then display the ones I need,
> the rest will be hidden.
>
> Cristian
>
|
|
| |
| Re: transform SQLs built in JSPs to BIRT data sets [message #194260 is a reply to message #194237] |
Wed, 04 October 2006 19:15 |
| Eclipse User |
|
|
|
Originally posted by: mhanganu.tremend.ro
Thank you Cristian,
I will try to identify a max interval and set a maximum number of columns.
Marius
Cristian wrote:
> Hi Marius,
>
> 1000+ columns is a big number. BIRT is a powerfull tool to generate
> PDFS. Try to determine if you ever want to generate PDFS that have a
> table with 1000+ columns (I don't think that will fit on any page). To
> answer you to the second question: I don't know of any limitation on the
> number of columns in Birt. I mostly use the Birt Design Tool.
>
> I pretty much had the same problem, but to display some statistics
> according to some companies. Because the companies were retrieved from a
> DB, their number was variable. The problem was that the table might not
> be accessible to be printed in a fancy format because it won't fit on
> any page. I've requested the client to determine the maximum number of
> companies that would be printed at a time. The solution also implements
> a jsp box where you could select the companies to be printed (a maximum
> number was defined).
>
> Cristian
>
|
|
|
| Re: transform SQLs built in JSPs to BIRT data sets [message #194268 is a reply to message #194191] |
Wed, 04 October 2006 19:20 |
| Eclipse User |
|
|
|
Originally posted by: mhanganu.tremend.ro
Hi Jason,
I'm trying to understand how can you make that result set perpendicular.
What do you mean by grouping by name and subgrouping by date?
Thanks,
Marius
Jason Weathersby wrote:
> Does this have to scroll to the right?
> You can do the aggregate piece of this easy just by grouping on name with a
> sub group of date.
>
> Jason
>
> "Marius Hanganu" <mhanganu@tremend.ro> wrote in message
> news:eg0pvk$u38$1@utils.eclipse.org...
>> Thanks Jason for the response. I will analyze the resources and hopefully
>> it will work out for me.
>>
>> I have another question though: the result sets retrieved in the JSP pages
>> are iterated and transform. For example, a result set looking like:
>> name value date
>> 'Dan' 1 10 sep 2006
>> 'Mark' 2 10 sep 2006
>> 'Dan' 1 11 sep 2006
>> 'Mark' 3 11 sep 2006
>> 'Dan' 5 12 sep 2006
>> 'Mark' 1 12 sep 2006
>>
>> is transformed to
>>
>> Name 10sep2006 11sep2006 12sep2006
>> Dan 1 1 5
>> Mark 2 3 1
>>
>> Is this kind of manipulation possible with BIRT? I guess the problem is to
>> generate a table with a variable no of columns.
>>
>> If so, perhaps you can help me with some indications.
>>
>> Thanks,
>> Marius Hanganu
>>
>> Jason Weathersby wrote:
>>
>>
>> Jason Weathersby wrote:
>>> As long as you do not modify the returned list of columns this can be
>>> done in scripting or property binding.
>>> If you need to modify the returned list of columns you will need to use
>>> the Design Engine API.
>>>
>>> Take a look at:
>>> http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp
>>> Data Set Events for an example of how to modify the query based on
>>> parameters.
>>>
>>> Jason
>>>
>>> "Marius Hanganu" <mhanganu@tremend.ro> wrote in message
>>> news:b2b765a41eb2e3200efbae434393efef$1@www.eclipse.org...
>>>> Hello,
>>>>
>>>> I am trying to migrate some pages of an existing reporting application
>>>> built with JSPs and SQL queries executed in the page (using <sql:query>
>>>> tag).
>>>>
>>>> I just convinced our client BIRT is a good solution but I found myself
>>>> faced with a major problem that I do not know how to solve:
>>>> 1. I need the report to receive several parameters (no problem here)
>>>> 2. Based on those parameters, the old rep. app. build a query. For
>>>> example:
>>>> if(someVar == "userX") {
>>>> sql += " and table1.col1 > 3";
>>>> } else {
>>>> sql += " and table1.col1 in (select col1 from table1 where id between
>>>> 2 and 4)";
>>>> }
>>>>
>>>> As you can see, the SQL queries are built on the fly.
>>>>
>>>> How can I transform this kind of constructions to BIRT?
>>>>
>>>> Thanks,
>>>> Marius Hanganu
>>>>
>>>
>>> As long as you do not modify the returned list of columns this can be
>>> done in scripting or property binding.
>>> If you need to modify the returned list of columns you will need to use
>>> the Design Engine API.
>>>
>>> Take a look at:
>>> http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp
>>> Data Set Events for an example of how to modify the query based on
>>> parameters.
>>>
>>> Jason
>>>
>>> "Marius Hanganu" <mhanganu@tremend.ro> wrote in message
>>> news:b2b765a41eb2e3200efbae434393efef$1@www.eclipse.org...
>>>> Hello,
>>>>
>>>> I am trying to migrate some pages of an existing reporting application
>>>> built with JSPs and SQL queries executed in the page (using <sql:query>
>>>> tag).
>>>>
>>>> I just convinced our client BIRT is a good solution but I found myself
>>>> faced with a major problem that I do not know how to solve:
>>>> 1. I need the report to receive several parameters (no problem here)
>>>> 2. Based on those parameters, the old rep. app. build a query. For
>>>> example:
>>>> if(someVar == "userX") {
>>>> sql += " and table1.col1 > 3";
>>>> } else {
>>>> sql += " and table1.col1 in (select col1 from table1 where id between
>>>> 2 and 4)";
>>>> }
>>>>
>>>> As you can see, the SQL queries are built on the fly.
>>>>
>>>> How can I transform this kind of constructions to BIRT?
>>>>
>>>> Thanks,
>>>> Marius Hanganu
>>>>
>
|
|
| |
| Re: transform SQLs built in JSPs to BIRT data sets [message #194284 is a reply to message #194276] |
Wed, 04 October 2006 20:08 |
| Eclipse User |
|
|
|
Originally posted by: mhanganu.tremend.ro
Hi Cristian,
I had no clue either how you can turn row headers into column headers.
Jason said something that confused me and I wanted more clarifications
from him.
But you are saying I can do some js logic that would turn my row headers
into column headers. If I do that, then how can I fill a table in the
report, with column headers and so?
Thanks,
Marius Hanganu
Cristian wrote:
> Hi Marius,
>
> what are you trying, from what I see, is to move the date elements as
> column headers. I have no idea how you could do that.
>
> Grouping on name with a sub group of date on your querry that returns:
> 'Dan' 1 10 sep 2006
> 'Mark' 2 10 sep 2006
> 'Dan' 1 11 sep 2006
> 'Mark' 3 11 sep 2006
> 'Dan' 5 12 sep 2006
> 'Mark' 1 12 sep 2006
>
>
> will have as result:
>
> 'Dan' 1 10 sep 2006
> 'Dan' 1 11 sep 2006
> 'Dan' 5 12 sep 2006
> 'Mark' 2 10 sep 2006
> 'Mark' 3 11 sep 2006
> 'Mark' 1 12 sep 2006
>
> To transform this result set into
>
> Name 10sep2006 11sep2006 12sep2006
> Dan 1 1 5
> Mark 2 3 1
>
> you will have to use some java logic or Rhino (BIRT scripting) logic.
> Store them into an object and redisplay that object into a table. The
> fetch is done on a row and not on a column, that's why you can't do that
> with a single table.
>
> Hope that helps you,
> Cristian
>
|
|
| |
Goto Forum:
Current Time: Thu Apr 25 12:44:20 GMT 2024
Powered by FUDForum. Page generated in 0.04590 seconds |
] 
Search
Help
Register
Login
Home
