Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Crystal 9.0 and stored procedures for SQL Server 2000

Posted on 2011-09-30
Medium Priority
Last Modified: 2012-08-14
I have a stored procedure that I want to execute from crystal.  This procedure truncates a table, inserts records into the table and exits.  I then want to use the content of the table in Crystal?

How do I get the SP to execute first?
CREATE PROCEDURE [DBO].[sp_StudentEvaluationSummary]

@product_code as varchar(10),
@begin_date as datetime,
@end_date as Datetime

--set @product_code = '2120141P01'
--set @begin_date = '09/23/2011'
--set @end_date = '9/23/2011'

--select mm.meeting,mm.begin_date into #tmp1 from meet_master mm where left(mm.meeting,7) = left(@product_code,7) and
--		mm.begin_date between @begin_date and @end_date 
--select * from #tmp1
--drop table #tmp1
--drop table #tmp1
select mm. meeting,mm.begin_date 
into #tmp1
from meet_master mm where left(mm.meeting,7) = left(@product_code,7) and
		mm.begin_date between @begin_date and @end_date 

--select * from #tmp1

--drop table #tmp2
-- Set up variables

truncate table summaryevalreport
-- Define cursor
	select meeting
	from #tmp1
-- Open Cursor

	OPEN program
-- Fetch loop
	FETCH NEXT FROM program into @product_code
	insert into summaryevalreport
		select 	left(mm.meeting_type,2) as PA,
		mm.meeting as SeminarNumber,
		mm.muf_8 As Program,
		mm.muf_6 as hours, as Location,
		(select count (id) from activity where product_code = @product_code and activity_type = 'VOLUNTEER') as faculty_count,
		(mm.total_registrants - mm.total_cancelations) as Student_count,
		(select count(product_code) from student_evals where @product_code = product_code) as number_evals,
		(select count(product_code) from student_evals where @product_code = product_code and rating = 1) as Poor,
		(select count(product_code) from student_evals where @product_code = product_code and rating = 2) as Fair,
		(select count(product_code) from student_evals where @product_code = product_code and rating = 3) as Good,
		(select count(product_code) from student_evals where @product_code = product_code and rating = 4) as Excellent,
		(select count(product_code) from student_evals where @product_code = product_code and rating not in (1,2,3,4)) as NoAnswer,
		(select count(product_code) from student_evals_faculty where @product_code = product_code and cast(comment as varchar(250)) <> '') as facultyevalcount,
		(select sum(rating) from student_evals_faculty where @product_code = product_code) as sumrating

		from meet_master mm
		where mm.meeting = @product_code and
		mm.begin_date between @begin_date and @end_date
	FETCH NEXT FROM Program into @product_code

	CLOSE program
--	select * from summaryevalreport

Open in new window

Question by:Jeff_Kingston
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
  • 4
  • 4
LVL 101

Expert Comment

ID: 36892536
Did you try using the SP as the data source?

That is the only way with Crystal by itself to execute an SP.

One idea would be to run the report from an application.  The pplication could execute the SP before calling the report.

LVL 35

Expert Comment

ID: 36896047
The obvious thing would be to just have the SP output the table at the end and try using that as the datasource, as mlmcc suggested.  No guarantees, but that might work.  I've never tried to do something like what you're doing in a SP for a report.  You may need to turn row counts off at the beginning of the SP.  CR seems to have problems if a SP produces messages like row counts or warnings before the actual data.

 A slight variation on that would be to create a new SP for the report that exec'd that SP, and then used a SELECT to output the table.  Again, no guarantees, but it might work.  That way you don't have to change the existing SP to output data at the end, which would be useful if you want to use it in other places where you don't want that output.

 If CR 9 supports Commands (where you create the query manually in CR), then you might be able to create a Command that exec'd the SP and then had a SELECT to output the table.  Again, not something that I've tried.

LVL 101

Expert Comment

ID: 37058261
I've requested that this question be closed as follows:

Accepted answer: 250 points for James0628's comment http:/Q_27373784.html#36896047
Assisted answer: 250 points for mlmcc's comment http:/Q_27373784.html#36892536

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 37056947
Neither worked, I rewrote the entire process and have solved the problem
LVL 101

Expert Comment

ID: 37058262
What did you do to reqrite the process?


Author Comment

ID: 37082482
Please close this question

Accepted Solution

Jeff_Kingston earned 0 total points
ID: 37084079
I wrote a sp that gets run every 4 hours an puts data into a truncated table which is used in Crystal

Klutzy I know but it is doing the job.

We had control of batch jobs and wait for completion options with VMS in 1984! when the hell is microsoft and their supporting theiving minions going to get on the train!!!
LVL 101

Expert Comment

ID: 37084113
You should be able to accept your last comment as the solution.


Author Closing Comment

ID: 37105844
a work around

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

688 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