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 a 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 your requirement. In this 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 the 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 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.
The built query is executing perfectly. If you wish to see the SQL code behind this query then, click on the Edit as Text option on top of the designer.
You can see the result. Click ok button to finish designing the SQL Query using the Query builder tool in SSRS
Thank You for Visiting Our Blog