Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
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 18:38 Go to next message
Dejan Vujanic is currently offline Dejan VujanicFriend
Messages: 67
Registered: October 2011
Member
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 12:37]

Report message to a moderator

Re: Null Parameter - showing and records with null values [message #1219691 is a reply to message #1219585] Thu, 05 December 2013 12:36 Go to previous messageGo to next message
Dejan Vujanic is currently offline Dejan VujanicFriend
Messages: 67
Registered: October 2011
Member
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 17:30 Go to previous messageGo to next message
donino donino is currently offline donino doninoFriend
Messages: 183
Registered: July 2011
Senior Member
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 17:37]

Report message to a moderator

Re: Null Parameter - showing and records with null values [message #1219837 is a reply to message #1219770] Fri, 06 December 2013 08:47 Go to previous messageGo to next message
Dejan Vujanic is currently offline Dejan VujanicFriend
Messages: 67
Registered: October 2011
Member
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 08:47]

Report message to a moderator

Re: Null Parameter - showing and records with null values [message #1219873 is a reply to message #1219837] Fri, 06 December 2013 12:27 Go to previous messageGo to next message
donino donino is currently offline donino doninoFriend
Messages: 183
Registered: July 2011
Senior Member
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 12:31]

Report message to a moderator

Re: Null Parameter - showing and records with null values [message #1219978 is a reply to message #1219873] Sat, 07 December 2013 22:12 Go to previous messageGo to next message
Dejan Vujanic is currently offline Dejan VujanicFriend
Messages: 67
Registered: October 2011
Member
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

[Updated on: Sat, 07 December 2013 22:13]

Report message to a moderator

Re: Null Parameter - showing and records with null values [message #1219987 is a reply to message #1219978] Sun, 08 December 2013 12:24 Go to previous message
donino donino is currently offline donino doninoFriend
Messages: 183
Registered: July 2011
Senior Member
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 12:32]

Report message to a 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:
  


Current Time: Tue Mar 19 09:02:57 GMT 2024

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

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

Back to the top