Xpivot User manual and version update announcement

This article only describes the general features of Xpivot, such as a message discussion that is of interest to the advanced features customized in the project

Xpivot Current Version v2.1 "2015-03-04 release"

v2.1 Download Link: http://pan.baidu.com/s/1i3sx3Sx Password: 1ra0

Installation considerations:

Installation files are divided into 32-bit and 64-bit two versions, please download the installation against your own version of Excel (note that it is not a Windows version), as shown in Excel 2010 and Excel 2013 version number viewing mode

After installation, if the following error message is reported during use, manually grant the current user writable permission to the C:\Program files\xpivot\xpivot 2.0\xpivot 2.0.dll.config file.

1. Logon

   The cross-domain authentication of SSAS has never been a problem, because SSAS ' rights control is based on the role created by Windows users, so there is a principle problem with service-side authentication, and it is easy for many novices to confuse the problem that authorized users must be server-side users that the SSAS server can identify. Instead of your access to the client users, so the official encouragement is the server and the client in the same domain, the server can be very convenient to the domain name authorization, and many cases in reality is the server and the client is not in the same domain, or even both are not in any domain, then the mainstream solution has the following ( Only scenarios for Excel clients are discussed here:

   1. Netonly runas, the principle is that the service-side account credential information is written to Startupinfo before the Excel process is created, and then the Excel process can access SSAS as a server user. The disadvantage is that each time the user creates a new Excel file, a netonly runas login authentication is required, and when you double-click to open an existing Excel report, you will find that you cannot modify the Update pivot table because the Excel process is not authorized at this time. In terms of the user experience is really patience, interested can refer to

      Https://technet.microsoft.com/en-us/sysinternals/cc300361.aspx

   2. Configure the client Windows user Password and the SSAS server user password consistent, in order to achieve the purpose of spoofing the server to complete authentication, this scheme seems to be a perfect solution to the problem of the previous solution, can fully configure user rights, the end user does not need to verify the identity every time, However, the actual production environment will cause some unnecessary trouble, such as the client user modified the password, resulting in a user with the same name as the server password inconsistent, then the access rights are terminated

   3. HTTP mode access, if the configured Web server is anonymous authentication, there is no permission to control, all access to the client's permissions are the same; If you are configuring Basic authentication (It seems that you can only use basic instead of Windows authentication when you don't have a domain environment), you're not too secure.

   4. Excel Client Access to SSAS, in addition to the above scenarios, but also support direct access, you need to open the server port 2383 (the default SSAS port) to the client access, The default Access wizard enters the service-side account password to the last step when you create a new PivotTable, the transport layer exception is reported because this step loses the account password information.

   Xpivot plug-in support all the above login authentication method, the following first step, configure the data source "source Config"

   Open the data source configuration you will see the following configuration information as a set of data sources, if you need to configure multiple sets of data sources, please use a vertical bar | Delimited, submit the configuration will be in the above drop-down box listing all the data source groups

   Cube=adventure Works; This configuration must be the name of the cube, see highlights

   Cube Data source=.; This configuration is cube's data source, if it is in the same domain or the fourth access mode only need to configure IP, if it is HTTP access, it needs to be configured as the full Http://....msmdpump.dll path

   Cube Initial Catalog=adventureworksdw2012multidimensional-se; This configuration is the SSAS DB instance name, see the database name under databases

   Cube Integrated Security=sspi; This configuration is optional, if set, Xpivot ignores login account information and accesses the SSAS directly using the current Windows user

   DW Data source=.; The following configuration items are optional, configuration is the DW database connection information, if not configured, can only access the SSAS Cube, Xpivot and DW-related functions are invalid

   DW Initial catalog=adventureworksdw2012;

   DW Integrated Security=sspi; This configuration item is optional, if not set, you must first log in to Xpiovt to use the DW-related features, it is important to note that if cube Integrated security and DW Integrated security are not set, In order for the identity of the login Xpivot to be able to access the DW and cube successfully, the cube and DW must be configured on the server side using the same account password, except that the former is a Windows user, which is a DB user

   Xpivot Landing window is very simple, enter the account password, account name if it is a domain name, you need to enter the full domain name, for example: Domainname\domainuser, the figure shown in the Test1 is a Windows user, so after landing will be in the Xpivot The logon button displays the. \test1 account information, the login window permanently Bind to computer option box if you select the login again, Xpivot will remember this account identity, and at any time on the same computer to open Excel, Will default to use this account identity to access the server, if the login is not checked this box, the identity of the login life cycle is just the current open Excel, close the Excel workbook, this identity will disappear, for temporary switch identity permissions

3. New PivotTable

   If you are not logged in at this time and the data source is not configured with integrated SECURITY=SSPI, the login window will pop up, and if you have already logged in or configured SSPI, you can insert the PivotTable report directly in the current cell

5. New Table

   If the current cell is empty, a text-editing box is used to enter the SQL script, which can be annotated with annotation parameter cells, such as /*a1*//*a1*/, so that when the specified cell contents change, will prompt you to refresh the table

   And if the current cell is inside the table, clicking this button pops up a text box that displays the query script for the current table for editing the update

7. Share

   Click the Share button to open a custom task pane, as shown, the first row of the public disk path can be manually entered and enter the confirmation, you can also click on the right button to navigate to a specific folder path, and then automatically in the following tree form to show all folders and Excel files in this directory.

   The right-click menu appears when you right-click a folder or Excel report file

   The following is a description of the relevant functions of the context menu:

   New folder creates a subfolder that is available only when you right-click a folder, under the currently selected folder

   Open file: Excel opens the selected Excel report file

   Save file: If you right-click on a folder, the current Excel document will be saved in this folder, and if an Excel file is selected, the current Excel document will be overwritten with the selected Excel document

   "Rename" Rename: Can rename folder or Excel file

   "Copy Path" copy path: can copy folder or Excel file of the public disk path, for email sharing to colleagues

9. Toggle fields

Shrink collapse PivotTable Field List, as shown, this feature is compatible with excel2007,2010 and 2013, because Excel2013 itself has the same functionality, so this feature is only meaningful on excel2007,2010 version

2. Copy Address

   Copies the selected cell range address (which can be contiguous or discontinuous multiple cells) to the Clipboard, which is used to paste the data reference address on the pinned report template to enable automatic synchronization of raw data to a fixed template sheet after PivotTable or QueryTable refresh.

4. Filter

   Automatic filter, as shown, if you need to do a multi-dimensional selection, and the number of options is limited, but also can be manually operated, and if too many options, cannot be seen by the naked eye filter, it is possible to use this function to locate the current cell on a PivotTable filter, Then click the Filter button in the menu, the pop-up window lists the currently selected options, in the edit box to enter all the options to select the confirmation, you can automatically complete the filtering work

   
   

6. Synchronize Filters

   Synchronous filters, similar to the functionality of the Slicer slicer, are not as flexible as the slicers have their functional limitations.

   As shown below three PivotTable filter different country respectively, modify the filter name of a and C to country AC, modify the filter name of B to country B

   Then clicking "Synchronize Filters" in the menu will create a PivotTable for you to set the global filter, add a country filter and modify the filter name to country AC

   After modifying the options for the global filter, Xpivot will remind you that the global filter has been updated, whether you need to sync to the other PivotTable on the current workbook, select Yes to start syncing, and the results are as follows

8. Drill down

   When a PivotTable row and column are occupied by various dimensions, if you want to continue to drill down to a new dimension, not only visually will feel cluttered, and the performance of the drill will be affected, as shown in the left image below to locate the current cell to B7, and then click "Drill Down" Will create a new PivotTable for you as follows right, the dimensions on all rows and columns are migrated to the filter area, then you can put the other dimensions you want to observe in the row and column area, the service side of the calculation is greatly reduced, so the front-end data loading efficiency will be much higher, This feature Excel2013 is available, so it applies to versions of Excel under 13

1. Dashboard

   As shown, create a new PivotTable and set up two filters, locate the current cell on this PivotTable, and then click the Dashboard button, at which time a blank PivotChart is created for you on the current page. Edit the contents of the chart on PivotChart, follow this step to create four PivotChart, render different chart contents, and build a dashboard together, then update the above PivotTable two filter options. The following four PivotChart will automatically synchronize the PivotTable filter to update the chart data

1. Mdx

This feature is available in Excel2013, as described in http://www.cnblogs.com/xpivot/p/4310998.html, so it is only available for versions under 13.

Navigate to a content PivotTable and click the MDX button to display the MDX script for the current PivotTable below the pop-up form

, edit an MDX calculation item above, enter the calculated item name in the first line, enter the MDX formula script for the calculated item below, click on submit to confirm the effect as follows:

Notice that the current PivotTable MDX script and the rendered data content have changed

Xpivot User manual and version update announcement