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 of 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 C1FlexReport Wizard. To open the C1FlexReport Wizard complete one of the following:

• In Visual Studio by selecting Edit Report from the C1FlexReport context menu
• In Visual Studio by selecting Edit Report from the C1FlexReport Tasks menu
• From the C1FlexReportDesigner application, click the New Report button from the Reports tab

For more information on accessing the Edit Report link, see Design-Time Support.

Populating a report from a stored procedure is no different than using SQL statements or straight tables. In the first screen of the C1FlexReport Wizard, click the ellipsis 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 ellipsis button "..."), then pick the table or sproc you want to use from the list. For example:

  connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Windows 8.1\Documents\ComponentOne Samples\Common\C1NWind.mdb;Persist Security Info=False;" RecordSource = "[Products by Category]"

  (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 (if you specify ConnectionString, RecordSource, and Recordset, C1FlexReport uses the Recordset).

  The syntax is different depending on the type of connection/adapter you want to use (OleDb, SQL, Oracle, and so on). The easier way to get the syntax right is to drag tables or sprocs from Visual Studio's Server Explorer onto a form. This adds 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, the SQL request that is specified in C1FlexReportDesigner (adjust the path to ADVENTUREWORKS_DATA.MDF as needed) is:

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