Solved

jaspersoft report

Posted on 2013-11-01
7
487 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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

685 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