Subreports run without parameters...(Generating data)

[Crystalboy1]

hi all.

While developing reports, I noticed a very strange problem. Let me explain what I am doing and how.

I got a MainReport, which got 2 SubReport.

The MianReport, I got 1 parameter Rundate, I execute a stored Procedure in the Command line to generate a Report ID. Example Code is given below. (dis is for audit purpose)

DECLARE @REPORTID_EXT AS INT

EXEC [dbo].[usr_BI_Orbit_Report_Audit_Insert]
'ORU1 Arrears KPI Report',
{?Rundate},
'',
'',
'',
'' ,
'' ,
'' ,
'',
@REPORTID_EXT OUTPUT


select @REPORTID_EXT as 'ReportID'


Now SubReport 1.
I pass two parameter from the mainreport to the subreport 1.

Rundate
ReportID (generated above).

Its suppose to run a procedure which will generate data,save in a tableA with the ReportID and show in grouped from in the sub report 1.

SubReport 2. got 1 parameter link
ReportID

It directly read the data (generated in subreport 1 procedure) from the tableA. Do some manipulation with the data and present.

That’s how the report should work.

Problem.
The Problem is, the Procedure in the subreport 1 executes as soon as I run the mainreport. Without the (reportID generated in the mainreport). Which results in an error that some paramters are missing. If I click on cancal or OK. by that time the reportID is generated and the report showes the correct result.

When I looked into the data, the StoredProceudre behind the SUbReport1 seems to be running 2 times, 1 without the ReportID, (which is int so for a null entry its put 0 (zero) and runs … and second time for the right values).

Though the report is returning me the right results but I don’t want it to run twice and use extra resources of the database. Any suggestions welcomed…

 

[Madawc]

Where have you put your subreports? Typically Crystal runs things where it finds them. In one of my reports, I used a subreport to pick up a detail that the main report needed, so I put it first.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[Crystalboy1]

thanks for your reply. well like you said it does matter in crystal where do you put the subreports. so initally i put them in the report header. then i moved them to report Footer. but still it doesnt make any difference.

 

[Madawc]

That makes no sense to me. Can anyone else help?

I would also suggest you use the subreport to display the parameter values, just to check that the problem is what you think it is.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

I know nothing about stored procedures, but I'll venture a guess here. If you are linking in the usual way, then in sub1, there will be a record selection formula that references the linking fields--but the stored procedure you are using in the sub has to run first, before the record selection formula is recognized, so it seems like you need to build the linking fields into the stored procedure used in sub1, although I don't have a clue whether this is possible.

-LB

 

[Crystalboy1]

@Madawc:
i already have tried this, i put in the parameter values to see what does it returns. but like i said,the stored procedure is triggered frist without the ReportID, and then with the reportID. i worked on crystal reports for almost 8 years. and never had this problem with subreports. anyway thanks for your help.

@Ibass.
There are no record selection formulas. We impliment all the business logic at the database level in stored Procedure and retrive the records which we need to display.
Crystal reports is just used to trigger procedures and Presentation of data.

 

[Crystalboy1]

Another strange finding. The reports are published in an Application, (not crystal server). from where the users run there reports. to my surprise it runs just once from the application, only for the right report ID. neither it give the error "the value for 1 or more then 1 parameter is missing".
Conculsion, from 3rd party application when i trigger the report, its run once and generate the data once for the right reportiD.

When i run the same report (same rpt file) directly in Crysal reports, it produces an error about parameter, and t hen show the right data. but at the backend produce 2 set of data. one for the right reportID and 1 for 0(zero) reportID.

strange crystal reports

 

[lbass]

So you link the subs to the main report and if you go into the subreport->report->selection formula, you don't see the linking clause?

-LB

 

[Crystalboy1]

@Ibass.
No there is nothing in subreport->report->selection formula.
and i think this is how it should be.

 

[Madawc]

Originally you said

I pass two parameter from the mainreport to the subreport 1.

Parameters get passed using Edit > Subreport Links, which generate formulas in the subreport selection. You can remove them, but I'd have thought with parameters you should use them that way. It may be the problem, though since I've never written a report with SQL Commands at two levels I can not be sure.



Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[Crystalboy1]

Yes i do link the sub and main report (by right clicking on subreport then subreport links).but by doing so they dont appear in the record selection of the subreport. as there is a stored procedure in the subreport.

 

[Nitmar]

Hi, I have the same problem with a report. Do you resolved the problem? If yes, How?