Can I set param val in qry based on another param?

[joepmo]

Please forgive me if I have missed something obvious but I am still getting my feel around Jasper and having searched many posts - I think I may be overcomplicating the issue, but we shall see.

I am trying to figure out how to set a parameter value in a query based on the value of another parameter!!

The situation is that I have written a report to track sales and cost on sales invoices which I would like to use with Jasper Server.  After a couple of miscues I figured out that if I can design one report template and apply multiple queries using the scheduler to obtain the data to fill the report it would be the most efficient way to maintain the reports. 

I am trying to figure out how to set a parameter value in a query based on the value of another parameter

For example I designed the report as follows:

Report Name

ReportStartDate       to      ReportEndDate

InvoiceNo       invoiceDate     SaleAmt    CostAmt

I then created a parameter to calculate the date of the beginning of the month and the end of the month relative to today using the  Commons-Lang as follows:

BeginCurrMonth  -  org.apache.commons.lang.time.DateUtils.truncate(new Date(), 2)

EndCurrMonth - org.apache.commons.lang.time.DateUtils.addMonths
                             (org.apache.commons.lang.time.DateUtils.addDays
                             (org.apache.commons.lang.time.DateUtils.truncate(new Date(), 2), -1),1)

I did the same for BeginPriorMonth, BeginYear, EndPriorMonth, EndYear, etc so that I can use those parameters as needed in my SQL query.

I am able to access these Parameters for use in my query below, which is great:

select invoiceno,invoicedate,billto,billtoname,
partscost as pcost,(partstaxable + partsnontax) as psell,
((partstaxable + partsnontax)- partscost )as pmarg,
(((partstaxable + partsnontax)- partscost )/(partstaxable + partsnontax)) as pmargperc
from invoicereg
where (partscost <> 0 or
(partstaxable + partsnontax) <> 0)
and invoicedate >= $P{BeginCurrMonth}
and invoicedate<= $P{EndCurrMonth}
and salebranch=1
and left(saledept,1)=3

Sometimes the date variable might be $P{BeginPriorMonth}, sometimes it might be $P{BeginYear}, etc.

******************************************************************************************************************

Now for my question - in my report header I want to print the beginning and ending date selections, and sale branch which are determined in the query.  I tried to set a generic parameter called ReportStartDate and ReportEndDate and I thought I could set those values in the query like this:

set $P{reportstartdate}=$P{BeginCurrMonth} --- This needs to be dynamic because I run the report from scheduler and I do not want to go in and maintain these dates each month
select $P{Begin_Date} = $P{BeginCurrMonth}
select invoiceno,invoicedate,billto,billtoname,
partscost as pcost,(partstaxable + partsnontax) as psell,
((partstaxable + partsnontax)- partscost )as pmarg,
(((partstaxable + partsnontax)- partscost )/(partstaxable + partsnontax)) as pmargperc
from invoicereg
where (partscost <> 0 or
(partstaxable + partsnontax) <> 0)
and invoicedate >= $P{BeginCurrMonth}
and invoicedate<= $P{EndCurrMonth}
and salebranch=1
and left(saledept,1)=3

But that did not work - any suggestions on how to set parameter  $P{startdate}=$P{BeginCurrMonth}, etc.?

 

Thanks,

Joe
 

[lucianc]

Have you tried using the default expression of the start date parameter to set its value?  E.g.:

<parameter name="startDate"><defaultValueExpression>$P{BeginCurrMonth}</defaultValueExpression></parameter>

Note that you will have to ensure that the startDate parameter is declared after the BeginCurrMonth parameter.

Regards,

Lucian

[joepmo]

Lucian,

Thanks for the response.  The challenge is that I am making the selection for what the start date will be in the query.

For  example, the first time I run the report the query looks like this:

select invoiceno,invoicedate,billto,billtoname,
partscost as pcost,(partstaxable + partsnontax) as psell,
((partstaxable + partsnontax)- partscost )as pmarg,
(((partstaxable + partsnontax)- partscost )/(partstaxable + partsnontax)) as pmargperc
from invoicereg
where (partscost <> 0 or
(partstaxable + partsnontax) <> 0)
and invoicedate >= $P{BeginCurrMonth}
and invoicedate<= $P{EndCurrMonth}
and salebranch=1
and left(saledept,1)=3

but the second time it looks like this:

select invoiceno,invoicedate,billto,billtoname,
partscost as pcost,(partstaxable + partsnontax) as psell,
((partstaxable + partsnontax)- partscost )as pmarg,
(((partstaxable + partsnontax)- partscost )/(partstaxable + partsnontax)) as pmargperc
from invoicereg
where (partscost <> 0 or
(partstaxable + partsnontax) <> 0)
and invoicedate >= $P{BeginPriorMonth}
and invoicedate<= $P{EndPriorMonth}
and salebranch=1
and left(saledept,1)=3

and the third time the query looks like this:

select invoiceno,invoicedate,billto,billtoname,
partscost as pcost,(partstaxable + partsnontax) as psell,
((partstaxable + partsnontax)- partscost )as pmarg,
(((partstaxable + partsnontax)- partscost )/(partstaxable + partsnontax)) as pmargperc
from invoicereg
where (partscost <> 0 or
(partstaxable + partsnontax) <> 0)
and invoicedate >= $P{BeginCurrYear}
and invoicedate<= $P{EndCurrYear}
and salebranch=1
and left(saledept,1)=3

In each case the report remains exactly the same, just the data filling it changes.   Basically I am looking for a way to fill out the StartDate and EndDate parameters based on whatever I choose as the upper and lower date limits in my query.  So for the first query StartDate would equal BeginCurrMonth, for the second query StartDate would equal BeginPriorMonth, for the third query StartDate would equal BeginCurrYear.  The value changes base on the query. 

I was hoping to set the value of StartDate in the query or in the alternative somehow extract whatever parameter I choose for the lower end of the range and populate the StartDate.

[lucianc]

I'm afraid I don't understand how you intend to choose between the 3 query options.

The only solution that I can think of is to introduce a parameter whose values will be used to switch between the 3 date sets:

<parameter name="Period"/><parameter name="StartDate" class="java.util.Date">  <defaultValueExpression>    $P{Period}.equals("CurrMonth") ? $P{BeginCurrMonth}    ($P{Period}.equals("PrevMonth") ? $P{BeginPrevMonth} : $P{BeginCurrYear})  </defaultValueExpression></parameter>

Then, you would pass a value for the Period parameter when filling the report.

Regards,

Lucian

[joepmo]

Lucian,

Thanks for the suggestion.  Regarding selecting between the 3 queries, what I am trying to do is setup one report template and 3 queries on Jasper Server.  So I would construct 3 report instances on Jasper Server each using the same .jrxml file but different queries.  Then I can run those three reports on different schedules in the scheduler.  The scheduler is the key issue because I am trying to run the reports automatically without user input.

You hit it on the head with this statement:

Then, you would pass a value for the Period parameter when filling the report.

I think this will work for what I am trying to do.  I can pass the period parameter when I setup the report schedule, I just did not have the syntax for setting a parameter from another parameter - also not being a programmer per se, I did not think of the consequence of where the parameter is in the list.  Obviously as the report compiles it cannot set parameter1 from parameter2 if it does not yet know fo the existence of parameter1 because it is lower in the list.

I will give it a try and let you know  - thanks again.

[joepmo]

Lucian,

Just a note of thanks for an awesome solution.  It is working perfectly.  Thanks for all you do for this project.

 

Joe