Transcription

Microsoft ExcelPivot TablesGeneral instructions withexercises on BI4Dynamics data

11PREFACE321.1 How to use this document1.2 BI4DynamicsINTRODUCTION33432.1 Understanding dimensions and cubes2.2 General features of BI4Dynamics2.3 Pivot TablesCONNECTING TO DATA445643.1 Creating a new Connection3.2 Using an existing connection3.3 Changing an existing report connection3.4 Refreshing a connection3.5 Connection properties3.6 Changing language3.7 ExercisesPIVOT TABLE OVERVIEW689101012131454.1 Show fields related to area4.2 Pivot table field list4.3 Pivot table preview4.4 Pivot Table tools tabsWORKING WITH PIVOT TABLES141516161865.1 Creating, modifying and deleting the pivot table layout5.2 Nesting dimensions5.3 ExercisesDATA DISPLAY OPTIONS1818192276.1 Dimension browsing (hierarchies)6.2 Sorting data6.3 Filtering data6.4 Slicers6.5 Search6.6 Subtotals, dimension levels display and grouping6.7 ExercisesFORMATTING222223262729303287.1 Pivot table formatting7.2 Displaying and formatting measures7.3 Conditional formatting7.4 Pivot charts vs. Normal chartsChart designChart layoutChart formatAnalyze chart options

298.6 Sparklines8.7 ExercisesADDITIONAL EXERCISES4143449.19.29.3444751Sales cubeInventory cubeGeneral ledger/Account schedules cube

31 PREFACE1.1 How to use this documentThis document’s purpose is to empower BI4Dynamics users to fully benefit from BI4Dynamics (as asource of data) and Microsoft Excel (as a viewing data software). This document is presented in twoparts:Part 1: How to use prebuilt Excel reports made on top of BI4DynamicsFor easier start BI4Dynamics created set of predefined reports that can be connected to Analysis database that is created with BI4Dynamics installation Wizard. Please note that you need successfullycomplete the installation to use prebuilt reports.Part 2: Excel exercises for creating your own reports and getting most of BI4DynamicsAs every company has its own needs and challenges BI4Dynamics delivers all you can imaginecontent that can be used for analysis. With drag and drop Pivot table functionality you have unlimitedpotential of building reports.1.2 BI4DynamicsBI4Dynamics is complete Business Intelligence solution that is specially build for Microsoft DynamicsAX & NAV. BI4Dynamics covers all Microsoft Dynamics AX & NAV application areas and includesnumerous built-in calculations for endless analysis possibilities. F.e. BI4Dynamics NAV Sales modulealone offers 254 measures, 37 dimensions and 137 attributes.It is open and completely customizable and it also serves as a framework on which you can extendthe solution to fit your needs. Customization Wizard enables building new cubes, modifying built-incubes and adjusting setup.As BI4Dynamics main focus is transforming your data into knowledge by storing your data into DataWarehouse and creating advanced calculations to empower you with the data and unique version ofthe truth that is available to your entire organization. To learn how to use it in best possible way wecreated this guide about using Excel Pivot table.

42 INTRODUCTION2.1Understanding dimensions and cubesIn this chapter, the difference between cubes, dimensions, attributes and hierarchies are presented. Every cube is composed of different dimensions and different set of measures. Dimension consists of single attributes that are grouped in predefined hierarchy. Hierarchies have the possibility to drilldown by levels thus making it easier for the businessto quickly analyze the granular data. Many attributes are visible and many more are hidden. They can be made visible via theBi4Dynamics customization wizard or by modification of properties in Microsoft AnalysisServer (cube).Example:Picture: Item Category Group by measures: Stock Quantity, Stock Value, Stock Rotation Days.Measures can be viewed over different dimensions and their hierarchies or attributes.2.2General features of BI4Dynamics2.2.1 One Installation, Any Database, Any Company,BI4Dynamics can join data across any supported version of Microsoft Dynamics NAV database andcompany within – in one single data warehouse.2.2.2 Global dimensionsEach measure in a specific OLAP cube can be viewed over multiple companies and over eight globaldimensions.

52.2.3 Local currency, additional currency, posting currencyStandard measures are in local currency. BI4Dynamics NAV supports by default one additionalcurrency that is calculated in data warehouse with the information from currency exchange rate datain Microsoft Dynamics NAV. The measures for additional currencies have an ACY suffix. Measureswith suffix of PCY have values in original currency posted. Such measures should always be analyzedwith dimension Currency to avoid incorrect totals.2.2.4 One Installation, Any LanguageTranslations for all languages are available in every cube. Users can set-up local connection string (inExcel) with Locale ID and change the display language in BI client (Excel).2.3 Pivot TablesPivot tables can be seen as a simplification of the more complete and complex OLAP concepts. Inother words they are your best choice when dealing with multidimensional data stored in OLAPcubes.The most used tool to work with pivot tables is Microsoft Excel.

63 CONNECTING TO DATAData is the base of every analysis and we will use Excel to connect to the OLAP cubes, where the datais stored and prepared for the business user.OLAP cubes reside on the SQL Analysis Services Server, so in order to get to the data, we first needto connect to the server.3.1 Creating a new ConnectionProcedure for connecting to OLAP cubes on Analysis Services is as follows:Go to: Data Get External Data From Other Sources From Analysis ServicesIn Connect to Database Server window write your server name. Choose Windows or Userauthentication. Press Next.

7In Database and Table window choose your database and select designated cube. Press Next.In Save Data Connection and Finish window choose the file name for your data connection. Werecommend you also fill in Description, Friendly Name and Search Keywords fields. Press Finish.As an optional last step you can also select where and what you want to create based on thisconnection (Pivot Table Report – pivot table, Pivot Chart and Pivot Table Report – pivot chart andpivot table, Only Create Connection – Create connection, Existing worksheet, New worksheet)You are now left with a blank pivot table placeholder, pivot table field list and pivot table tools tabs.Also a new connection file has been created on your file system (by default C:\Users\YOURUSERNAME\Documents\My Data Sources).

83.2 Using an existing connectionWhenever you create a new connection or receive a connection file from someone else, it is availablefor you to use, without having to go through the process in the previous step.For example, your administrator could define the connection, save it on your network and you canuse it create your reports.Procedure for connecting to OLAP cubes based on an existing connection:Go to: Data Existing ConnectionsIn the next windows you can choose which of the existing connection files you want to use.

93.3 Changing an existing report connectionOnce you create a pivot table that pivot table will be associated with a connection. You can alwayschange the connection the pivot table uses.Go to: PivotTable Tools Change Data SourceThen in the next window click Choose Connection and select the connection you want to use.

103.4 Refreshing a connectionTo refresh the data, you need to refresh the connection which effectively connects to the OLAP cubeand returns the latest data available.One connection can only be linked to a single OLAP cube. You can however have several connectionsin a workbook, though each pivot table can only be based on a single connection.You can Refresh individual connections (Refresh) orRefresh all connections at once (Refresh All).Go to: Data Refresh All (or Refresh)3.5 Connection propertiesEach individual connection has several properties you can set. To view the list of all connections in aworkbook go to Data Connections, which will bring up the Workbook Connections windows.

11Besides having the option to also create, delete and refresh individual connections from this window,you can click on properties button to bring out the properties of the chosen connection.On the usage tab you can control:1. The refresh rate of the connection: Refresh every X minutes Refresh data when opening the file2. OLAP Server Formatting for numbers, fonts and colors.3. OLAP Drill Through which controls the number of records retrieved when performing drillthough (seeing the numbers behind the number).4. Language settings, which, when available, returns the names of dimensions and measures inthe language of the MS Office.On the definition tab you can control:1. The connection file used by the connection (which you can also change).2. The connection string and command text (which you can also modify).3. The Excel Services authentication settings which are important if you plan to use your Excelfiles with SharePoint Excel Services.

123.6 Changing languageTranslations for all languages are available in every cube. Users can set-up local connection string (inExcel) with Locale ID and change the display language in BI client (Excel).LanguageLocaleidentifierConnection stringCroatian1050Locale identifier 1050Czech1029Locale identifier 1029Danish1030Locale identifier 1030Dutch - Belgium2067Locale identifier 2067Dutch - Netherlands1043Locale identifier 1043English - United States1033Locale identifier 1033Estonian1061Locale identifier 1061Finnish1035Locale identifier 1035French - France1036Locale identifier 1036German - Germany1031Locale identifier 1031German - Switzerland2055Locale identifier 2055Italian - Italy1040Locale identifier 1040Lithuanian1063Locale identifier 1063Norwegian (Bokmål)1044Locale identifier 1044Polish1045Locale identifier 1045Portuguese - Portugal2070Locale identifier 2070

13Serbian (Cyrillic)3098Locale identifier 3098Serbian (Latin)2074Locale identifier 2074Slovak1051Locale identifier 1051Slovenian1060Locale identifier 1060Spanish - Spain (Traditional Sort)1034Locale identifier 1034Swedish1053Locale identifier 1053Table of available languages and locale identifiers. This feature is available on STANDARD version ofMicrosoft SQL server.3.7 Exercises3.7.1 Exercise 1 – Creating a new connectionStep 1: Select to create a new Analysis Services connectionStep 2: Connect to the server BI4NAVStep 3: Select the BI4NAV databaseStep 4: Select sales cubeStep 5: Rename you connectionStep 6: Select to create a pivot table report on the existing worksheet3.7.2 Exercise 2 – Use an existing connectionStep 1: Select to use existing connectionStep 2: Select the connection you created in the previous exerciseStep 3: Select to create a pivot table report on the existing worksheet

144 PIVOT TABLE OVERVIEWIn Excel, a pivot table has 3 unique parts: Pivot table filed list, which shows the structure of the OLAP cube at the top and providesplaceholders for the desired layout of your pivot table. Pivot table preview, where the pivot table will be created. PivotTable tools tabs in main menu, where you can find various options to modify your pivottable.4.1 Show fields related to areaAreas in Sales cube are: Sales BudgetsSales ValuesSales QuantitiesSales OrdersSales Delivery

154.2 Pivot table field listPivot table field list contains dimension andmeasures.By checking checkboxes we filter the datacube.Checked attributes are automaticallypositioned in pivot table rows and columns.This can be done manually by dragging anddropping the attribute into Column Labels,Row Labels, and Values in Report Filters.For each dimension we can examine itshierarchy by clicking on .Filters can be added to reports in Report Filter.Top – MEASURESBottom – DIMENSIONS-----------------------------Sort is alphabetical

164.3 Pivot table previewPivot table preview is divided in data description (dimensions) part and data value part.Data description part contains: Header filter (global data filter),Row filter (filter applied on rows) andColumn filter (filter applied on columns).4.4 Pivot Table tools tabsPivotTable tools tabs are automatically shown when clicking on one or more pivot table .19.20.21.22.23.24.Pivot Table Name – name of pivot table.Options – changing pivot table options.Active Field – show active cell name.Field Settings – changing cell settings (sum, format cell ).Expand Entire Field – entering deeper hierarchy level of selected cellCollapse Entire Field – exiting deeper hierarchy level of selected cell.Group Selection – active on column and row header. Grouping data.Ungroup – active on column and row header. Ungrouping data.Sort – sorting data (ascending, descending,).Insert Slicer – inserting different slicers.Refresh – refreshing data from the data source.Change Data Source – choose a different data source.Clear – clear pivot table.Select – select parts of pivot table.Move Pivot Table – moving pivot table (into new sheet).Summarize Values By – group data (power pivot).Show Values As – customize pivot table values, % of total, Fields, Items, & Sets – defining additional groups.Pivot Chart – adding pivot chart.Formulas – adding formulas.OLAP tools – Working without a connection to OLAP cube and converting into formulas.Field List – turn on/off data field list. /- Buttons – turn on/off entering into dimension hierarchies.Field Headers – turn on/off pivot table header.

171. Subtotals – turn on/off sum, moving sum in the top or bottom of the table. Sum of selecteddimension values.2. Grand Totals – turn on/off sum total and preview the sum for either column or row values.Partial sum of all dimension values.3. Report Layout – different report layouts.4. Blank Rows – turn on/off blank rows in dimensions.5. Row Headers – selecting row header color.6. Column Headers – selecting column header color.7. Banded Rows – even rows in other color.8. Banded Columns – even columns in other color.9. Pivot Table Styles – Setting pivot table appearance.

185 WORKING WITH PIVOT TABLES5.1 Creating, modifying and deleting the pivot table layoutThe first thing we need to do before designing our pivot table is to think about which dimensionsand measures we are going to need.Once we have identified them, we can select or just drag & drop them to the desired location in ourpivot table. The fields will be shown automatically in the pivot table preview.We can rearrange, add or remove the fields from our pivot table at any time by dragging anddropping or by using the arrow buttons and selecting the action from the contextual menu thatcomes up.Click on the arrow next to Date YMD andselect Move to Column Labels. Dimension DateYMD will be moved from rows to columns.Move to Row Labels moves dimension fromcolumns to rows. (Row is active only if data inthe column was previously selected)We can also use Drag and drop.5.2 Nesting dimensionsWhen we set up multiple dimensions in columns/rows, we are talking about dimension nesting. Thisis very useful when we want to see related information from different dimensions grouped together.In our example we want to see how well our salespeople did within the years. In the pivot table fieldlist we select the Salesperson - Purchaser dimension and drag it below the Data YMD dimension. Theorder in which dimensions appear is important, because the order defines the grouping.

19Adding dimension:Result:5.3 Exercises5.3.1 Exercise 1 – Creating a new pivot tableWe will create a report showing Net Sales by Customer by country over yearsStep 1: Drag Customer by country to row labelsStep 2: Drag Date YQMD to columns labelsStep 3: Select Net Sales and see it being added to the Values

205.3.2 Exercise 2 – Modify layoutUse the report you created in exercise 1 (copy the existing report to a new sheet).Step 1: Drag the date dimension in the filter.Step 2: Select additional measures: Cost, Profit.Step 3: Replace customer by country dimension with Bill to customer dimension in row labelStep 4: Which Bill to Customer was the most profitable? HINT: (Sort on Profit from biggest tosmallest)

215.3.3 Exercise 3 – Nesting dimensionsUse the report you created in exercise 1 (copy the existing report to a new sheet).Step 1: Remove the Customer by Country from the report.Step 2: Drag Item by Category to rows.Step 3: Drag Salesperson-Purchaser below Item by Category in rows.

226 DATA DISPLAY OPTIONS6.1 Dimension browsing (hierarchies)Dimension browsing is one of the most interesting pivot table features. Each dimension usually hasat least one hierarchy defined. By browsing a specific dimension hierarchy we can access moredetailed data in a very simple and effective way just by clicking on next to the dimension.The result is shown in the next picture. The procedure can be repeated until we reach the lowesthierarchy level (In our example the lowest level is day).6.2 Sorting dataSorting can performed with standard Excel functionality. In pivot tables we can sort by dimensionsand measures. Sorting in ascending or descending order is always possible. The more advancedoptions are context based and depend on whether we want to sort dimensions or measures. By rightclicking a field and selecting Sort More Sort options we will be presented with the relevantpossibilities.Dimension Sort options:Measures Sort Options:

236.3 Filtering dataFiltering allows us to limit the data presented to us based on certain conditions. Like with sorting, wecan filter on dimensions and measures and for that we have two types of filters plus a special Topfilter option: Label filtersValue filtersTop filterAny pivot table can also contain a report filter, which is a dimension filter applied to the entire report.6.3.1 Label filtersLabel filters are based on dimension values.In our example we wish to display only gross sales in Canada, therefore it is necessary to setup afilter on Customer by Country dimension. This is done by clicking on iconand choosing a filtervalue. We can also filter by multiple values. For more options on setting Label filters you can alsoselect Filter Label Filters We can have filters set on rows and columns at the same time for any dimension present. We will setup the filter for our date dimension on years 2006 and 2007.

24If we wish to remove filter, we right click on a dimension value and select Filter Clear Filter From in drop down menu.6.3.2 Top 10 filtersWe can filter our data to display top X customers, vendors . To set a Top filter we right click on oneof the countries in our rows and select Filter Top 10 from the drop down menu.In the Top 10 Filter window we control whether we want to select top or bottom members, thenumber of members, whether we want to count them as individual items or percentages and bywhich measure we want our filter to display. Please note that you can choose any measure in theOLAP cube, even if it not present in the pivot table.6.3.3 Value filtersValue filters are based on the measure values and are set up by right clicking and choosing Filter Value Filters from the drop down menu.In the Value Filter window we select the measure on which we want to base our filter on, and thecondition for the filter.

256.3.4 Report filtersReport filter is a separate placeholder in the pivot table layout. Only dimensions can be a part of thereport filter. By setting report filter you are filtering all the other measures and dimensions in thepivot table (in rows, columns and measures).Report filters appear above the pivot table and you can set a value by clicking on theto the name.icon next

266.4 SlicersA special kind of report filters introduced in Excel 2010 are Slicers. Slicers offer several advantagesover standard report filters: They are a much more visual way to present your filters and you can quickly see what youhave filtered on and even indicate where there is no data.They can be tied to more than one pivot table (if they use the same connection)They can be formatted, customized and rearranged.To create a Slicer you can select the pivot table and click Options Insert Slicer. This brings up awindow where you can select individual hierarchy levels of dimensions to create desired slicers. Eachlevel will create a separate slicer.SlicersStart from pivot table

27There is a new Slicer Tools tab available when you select a slicer. This gives you several option tocustomize the appearance and behavior of your Slicer.1.2.3.4.5.6.7.8.9.10.11.Slicer Caption – name of the slicer.Pivot Table Connections – connection between slicer and pivot table.Slicer Styles – slicer style.Bring Forward – position of the slicer.Send Backward – position of the slicer.Selection Pane – turns on the ribbon with all slicers.Align – position of the slicers.Group – slicers grouping.Rotate – rotates the slicer.Buttons – defining option buttons in the slicer.Size – defining slicer width and height.6.5 SearchAnother powerful new feature of Excel 2010 is the Search.By clicking on the iconyou will see the Search box in the dropdown menu. Based on the selectedfield of the dimension hierarchy you can search for members by typing in the box. The results arefiltered on-the-fly as you start typing. This is very useful when you want to find a specific member ina large dimension (for example a specific customer from the customer dimension, or all documentsthat start with 011 ).

28You can select more options by clicking on button on the right area of search window so that youcan search by different hierarchy levels: CountryTerritoryCityCustomerSearch by different hierarchies

296.6 Subtotals, dimension levels display and grouping6.6.1 SubtotalsBy default, your pivot table will show Subtotals for various dimension hierarchy levels when you drilldown. We can turn it off by removing the thick in front of Subtotal option in the right-click dropdown menu.6.6.2 Dimension levels displayWhen browsing dimensions, many times pivot tables become too big. In theexample above we browsed to the deepest level of Date YQMD dimension.We can hide individual level from the hierarchy by right clicking on the fieldand select Show/Hide Fields. This can help us in keeping our pivot tableseasier to read and understand. In the below example we hid the quarter andday level of the Date YQMD dimension.

306.6.3 Grouping dimension dataIn pivot tables we can group data into custom groups. In our example we will group years 2010 and2011.We select the cells for 2010 and 2011 and right click to open the drop down menu. There we selectGroup option. This can be also done by clicking pivot table tab Options Group GroupSelection.Grouping: ad hoc, local, My topcustomersWe can rename the groups by clicking on their name and typing.If we wish to ungroup, we select a group, right click to open drop down menu and select Ungroup option. We can also do it by clicking Options Group Ungroup.6.7 Exercises6.7.1 Exercise 1 – Filters, Search and SortStep 1: Create a new report with Bill-to-Customer in rows, Item by Category in report filters and NetSales and Profit % as measures.Step 2: Set the report filter to Disk Product Group by Searching for “DISK” (remember to set theProduct Group Level!)Step 3: Set a TOP 10 display filter by Net SalesStep 4: Sort the pivot table descending by Profit%

316.7.2 Exercise 2 – SlicersUse the report created in the previous exercise.Step 2: Click on the pivot table and click Insert SlicerStep 3: Select Date YMD Year and under More Fields Month for the Slicers and click OK.Step 4: Resize the Month slicer and increase the number of columns to fit all the members.Step 5: Filter on Year 2010 and Month of June

327 FORMATTING7.1 Pivot table formattingFor pivot table formatting we can use all common Excel formatting functions. We can change fontsize, colors, cell size, cell color.Style does not change when we nest dimensions, add new dimensions and refresh data.We can find all formatting options in Styles toolbar. We can find it in Microsoft Excel Home tab.There is also separate PivotTable Styles section on the Design tab of the pivot table Tools group.7.2 Displaying and formatting measuresWe manage measures in the same way as dimensions. Usually we wish to show measures in a specificformat (numbers, percentage .) and style (red for negative or based on certain condition).In our example we remove the Sales-purchaser Person dimension and we add the Sales DiscountAmount measure. By doing this we are able to see by years what the discount amount was.7.2.1 Show values as By default measures are shown in the format defined in the OLAP cubes. But, for example, we aresometimes interested in percentage values rather than the actual values. In this way we can monitorchanging measures throughout a period of time in a more descriptive way. In our example we willshow Gross sales in percentage.We select the cells we want to format and right click. In the drop down menu we select Value FieldSettings. We can do this also by Options Active Field Field Settings.Other way of showing value in percentage, you need to selectappropriate measure and click on icon Show Value As in theribbon.

337.3 Conditional formattingConditional formatting enables us to visually indicate outliers based on the conditions we define.Including traffic lights into a pivot table is presented in this chapter. Traffic lights help managers toobserve business indicators and take measures if necessary. There are however other types availableas well: Highlight Cells Rules – highlight: greater than, lower than, equal to, between Top/Bottom Rules – Displaying top 10, bottom 10, top 10 %, bottom 10 %, above average,below average Data Bars – graphically displaying cell values.Color Scales – displaying cell color according to its valueIcon Sets – displaying cell icons according to its value (arrows, traffic lights ).First we have to select the cells we want to add traffic lights to. Then we go to Microsoft Excel Home Conditional Formatting Icon Sets.Selected traffic lights are automatically assigned to rows. By default Excel selects a light according tothe averages. If we wish to have our own rules we click More Rules In Apply Rule To – we select the location of the traffic light cells. In Select a Rule Type we select a ruletype. In our case the rule is determined by cell values. In Edit the Rule Description we set up rules foreach light. We click OK.

347.4 Exercises7.4.1 Exercise 1 - FormattingCreate report Sales by country in Dec 2009 over multiple measures.Step 1: Connect to server to establish connection to Sales cube.Step 2: Select Customer by country (Row label).Step 3: Drag Net Sales, Net Sales YTD and Net Sales YTD index in column label

35Step 4: Which country has the biggest YTD index?Step 5: Format numbers to remove decimals.Step 6: Drag Net Sales (again) in column label and sort by Net Sales YTD.Step 7: Show Net Sales as percentage of column.Step 8: Use conditional formatting

367.4.2 Exercise 2Bill to sell to ship to over net salesStep 1: Copy existing report you created in exercise 1 on same sheet.Step 2: Remove all measures except net sales.Step 3: Filter year 2006 in the background.Step 4: Filter country GB in the background.Step 5: Add bill to, sell to, and ship to dimension in row label.Step 4: Add additional measures shipped quantity, shipped/invoiced variance in column labels.Step 5: Format measures (no decimals)End result7.4.3 Exercise 3Sales/purchaser over multiple measures over customer by county and year 2009 Dec.Step 1: Create new report on the same sheet.Step 2: Select Sales/Purchaser dimension and drag it to row label.Step 3: Drag Net sales, profit, Net sales last YTD to column label.Step 4: Put Date dimension in the report filter and set filter 2009 – Dec.Step 5: Put Customer by country dimension in report filter and set it for Switzerland.Step 6: Format numbers.HINT: You have to format the description of measures to get the same result as in picture.

377.4.4 Exercise 4Conditional formatting in action:

387.4.5 Exercise 5Trend analysis: Net sales over quarters and years for GB by posting group.Step 1: Create new report on the same sheet.Step 2: Drag in Date dimension YMD in row label.Step 3: Drag in Quarter (separate date dimension under additional fields) in column label.Step 4: Set up Customer by country in background filter and set it for Great Britain.Step 5: Show net sales as percentage of row (HINT: Select all measures right click value fieldsettings).Step 6: Format cells to get the similar result as in the picture.Step 7: Apply conditional formatting:Step 8: Create 0,00%5,00%0,00%2012201320142015

398 CHARTS8.1 Pivot charts vs. Normal chartsPivot charts are linked with the pivot table. Whatever changes you make in the pivot table areimmediately reflected on the chart and vice versa. Everything in the pivot table is also present on thechart.Normal charts on the other hand have to be manually defined and therefore any changes to thepivot table don’t automatically reflect in the chart. Although this can be seen as a downside, it alsogives you a lot more options in customizing your chart independently of the pivot table.8.1.1 Pivot table and pivot chart commands comparisonPivot tableDescriptionPivot chartDescriptionValuesSum of numeric dataValuesSum of numeric dataRow labelsDisplaying fields on a margin Axis fieldsof the report(Categories)Displaying fields on chart axisColumnlabelsDisplaying fields in the top of Legend fieldsthe report(Series) labelsDisplaying fields in chart legendReport filterIt is used for filtering reports Report filterby selected criteriaIt is used for filt

4 PIVOT TABLE OVERVIEW 14 4.1 Show fields related to area 14 4.2 Pivot table field list 15 4.3 Pivot table preview 16 4.4 Pivot Table tools tabs 16 5 WORKING WITH PIVOT TABLES 18 5.1 Creating, modifying and deleting the pivot table layout 18 5.2 Nesting dimensions 18 5.3 Exercises 19 6 DATA DISPLAY OPTIONS 22