Use OLE DB Query & PivotTable in Excel
Excel can retrieve data from external data sources via ODBC connection or OLE DB connection.
We can use OLE DB query to get cubes in SSAS, or access stored procedures in SQL server.
Give it the SQL server name.
Let’s pick the PivotTable Report type.
We get this PivotTable, and fields are ready to be dropped into the right place.
If we do not need to sub-total, then select option None in the Field Settings for each Row Labels.
If we don’t need grand totals, uncheck these two options in teh PivotTable Options.