Microsoft has introduced Slicers from Excel 2010 which is a really great feature. It is nothing but filters. It should be connected to pivot tables or data connections. Also it can be connected to multiple pivot tables (provided every pivot table has same source data). This is useful to slice same data in different views. such as Dashboards. They can be used using Excel services in SharePoint 2010 also
e.g.
Let us say we have Sales data for as Sales Person, Region, Month,Quarter and Units sold.
Requirement :
Output should have two charts
1. Sales by Month (Column Chart)
2. Sales by Region (Pie Chart)
Both of above charts should have slice and dice facility by Sales Person and Quarter
Design:
Create Two different Pivot tables.
1. Pivot Table 1: Structure
Columns: B and C
Report filter:
Sales Person
Quarter
Column Labels:
Month
Values:
Sum of Unit sold
Format chart as per your need.
2. Pivot Table 2: Structure
Columns: F & G
Report filter:
Sales Person
Quarter
Column Labels:
Region
Values:
Sum of Unit sold
Create Pivot Chart of Above Pivot Table of type 'Pie'; Hide field buttons using 'Pivot Chart Tools -> Analyse -> Field Buttons -> Hide All
Format chart as per your need.
3. Insert Slicer
Select any Pivot table or Pivot Chart and add slicer
Insert -> Slicer (You must select Pivot table or Pivot Chart before inserting)
Select Field names which will be used to slice and dice
Now, you need to give connection of pivot tables to both the slicers, When you create slicer it has only one pivot table connection; to connect slicer to another pivot table right click on slicer and select 'PivotTable Connections' and select another pivot table
You can format slicers if you want by selection Slicers and then Slicer tools on Ribbon.
4. Create formulae for dynamic update of Chart Titles
Now Insert formulae next to any of the Pivot table to display chart titles as per selection of slicer items
Formula in D3 will depict which SalesPersons are selected
Formula in D4 will depict which Quarters are selected
Formula in D5 will depict which Quarter and SalesPersons are selected
Assign D5 value to chart title.: Select Chart Title then go to Formula bar and type '=' and the select D5 cell
Press Enter button Repeat this step to other chart as well
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBW8_VNhaYSbOb38J8NptlzTCifIAsmGM3rG_czbvXmJlkpcNDusgfkEzhodkqn1TfCQV5nmRLdzjxKmIVTe02Cuoy3cQvSMwFBDZ8jtTcRLNHezQpQY_sWDJ5bPCZOUwcz2Tcl0ZnQIzz/s400/formula2.jpg)
5. Finalize Dashboard
Move Charts & Slicer to Column I, Format Charts and Hide Columns where Pivot tables are available i.e. Hide Columns A to G, Hide Gridlines and Headings
You can select any Slicer items and both the Charts will change accordingly; You can select multiple slicer items by using ctrl + click.
5. SharePoint 2010 Integration
Upload Excel file in SharePoint library and right click on it to select 'View in a browser' option. Slicers can be used through SharePoint as well :)
No comments:
Post a Comment