Stored procedures (or sprocs) can assist you in achieving a consistent implementation of logic across applications, improve performance, and shield users from needing to know the details of the tables in the database. One of the major advantages to stored procedures is you can pass in parameters to have the database filter the recordset. This returns a smaller set of data, which is quicker and easier for the report to manipulate.

You can populate a report from a stored procedure in the C1Report Wizard. To open the C1Report Wizard complete one of the following:

• From the C1ReportDesigner application, click the New Report button from the Reports tab
• In Visual Studio by select Edit Report from the C1Report context menu
• In Visual Studio by select Edit Report from the C1Report Tasks menu

For more information accessing the Edit Report link, see C1Report Tasks Menu or C1Report Context Menu.

Populating a report from a stored procedure is no different than using SQL statements or straight tables. In the first screen of the C1ReportWizard, click the ellipses button to choose a datasource. Then choose a Stored Procedure from the list of available Data sources:

Select Next and continue through the wizard.

As with loading other forms of data, you have two options:

• You can use the DataSource's ConnectionString and RecordSource properties to select the datasource:

  In the Designer, use the DataSource dialog box to select the connection string (by clicking the ellipses button "..."), then pick the table or sproc you want to use from the list. For example:

  connectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" + "Persist Security Info=False;Initial Catalog=Northwind;Data Source=YOURSQLSERVER;" recordsource = "[Employee Sales by Country]('1/1/1990', '1/1/2010')"

  (In this case the stored procedure name has spaces, so it's enclosed in square brackets).

• You can create the data source using whatever method you want, then assign it to the DataSource's Recordset property:

  This method requires you to write code, and is useful when you have your data cached somewhere and want to use it to produce several reports. It overrides the previous method (it you specify ConnectionString, RecordSource, and Recordset, C1Report will use the Recordset).

  The syntax will be different depending on the type of connection/adapter you want to use (OleDb, SQL, Oracle, and so on). The easiest way to get the syntax right is to drag tables or sprocs from Visual Studio's Server Explorer onto a form. That will add all the cryptic elements required, and then you can go over the code and pick up the pieces you want.

  You can specify stored procedures as data sources by their name. If the sproc has parameters, you pass them as parameters. For example in a report definition built against MSSQL and ADVENTURE_WORKS.mdf database, here's the SQL request that is specified in the C1Report Designer (adjust the path to ADVENTUREWORKS_DATA.MDF as needed):

  PARAMETERS Employee Int 290;
DECLARE @RC int
DECLARE @EmployeeID int
set @EmployeeID = [Employee]
EXECUTE @RC = [C:\ADVENTUREWORKS_DATA.MDF].[dbo].[uspGetEmployeeManagers]
@EmployeeID