Solved

jaspersoft report

Posted on 2013-11-01
7
486 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 76

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Salesforce.com’s Console is a great tool to view activities, leads, contacts, accounts and opportunities all in one screen. It is particularly effective during call blocks and working numerous activities at a time in a quick, repetitive fashion (suc…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

816 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now