Eclipse Community Forums: BIRT » Parameter with % value - showing and records with null values

Home » Archived » BIRT » Parameter with % value - showing and records with null values

Parameter with % value - showing and records with null values [message #1219585]

Wed, 04 December 2013 13:38

Eclipse User

Hi,

I suppose this is simple question for many.
Lets say I have this table:

SERVICE:
A
B
C
null
null

I made a SQL data set (not by scripting) query:

SELECT SERVICE FROM TABLE WHERE SERVICE LIKE ?

I created and parameter service which is not required (to be able to select null value)

So If I enter A it shows A which is ok.
If I enter % it will show A,B,C which is not ok, it should show and two others.
I can not put in where clause "service like % or service is null" because if I enter A it will show 3 records in that case which is not good.

I want when "%" is entered to show all records but if specific value is entered to show only that value.

Please can someone give me instructions how to solve this in BIRT?

Thank you

[Updated on: Thu, 05 December 2013 07:37] by Moderator

Re: Null Parameter - showing and records with null values [message #1219691 is a reply to message #1219585]

Thu, 05 December 2013 07:36

Eclipse User

I would really appreciate help regarding this.

So my wish is when user enter % for parameter to list him all records + records with null value in that field.

Thank you

Re: Null Parameter - showing and records with null values [message #1219770 is a reply to message #1219691]

Thu, 05 December 2013 12:30

Eclipse User

Hi,

there is a smart and incredibly useful tip to do this, by using 'OR' sql operator! Change your query like this:

SELECT SERVICE 
FROM MYTABLE 
WHERE (?='%' OR SERVICE LIKE ?)

We make use of the symbol '?' twice , so we need to declare two dataset parameters, both linked to the same report parameter 'service'. When the first condition ?='%' is true, the database won't evaluate the second condition therefore even null values will be returned

[Updated on: Thu, 05 December 2013 12:37] by Moderator

Re: Null Parameter - showing and records with null values [message #1219837 is a reply to message #1219770]

Fri, 06 December 2013 03:47

Eclipse User

Hi,

thank you very much for information but it seems that this does not work completely.
If I enter as parameter value % it works OK.
But if enter some specific value, for example "Internet" I am getting error.
If I do the regular expression with only one "?" and enter "Internet" I do not have those errors.
Below are errors which I am getting when using your query.

The following items have errors:

Data (id = 367):
- Cannot execute the statement.
SQL statement does not return a ResultSet object.
SQL error #1: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.53.70

Cannot execute the statement.SQL statement does not return a ResultSet object.SQL error #1: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.53.70 ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Cannot execute the statement.SQL statement does not return a ResultSet object.SQL error #1: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.53.70 at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1121) at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1085) at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:88) at org.eclipse.birt.report.engine.executor.DataItemExecutor.execute(DataItemExecutor.java:75) at

[Updated on: Fri, 06 December 2013 03:47] by Moderator

Re: Null Parameter - showing and records with null values [message #1219873 is a reply to message #1219837]

Fri, 06 December 2013 07:27

Eclipse User

This is a standard SQL query, there is no reason why it would not work with DB2.

As you describe the problem it seems the second '?' is not mapped to the report parameter so it returns null. Double-check you have mapped it as expected in dataset parameters: oftenly there are confusions between dataset parameters and report parameters.

Any chance you can post the .rptdesign, after removing credential / confidential informations?

[Updated on: Fri, 06 December 2013 07:31] by Moderator

Re: Null Parameter - showing and records with null values [message #1219978 is a reply to message #1219873]

Sat, 07 December 2013 17:12

Eclipse User

Hi,

Thank you for all your efforts.
I associated the parameter correctly but it does not work.
As I seed I have doubts this can work in my case I do not know why, maybe it has some issue with DB2 database.

Please tell me you figure what is the issue

Attachment: SLAandUrgencyAverageResolutionTime2.rptdesign
(Size: 31.93KB, Downloaded 457 times)

[Updated on: Sat, 07 December 2013 17:13] by Moderator

Re: Null Parameter - showing and records with null values [message #1219987 is a reply to message #1219978]

Sun, 08 December 2013 07:24

Eclipse User

Hi Dejan,

It seems your setup is fine, at first sight i can't find out why it does not work. Though you should set a default value in the 'service' report parameter: it would allow to preview data within the dataset definition, and therefore might help to solve the problem.

A key point would be to know if this very query runs correctly when we hard-code parameter values: for example duplicate the dataset and replace all '?' with possible values, in particular replace the two last '?' with 'Internet', can we preview some data like this?
If so, the problem might be a birt bug. It seems the report is designed with birt 2.3.2, i never used this version may be it was not possible to use twice a same report parameter in a query?

In this case, i don't see any other easy way to apply that filter. A possible workaround would be to use a DB2 stored procedure instead of a regular SQL query.

You could also apply this 'service' filter in a BIRT expression, but this would change significantly the report design and might be less efficient.

[Updated on: Sun, 08 December 2013 07:32] by Moderator

Previous Topic:	BIRT Report Dynamic Mysql Database Name, user name and password in rptdesign files
Next Topic:	Writing BIRT reprot results in txt file

Goto Forum:

-=] Back to Top [=-

Current Time: Mon Jul 07 08:34:15 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter