Solved

jaspersoft report

Posted on 2013-11-01
7
495 Views
Last Modified: 2014-01-23
Is there a way to allow the user to input multiple dates and have the report filtered on those selected dates? For example:

Loan Table:

Loan#   Date
1   11/1/2013
2   11/2/2013
3   11/3/2013
4   11/4/2013
5   11/5/2013
User select: 11/01/2013 & 11/03/2013

Results:    
Loan#   Date
1   11/1/2013
3   11/3/2013

Any suggestion would be greatly appreciated.
report-sql.txt
0
Comment
Question by:Lambel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39617803
Are you looking for just the specific dates selected?

If so, try an IN list:

select
to_char(prior_pymt_date,'mm/dd/yyyy') as prior_pymt_date,
(to_char(prior_pymt_date, 'DY')) as day_of_week,
count(issue_number)
from dba.issuenum
where
trunc(prior_pymt_date) in (to_date('11/01/2013','MM/DD/YYYY'),to_date('11/03/2013','MM/DD/YYYY'))

Open in new window


Note: the trunc around prior_pymt_date will ignore any index on that column.  If your dates have a time portion and you have an index, you may need a Function-Based Index.

If your dates do not have a time portion (all of them are set to 0), then you don't need trunk.
0
 

Author Comment

by:Lambel
ID: 39617885
Thanks, this is close.  I am looking for something to allow the user to enter separate dates to filter out of the selection using the jaspersoft interface for parameters or collections, or something similar. Then I would feed the parameters to the sql.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39617944
Allowing a user to select multiple, non-contiguous entries from a list is a common challenge that is not easy to solve in Oracle.  I'm not familiar with Jaspersoft, so I don't knowif or  how that could help with this.
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39617953
So is this more of how to code the interface in Jaspersoft than it is how to turn that into the correct SQL?
0
 

Author Comment

by:Lambel
ID: 39617971
@slightwv: Yes, that's correct. I'm looking for how to set up Jaspersoft to allow me to use multiple date parameters (not sequential) to build the filters of the sql.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39617976
I'll see if I can find any Experts that know about Jaspersoft.
0
 
LVL 5

Expert Comment

by:Jan Louwerens
ID: 39618022
Try the $X{} clause.

$X{IN, <field>, <collection>}

Assuming "FilterDates" is a Parameter you've declared in your report as a Collection of Dates, in your query, you could use:

$X{IN, trunc(prior_pymt_date), FilterDates}

select
to_char(prior_pymt_date,'mm/dd/yyyy') as prior_pymt_date,
(to_char(prior_pymt_date, 'DY')) as day_of_week,
count(issue_number)
from dba.issuenum
where
$X{IN, trunc(prior_pymt_date), FilterDates}

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: You must have administrative privileges in order to create/edit Roles. Salesforce.com (http://www.salesforce.com/) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales and marke…
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question