The SSRS Query Designer makes the developer life easy to design the SQL Queries. If you don’t know how to write the Queries or if you find it challenging to perform Grouping, Sorting, and Join operations.
Or, if you find it challenging to design complex SQL Queries, use SSRS Query Designer to create your desired queries using a 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 SSRS Query Designer button, as shown below.
Once you click on the Query Designer button, it will open the Query Designer in a separate window. Before designing the transact query, you should understand its different panes.
- 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 choose the columns needed by check-marking the columns.
- Grid Pane: All the selected columns will display in these panes. This pane is handy for applying sorting (ORDER BY ASC and ORDER BY DESC) and also grouping the column names (GROUP BY Statement)
- SQL Pane: It will automatically build the query for us in this pane.
- Result Pane: Once you have finished designing your query, click the Execute button to display the 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 SSRS Query designer screenshot, you can observe that we selected the Customers table and Fact Internet Sales table. Please choose the required columns by check-marking the column names in the Diagram Pane.
From the above screenshot, you can observe that the 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 your requirement. In this SSRS query designer example, we are sorting First Name Ascending and the 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 Ascending order first, and then it will sort the Data by the Last Name in descending order.
From the above screenshot, you can see the final 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.
The built query is executing flawlessly. If you like to see the generated code behind this query, select Edit as Text option on top of a designer.
Now, you can see the result. Click the ok button to finish designing the Query using the Query Builder tool in SSRS
