Common Questions Around Excel 2007 OLAP PivotTables

re: Common Questions Around Excel 2007 OLAP PivotTables

AM — Tue, 04 Mar 2008 05:18:58 GMT

Hello,

I have a question about OLAP HTTP Datasource and it's related security. My Datasource is running on IIS6 under IUSR account.

One of my users is exporting the "Export to Excel" from a OWC11 cube on the web and along with that comes the datasource. The user then sends the spreadsheet to some other user and that user has access to the cube as well. How do I secure it such that the first user is still able to download the Excel (with or without the connection string) such that the cube's data is not available to the 2nd user?

Can you guys help?

Thanks

re: Common Questions Around Excel 2007 OLAP PivotTables

BJ — Fri, 29 Feb 2008 21:47:33 GMT

Hello,

I have a question about Pivot tables. I want to change several "sum of ...." to "average ..." with one move. How can I do this? When I select multiples and format to change to average it just changes one. Can anyone help me?

re: Common Questions Around Excel 2007 OLAP PivotTables

Hank — Thu, 28 Feb 2008 01:51:01 GMT

Hi Allan,

I have a question regarding PivotTable security.

We want to publish PivotTable reports with external connections to our AS2005 cubes to a document library on MOSS. We want to security trim external data based on users' permissions on the AS DB when they open the workbook either in browser or in Excel.

I was able to make it to work in browser using refreshing on open Excel connection property and MOSS view items only permission based on recommendation in a January blog entry blogs.msdn.com/.../personalized-data-in-excel-services.aspx. But then users would not be able to open the workbook in Excel any more, since if i give them the permissions then when refreshing on open failed, users still can see data originally published by the workbook authors. The behavior is same whether in browser or in Excel.

It seems to me Excel refresh on open is not designed as a security mechanism, but rather as an convenience to synchronize data. But i notice there is another check box called remove data from external data range before saving the workbook. That appears exactly what we are looking for. But unfornately it is disabled for AS2005 data source (i checked it works with SQL data source)

The bottom line is why it is disabled for olap data source and how can we accomplish our security requirements?

thanks

Hank

re: Common Questions Around Excel 2007 OLAP PivotTables

Allan Folting (Microsoft) — Fri, 22 Feb 2008 22:47:02 GMT

skyMike001, for OLAP PivotTables in Excel, members are listed in the order specified on the OLAP server by default (Data source order).

For a time dimension, if you make sure to sort the attribute members by key (OrderBy=Key) on the OLAP server, and the Key is a DateTime type attribute, then Excel will list the members in chronological order.

If you use the sorting options in Excel OLAP PivotTables however, member captions are retrieved as strings (even when they really are dates) and therefore sorting hierarchies with dates will typically not give you what you want but instead do a string based sort.

So to control sort order for dates in Excel OLAP PivotTables, you typically need to rely on the sorting capabilities of the OLAP server.

In non-OLAP PivotTables you can specify any sort order for items of fields by moving them around manually and that order will be used in the report filter field drop down as well as on rows and columns. Also, in non-OLAP PivotTables, date values are treated as dates (not strings) so sorting in the PivotTable will sort chronologically (ascending or descending depending on what you choose).

You can also manually specify the sort order for members of an OLAP hierarchy on rows and columns in the PivotTable but when the hierarchy is moved to the report filter area, this order is not used since the filter drop down is populated by a query to the OLAP server when it is opened. Therefore members are always listed in the order specified on the OLAP server in the filter drop downs.

As you discovered, in the object model for OLAP PivotTables, CubeFields represent hierarchies and PivotFields represent levels within hierarchies. Excel does not keep track of all levels of all hierarchies at all times so for hierarchies not added to the PivotTable yet, you need to use the CreatePivotFields method to create PivotFields representing levels of those hierarchies if you want to work with them.

Here is an object model example of sorting and filtering a hierarchy before adding it to the PivotTable (and then adding it to rows at the end):

Sub SortAndFilter()

Dim myPT As PivotTable

Set myPT = ActiveSheet.PivotTables("PivotTable1")

With myPT

.CubeFields("[Customer].[Customer Geography]").CreatePivotFields

.PivotFields("[Customer].[Customer Geography].[Country]").AutoSort xlDescending, "[Customer].[Customer Geography].[Country]"

.PivotFields("[Customer].[Customer Geography].[Country]").VisibleItemsList = Array("[Customer].[Customer Geography].[Country].&[United States]", "[Customer].[Customer Geography].[Country].&[Germany]", "[Customer].[Customer Geography].[Country].&[Australia]")

.PivotFields("[Customer].[Customer Geography].[State-Province]").VisibleItemsList = Array("")

.PivotFields("[Customer].[Customer Geography].[City]").VisibleItemsList = Array("")

.PivotFields("[Customer].[Customer Geography].[Postal Code]").VisibleItemsList = Array("")

.PivotFields("[Customer].[Customer Geography].[Customer]").VisibleItemsList = Array("")

.CubeFields("[Customer].[Customer Geography]").Orientation = xlRowField

End With

End Sub

re: Common Questions Around Excel 2007 OLAP PivotTables

Allan Folting (Microsoft) — Fri, 22 Feb 2008 20:41:31 GMT

Eric, creating session members/calculations are supported in Analysis Services local cubes as long as all the entities referenced by the expressions for these members/calculations are included in the local cube definition.

I just created a local cube from within Excel 2007 based on the Adventure Works cube and then I used the Excel object model code that you tried and I was able to create the calculations just fine.

If you can provide some more details on the specific steps you take when creating the local cube etc. I will do my best to try to help out.

Excel 2007 OLAP PivotTables - Calculated Members

Eric — Tue, 19 Feb 2008 16:00:16 GMT

I got a chance to experiment a little more.

Even Using OLAP 8.0 or 9.0 in Excel 2007 (or 2003), with or without "Show calculated members from OLAP server" checked, I can't get this to work with my offline cube.

If I look at the DLL source of the error for the pvt.CalculatedMembers.Add line, it tells me "syntax error in axis definition" (if I try strFormula = "[Measures].[Internet Sales Amount]") or "syntax error, did not expect end of statement but found extra text near "+1" " (for example, if I tried strFormula = "1+1".

So unless it's some OLAP driver setting issue somewhere, I'm guessing this is a limitation of offline cubes? (at least those created with the Office Offline Cube Wizard)

re: Common Questions Around Excel 2007 OLAP PivotTables

Eric — Mon, 18 Feb 2008 17:09:44 GMT

I'm trying this out with Excel 2003 and an offline OLAP cube (.cub file) generated via Microsoft Query's Offline Cube Wizard, and, unfortunately, it isn't working out so well.

For your code that shows:

Set pvt = Sheet1.PivotTables("PivotTable1")

strName = "[Measures].[Internet Sales Amount 25 %]"

strFormula = "[Measures].[Internet Sales Amount]*1.25"

pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember

I can only get a constant integer measure to be added successfully. That is, if I set strFormula = 1, or strFormula = "2", then it works. If I try something as simple as strFormula = "1+1", or strFormula = "[Measures].[Internet Sales Amount]", or strFormula = 2.52 though, it fails.

Does anyone know if this is a limitation of Excel 2003, the Offline Cube Wizard, or both?

I'm searching for a way to get average and standard deviation calculations in the cube using these technologies.

OLAP PivotTable filter ordering

skyMike001 — Sun, 17 Feb 2008 22:15:33 GMT

Hi There

I've tried to get help on this elsewhere with no joy. I have a pivot table based on a SQL Server 2005 cube. At the moment the database behind the cube has dates until the end of this year in descending order. When the data is loaded into a pivottable and the dates are selected as a report filter, they are not in descending order. I would also like a way to stop the dates in the future from being displayed in the report filter.

1. Timothy Zapawa’s book “Excel 2007 Advanced Report Development” pp276 said I could drag that field into the row labels or column labels area, sort it there, then drag it back to the report filter. This hasn’t worked.

2. Another option was to apply filters by manually adding the cube field to the pivottable (see msdn2.microsoft.com.en-us/.../bb256319.aspx and ssas-info.com/RamunasBalukonisBlog)

First of all, I’ve needed to get a list of all of the programmatic field names for the pivottable. This didn’t work because the cube based fields aren’t referred to as PivotFields …(Application Error 1004)

Sub List_PvtFields()

Dim pvtTable As PivotTable

Dim pvtField As PivotField

Dim objNewSheet As Object

Dim intRow As Integer

Set objNewSheet = Worksheets.Add

objNewSheet.Activate

intRow = 1

Set pvtTable = ActiveSheet.PivotTables(1)

For Each pvtField In pvtTable.PivotFields

objNewSheet.Cells(intRow, 1).Value = pvtField.Name

intRow = intRow + 1

Next pvtField

End Sub

The following did work…

Sub list_cube_fields()

Dim objNewSheet As Object

Dim intRow As Integer

Dim objCubeFld As Object

Set objNewSheet = Worksheets.Add

objNewSheet.Activate

intRow = 1

For Each objCubeFld In Worksheets("DailyGraph").PivotTables(1).CubeFields

objNewSheet.Cells(intRow, 1).Value = objCubeFld.Name

intRow = intRow + 1

Next objCubeFld

End Sub

Next I followed (see msdn2.microsoft.com.en-us/.../bb256319.aspx) and I think that the following line worked….

ActiveSheet.PivotTables _("ptDailyGraph").CubeFields("[Date].[Year]").CreatePivotFields

So this should have added this cube field as a pivottable field. But this next line doen’t work…(I get “RunTime Error 438 – Object does not support this property or method”)

ActiveSheet.PivotTables("ptDailyGraph").PivotField("[Date].[Year]").VisibleItemsList = Array("[Date].[Year].&[2006]", "[Date].[Year].&[2007]")

I’ve also tried this line…and got the same error message…

ActiveSheet.PivotTables("ptDailyGraph").PivotField("[Date].[Year]").AutoSort

xlDescending

Any ideas?

Thanks a lot.

re: Common Questions Around Excel 2007 OLAP PivotTables

Marco Russo — Sun, 10 Feb 2008 20:45:16 GMT

One year ago I opened an incident to fix the AS2005 SP2 issues with filtering calculated members. After a few months, the answer was that it was a "by design" behavior and no fix would be created. One year after, I see that this is still an issue affecting more and more people and I still think MS had not handled this issue in a good way, limiting the adoption of Excel 2007 and sometimes of SSAS 2005.

The fact that there is not a real fix for a breaking change introduced by a Service Pack one year ago is really unbelieavable. Workaround like that of using an older version of the pivot table cannot be considered a workaround at all!

Marco