Need suggestion on solving the prarameter problem using 2 DB?

[rajrev]

Hi,
We are using CR 8.5 / DB2 / SQL server.

we don't care about the performance.

Because of the linking 2 diff database in the main report, we go with subreport.
(ie) the main report contains only the SQL database tables linked using left outer join.(sql server tables only for hanldling the parameters )

For example, consider sql server tables;
1. EmpMain
- Email
- EmpID

2. EmpExp
- Email
- Position
- YearsofExp
These two tables are linked with Email (not EmpID)using Left Outer join.

Parameters
- Position
- EmpID

Farmula:
(
if ({?EmpID}="ALL") then
true
else
{?EmpID}={EmpMain.EmailID}
)
and
(
if ({?Position}="ALL" ) then
true
else
{?Position}={EmpExp.Position_Name}
)

Group by:
EmpMain.EmpID (and place the subreport in the groupfooter)

------------------------------------------------------
In the supreport:

Tables :
1. EmpMain
- EmpID
- Email
2. EmpDB2
- EmpID
- Name
- DOB
- Title
- Degree

These two tables are linked with EmpID. Of course inner join.

Parameters:
- Title
- Degree
- EmpID (Copied from Main reprot)

Farmula:

?EmpID = EmpMain.EmpID
and
(
if ({?Title}="ALL") then
true
else
{?Title}={EmpDB2.Title}
)
and
(
if ({?Degree}="ALL" ) then
true
else
{?Degree}={EmpDB2.Degree_Name}
)
-----------------------------------------------------
Files selected for output (in the subreport)

1. EmpDB2.Name 2. EmpDB2.Degree

The subreport also grouped by EmpMain.EmpID and placed the above output columns in the group footer.

-----------------------------------------------------
And from the main report I right the subreport and change subreport link... and select EmpMain.EmpID and from the drop down I select ?EmpID.
------------------------------------------------------
Now, I got the output of the report using the parameter filter. BUT I am getting the BLANK lines, because of the parameter codition.
ie for example if i gave

EmpID - ALL
Postion - ALL
Title - ALL
Degree - Master's, LAW

the output is :

Name Degree
---------------------------
xxxx Master's
uuuu Master's
rrrrr LAW
eee Master's


dddd LAW


oooo Master's
iiii Master's
...


I need your help on hide the blanks. By the way I am not sure I'm using the correct formula?

(NOTE : I tried the suppressing the subreport blank artical, I hope that is not for the above problem, because in my view, it is seems to be formula problem)

Please guide me to fix the problem.
Need more info. please let me know.

Thanks,
MK

 

[rajrev]

please let me know if you need more information

 

[synapsevampire]

Consider that you're reversed the links based on how you intend to use the data.

If the DB2 database was the main report, then you wouldn't have this problem.

As designed, you're pulling data in the main report (which you don't need), and then not finding a match in the subreport, hence the blanks, so depending upon where the subreport is firing you'd have to prefire the subreport in the group header to learn if it matches the criteria, but that's ill advised, redesign the report to match requirements.

-k