ActiveReports 15 .NET Edition
ActiveReports 15 User Guide / Concepts / Visual Query Designer / Using the Visual Query Designer
In This Topic
    Using the Visual Query Designer
    In This Topic
    The following walkthrough shows how to implement an Inner Join using the tables Categories and Products from the Nwind database. It also explains how to use a parameter and sort data in a Visual Query Designer. You may also see Tables and Relations for more information on table relationships and joins in Visual Query Designer.

    Note:

    • This walkthrough uses the Products and Categories table from the NWIND database. By default, in ActiveReports, the NWIND.mdb file is located in [User Documents folder]\GrapeCity Samples\ActiveReports 15\Data\NWIND.mdb.
    • This walkthrough uses Page Reports, but the same steps can be used to generate queries in Rdl or Section reports also. See Accessing the Visual Query Designer, for more information on how to access the Visual Query Designer in Rdl and Section reports.

    Add an ActiveReport to a 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 15 Page Report and in the Name field, rename the file as VisualQueryDesigner.
    4. Click the Add button to open a new Page Report in the designer.

    See Basic Concepts for information on adding different report layouts.

    Access the Visual Query Designer

    1. Connect a Page Report to a data source. See Connect to a Data Source for details on how to connect to a data source in Page Reports.
    2. Right-click the data source node (DataSource1 by default) and select the Add Data Set option or select Data Set from the Add button on the Report Explorer toolbar to add a data set to the report.
    3. In the DataSet Dialog that appears, select the Query page and then select the Edit with visual query designer button Edit with visual query designer button.
      This opens the Visual Query Designer.

    Create a Query

    Visual Query Designer provides you with an interface to reference multiple tables, set up relationships, sort the data and add parameters to your query.
    Follow the steps below to create a query like the following using the Visual Query Designer.

    Query Result in SQL

    SQL Query
    Copy Code
    select Products.ProductName, Products.UnitPrice, Categories.CategoryName
    from Products inner join Categories on Products.CategoryID = Categories.CategoryID
    where Categories.CategoryName = ?
    order by Products.UnitPrice desc
    

    Steps to create a Query in Visual Query Designer

    1. From the Products table in the Database view, drag and drop the fields ProductName and UnitPrice to the Selected Fields panel.
    2. From the Categories table in Database view, drag and drop the field CategoryName to the Selected Fields panel.
    3. When you add the field in step 2, a Tables relations dialog automatically appears on the screen.
      In the Tables relations dialog, you can also select any other field from Categories table which matches the related table's field to form a join between the both.
    4. In Tables relations dialog, select the Inner Join Type for joining the two tables Products and Categories. The Inner Join tab is selected by default.
      Refer to Tables And Relations for more information on types of joins.
      Tables relations dialog   
    5. In Tables relations dialog, click OK to save the relationship between tables.
      Once the relationship has been set up between tables, you may also access the Tables relations dialog from the Relations button in the Tables and Relationships panel under the Query Tools section.
    6. In the Selected Fields panel under the CategoryName field, select the option Where and set its value to "= @CategoryName". This creates a parameter on the CategoryName field.

      Selected Fields panel
    7. In the Selected Fields panel, go to the UnitPrice field and set the Sort option to descending. This sorts the data in descending order on UnitPrice.
    8. On the Toolbar of the Visual Query Designer, click the Execute button. A Parameters dialog appears on the screen.
      Parameter dialog
    9. Enter any parameter value, for example, Produce in the dialog box and click OK.

      Result data similar to the following appears in Results panel.
      Selected Fields panel

    Save a Query

    1. Once your query is created in Visual Query Designer, go to the Toolbar in the Query Tools section of the Visual Query Designer.
    2. Click the Save button button. Your query appears in the Query field of the Query page in the DataSet dialog. 
      Note: On clicking the Save button, your query is automatically validated by the Visual Query Designer.

    View the Report

    1. Place a data region like a Table onto the design surface and add fields to it. For more information on how to add fields to a table, see the Adding Data on the Table data region page.
    2. Click the preview tab to view the report with the Parameters panel displayed in the sidebar.
      Previewing report in Viewer

      Previewing report in Viewer



      OR


      Open the report in the Viewer to view the report with the Parameters panel displayed in the sidebar. See Windows Forms Viewer for further information.