Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Problem with input parameters like ?(Report parameter)
Problem with input parameters like ? [message #669844] Wed, 11 May 2011 19:00 Go to next message
gin1975  is currently offline gin1975 Friend
Messages: 30
Registered: April 2011
Member

Hello,

I have a problem when filtering reports from a user-entered parameter. I put a simple example for you to try to pinpoint the problem:

- The user must enter a parameter from a listbox with a number of values ​​of relating to clients. Example: customer1, customer2, customer3, etc.
- Looking for that parameter input, I'm trying to work the following Dataset from a DB that contains the list of customers and their associated information (ID, email, etc.). Data Set Query:

select id, email
from customers
WHERE email.customers like "%?%"

The problem is that this query gives me the following error: SQL error # 1: Parameter index out of range (1> number of parameters Which is 0).

Using other sentences such as like ? or =? works, but in this case, I need to make the condition which I have indicated above.

Can you tell me where is the problem?

Thanks.
(no subject) [message #669891 is a reply to message #669844] Thu, 12 May 2011 00:11 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

When you need like and between and in clauses it is better to put these
in a beforeOpen script on the dataset. Take a look at the attached example.

Jason

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.22"
id="1">
<property name="comments">Copyright (c) 2007 &lt;&lt;Your Company
Name here>></property>
<property name="createdBy">Eclipse BIRT Designer Version
2.6.1.v20100902 Build &lt;2.6.1.v20100915-1750></property>
<html-property name="description">Creates a blank report with no
predefined content.</html-property>
<property name="units">in</property>
<text-property name="displayName">Blank Report</text-property>
<property name="iconFile">/templates/blank_report.gif</property>
<property name="layoutPreference">auto layout</property>
<parameters>
<scalar-parameter name="parmorders" id="8">
<property name="valueType">dynamic</property>
<property name="dataSetName">parmDataSet</property>
<expression name="valueExpr"
type="javascript">dataSetRow["CUSTOMERNUMBER"]</expression >
<expression name="labelExpr"
type="javascript">dataSetRow["CUSTOMERNUMBER"]</expression >
<property name="dataType">string</property>
<property name="distinct">true</property>
<property name="paramType">multi-value</property>
<property name="controlType">list-box</property>
<property name="mustMatch">true</property>
<property name="fixedOrder">true</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">
<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="parmDataSet" id="7">
<list-property name="columnHints">
<structure>
<property name="columnName">CUSTOMERNUMBER</property>
<text-property
name="displayName">CUSTOMERNUMBER</text-property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">CUSTOMERNUMBER</property>
<property name="dataType">integer</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">CUSTOMERNUMBER</property>
<property name="nativeName">CUSTOMERNUMBER</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[select customernumber
from orders]]></xml-property>
</oda-data-set>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="orderdetails" id="10">
<list-property name="columnHints">
<structure>
<property name="columnName">ORDERNUMBER</property>
<text-property
name="displayName">ORDERNUMBER</text-property>
</structure>
<structure>
<property name="columnName">ORDERDATE</property>
<text-property
name="displayName">ORDERDATE</text-property>
</structure>
<structure>
<property name="columnName">REQUIREDDATE</property>
<text-property
name="displayName">REQUIREDDATE</text-property>
</structure>
<structure>
<property name="columnName">SHIPPEDDATE</property>
<text-property
name="displayName">SHIPPEDDATE</text-property>
</structure>
<structure>
<property name="columnName">STATUS</property>
<text-property
name="displayName">STATUS</text-property>
</structure>
<structure>
<property name="columnName">COMMENTS</property>
<text-property
name="displayName">COMMENTS</text-property>
</structure>
<structure>
<property name="columnName">CUSTOMERNUMBER</property>
<text-property
name="displayName">CUSTOMERNUMBER</text-property>
</structure>
</list-property>
<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">ORDERDATE</property>
<property name="dataType">date</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">REQUIREDDATE</property>
<property name="dataType">date</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">SHIPPEDDATE</property>
<property name="dataType">date</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">STATUS</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">6</property>
<property name="name">COMMENTS</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">7</property>
<property name="name">CUSTOMERNUMBER</property>
<property name="dataType">integer</property>
</structure>
</list-property>
</structure>
<method name="beforeOpen"><![CDATA[var parmcount =
params["parmorders"].value.length
var whereclause = "";
if( parmcount > 0 ){
whereclause = " where customernumber in ( ";
}
for( i=0; i < parmcount; i++ ){
if( i == 0 ){
whereclause = whereclause + params["parmorders"].value[i];
}else{
whereclause = whereclause + " , " + params["parmorders"].value[i];
}
}
if( parmcount > 0 ){
this.queryText = this.queryText + whereclause + " ) ";
}
]]></method>
<property name="dataSource">Data Source</property>
<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">ORDERDATE</property>
<property name="nativeName">ORDERDATE</property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">REQUIREDDATE</property>
<property name="nativeName">REQUIREDDATE</property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">SHIPPEDDATE</property>
<property name="nativeName">SHIPPEDDATE</property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">STATUS</property>
<property name="nativeName">STATUS</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">6</property>
<property name="name">COMMENTS</property>
<property name="nativeName">COMMENTS</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">7</property>
<property name="name">CUSTOMERNUMBER</property>
<property name="nativeName">CUSTOMERNUMBER</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[select *
from orders]]></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>
<style name="detail" id="45">
<property name="backgroundColor">#BACAE2</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="headerfooter" id="46">
<property name="backgroundColor">#004080</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
</style>
<style name="groupheader" id="47">
<property name="backgroundColor">#0080FF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
</style>
</styles>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<property name="topMargin">1in</property>
<property name="leftMargin">1.25in</property>
<property name="bottomMargin">1in</property>
<property name="rightMargin">1.25in</property>
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property name="content"><![CDATA[<VALUE-OF>
var parmcount = params["parmorders"].value.length
for( i=0; i < parmcount; i++ ){
if( i == 0 ){
var lbl = params["parmorders"].value[i];
}else{
lbl = lbl + " , " + params["parmorders"].value[i];
}
lbl;
}</VALUE-OF>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
<body>
<table id="97">
<property name="width">100%</property>
<property name="dataSet">orderdetails</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">ORDERDATE</property>
<expression
name="expression">dataSetRow["ORDERDATE"]</expression>
<property name="dataType">date</property>
</structure>
<structure>
<property name="name">REQUIREDDATE</property>
<expression
name="expression">dataSetRow["REQUIREDDATE"]</expression >
<property name="dataType">date</property>
</structure>
<structure>
<property name="name">SHIPPEDDATE</property>
<expression
name="expression">dataSetRow["SHIPPEDDATE"]</expression >
<property name="dataType">date</property>
</structure>
<structure>
<property name="name">STATUS</property>
<expression
name="expression">dataSetRow["STATUS"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">COMMENTS</property>
<expression
name="expression">dataSetRow["COMMENTS"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">CUSTOMERNUMBER</property>
<expression
name="expression">dataSetRow["CUSTOMERNUMBER"]</expression >
<property name="dataType">integer</property>
</structure>
</list-property>
<property name="pageBreakInterval">50</property>
<column id="136"/>
<column id="137"/>
<column id="140"/>
<header>
<row id="98">
<property name="style">headerfooter</property>
<cell id="99">
<label id="100">
<text-property
name="text">ORDERNUMBER</text-property>
</label>
</cell>
<cell id="101">
<label id="102">
<text-property
name="text">ORDERDATE</text-property>
</label>
</cell>
<cell id="107">
<label id="108">
<text-property
name="text">STATUS</text-property>
</label>
</cell>
</row>
</header>
<group id="143">
<property name="groupName">NewTableGroup1</property>
<property name="interval">none</property>
<property name="sortDirection">asc</property>
<expression
name="keyExpr">row["CUSTOMERNUMBER"]</expression>
<structure name="toc">
<expression
name="expressionValue">row["CUSTOMERNUMBER"]</expression >
</structure>
<property name="repeatHeader">true</property>
<property name="hideDetail">false</property>
<property name="pageBreakAfter">auto</property>
<property name="pageBreakBefore">auto</property>
<property name="pageBreakInside">auto</property>
<header>
<row id="144">
<property name="style">groupheader</property>
<cell id="145">
<data id="154">
<property
name="resultSetColumn">CUSTOMERNUMBER</property>
</data>
</cell>
<cell id="146"/>
<cell id="147"/>
</row>
</header>
</group>
<detail>
<row id="113">
<property name="style">detail</property>
<cell id="114">
<data id="115">
<property
name="resultSetColumn">ORDERNUMBER</property>
</data>
</cell>
<cell id="116">
<data id="117">
<property
name="resultSetColumn">ORDERDATE</property>
</data>
</cell>
<cell id="122">
<data id="123">
<property
name="resultSetColumn">STATUS</property>
</data>
</cell>
</row>
</detail>
</table>
</body>
</report>


On 5/11/2011 3:00 PM, gin1975 wrote:
>
> Hello,
>
> I have a problem when filtering reports from a user-entered
> parameter. I put a simple example for you to try to
> pinpoint the problem:
>
> - The user must enter a parameter from a listbox with a
> number of values ​​of relating to clients. Example:
> customer1, customer2, customer3, etc.
> - Looking for that parameter input, I'm trying to work the
> following Dataset from a DB that contains the list of
> customers and their associated information (ID, email,
> etc.). Data Set Query:
>
> select id, email
> from customers
> WHERE email.customers like "%?%"
>
> The problem is that this query gives me the following error:
> SQL error # 1: Parameter index out of range (1> number of
> parameters Which is 0).
>
> Using other sentences such as like ? or =? works, but in
> this case, I need to make the condition which I have
> indicated above.
>
> Can you tell me where is the problem?
>
> Thanks.
Previous Topic:Problem with input parameters like ?
Next Topic:how to use the validate event of parameter
Goto Forum:
  


Current Time: Sun Nov 23 14:47:02 GMT 2014

Powered by FUDForum. Page generated in 0.02475 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software