We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Step-By-Step Needed - Crystal Report XI Main report passing parameter to subreport that uses a stored procedure

Medium Priority
663 Views
Last Modified: 2012-05-07
I have a complex Crystal Report to do against 2 tables that have no distinct relationship.  In order to get the information out of the 2nd table, I created a stored procedure that accepts a parameter.  I then created a report that pulls the 1st tables information.  I created a sub-report that calls the stored procedure.  However I do not know how to take the linked filed from the main report and pass it into the stored procedure.

Can someone please provide me with some step-by-step instructions on how to pass the linked field to the stored procedure?
"USE [APTIFY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Bill Mannion
-- Create date: 7/6/09
-- Description: Survey Report Data Gather for Selection Criteria
-- =============================================
Create PROCEDURE 
[dbo].[spGetGHCSurveyInfo] (@MeetingTitle VarChar(150) )AS
BEGIN
 SET NOCOUNT ON;
  select * from vwKnowledgeResultDetails
 Where KnowledgeResultID in (select vwKnowledgeResultDetails.KnowledgeResultID 
  from vwknowledgeResultDetails 
   where vwKnowledgeResultDetails.KnowledgeAnswerValue LIKE '%'+@MeetingTitle+'%')
END
GO"

Open in new window

Comment
Watch Question

From the main report, right click on the sub and choose Edit Sub Report Parameters.  Select the field with the MeetingTitle.

In the sub report, you will now have a parameter field with a name something like {?PM-MeetingTitle}
Use that field as the parameter to the stored procedure.
correction, that should be "Change subreport links", not Edit Sub Report parameters.

Author

Commented:
Thanks, got that part.
Need the next set of steps to use {?PM-MeetingTitle} to be passed to the stored procedure.  Right now the sub-report is prompting for the user to enter a value for the parameter.  So I have not completed the linking fully somehow.
Did you allow it to create the parameter field automatically? (i.e., you did not go into the sub and choose New Parameter Field)  If the only Parameter Field in the sub is the one passed from the main report, it should not ask.  If it is, there must be something about the field from the main report that you are passing--is it a regular data field?  where is the sub located on the main report (header, detail, etc)?
what is the exact statement you have for executing the proc?

Author

Commented:
It is a regular data field displayed on the main report.  The sub-report is located in the Group Header #2, which groups on vwknowledgeAnswersText.  The Group 1 is based on vwMeetings.Title.  So what you are suggesting, if I am reading this write, is that the sub-report should be Group 1, where the title linking is being done.  This should in turn link the meeting title to the sub-report parameter?

Below is the executing of the sub-report.  Where "E1" is the value I entered when prompted.

Why am I starting to think that I have done something simple, very wrong.....
APTIFY
{CALL "APTIFY"."dbo"."spGetGHCSurveyInfo";1('E1')}
 EXTERNAL JOIN spGetGHCSurveyInfo;1.KnowledgeResultID={?APTIFY: vwKnowledgeResultDetails.KnowledgeResultID}
 
 
APTIFY
 SELECT "vwKnowledgeAnswers"."Text", "vwKnowledgeResultDetails"."KnowledgeAnswerID", "vwKnowledgeResultDetails"."KnowledgeAnswerID_Name", "vwKnowledgeResultDetails"."ID", (Case when vwKnowledgeResultDetails.KnowledgeAnswerValue = '3' 
Then 1 else 0 
end)
, (Case when vwKnowledgeResultDetails.KnowledgeAnswerValue = '1' 
Then 1 else 0 
end)
, (Case when vwKnowledgeResultDetails.KnowledgeAnswerValue = '2' 
Then 1 else 0 
end)
, (Case when vwKnowledgeResultDetails.KnowledgeAnswerValue = '4' 
Then 1 else 0 
end)
, (Case when vwKnowledgeResultDetails.KnowledgeAnswerValue = '5' 
Then 1 else 0 
end)
, "vwKnowledgeResultDetails"."KnowledgeResultID", "vwKnowledgeResultDetails"."KnowledgeAnswerValue"
 FROM   "APTIFY"."dbo"."vwKnowledgeResultDetails" "vwKnowledgeResultDetails" INNER JOIN "APTIFY"."dbo"."vwKnowledgeAnswers" "vwKnowledgeAnswers" ON "vwKnowledgeResultDetails"."KnowledgeAnswerID"="vwKnowledgeAnswers"."ID"
 WHERE  "vwKnowledgeResultDetails"."KnowledgeResultID"={?APTIFY: spGetGHCSurveyInfo;1.KnowledgeResultID}
 ORDER BY "vwKnowledgeAnswers"."Text"

Open in new window

I didn't mean to suggest anything yet, just making sure I understand.  Your approach is a little different than most of my experience, but if I am understanding this correctly, can you replace 'E1' with '{PM-MeetingTitle}'?

Author

Commented:
There in lies the problem. This is what I need to do,   I can not figure out how to do that.  If the linking of {PM-MeetingTitle} from the main report to the sub-report should have done it during the creation of the sub-report, what happened to it?  

More importantly, how do I correct it?  

The sub-report has all the fields and formulas working correctly, I don't want to restart from scratch and by recreating the sub-report.
I guess where I am losing you is on "the executing of the sub-report".  I am expecting the sub report to be saved within the main report.  If I had created the sub as a separate rpt file, then in the main report I would choose "insert sub report", and then Select Existing Report.  Then, when I add the link from the main it creates it automatically in the sub.  

Author

Commented:
The sub-report is saved within the main report.  Does that help?
I'm not familiar with the syntax of that last code snippet (other than the sql).  Where did you copy that from?

Rereading, I realize you said E1 was the value contained in the parameter, not the name of it.  Do you have an Exec statement somewhere in crystal where you pass that parameter by name?

Author

Commented:
No.

Leaving for the weekend, as I have not seen my wife in a few days.  Will pick this up on Monday.

Thanks so much for walking through this mess with me.
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Indeed I was imposing an incorrect parameter of ?PM and not selecting the ?@ parameter.  As I was starting to think, I was doing something simple, very wrong.  Now I have to just adjust the stored procedure to complete some scoring calculations.  Thank you very much for the simple to follow instructions.
CERTIFIED EXPERT

Commented:
You're welcome.  Glad I could help.

 James
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.