This SSRS article shows how to use the Query Builder in the report wizard to generate a T-SQL query with an example. For this query builder demo, we create a data source pointing to Adventure Works DW 2019.
SSRS Report Wizard Query Builder
In the Solution Explorer, right-click on the Reports folder and select the Add New Report option from the context menu.
It opens the report wizard welcome page. Click the next button to continue.
If you have an existing Data Source, select the shared data source. Here, we create a new pointing Adventure Works DW Database with SQL Server authentication.
The next page is to design the Sql query to use in SSRS. To use the existing query string, paste it inside the space provided. However, in this example, we want to use the query builder to design, so click on it.
It opens the SSRS Query Designer or Builder window inside a report wizard. Right-click on the empty space and choose the Add Table.. option to add database tables in Report Wizard. Here, we have chosen the DimCustomer and FactInternetSales tables.
Checkmark the columns you need to design a report.
- Alias – Assign a New name to the Column.
- Sort Type – Order by Clause means Ascending and descending. Please use the drop-down list to choose the Sort type and Sort order.
- Sort Order – Which one is the first one to apply to sort?
- Use the ! button to see the result.
The code below selects the employee sales order by first name in ascending order and last name in Ascending.
Filter – To apply where condition in SSRS Report Wizard Query Builder.
Here, we used the Filter on Sales column to return the employees whose sales amount is greater than 1000. Once you are OK with the result, click OK to close the Query Designer window.
Click the Next button.
Select Tabular as the report type and click Next.
Design the table with the available fields. Let me add all the columns to the Details section to create a table. Next, change the default report name and click the Finish button.
Now you can see the auto-generated table using the SSRS report wizard query builder.
Let us see the report preview.
