Home » Archived » BIRT » evaluating
| evaluating [message #251738] |
Wed, 08 August 2007 12:13  |
Eclipse User |
|
|
|
Originally posted by: wade.girard.gmail.com
We are evaluating using BIRT to replace the reports that we are
creating. Our product is a web app, and all of our reports are
generated using servlets/jsp and forms for gathering report filtering.
I have a question about "filtering". We present the user with a page
that allows them to select about a dozen things, like date ranges,
names of stuff, and other things, and we use that to build a SQL string
that we execute, get the repose back, and format into a web page.
It "seems" that the SQL for BIRTS reports are part of the report, and
cannot be altered. Is that correct? Or can I specify SQL via a
parameter into a report.
An example:
if the user specifies a string value to filter out a column named
"NAME", we add
AND NAME='foo'
to the SQLs WHERE clause. But if the user left the name blank, the SQL
in BERT would be
AND NAME=''
which would end up filtering out all rows with no NAME value, rather
than just not having the AND clause at all.
Is my question making sense?
|
|
|
| Re: evaluating [message #251754 is a reply to message #251738] |
Wed, 08 August 2007 13:20  |
| Eclipse User |
|
|
|
Originally posted by: jasonweathersby.alltel.net
You can always check to see if the parameter value has anything in it
and then use the beforeOpen script to append to your SQL.
So in your report your report SQL would look like:
Select * from mytable
and in the beforeOpen script you could use
if( params["whereclause"].value != null &&
params["whereclause"].value.length > 0 ){
this.queryText = this.queryText + " " + params["whereclause"];
}
attached is an example
Jason
<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.14"
id="1">
<property name="createdBy">Eclipse BIRT Designer Version
2.2.0.v20070620 Build <2.2.0.v20070626-1003></property>
<property name="units">in</property>
<property name="comments">Copyright (c) 2007 <<Your Company
Name here>></property>
<html-property name="description">Creates a blank report with no
predefined content.</html-property>
<text-property name="displayName">Blank Report</text-property>
<property name="iconFile">/templates/blank_report.gif</property>
<parameters>
<scalar-parameter name="whereclause" id="42">
<property name="valueType">static</property>
<property name="dataType">string</property>
<property name="isRequired">false</property>
<property name="controlType">text-box</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
</parameters>
<data-sources>
<oda-data-source
extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source"
id="6">
<text-property name="displayName"></text-property>
<property
name="odaDriverClass">org.eclipse.birt.report.data.oda.sampledb.Driver </property>
<property name="odaURL">jdbc:classicmodels:sampledb</property>
<property name="odaUser">ClassicModels</property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="Data Set" id="7">
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">ORDERNUMBER</property>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">PRODUCTCODE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">QUANTITYORDERED</property>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">PRICEEACH</property>
<property name="dataType">float</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">ORDERLINENUMBER</property>
<property name="dataType">integer</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<method name="beforeOpen"><![CDATA[if(
params["whereclause"].value != null &&
params["whereclause"].value.length > 0 ){
this.queryText = this.queryText + " " + params["whereclause"];
}]]></method>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">ORDERNUMBER</property>
<property name="nativeName">ORDERNUMBER</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">PRODUCTCODE</property>
<property name="nativeName">PRODUCTCODE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">QUANTITYORDERED</property>
<property name="nativeName">QUANTITYORDERED</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">PRICEEACH</property>
<property name="nativeName">PRICEEACH</property>
<property name="dataType">float</property>
<property name="nativeDataType">8</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">ORDERLINENUMBER</property>
<property name="nativeName">ORDERLINENUMBER</property>
<property name="dataType">integer</property>
<property name="nativeDataType">5</property>
</structure>
</list-property>
<property name="queryText">select *
from orderdetails</property>
<xml-property name="designerValues"><![CDATA[<?xml
version="1.0" encoding="UTF-8"?>
<model:DesignValues
xmlns:design="http://www.eclipse.org/datatools/connectivity/oda/design"
xmlns:model="http://www.eclipse.org/birt/report/model/adapter/odaModel">
<Version>1.0</Version>
<design:ResultSets derivedMetaData="true">
<design:resultSetDefinitions>
<design:resultSetColumns>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>ORDERNUMBER</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>4</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>ORDERNUMBER</design:label>
<design:formattingHints>
<design:displaySize>11</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRODUCTCODE</design:name>
<design:position>2</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>15</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>PRODUCTCODE</design:label>
<design:formattingHints>
<design:displaySize>15</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>QUANTITYORDERED</design:name>
<design:position>3</design:position>
<design:nativeDataTypeCode>4</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>QUANTITYORDERED</design:label>
<design:formattingHints>
<design:displaySize>11</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRICEEACH</design:name>
<design:position>4</design:position>
<design:nativeDataTypeCode>8</design:nativeDataTypeCode>
<design:precision>15</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>PRICEEACH</design:label>
<design:formattingHints>
<design:displaySize>22</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>ORDERLINENUMBER</design:name>
<design:position>5</design:position>
<design:nativeDataTypeCode>5</design:nativeDataTypeCode>
<design:precision>5</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>ORDERLINENUMBER</design:label>
<design:formattingHints>
<design:displaySize>6</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
</design:resultSetColumns>
</design:resultSetDefinitions>
</design:ResultSets>
</model:DesignValues>
]]></xml-property>
</oda-data-set>
</data-sets>
<styles>
<style name="crosstab" id="4">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
<style name="crosstab-cell" id="5">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
</styles>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property
name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
<body>
<table id="8">
<property name="width">100%</property>
<property name="dataSet">Data Set</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">ORDERNUMBER</property>
<expression
name="expression">dataSetRow["ORDERNUMBER"]</expression >
<property name="dataType">integer</property>
</structure>
<structure>
<property name="name">PRODUCTCODE</property>
<expression
name="expression">dataSetRow["PRODUCTCODE"]</expression >
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">QUANTITYORDERED</property>
<expression
name="expression">dataSetRow["QUANTITYORDERED"]</expression >
<property name="dataType">integer</property>
</structure>
<structure>
<property name="name">PRICEEACH</property>
<expression
name="expression">dataSetRow["PRICEEACH"]</expression>
<property name="dataType">float</property>
</structure>
<structure>
<property name="name">ORDERLINENUMBER</property>
<expression
name="expression">dataSetRow["ORDERLINENUMBER"]</expression >
<property name="dataType">integer</property>
</structure>
</list-property>
<column id="37"/>
<column id="38"/>
<column id="39"/>
<column id="40"/>
<column id="41"/>
<header>
<row id="9">
<cell id="10">
<label id="11">
<text-property
name="text">ORDERNUMBER</text-property>
</label>
</cell>
<cell id="12">
<label id="13">
<text-property
name="text">PRODUCTCODE</text-property>
</label>
</cell>
<cell id="14">
<label id="15">
<text-property
name="text">QUANTITYORDERED</text-property>
</label>
</cell>
<cell id="16">
<label id="17">
<text-property
name="text">PRICEEACH</text-property>
</label>
</cell>
<cell id="18">
<label id="19">
<text-property
name="text">ORDERLINENUMBER</text-property>
</label>
</cell>
</row>
</header>
<detail>
<row id="20">
<cell id="21">
<data id="22">
<property
name="resultSetColumn">ORDERNUMBER</property>
</data>
</cell>
<cell id="23">
<data id="24">
<property
name="resultSetColumn">PRODUCTCODE</property>
</data>
</cell>
<cell id="25">
<data id="26">
<property
name="resultSetColumn">QUANTITYORDERED</property>
</data>
</cell>
<cell id="27">
<data id="28">
<property
name="resultSetColumn">PRICEEACH</property>
</data>
</cell>
<cell id="29">
<data id="30">
<property
name="resultSetColumn">ORDERLINENUMBER</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="31">
<cell id="32"/>
<cell id="33"/>
<cell id="34"/>
<cell id="35"/>
<cell id="36"/>
</row>
</footer>
</table>
</body>
</report>
Wade Girard wrote:
> We are evaluating using BIRT to replace the reports that we are
> creating. Our product is a web app, and all of our reports are generated
> using servlets/jsp and forms for gathering report filtering. I have a
> question about "filtering". We present the user with a page that allows
> them to select about a dozen things, like date ranges, names of stuff,
> and other things, and we use that to build a SQL string that we execute,
> get the repose back, and format into a web page.
>
> It "seems" that the SQL for BIRTS reports are part of the report, and
> cannot be altered. Is that correct? Or can I specify SQL via a parameter
> into a report.
>
> An example:
> if the user specifies a string value to filter out a column named
> "NAME", we add
>
> AND NAME='foo'
>
> to the SQLs WHERE clause. But if the user left the name blank, the SQL
> in BERT would be
>
> AND NAME=''
>
> which would end up filtering out all rows with no NAME value, rather
> than just not having the AND clause at all.
>
> Is my question making sense?
>
|
|
| | |
| Re: evaluating [message #251770 is a reply to message #251766] |
Wed, 08 August 2007 14:25 |
| Eclipse User |
|
|
|
Originally posted by: wade.girard.gmail.com
On 2007-08-08 13:08:10 -0500, Jason Weathersby
<jasonweathersby@alltel.net> said:
Okay, thats cool, how about doing x number of rows at a time...
Right now we only fetch/display the first 20 rows of a result set, and
put up paging controls to view the next /previous results at 20/per
page, the paging controls are at the top and bottom of the results
table.
Also, when I edit xml like this, does that render it unusable in the
report designer?
> Yes. Or you use multiple parameters, say one for table, one for a filter. etc.
|
|
|
| Re: evaluating [message #251782 is a reply to message #251770] |
Wed, 08 August 2007 15:05 |
| Eclipse User |
|
|
|
Originally posted by: jasonweathersby.alltel.net
Wade,
You can setup page breaks on groups or use the page interval setting on
the table element. The interval is the number of rows to display per
page in html. You should not have to modify any xml. Just create your
report parameters and then select the data set and then select the
script tab and choose the beforeOpen script.
Jason
Wade Girard wrote:
> On 2007-08-08 13:08:10 -0500, Jason Weathersby
> <jasonweathersby@alltel.net> said:
>
> Okay, thats cool, how about doing x number of rows at a time...
>
> Right now we only fetch/display the first 20 rows of a result set, and
> put up paging controls to view the next /previous results at 20/per
> page, the paging controls are at the top and bottom of the results table.
>
> Also, when I edit xml like this, does that render it unusable in the
> report designer?
>
>> Yes. Or you use multiple parameters, say one for table, one for a
>> filter. etc.
>
>
|
|
|
| Re: evaluating [message #251789 is a reply to message #251782] |
Wed, 08 August 2007 15:23 |
| Eclipse User |
|
|
|
Originally posted by: wade.girard.gmail.com
On 2007-08-08 14:05:21 -0500, Jason Weathersby
<jasonweathersby@alltel.net> said:
Jason,
I went to the report designer, selected the table, selected "Page
Break" in the Properties and set the "Page Break Interval" to 20, set
value of "After" to "Always" and tested the report. I got all 1665 rows
in one table, it did not just give me the first 20 rows with page
controls.
Note that the url I am using is birt?run?__report=new_report.rptdesign
Do I need to use the frameset?? Are the URL patterns documented somewhere?
Also, I see how I can edit the script, this is cool, and I can edit the
XML directly, this is cool too. I did notice that it keeps loosing the
<property name="isRequired">false</property> in the XML. I keep adding
it manually.
Thanks,
Wade
> Wade,
>
> You can setup page breaks on groups or use the page interval setting on
> the table element. The interval is the number of rows to display per
> page in html. You should not have to modify any xml. Just create your
> report parameters and then select the data set and then select the
> script tab and choose the beforeOpen script.
>
>
> Jason
|
|
|
| Re: evaluating [message #251792 is a reply to message #251789] |
Wed, 08 August 2007 15:32 |
| Eclipse User |
|
|
|
Originally posted by: jasonweathersby.alltel.net
Wade,
The run pattern does not support pagination.
We are still in the process of making updates to the web site.
The settings are similar to the 2.1 settings and can be found here
http://www.eclipse.org/birt/phoenix/deploy/viewerUsage.php
We now have 3 main patterns frameset, run and preview.
frameset supports pagination, TOC, AJAX framework etc
run supports AJAX framework for cancelling a report and prompting for
parameters.
preview supports parameter page and then generates output directly to
the browswer.
Jason
Wade Girard wrote:
> On 2007-08-08 14:05:21 -0500, Jason Weathersby
> <jasonweathersby@alltel.net> said:
>
> Jason,
> I went to the report designer, selected the table, selected "Page
> Break" in the Properties and set the "Page Break Interval" to 20, set
> value of "After" to "Always" and tested the report. I got all 1665 rows
> in one table, it did not just give me the first 20 rows with page controls.
>
> Note that the url I am using is birt?run?__report=new_report.rptdesign
>
> Do I need to use the frameset?? Are the URL patterns documented somewhere?
>
> Also, I see how I can edit the script, this is cool, and I can edit the
> XML directly, this is cool too. I did notice that it keeps loosing the
> <property name="isRequired">false</property> in the XML. I keep adding
> it manually.
>
> Thanks,
> Wade
>
>> Wade,
>>
>> You can setup page breaks on groups or use the page interval setting
>> on the table element. The interval is the number of rows to display
>> per page in html. You should not have to modify any xml. Just create
>> your report parameters and then select the data set and then select
>> the script tab and choose the beforeOpen script.
>>
>>
>> Jason
>
>
|
|
|
| Re: evaluating [message #251805 is a reply to message #251792] |
Wed, 08 August 2007 16:10 |
| Eclipse User |
|
|
|
Originally posted by: wade.girard.gmail.com
On 2007-08-08 14:32:32 -0500, Jason Weathersby
<jasonweathersby@alltel.net> said:
Jason,
Thanks for taking the time to answer all my questions. I really appreciate it.
My next one... Is it possible to write our own viewer? Is this
"normal" to want to have our own viewer/web app? And, where do I begin
to learn/investigate doing this?
-Wade
> Wade,
>
> The run pattern does not support pagination.
> We are still in the process of making updates to the web site.
> The settings are similar to the 2.1 settings and can be found here
>
> http://www.eclipse.org/birt/phoenix/deploy/viewerUsage.php
>
> We now have 3 main patterns frameset, run and preview.
> frameset supports pagination, TOC, AJAX framework etc
> run supports AJAX framework for cancelling a report and prompting for
> parameters.
> preview supports parameter page and then generates output directly to
> the browswer.
>
> Jason
>
> Wade Girard wrote:
>> On 2007-08-08 14:05:21 -0500, Jason Weathersby
>> <jasonweathersby@alltel.net> said:
>>
>> Jason,
>> I went to the report designer, selected the table, selected "Page
>> Break" in the Properties and set the "Page Break Interval" to 20, set
>> value of "After" to "Always" and tested the report. I got all 1665 rows
>> in one table, it did not just give me the first 20 rows with page
>> controls.
>>
>> Note that the url I am using is birt?run?__report=new_report.rptdesign
>>
>> Do I need to use the frameset?? Are the URL patterns documented somewhere?
>>
>> Also, I see how I can edit the script, this is cool, and I can edit the
>> XML directly, this is cool too. I did notice that it keeps loosing the
>> <property name="isRequired">false</property> in the XML. I keep adding
>> it manually.
>>
>> Thanks,
>> Wade
>>
>>> Wade,
>>>
>>> You can setup page breaks on groups or use the page interval setting on
>>> the table element. The interval is the number of rows to display per
>>> page in html. You should not have to modify any xml. Just create your
>>> report parameters and then select the data set and then select the
>>> script tab and choose the beforeOpen script.
>>>
>>>
>>> Jason
|
|
|
| Re: evaluating [message #251818 is a reply to message #251805] |
Wed, 08 August 2007 17:05 |
| Eclipse User |
|
|
|
You could if you must.
The book "Integrating and Extending BIRT" is one place to start.
Actuate Corporation has workgroup (iPortal workgroup) and
enterprise-level products (iServer and iPortal) for report deployment
and viewing. It might be worth checking them out.
--brian
Wade Girard wrote:
> On 2007-08-08 14:32:32 -0500, Jason Weathersby
> <jasonweathersby@alltel.net> said:
>
> Jason,
> Thanks for taking the time to answer all my questions. I really
> appreciate it.
>
> My next one... Is it possible to write our own viewer? Is this
> "normal" to want to have our own viewer/web app? And, where do I begin
> to learn/investigate doing this?
>
> -Wade
>
>> Wade,
>>
>> The run pattern does not support pagination.
>> We are still in the process of making updates to the web site.
>> The settings are similar to the 2.1 settings and can be found here
>>
>> http://www.eclipse.org/birt/phoenix/deploy/viewerUsage.php
>>
>> We now have 3 main patterns frameset, run and preview.
>> frameset supports pagination, TOC, AJAX framework etc
>> run supports AJAX framework for cancelling a report and prompting for
>> parameters.
>> preview supports parameter page and then generates output directly to
>> the browswer.
>>
>> Jason
>>
>> Wade Girard wrote:
>>> On 2007-08-08 14:05:21 -0500, Jason Weathersby
>>> <jasonweathersby@alltel.net> said:
>>>
>>> Jason,
>>> I went to the report designer, selected the table, selected "Page
>>> Break" in the Properties and set the "Page Break Interval" to 20, set
>>> value of "After" to "Always" and tested the report. I got all 1665
>>> rows in one table, it did not just give me the first 20 rows with
>>> page controls.
>>>
>>> Note that the url I am using is birt?run?__report=new_report.rptdesign
>>>
>>> Do I need to use the frameset?? Are the URL patterns documented
>>> somewhere?
>>>
>>> Also, I see how I can edit the script, this is cool, and I can edit
>>> the XML directly, this is cool too. I did notice that it keeps
>>> loosing the <property name="isRequired">false</property> in the XML.
>>> I keep adding it manually.
>>>
>>> Thanks,
>>> Wade
>>>
>>>> Wade,
>>>>
>>>> You can setup page breaks on groups or use the page interval setting
>>>> on the table element. The interval is the number of rows to display
>>>> per page in html. You should not have to modify any xml. Just
>>>> create your report parameters and then select the data set and then
>>>> select the script tab and choose the beforeOpen script.
>>>>
>>>>
>>>> Jason
>
>
|
|
|
Goto Forum:
Current Time: Sun Jul 13 09:47:45 EDT 2025
Powered by FUDForum. Page generated in 0.09580 seconds |
Home