Parameterized Reports

[sammiebone]

I have this parameterized report that i am working on(SQL Script below), anytime i preview the report in iReport with the CORRECT working values i get result,but anytime i used parameters and i supply the Correct values at run time, it tells me "The Document has no Pages"...I dont know what i am getting wrong. I need help asap,Please.

SQL Query

SELECT  v.Licensee AS Licensee_ID,
                vb.Visit AS Certificate_No,
                vb.Visit,vb.Baby, g.Gender,vb.Dad,e.Full_Name,
                vb.Born, vb.weight,  vb.Creator,
                User_Name AS Attending_Physician,
                vb.Created, v.Enrolee,
                TRIM(l.Licensee) AS Licensee, l.Licensee_Address ,
                IFNULL(l.Contact_Mobile_Phone, l.Contact_Business_Phone) AS Licensee_Address
        FROM   (Enrolee AS e INNER JOIN (
                Licensee AS l INNER JOIN
                Visit AS v ON l.Id = v.Licensee) ON e.LId = v.Enrolee) INNER JOIN (
                `User` AS u INNER JOIN (
                Gender AS g INNER JOIN
                Visit_Birth AS vb ON g.Id = vb.Gender) ON u.HId = vb.Creator) ON v.LId = vb.Visit
        WHERE   v.Licensee= $P{Licensee} AND
                DATE_FORMAT(v.Visit_date,'%Y-%m') IN ($P{months}) AND
                vb.Visit LIKE '%$!P{Visit}%' AND
                vb.Include_YN=True

[sofiano]

I have approximatively the same problem..

[braxivamov]

Hi, your parameters have a String type ? You inserted the correct values in the 'default value' of the parameters ? when u try to 'preview data' from query editor u get something ?

[despec]

There is a special parameter symbol in Ireport--the $X parameter as described in this excerpt from the Ultimate guide:

 

6.2.2 IN and NOT IN clause

 

JasperReports provides a special syntax to use with a where condition: the clause IN and NOT IN. The clause is used to check whether a particular value is present in a discrete set of values. Here is an example:

 

SELECT * FROM ORDERS WHERE SHIPCOUNTRY IS IN ('USA','Italy','Germany')

 

The set here is defined by the countries USA, Italy and Germany. Assuming we are passing the set of countries in a list (or better a java.util.Collection) or in an array, the syntax to make the previous query dynamic in reference to the set of countries is:

 

SELECT * FROM ORDERS WHERE $X{IN, SHIPCOUNTRY, myCountries}

 

where myCountries is the name of the parameter that contains the set of country names. The $X{} clause recognizes three

parameters:

 

Type of function to apply (IN or NOT IN)

Field name to be evaluated

Parameter name

 

JasperReports will handle special characters in each value. If the parameter is null or contains an empty list, meaning no value has been set for the parameter, the entire $X{} clause is evaluated as the always true statement “0 = 0â€.

 

 

Hope this helps,

 

David

[kolaitis]

WHERE   v.Licensee= $P{Licensee} AND

                DATE_FORMAT(v.Visit_date,'%Y-%m') IN ($P{months}) AND

                vb.Visit LIKE '%$!P{Visit}%' AND

                vb.Include_YN=True

try this format : vb.Visit LIKE '%$P!{Visit}%'

I hope it will works.