Solved

jaspersoft report

Posted on 2013-11-01
7
490 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
best datatype for oracle table email creation 8 108
Oracle 12c Default Isolation Level 17 64
Oracle Date 6 42
Check for any ASM patches and install them. 1 25
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

751 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