Many developers create reports using SSRS by taking SSAS as source. SSRS is used to create Formal Reports where the pattern of the report is known in advance and it has fixed number of Measure and Dimension columns. You can’t add new dimension or measure in the existing report.
These days Self Service Reporting is getting popular in which the end user can connect to all the Measure and Dimension columns of the datasource and then end user can create the report on its own by choosing the desired Measure and Dimension columns.
Excel is one of the tools by which you can perform Self Service Reporting.In future excel popularity will increase more as a Business Intelligence Tool because of its many new add-ins like PowerPivot, PowerView and PowerQuery etc. I shall discuss this on some other day.
Please note Pivot Table option is different from PowerPivot Addin.
Here I am writing about the Pivot Table option available in excel.
Pivot Table is an option available in excel with which you can create interactive view of data from many different sources .These sources can be SSAS,SQL Server Databases and even excel sheets etc. You can group ,drill down and slice the data with it.
In this post I am discussing only about Pivot Table option and how to create Pivot Table in excel from SQL Server Analysis Services (SSAS).
Open excel -> Go to Data->Get External Data->From other sources-> From Analysis Services
Connect to the Analysis Server. I am connecting to my local SSAS by writing server name as .(dot). Dot represents local SSAS. And Click Next
Choose the desired database from the dropdownlist and then select the cube under the option Connect to a specific cube or table and click Next and Finish.
Then it will ask you to create a Pivot Table . Choose the option existing worksheet and click the cell in the excel worksheet from where you want to generate the Pivot Table.
E.g I have clicked on C4 cell and automatically the option for Existing worksheet has been changed to =Sheet1!$C$4 .
After that list of Dimensions and Measures will appear on Left Hand Side. You can drag and drop the dimensions on Row Labels and Column Labels and Measures into the Values section.
In the following figure , I drag and drop DateHierarchy into Column Labels and SalesTerritory into the Row Labels and Sales Amount into the Values Section.You can drill down DateHierarchy from Year-> Semester-> Quarter-> Month .It shows that Pivot Table supports drilldown operations.
Also you can add slicers to filter the data.
Under Pivot Table Tools –> Choose Options-> Choose Insert Slicer-> Insert Slicer
I have chosen English Product Name to Slice the data.
In the English Product Name slicer list, I have chosen Classic Vest,M and it is showing me the pivot table report only for that product. With the help of control key you can choose multiple options.
This is the example of Self Service Reporting where end user is creating the report for itself by choosing the desired Measures and Dimensions.
See the related Post