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
637 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
  • 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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 34

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 34

Expert Comment

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

 James
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now