Passing parameters from one sp to another  Topic is solved

Support for Crystal Reports within Jiwa.

Passing parameters from one sp to another

Postby Atronics » Tue May 28, 2019 12:28 pm

I have a simple report which is driven by a sp to deliver average invoice values. The report has a sub-report to deliver credit note values. I am unable to get the sp parameters which are used in the main report to pass to the sub report which uses a similar sp. The report and sp's are attached.
Attachments
usp_JohnValves_CreditNotesAverageSell.sql
(6.67 KiB) Downloaded 350 times
usp_JohnValves_InvoiceAverageSell.sql
(13.3 KiB) Downloaded 342 times
JohnValves - Average Invoice Value.rpt
(41.5 KiB) Downloaded 359 times
Atronics
Frequent Contributor
Frequent Contributor
 
Posts: 119
Joined: Fri Feb 29, 2008 4:40 pm
Topics Solved: 10

Re: Passing parameters from one sp to another

Postby Scott.Pearce » Tue May 28, 2019 12:55 pm

Here is what I did. I *think* it's working OK but have not tested thoroughly.

1. Right-click on subreport and choose "Change subreport links..."
2. Remove all fields in the right hand pane.
3. Add the following fields to the right hand pane:

@sp_DateFirst
@sp_DateLast
@sp_ClassificationFirst
@sp_ClassificationLast
@sp_AccountNoFirst
@sp_AccountNoLast

NOTE: These are the crystal generated parameter fields, NOT the formulae you created that are prefixed with "PASS_"!

Now change the "Subreport parameter field to use:" value for each field to link to thus:

@sp_DateFirst - ?@sp_DateFirst
@sp_DateLast- ?@sp_DateLast
@sp_ClassificationFirst - ?@sp_ClassificationFirst
@sp_ClassificationLast - ?@sp_ClassificationLast
@sp_AccountNoFirst - ?@sp_AccountNoFirst
@sp_AccountNoLast - ?@sp_AccountNoLast

I've attached the modified report, and have also attached fixed sql scripts, as they were failing when there was no data being returned (The insert in the stored proc for when there is no data was erroring with "Column name or number of supplied values does not match table definition")
Attachments
JohnValves - Average Invoice Value.rpt
(48 KiB) Downloaded 342 times
usp_JohnValves_InvoiceAverageSell.sql
(12.92 KiB) Downloaded 354 times
usp_JohnValves_CreditNotesAverageSell.sql
(6.47 KiB) Downloaded 356 times
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Passing parameters from one sp to another  Topic is solved

Postby Atronics » Tue May 28, 2019 1:19 pm

Hi Scott,
Thank you for your recommendations and rapid response. Much appreciated.
John I.
Atronics
Frequent Contributor
Frequent Contributor
 
Posts: 119
Joined: Fri Feb 29, 2008 4:40 pm
Topics Solved: 10


Return to Crystal Reports

Who is online

Users browsing this forum: No registered users and 1 guest

cron