SSRS Report With Grouping of Data having hide and unhide feature in excel of grouped data
- Microsoft SQL Server database engine.
- SQL Server 2016 Reporting Services or later (SSRS).
- The AdventureWorks2016 database. https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak
- SQL Server Data Tools for Visual Studio along with the Reporting Services extension installed to enable access to the Report Designer.
- From the File menu, select New > Project.
- In the left-most column under Installed, select Reporting Services. In some cases, it may be under the group Business Intelligence.
- Select the Report Server Project icon in the center column of the New Project dialog box.
- In the Solution Explorer pane, right-click on the Reports folder. If you don't see the Solution Explorer pane, select View menu > Solution Explorer.
- In the Add New Item window, select the Report icon.
- Type "Sales Orders.rdl" into the Name text box. Select the Add button on the lower right side of the Add New Item dialog box to complete the process. Report Designer opens and displays the Sales Orders report file in Design view.
- In the Report Data pane, select New > Data Source. If the Report Data pane isn't visible, then select View menu > Report Data.
- In the Name text box, type "AdventureWorks2016".
- In the Connection string text box, type the following string: Data source=localhost; initial catalog=AdventureWorks2016
- Select the Credentials tab, and under the section Change the credentials used to connect to the data source, select the Use Windows Authentication (integrated security) radio button. Select OK to complete the process
- In the Report Data pane, select New > Dataset.... The Dataset Properties dialog box opens with the Query section displayed.
- In the Name text box, type "AdventureWorksDataset".
- From the Data source dropdown box, select AdventureWorks2016.
- For the Query type, select the Text radio button.
- Type Transact-SQL query into the Query text box.
Select the Preview tab. Report Designer runs the report and displays it in the Preview view.