Crystal reports and stored procedures

Question

I'm new at Crystal Reports and still learning so I'm wondering how I should do this. I have the following stored procedure:

CREATE PROCEDURE GetSurveyAnswerDetail
(@Question VARCHAR(255) = NULL, @AllowReportFlag CHAR(1) = NULL)
AS
    SET NOCOUNT ON
    DECLARE @rc INT
    SET @rc = 1

    IF (@Question IS NULL OR DATALENGTH(@Question) = 0
     OR @AllowReportFlag IS NULL OR DATALENGTH(@AllowReportFlag) = 0)
     RAISERROR('GetSurveyAnswerDetail is missing parameters.', 16, 1)
    ELSE
     BEGIN
      DECLARE @AllowReport VARCHAR(100)
      IF (@AllowReportFlag = 'N')
       SET @AllowReport = ' AllowReport = ''Y'' AND '
      ELSE
       SET @AllowReport = ''

      DECLARE @SQLStatement VARCHAR(5000)
      SET @SQLStatement = 'SELECT COUNT(' + @Question + ') FROM tblSurveyAnswer WHERE ' + @AllowReport + @Question + ' != '''' GROUP BY ' + @Question + ' ORDER BY ' + @Question + ' DESC'
      EXEC (@SQLStatement)

      IF @@ERROR <> 0
       RAISERROR('GetSurveyAnswerDetail has failed. Question may not exist.', 16, 1)
      ELSE
       SET @rc = 0
     END
RETURN @rc
GO

This returns a list of numbers. What I'd like to do is create a pie chart from these numbers in Crystal Reports. I know you can set your data source from a stored procedure but when I do this, there are no fields I can choose. I'm probably going about this the wrong way so I'd appreciate any comments.

Answer 1

Michael, try making your stored procedure return a table. My guess is that you don't see any fields because you aren't returning any. Change the return line to

SELECT @rc AS Result

This should show you the Result as a field in the Report. I'm not sure that you can run the proc individually on a loop to manufacture a pie-chart. You may need to return all the data you want out of one stored procedure.

Answer 2

The answer is very silly... And no one seems to know. The answer is There MUST be a Select Query that is NOT hiding behind an IF statement. If you stored procedure is: If @question = 'Y' begin SET @SQLStatement = 'select field from table where condition='OK' EXEC (@SQLStatement)

end In this example NO fields will be shown... so change it to:

If @question = 'Y' begin SET @SQLStatement = 'select field from table where condition='OK' end else Begin select field from table where condition='impossiblecondition' end

In this example it WILL work and fields will be shown. I create a dummy parameter like @question and pass the 'Y'...