recursive stored procedure and crystal reports

[The1dono]

Hi,
I have a recursive stored procedure, that returns all the children of a parent record (which is a question) in my database.
Refer to the bottom of this question to see the output of the stored procedure.

This is just a simple example. To put this in context, I want a report to show the question hierarchy with all associated answers.

--------MY PROBLEM--------

For some reason CR only returns the first record (ie. QuestionID = 251). I have a feeling this is something to do with recursion and that each "Nested level" is a new temp table (I think anyway)

How can I get CR to show all the records.

I've also included the stored procedure that I'm using for anyone who is interested.(search for "zen of recursion" at msdn.microsoft.com for more info)

Thanks Don

This is what is returned:

QuestionID Question ParentID
251 1_What type of Internet connection? 249
--------------------------------------------------------
QuestionID Question ParentID
252 2_56K or 64k 251
--------------------------------------------------------
QuestionID Questio ParentID
261 1_Apple Mac or PC 249
--------------------------------------------------------
QuestionID Question ParentID
262 2_Will these computers ... 261
--------------------------------------------------------
QuestionID Question ParentID
264 2_How many computers will be... 261

No more results.
(5 row(s) returned)
@RETURN_VALUE = 0
Finished running dbo."sp_SimpleRecurse".



Stored Procedure:

ALTER PROCEDURE sp_SimpleRecurse
@QuestionID int
AS
DECLARE @Question char(255)
DECLARE @ParentID int
DECLARE @Rank char(255)
DECLARE cur_Level CURSOR LOCAL FOR
SELECT

QuestionID AS ID,
Question,
ParentID

FROM BP_Question
WHERE ParentID = @QuestionID
OPEN cur_Level
FETCH NEXT FROM cur_Level INTO @QuestionID, @Question, @ParentID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @QuestionID AS QuestionID, CONVERT(VARCHAR(5),@@Nestlevel)+'_' + @Question AS Question , @ParentID AS ParentID
EXEC sp_SimpleRecurse @QuestionID
FETCH NEXT FROM cur_Level INTO @QuestionID, @Question, @ParentID
END
CLOSE cur_Level
DEALLOCATE cur_Level

 

[The1dono]

After alot of thinking,
I thought of designing a report and then inserting itself as a sub-report.
How do I set the sub-report QuestionID = main report ParentID.

Is this possible?

Because this might work - but I don't know how :-(
Cheers Dono

 

[dgillz]

You can do this, but a subreport cannot have a subreport, so you only get two levels of the report, not a true recursive report.

I am not clear from your example what it is you want, but have a look at hierarchecal grouping options in the help files and see if this helps you. This is the only kind of recursive report that you can get with Crystal that I am aware of. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com