Solved

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

Posted on 2009-07-10
14
640 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

0
Comment
Question by:globalhealthcouncil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
14 Comments
 
LVL 5

Expert Comment

by:jgbreeden
ID: 24825948
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
 
LVL 5

Expert Comment

by:jgbreeden
ID: 24825992
correction, that should be "Change subreport links", not Edit Sub Report parameters.
0
 

Author Comment

by:globalhealthcouncil
ID: 24825993
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:jgbreeden
ID: 24826313
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
 

Author Comment

by:globalhealthcouncil
ID: 24826411
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
 
LVL 5

Expert Comment

by:jgbreeden
ID: 24827353
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
 

Author Comment

by:globalhealthcouncil
ID: 24827399
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
 
LVL 5

Expert Comment

by:jgbreeden
ID: 24827433
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
 

Author Comment

by:globalhealthcouncil
ID: 24827461
The sub-report is saved within the main report.  Does that help?
0
 
LVL 5

Expert Comment

by:jgbreeden
ID: 24827543
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
 

Author Comment

by:globalhealthcouncil
ID: 24827584
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
 
LVL 35

Accepted Solution

by:
James0628 earned 250 total points
ID: 24832584
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
 

Author Closing Comment

by:globalhealthcouncil
ID: 31602200
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
 
LVL 35

Expert Comment

by:James0628
ID: 24848224
You're welcome.  Glad I could help.

 James
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question