Stored procedure confusion.

[desperateUser]

----this is cross-posted to the CR Data Access forum also---

If I have a SQL stored procedure providing data for a CR 9 report are the parameters in the stored procedure available for the report?

The stored procedure calculates a date range. Let's say @StartDate and @EndDate. Then it retrieves records for the date range. It puts @StartDate and @EndDate in the record itself, then the report uses those dates in the report header. It all works great - until there are no records for that week. Then my header is empty = no dates.

Is it possible to use the @StartDate and @EndDate parameter even though SQL is "done" with the query? I've tried to create a formula to use the parameters but I'm missing something.

These reports are scheduled on Crystal Enterprise; the stored procedure is so the report is automated. It just runs every Monday. I have a formula that returns the text "There are no records for the last week." in the group footer if there aren't any results. I just can't figure out how to get those parameter dates in the report header.

Any help is appreciated. Let me know if more code or example would help.

Thanks!
Penelope

 

[Madawc]

You could do a cut-down version of the Stored Procedure that just calculates the date. If you're not allowed to have two Stored Procedures in the same report for Crystal 9, it could go in a subreport in the report header.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10