For Example,Having the tables as as shown below.
Table_1 which is of faculty information,
Faculty_ID Faculty_Name Father_Name
1155 Raju Ramnath
1166 Ramu Somnath
1177 Anil Srinivas
Table_2 work experiance of faculty,
Faculty_ID Work_Experiance
1155 5
1166
1177 4
So,I want create a report in such a way that as shown below,where if the data not exist it should show me an empty field
Faculty_ID Faculty_Name Father_Name
1155 Raju Ramnath
Work_Experiance
5
Faculty_ID Faculty_Name Father_Name
1166 Ramu Somnath
Work_Experiance
Faculty_ID Faculty_Name Father_Name
1166 Ramu Somnath
Work_Experiance
4
I had tried by creating the report in such a way that ,I had defined Faculty information in main report and work experiance in sub report.
But,I had got strucked in creation of parameters,I had tried with creating the parameters but the work experiance which is getting repeated to all the faculties if there exist or not.
I am not at all getting how to map the fields from main report to sub report.
Please, help me in creating this report and if the data not exist in sub report it should show me an empty data.
If any help,Thank's in advance.
2 Answers:
Hi Sumit
In Subreport create two parameters as
Department
Employee
In Main Report Do the below steps
1.From main report click on sub report ( Don't open sub report).
2. Right side sub report properties is there in that click on parameters
3. Click on add Give me the parameter Name as Department ( Same as Sub report Parameter Name).
Click on the variable expression ( Right side you will find like editor symbol).
click on fields select what ever field you want to pass the above parameter name ( Here You can pass hr_employee_setup_department_id this field).
4. Same steps for Employee
in the place of Department pass as Employee and in the place of hr_employee_setup_department_id pass as hr_employee_hr_employee_id
then click on Apply--> o.k --> o.k
I think right now you will understand.
I think the below line is wrong in your sub report query
WHERE hr_employee."setup_department_id" = $P{Department} and hr_employee."setup_department_id" = $P{Employee}
both places you are checking setup_department_id for Employee and Department condition. Change that line like below.
WHERE hr_employee."setup_department_id" = $P{Department} and hr_employee."hr_employee_id" = $P{Employee}
Regards
Surendra Reddy M
Hi Surendra,
Thank's for your kind response.
Please,If possible can you explain using an any example it is not clear at the point of variable expression and I am unable to know what are the parameters should pass in main report and sub report.
For the clear explaination the below are the queries which I am trying to create a report.Can i have an explaination in what way the parameters should I pass.
THE Below is the Main report query which I am using,
SELECT
AD_Column_Identifier('setup_department',hr_employee.setup_department_id,'en_US') AS hr_employee_dept,
hr_employee."hr_prefix" AS hr_employee_hr_prefix,
hr_employee."hr_firstname" AS hr_employee_hr_firstname,
hr_employee."hr_lastname" AS hr_employee_hr_lastname,
hr_employee."hr_father_name" AS hr_employee_hr_father_name,
hr_employee."hr_gender" AS hr_employee_hr_gender,
hr_employee."hr_dob" AS hr_employee_hr_dob,
hr_employee."hr_age" AS hr_employee_hr_age,
hr_employee."hr_desig" AS hr_employee_hr_desig,
hr_employee."hr_email" AS hr_employee_hr_email,
hr_employee."hr_mobile" AS hr_employee_hr_mobile,
hr_employee."hr_jntuhid" AS hr_employee_hr_jntuhid,
hr_employee."hr_doj" AS hr_employee_hr_doj,,
hr_employee."hr_address" AS hr_employee_hr_address,
hr_employee."hr_employee_id" AS hr_employee_hr_employee_id,
hr_employee."setup_department_id" AS hr_employee_setup_department_id
FROM
"public"."hr_employee" hr_employee
where
hr_employee."setup_department_id"=$P{Department_ID}
THE Below is the Sub report query which I am using,
SELECT
hr_define_publctn."hr_type_publictn" AS hr_define_publctn_hr_type_publictn,
hr_define_publctn."hr_title" AS hr_define_publctn_hr_title,
hr_define_publctn."hr_journal_conf" AS hr_define_publctn_hr_journal_conf,
hr_define_publctn."hr_publication" AS hr_define_publctn_hr_publication,
hr_define_publctn."hr_employee_id" AS hr_define_publctn_hr_employee_id,
hr_employee."hr_employee_id" AS hr_employee_hr_employee_id,
hr_employee."setup_department_id" AS hr_employee_setup_department_id
FROM
"public"."hr_employee" hr_employee INNER JOIN "public"."hr_define_publctn" hr_define_publctn ON hr_employee."hr_employee_id" = hr_define_publctn."hr_employee_id"
WHERE hr_employee."setup_department_id" = $P{Department} and hr_employee."setup_department_id" = $P{Employee}
Thank's in advance.
Hi Sumit
Check Below Reply with your eample.
Regards
Surendra
Thank's a lot for your kind responce.
1.For the parameters in subreport what are the value expression should I define.
2.I had tried in what way you had guided me but I didn't get what the result I had expected.
3.Please,If you dont mind does can i have any direct interaction with you where i can resolve my Issues much faster and better whith these issues.I am struggling since last 2 weeks.Hope to hear a good responce from you.
surendra29
Hi, am using Jasper Studio I am also having same problem not getting the way to pass a field from master report to sub report , I am using Jasper Open source,i think this facility is not available in open ssource