Returning values from subreports

[swhitt]

I am using CR 8.5 on an Oracle 8i database. I have a main report which gives me all of the values that I need except for one which is the "Available Credit". I have determined that I need to use 2 subreports and find the difference between these to derive the "Available Credit". Also, one of the subreports consists of a subquery. The first subreport is named "Unpaid Invoice Fees". The second is named "Unbilled Permit Fees". "Available Credit" = sum("Unpaid Invoice Fees") - sum("Unbilled Permit Fees").
I'm not sure of how to derive the values of the two subreports to subtract them. Also the sql statement for "Unbilled Permit Fees" consists of a subquery.
Eg: select *
from contrctr, liccntct, aptrans
where contrctr.contid = liccntct.contid
and liccntct.lic = 'BONDED'
and liccntct.inactvflag = 'N'
and contrctr.contid = aptrans.contid
and contrctr.escrowno = aptrans.escrowno
and aptrans.trncat = 'E'
and aptrans.trntype = 'P'
and aptrans.stat = 'P'
and aptrans.trnno not in (select varbillpay.trnno
from varbillpay)
I'm not sure of how to deal with a subquery in a subreport. If anyone could offer me some advice on these 2 issues I would greatly appreciate it.

 

[synapsevampire]

How can you already have a subreport based on a subquery and not know how to build a subquery?

Perhaps what you're saying is that you're going to use this architecture and that you're not interested in other approaches?

Anyway, I would avoid using subreports if possible, but if you want to use subreport values in a main report construct shared variables in the subreports, they will be available back in the main report (or other subreports) after the subreports have fired:

example:

whileprintingrecords;
shared numbervar MyNum := 1000

This would set a value for MyNum which would be available to all subsequent sections/subreports.

If you'd like to consider other approaches, try posting technical information:

Example data (show what's in the database)
Expected output

You can create a subquery within a Crystal report by using a SQL Expression, or you can paste in SQL if you use the More Data Cources AOD/RDO/ADO, it's fairly tricky, and is dependent upon the connectivity.

You're probably best served to create Views or Stored Procedures on the database.

-k

 

[fredp1]

swhitt,
As synapsevampire pointed out, you can not have a subquery within a sub report.
Depending on how you link to the subreports, you can do this with SQL Expressions. As long as you are using constants and fields from the main report, you can do it. If you need to link to formula fields or parameters, then this will not work. The beauty is that this works much fast than subreports.

Try this,
Create a SQL Expression say {%sqlUnBilled Permit Fees}
Type in the sql then is needed to return the value for one record only. e.g.
(Select sum(unpaidpermitfees) from contrctr, liccntct, aptrans
where contrctr.contid = liccntct.contid
and liccntct.lic = 'BONDED'
and liccntct.inactvflag = 'N'
and contrctr.contid = aptrans.contid
and contrctr.escrowno = aptrans.escrowno
and aptrans.trncat = 'E'
and aptrans.trntype = 'P'
and aptrans.stat = 'P'
and aptrans.trnno not in (select varbillpay.trnno
from varbillpay)
and contrctr.conid=ALIAS_IN_MAINREPORT.conid
)
Note the last line that I added which links the record on the main report to the sql Expression.

Do the same for the first formulae and add it to the report.
Now you can do the calcs as a normal expression.
Look at the SQL generated to see the results.
Powerful stuff.

You also need to provide more details in future for a more accurate solution.

Have a look at this posting for a similar question/solution.

http://www.tek-tips.com/viewthread.cfm?SQID=768275&SPID=767&newpid=767&page=1

Cheers
Fred