Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

jaspersoft report

Posted on 2013-11-01
7
Medium Priority
?
507 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 1000 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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-…
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

688 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