CODEWITHSUNDEEP
crystal-reports
Michael
Michael

Reputation: 1865

Crystal reports and stored procedures

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.

Upvotes: 0

Views: 3089

Answers (2)

ricky
ricky

Reputation: 1

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. 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'.

Upvotes: 0

theo
theo

Reputation: 8921

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.

Upvotes: 2

Related Questions