Microsoft Technologies: SSRS Query Designer

In SQL Server Reporting Services, Query Designer makes the developer life easy to design the SQL Queries. If you don’t know how to write the SQL Queries or if you find it difficult to perform Grouping, Sorting and Join operations or if you find it difficult to design the complex SQL Queries then you can use SSRS Query Designer to design your desired queries using Graphical User Interface.

SSRS Query Designer Example

In this example we will show you, How to design an SQL Query using SSRS Query Designer. For this example we will design the query against the Adventure Works DW database.

First, Right click on the shared dataset and click on Add New Dataset option. Next, select the Query type as Text and click on the Query Designer button as shown below.

Once you click on the Query Designer button it will open the Query Designer in separate window. Before designing the SQL query you should understand the different panes present in the SSRS Query Designer.

Diagram Pane: This pane is used to select the tables, Views or functions. SSRS Query Designer will automatically join the tables as per the database relations. After selecting the required tables we can select the required columns by check marking the columns.
Grid Pane: All the selected columns will be displayed in this panes. This pane is very useful to apply sorting (ORDER BY ASC and ORDER BY DESC) and also grouping the column names (GROUP BY Statement)
SQL Pane: SSRS Query Designer will automatically build the SQL query for us in this pane.
Result Pane: Once you finished designing your SQL query then click on the Execute button will display the SQL Query result in this pane.

Right Click on the Diagram Pane will open the context menu. Click on the Add table.. option and add the required table.

From the below screenshot you can observe that, we selected the Customers table and Fact Internet Sales table. Please select the required columns by check marking the column names in the Diagram Pane.

From the above screenshot you can observe that, SSRS Query Designer automatically applied the Inner Join between the two tables Customers and Fact Internet Sales using the CustomerKey column.

Sorting: Within the Grid Pane, Please change the Sort Type to Ascending or Descending as per you requirement. In this example we are sorting First Name Ascending and Last Name Descending

Please change the Sort Order option as per your requirement. For this example, it will sort the data by First Name in the Ascending order first and then it will sort the Data by Last Name in descending order.

From the above screenshot you can see the final SQL Query generated by the SSRS Query Designer. Let us see whether the built query is executing perfectly or not by clicking the execute button on the top.