In the Query Builder dialog, you can add data tables and views to the data source, and select which columns to include. The Query Builder automatically joins the related tables, so all you need to do is drag-and-drop.
To add the required tables/views to a data source, double-click the table (or view) or drag-and-drop it from the Tables pane onto the Diagram pane.
Then, select the required columns.
If necessary, you can customize a data store schema to display only the required tables/columns in the Tables/Diagram panes. To learn more, see How to: Customize a Data Store Schema for SQL Data Sources.
Note that if at least one table has been added to the Diagram pane, the Tables pane highlights tables that have a relationship with any of the recently added tables.
To join the already added table with an another table, do one of the following.
The Query Builder will display a relationship between tables.
To edit this relation, select it and use its context menu.
The following commands are available.
Edit Relation - Allows you to edit the selected relation. Clicking this menu item invokes the Join Editor dialog.
First, check the join type. You can specify it in the Join type combo box (Inner join or Left outer join). To edit column and table names in the existing condition, click the name you wish to replace and choose a different name from the popup menu.
Note that the Join Editor dialog will be invoked automatically if you join tables that do not have a relationship at the database level.
After you have added the tables and selected the required columns, you can change settings for each column in the Grid pane.
The following settings are available for each column.
Note that aggregated columns should always have an alias.
Note that you should apply aggregation/grouping either to all columns or to none of them.
To filter data in the Query Builder, click the Filter... button. This will invoke the Filter Editor dialog, which allows you to build filter criteria.
To learn more, see Filter Queries.
Note that the Query Builder does not allow you to use custom SQL queries. To enable the capability to specify custom SQL queries within the Data Source wizard and Query Builder, set the DashboardDesigner.DataSourceWizard.SqlWizardSettings.EnableCustomSql property to true.
After you add the tables, you can customize the automatically generated SQL query. To do this, enable the Allow Edit SQL checkbox and edit the SQL query displayed in the SQL pane.
For instance, you can add a WHERE clause to the SQL expression.
Note that if you edit the automatically generated query and uncheck the Allow Edit SQL check box, your changes will be discarded, and the generated query will be restored.
The Query Builder allows you to preview data for the created SQL query. To do this, click the Preview Results... button.
This invokes the Data Preview window containing data returned after executing the query.