ActiveReports 9 > ActiveReports User Guide > Concepts > Visual Query Designer > Query Building With Visual Query Designer |
The topic takes you through the query building process in the Visual Query Designer. Query building in Visual Query Designer can be accomplished in a few simple steps:
Step 1: Adding fields from a table to generate a simple query
Step 2: Setting relationships (only applicable to queries using multiple tables)
Step 3: Setting options for individual fields or tables
Step 4: Executing a query
Step 5: Previewing a query
The following steps assume that you have already added a Page Report or an Rdl Report template and connected it to a data source. See Adding an ActiveReport to a Project and Connect to a Data Source for further information.
For more information on how to access Visual Query Designer, see Accessing Visual Query Designer.
Note: This topic uses the Reels database. By default, the Reels.mdb file is located in the [User Documents folder]\GrapeCity Samples\ActiveReports 9\Data folder. |
In the Visual Query Designer, you get a visual interface to assist you in quickly designing simple queries that reference a single table.
Query Result in SQL
SQL Query |
Copy Code
|
---|---|
select Movie.MovieID, Movie.Title from Movie |
Steps to create the Query in Visual Query Designer
In the Visual Query Designer, you get a visual interface to reference multiple tables and set up relationships between them.
The following example shows how to implement a right outer join using the tables Movie and MovieCrew from Reels database and apply a filter condition to your result set using the WHERE clause. For more information about table relationships and joins in Visual Query Designer, see Tables and Relations.
Query Result in SQL
SQL Query |
Copy Code
|
---|---|
select Movie.MovieID, Movie.Country, Movie.Title, MovieCrew.CastID, MovieCrew.TitleID from Movie right join MovieCrew on MovieCrew.MovieID = Movie.MovieID where (Movie.Country = 'USA' and MovieCrew.CastID =1) |
Steps to create the Query in Visual Query Designer
When you have finished designing your query, you can execute it and then preview the result in the Visual Query Designer.
Note: When previewing a query, Visual Query Designer shows only a part of the data from the database. |
Page Report/Rdl Report
Once a query is created in the Visual Query Designer, the Save button allows you to save the query into the DataSet dialog.
Section Report
Once a query is created in the Visual Query Designer, the Save button allows you to save the query into the Report Data Source dialog.
Once a query is created, the Selected Fields panel is populated with fields and Tables and Relationship panel displays the tables to which the fields used in the query belong.
There are two ways to edit a query in the Visual Query Designer:
SQL Query |
Copy Code
|
---|---|
select Movie.MovieID, Movie.Title, Movie.Length from Movie |
SQL Query |
Copy Code
|
---|---|
select Movie.MovieID, Movie.Title from Movie |
SQL Query |
Copy Code
|
---|---|
select Movie.MovieID, Movie.Title, Movie.Length from Movie |
You can delete any field from a query in the Visual Query Designer. When you delete a field from a query, the field remains in the database, but is no longer used in the query.
You can sort the records in a table, query, form, or a report on one or more fields in the Visual Query Designer. For example, you can sort the Movie table by Title in ascending order and Country in descending order. In case multiple fields are being sorted, you can also determine which field is sorted first and which is sorted later.
Query Result in SQL
SQL Query |
Copy Code
|
---|---|
select Movie.MovieID, Movie.Title, Movie.Countryfrom Movieorder by Movie.Country desc, Movie.Title asc |
Steps to create the Query in Visual Query Designer
When retrieving data from a table, you may get duplicate records. Use the Distinct operator in the Select statement of your query to remove such values.
In the Visual Query Designer, you can use the Distinct checkbox available in the Toolbar to eliminate duplicate records. For example, you can retrieve unique records from YearReleased field of the Movie table.
Query Result in SQL
SQL Query |
Copy Code
|
---|---|
select DISTINCT Movie.Title, Movie.YearReleased from Movie |
Steps to create the Query in Visual Query Designer
You can group data on a field and create an aggregate query that involves a function such as Sum or Avg in the Visual Query Designer. For example, you can group the movies in the Movie table by Country and calculate the average movie ratings for different countries using the Visual Query Designer.
Query Result in SQL
SQL Query |
Copy Code
|
---|---|
select Movie.Country, Avg(Movie.UserRating) as [Average Ratings] from Movie group by Movie.Country |
Steps to create the Query in Visual Query Designer
The Hide option in the Visual Query Designer allows you to hide part of the data that a query retrieves. For example, you can hide the MovieID field in the Movie table, from the result set of your query.
Follow the steps below to hide a field through the Visual Query Designer:
The SQL Where clause is used to filter results that match a given criteria. The Where clause can be used when you want to fetch any specific data from a table omitting other unrelated data.
For example if you want to display UserRating of only those movies where the MovieID is either 1 or 2, you can use the Where clause with an '=' operator in the Visual Query Designer.
Query Result in SQL
SQL Query |
Copy Code
|
---|---|
select Movie.MovieID, Movie.UserRating from Movie where (Movie.MovieID = 1 or Movie.MovieID = 2) |
Steps to create the Query in Visual Query Designer
You can set paramaters in your query using the Visual Query Designer. A parameterized query generally prompts the user to enter a value before the query is executed, to determine the type of data to be displayed in the result set.
As an example of a simple parameterized query, you can create a query parameter that prompts a user for a Movie ID and displays the Title, UserRating and Length of the movie based on the ID entered.
Query Result in SQL
SQL Query |
Copy Code
|
---|---|
select Movie.MovieID, Movie.Title, Movie.Country, Movie.UserRating from Movie where Movie.Country = ? |
Steps to create the Query in Visual Query Designer