office 2010 pro plus 32bit Common Questions Around

[titejj52]

Today's writer: Allan Folting, a system manager who functions around the Excel group. have gotten lots of concerns from clients all around certain performance in Microsoft Excel 2007 PivotTables that may be missing when related to Microsoft SQL Server 2005 Evaluation Solutions Services Pack 2 cubes: individual calculated members Showing non-visual totals when filtering
I have also gotten a lot of requests for that capability to add calculations to OLAP PivotTables making use of the object model,buy microsoft office 2007 Standard, specifically: calculated members/measures Adding named sets
Today I will explore these topics and outline present tactics and workarounds to address this. Excel 2007 precise to Evaluation Solutions 2005 Services Pack 2 (SP2) cubes having a little bit of qualifications data. Solutions 2005 SP2+, Excel 2007 works by using subselects inside the MDX queries generated when filtering is applied to a PivotTable. following instance of MDX produced by an Excel 2007 PivotTable, the person is filtering a geography hierarchy to only display United states of america and Australia which is represented from the subselect part for the query (in bold font). EMPTY Hierarchize({DrilldownLevel([Customer].[Customer Geography].[All Customers])}) DIMENSION Properties PARENT_UNIQUE_NAME ON COLUMNS
FROM
( Geography].[Country].&[United States], [Customer].[Customer Geography].[Country].&[Australia]}) ON COLUMNS FROM [Adventure Works] ([Measures].[Internet Sales Amount])
CELL Attributes VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS inside OLAP server is required in order for your new Label, Date and Value filtering features to be available in Excel 2007 PivotTables. The reason is that the subselect feature is designed for making Analysis Solutions calculate visual totals while filtering which was also an explicit design goal for Excel 2007 PivotTables. filters are applied to different hierarchies at the same time, the filters are applied inside the order that the person added them, employing subselects, which ensures an intuitive filter experience where filtering is always based around the totals currently displayed within the PivotTable. why subselects are only used with Analysis Providers 2005 SP2 or newer versions is that Evaluation Companies 2005 introduced a provider property in SP2 so that client applications can determine whether the provider supports subselects. The provider property is MDPROP_MDX_SUBQUERIES. this provider property to determine whether subselects are supported,windows 7 home premium x64, Excel exposes the new filtering features with any OLAP provider that implements this property (and supports other fundamental OLAP provider capabilities needed, I included a link to more data on provider attributes that Excel reads inside additional resources section below). discuss the unique features affected by using subselects for filtering. I will start with filtering particular person calculated members. calculated members with Excel 2007 PivotTables related to Analysis Solutions 2005 SP2+ cubes are not supported in subselects with Evaluation Services 2005 so Excel 2007 does not offer filtering on individual calculated members for Analysis Providers 2005 SP2+ servers. can choose to display all calculated members, or no calculated members at all, within the PivotTable Options dialog box utilizing the "Show calculated members from OLAP server" option. this does not apply to calculated members from the Measures hierarchy (calculated measures); those are always displayed regardless for the option. dialog with the "Show calculated members from OLAP server" option below illustrates the filter drop down in a PivotTable for a hierarchy with calculated members ("Budget Variance" and "Budget Variance %"). the checkboxes for the calculated members are disabled and therefore cannot be unselected. cannot be individually selected when subselects are used for filtering with Evaluation Solutions 2005 SP2+ with Excel 2007 PivotTables connected to Analysis Solutions 2005 SP2+ cubes behavior of subselects with Analysis Services 2005 is to always generate visual totals. reason, PivotTables created by using Excel 2007 do not allow the consumer to turn off visual totals with Analysis Solutions SP2+ cubes. screenshot below illustrates a PivotTable with visual totals. The total for U.s. is the sum of your states currently with the filter (California, New York and Washington). visual totals screenshot illustrates a PivotTable with non-visual totals. The total for Usa is not affected from the fact that some states are filtered out; it is still displaying the total sales amount for all existing states. non-visual totals filtering calculated members and generating non visual totals are important features for common scenarios. The Excel and Analysis Solutions teams are working together on a future solution for lifting these limitations and this section outlines recent workarounds for enabling these features in Excel 2007 with Evaluation Companies 2005 SP2+. for creating PivotTables that support both non visual totals and filtering particular person calculated members with Evaluation Solutions 2005 SP2+ cubes is to create PivotTables in Excel 2007 using a pre-2007 version. was introduced in Excel 2000. PivotTables created which has a certain version ensures that the PivotTable is compatible with the corresponding version of Excel in that it is generally created with the same default settings as in that version of Excel and it exhibits the same behaviors. This is also true when applying the Excel PivotTable object model through code. A benefit from this is that users can expect macros created in previous versions of Excel to still work exactly the same way in newer versions of Excel. below outlines the different PivotTable versions supported in Excel as well as which version is the default created in different versions of Excel. of Excel
Version number
Default PivotTable version created
Excel 2000
9
PivotTable.Version = 0 (xlPivotTableVersion2000)
Excel 2002
PivotTable.Version = 1 (xlPivotTableVersion10)
Excel 2003
PivotTable.Version = 1 (xlPivotTableVersion10)
Excel 2007
PivotTable.Version = 3 (xlPivotTableVersion12) is an additional PivotTable version supported in Excel 2007, namely version two (xlPivotTableVersion11). However, there is no functional difference between version 1 and version two PivotTables. Since no version of Excel creates version two PivotTables by default, it isn't included in the table above. Version two was added in Excel 2007 for completeness although Excel 2003 creates version 1 PivotTables. that has a version number less than 3, Excel will generate Excel 2003 style MDX queries (no use of subselects) and will support filtering calculated members individually and toggling visual totals. above, the drawback of working with old version PivotTables is that the new filtering features are not available. two solutions in Excel 2007 to create PivotTables that has a precise version: with Excel 2007 in compatibility mode (this mode is on when you open xls files) since then Excel automatically creates version 1 PivotTables You can explicitly specify the version of a PivotTable when creating it if you create it utilizing the object model (instance below)
When Excel 2007 is in Compatibility Mode, version 1 PivotTables are created code below creates a version 1 (PivotTable.Version = xlPivotTableVersion10) PivotTable, turns on the new Excel 2007 default layout and turns off visual totals. It is implementing the sample Adventure Performs cube that comes with Analysis Solutions 2005. You'll need to change the OLAPServerName to your actual OLAP server name and make sure there is a worksheet named Sheet1 that's empty during the area where the PivotTable is placed to try out this code. With ActiveWorkbook.PivotCaches.Create(SourceType:=xlEx ternal,office Standard license, Version:=xlPivotTableVersion10) Array("OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Performs DW;Data Source=OLAPServerName;MDX Compatibility=1;Safety Options=2;MDX Lacking Member Mode=Error")
.CommandType = xlCmdCube
.CommandText = Array("Adventure Works")
.MaintainConnection = True
.CreatePivotTable TableDestination:="Sheet1!R1C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 With = False
ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight16"
ActiveSheet.PivotTables("PivotTable1").VisualTotal s = False below shows the PivotTable Options dialog box for a version 1 (xlPivotTableVersion10) PivotTable in Excel 2007 with the option to toggle visual totals utilising the "Include filtered items in totals" checkbox. Non-visual totals are displayed when the checkbox is selected. using a version less than 3 in Excel 2007 allow users to turn off visual totals in OLAP PivotTables applying the PivotTable Options dialog box ("Include filtered items in totals") if you save to an Excel 2007 file format while Excel is in compatibility mode, all PivotTables using a version number less than 3 will be marked for upgrade to version 3 (xlPivotTableVersion12) and the next refresh you perform on each PivotTable will do the actual upgrade. Once a PivotTable is upgraded to version 3, you'll lose the ability to filter on calculated members individually and toggling visual totals. want to avoid the automatic upgrade,genuine windows 7 serial, before refreshing the PivotTables, go to each one and set PivotTable.PivotCache.UpgradeOnRefresh to False using the object model. For illustration, during the immediate window (press ALT+F11 and then CTRL+G to display the immediate window) type and run Activecell.PivotTable.PivotCache.UpgradeOnRefresh = False with the active cell inside the PivotTable. PivotTable version upgrade for that selected PivotTable while in the Immediate window 2007 and SQL Server Evaluation Solutions 2005 Service Pack 2 2007 Document: Designing SQL Server 2005 Evaluation Services Cubes for Excel 2007 PivotTables DB for OLAP Properties Used by Excel 2007 calculated members/measures and sets in Excel 2007 OLAP PivotTables offer a consumer interface for creating calculated members/measures and sets in PivotTables connected to OLAP cubes. However, while in the object model there is support for creating personal calculations and sets in PivotTables with OLAP data sources. collection holds calculations and sets private to a PivotTable and it has an Add method for creating new calculations and sets. examples of creating a calculated measure, a calculated member and a named set applying the CalculatedMembers collection for the PivotTable object. All the examples are implementing the sample Adventure Functions database that comes with Analysis Providers 2005. adding a calculated measure in an Excel PivotTable linked to an OLAP data source:
Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String = Sheet1.PivotTables("PivotTable1")
strName = "[Measures].[Internet Sales Amount 25 %]"
strFormula = "[Measures].[Internet Sales Amount]*1.25"
pvt.CalculatedMembers.Add Identify:=strName, Formula:=strFormula,office 2010 pro plus 32bit, Type:=xlCalculatedMember measure "Internet Sales Amount 25%" added to an OLAP PivotTable making use of the CalculatedMembers collection from the object model adding a calculated member to the Adventure Will work Product level in the Product Categories hierarchy in an Excel PivotTable connected to an OLAP data source:
Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String = Sheet1.PivotTables("PivotTable1")
strName = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38 25 %]"
strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38]*1.25"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember
pvt.ViewCalculatedMembers = True below shows the PivotTable with the calculated member ("Mountain-100 Silver, 38 25%") added to the Product level with the Product Categories hierarchy. "Mountain-100 Silver, 38 25%" added to an OLAP PivotTable utilising the CalculatedMembers collection while in the object model including a named set in an Excel PivotTable connected to an OLAP data source:
Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Dim cbf As CubeField = Sheet1.PivotTables("PivotTable1")
strName = "[My Mountain Bikes]"
strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].children"
pvt.CalculatedMembers.Add Identify:=strName, Formula:=strFormula, Type:=xlCalculatedSet
Set cbf = pvt.CubeFields.AddSet(Name:="[My Mountain Bikes]", Caption:="Mountain Bikes") below shows the PivotTable with the named set ("Mountain Bikes") added. "Mountain Bikes" added to an OLAP PivotTable utilizing the CalculatedMembers collection inside the object model Excel 2007 only support named sets consisting of members from a single hierarchy. that even though Excel Solutions 2007 doesn't support Excel workbooks with macros/code, you can still use code as illustrated here to create calculated members/measures and named sets, remove the code after creating these and then publish the workbook to Excel Solutions. This way the calculations created in Excel can be exposed through Excel Services.