ActiveReports 14
ActiveReports 14 User Guide / Samples and Walkthroughs / Walkthroughs / Page Report/RDL Report Walkthroughs / Data / Reports with Stored Procedures
In This Topic
    Reports with Stored Procedures
    In This Topic

    You can create a report using a stored procedure as a dataset. A stored procedure is a group of SQL statements that are used to encapsulate a set of operations or queries to execute on a database.

    This walkthrough illustrates how to create a report that uses a stored procedure as a data set. The walkthrough is split into the following activities:

    Note:
    • This walkthrough uses a table from the Reels database. The Reels.mdb file can be downloaded from GitHub: ..\Samples14\Data\Reels.mdb.
    • Although this walkthrough uses Page reports, you can also implement this using RDL reports.

    When you complete this walkthrough you get a layout that looks similar to the following at design time and at run time.

    Design-Time Layout

    Run-Time Layout

    To add an ActiveReport to the Visual Studio project

    1. Create a new Visual Studio project.
    2. From the Project menu, select Add New Item.
    3. In the Add New Item dialog that appears, select ActiveReports 14 Page Report and in the Name field, rename the file as StoredProcedure.
    4. Click the Add button to open a new fixed page report in the designer.

    See Quick Start for information on adding different report layouts.

    To connect the report to a data source

    1. In the Report Explorer, right-click the Data Sources node and select the Add Data Source option or select Data Source from the Add button.
    2. In the Report Data Source Dialog that appears, select the General page and in the Name field, enter a name like Reels.
    3. On this page, create a connection to the Reels database. See Connect to a Data Source for information on connecting to a data source.

    To add a dataset with a parameter

    1. In the Report Explorer, right-click the data source node and select the Add Data Set option or select Data Set from the Add button.
    2. In the DataSet Dialog that appears, select the General page and enter the name of the dataset as SalesDataForStore. This name appears as a child node to the data source icon in the Report Explorer.
    3. On the Query page of this dialog, set the Command Type to Stored Procedure.
    4. On the Query page of this dialog, in the Query field enter the stored procedure name (e.g. SalesDataForStore).
    5. Click the Validate icon to validate the query. You may receive an error at this point since the required parameters have not yet been added.
       
    6. Go to the Parameters page and add a Parameter using the Add(+) button.
    7. On the same page, enter Name as StoreID and Value as 1002.
    8. Select the Query page of DataSet dialog, and click the Validate icon to validate the query and load the fields. 
    9. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

    To create a layout for the report

    1. From the toolbox, drag a Table data region onto the report design surface.
    2. In the Table data region, place your mouse over the cells of the table details row to display the field selection adorner.
    3. With the Table selected, right-click and open the Properties Window to set the following properties:
      Property Name Property Value
      Location 0in, 0.5in
      Size 6.5in, 0.75in
      FixedSize 6.5in, 7in
    4. Click the adorner to show a list of available fields from the SalesDataForStore dataset and add the following fields to the cells of the table details row.
      Cell Field
      Left Cell StoreID
      Middle Cell UnitsSold
      Right Cell NetSales
      This automatically places an expression in the detail row and simultaneously places a static label in the header row of the same column.
    5. Select the Header row by clicking the table handle to the left of the row and go to the Properties Window to set the following properties:
      Property Name Property Value
      FontWeight Bold
      RepeatOnNewPage True
    6. Click the column handle at the top of each column in turn to select it, and in the Properties Window, set the Width property as indicated in the table:
      Column Width
      First 3.5in
      Second 2in
      Third 1in
    7. Set the TextAlign property of all the columns to Left.
    8. From the toolbox, drag the Textbox onto the design surface to span the entire width of the report and go to the Properties Window to set the following properties:
      Property Name Property Value
      TextAlign Center
      Size 6.5in, 0.35in
      Location 0in, 0.125in
      FontSize 14pt
      Value Net Sales by Store
      Tip: In a RDL report, you can also add a Page Header to place the Textbox control.

    To view the report

    • Click the preview tab to view the report at design time.

    OR

    See Also