Create a SQL report from a Stored Procedure

[LonC25]

I hope someone can help me with this. I am sure its probally easy but I am a newbie.

I have a stored prodcedure that prompts me to enter a specific Order Number, and then shows me the results I need to see. However its in "table form". My boss needs it in report form.

I can create a report from a view, but that doesn't prompt me for the Order Number. I am in a catch 22. Can someone tell me how to generate a report from the results of a Stored Procedure, or how to get a View to prompt you.

Thanks

 

[dan1967]

Try using a front end like Access or Crystal Reports to run your proc. You can format your output any way you like. You should also be able to use Word and the mail merge function against your view.

What are you using now to enter your Order Number? If it is a web page (ASP), you can format the result page to look like any report you like. If you are just using SQLAnalyzer or iSQL-type text editors, you will have to copy and past your results into a spreadsheet and format any report from that.

Dan.

 

[LonC25]

I am using Access as the front end. If I try to build a report, the wizard only has options for tables and views. The View will not prompt me for the OrderNumber. Here is the Stored Procedure.

@PartOrderNumber int
As
SELECT FinalInspection.Comments, FinalInspection.DOther, FinalInspection.DOtherDesc, FinalInspection.NOther, FinalInspection.NOtherDesc, PartOrders.PartOrderNumber, Programs.Program

FROM ((FinalInspection INNER JOIN PartOrders ON FinalInspection.PartOrderID = PartOrders.PartOrderID) INNER

JOIN ProductionOrders ON PartOrders.ProductionOrderID = ProductionOrders.ProductionOrderID)

INNER JOIN Programs ON ProductionOrders.ProgramID = Programs.ProgramID

WHERE (dbo.PartOrders.PartOrderNumber = @PartOrderNumber)
/* set nocount on */
return

 

[dan1967]

You will have to go into the design mode for that report. Open the properties box for the entire report and select the data tab. In the first box at the top, click on the "..." to open the query window. Go to the field that has the criteria in it and type:

[Enter Order Number]

Without seeing your set up, this should work. When you enclose text inside brackets in the criteria of an Access query, you will get a prompt for that value before the query will run.

Dan.