ActiveReports 12
Reports with Parameterized Queries
ActiveReports 12 > ActiveReports User Guide > Samples and Walkthroughs > Walkthroughs > Page Report/RDL Report Walkthroughs > Data > Reports with Parameterized Queries
See Parameterized Reports topic for an alternative approach to build parameterized query for data sets.

h illustrates how to create a simple dynamic query.

The walkthrough is split into the following activities:

Note:

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 12 Page Report and in the Name field, rename the file as DynamicQueries.
  4. Click the Add button to open a new fixed page report in the designer.

See Adding an ActiveReport to a Project 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 ReportData.
  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

  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 Products. This name appears as a child node to the data source icon in the Report Explorer.
  3. On the Query page of this dialog, in the Query field enter the following SQL query.
    SQL Query
    Copy Code
    SELECT Movie.Title, Product.InStock, Product.StorePrice
    FROM MediaType
    INNER JOIN
    (Movie INNER JOIN
    (Product INNER JOIN MovieProduct
      ON Product.ProductID = MovieProduct.ProductID)
     ON Movie.MovieID = MovieProduct.MovieID)
    ON MediaType.MediaID = MovieProduct.MediaType
    WHERE (((MediaType.MediaID)=1))
    ORDER BY Movie.Title
    
  4. Click the Validate DataSet icon at the top right hand corner above the Query box to validate the query.
  5. 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 and set the following properties in the Properties Window:
    Property Name Property Value
    Location 0in, 0.5in
    Size 5.5in, 0.75in
    FixedSize (only for FixedPageLayout reports) 6.5in, 7in
  2. In the Report Explorer from the Products dataset, drag the following fields onto the detail row of the table.
    Data Field Column Name
    Title TableColumn1
    InStock TableColumn2
    StorePrice TableColumn3
    Note: This automatically places an expression in the detail row and simultaneously places a static label in the header row of the same column.
  3. Select the header row, click the table handle to the left of the row and in the Properties Window, set the following properties:
    Property Name Property Value
    FontWeight Bold
    BackgroundColor DarkSeaGreen
    RepeatOnNewPage True
  4. Select the StorePrice field in the detail row and in the Properties Window, set its Format property to Currency.

  5. 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 4.5in
    Second 1in
    Third 1in

To create a second dataset for use by the parameter list

  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 MediaType.
  3. On the Query page, paste the following SQL command into the Query text box:
    SQL Query
    Copy Code
    SELECT 0 AS MediaID, "All" AS Description
    FROM MediaType
    UNION SELECT MediaID, Description
    FROM MediaType
    ORDER BY Description
    
  4. Click the Validate icon to validate the query and to populate the Fields list.
  5. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

To add parameters to the report

  1. In the Report Explorer, select the Parameters node.
  2. Right-click the node and select Add Parameter to open the Report - Parameters dialog.
  3. In the dialog box that appears, select the parameter from the parameters list.
  4. Set properties in the following fields below the parameters list.

    In the General tab:

    • Name: MediaType
    • DataType: String
    • Text for prompting users for a value: Select a media type

    In the Available Values tab select From query:

    • DataSet: MediaType
    • Value: MediaID
    • Label: Description
  5. Click OK to close the dialog and add the parameter to the collection. This parameter appears under the Parameters node in the Report Explorer.

To modify the Products dataset to use a dynamic query

  1. In the Report Explorer, right-click the Products dataset and select Edit.
  2. In the DataSet dialog that appears, select the Query page.
  3. In the Query field, change the query to the following expression:
    Query
    Copy Code
    ="SELECT Movie.Title, Product.InStock, Product.StorePrice, MediaType.Description FROM MediaType INNER JOIN (Movie INNER JOIN (Product INNER JOIN MovieProduct ON Product.ProductID = MovieProduct.ProductID) ON Movie.MovieID = MovieProduct.MovieID) ON MediaType.MediaID = MovieProduct.MediaType" & IIf(Parameters!MediaType.Value = 0, ""," WHERE (MediaType = " & Parameters!MediaType.Value & ")") & " ORDER BY Movie.Title"
  4. Click OK to close the dialog.

To add a header to display the chosen parameter label

  1. From the toolbox, drag and drop a Textbox control onto the report design surface. In RDL reports, you can place the Textbox control in the PageHeader.
  2. Select the Textbox and set the following properties in the Properties window.
    Property Name Property Value
    TextAlign Center
    FontSize 14pt
    Location 0in, 0in
    Size 6.5in, 0.25in
    Value =Parameters!MediaType.Label & " Movies in Stock"
    Note: Using Label instead of Value in the expression displays a more readily understandable Description field instead of the MediaID field we used for the parameter's value.

To view the report

OR

See Also