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

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

globalhealthcouncilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jgbreedenCommented:
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.
0
jgbreedenCommented:
correction, that should be "Change subreport links", not Edit Sub Report parameters.
0
globalhealthcouncilAuthor 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.
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

jgbreedenCommented:
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?
0
globalhealthcouncilAuthor 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

0
jgbreedenCommented:
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}'?

0
globalhealthcouncilAuthor 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.
0
jgbreedenCommented:
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.  
0
globalhealthcouncilAuthor Commented:
The sub-report is saved within the main report.  Does that help?
0
jgbreedenCommented:
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?
0
globalhealthcouncilAuthor 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.
0
James0628Commented:
It is not possible to link one parameter in the subreport ({?PM-MeetingTitle}) to another parameter in the subreport (the one for the stored procedure).

 Subreport parameters that start with "{?PM-" are created automatically by CR when you link a field to the subreport, but _not_ to an existing parameter in the subreport.  When you add a subreport link, CR defaults to using the "Select data in subreport based on field" option, which creates a new parameter in the subreport and attempts to filter the data by comparing that new parameter to a field in the subreport.

 What you need to do is go to the "Subreport Links" dialogue, select the field under "Field(s) to link to" (if it's not already selected), click on the arrow in the lower left, under "Subreport parameter field to use", to get the dropdown list, and the stored procedure parameter should be in that list.  Select it, and the field in the main report should now be linked to the stored procedure parameter in the subreport.  If the parameter is not in the dropdown list, the field that you're linking and the subreport parameter are different data types (eg. numeric and string).  In that case, you can use a formula in the main report to convert the field to the same data type as the subreport parameter and then link that formula to the subreport parameter.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
globalhealthcouncilAuthor 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.
0
James0628Commented:
You're welcome.  Glad I could help.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.