Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two questions about PivotTable in Excel
Hello! I have 2 new issues:
1. I cannot address calculated member, that is associated with non-All member of a dimension. For ex., if I declare calculated member (AdventureWorks) using Create member [Adventure Works].[Date].[Calendar Year].[All Periods].MyMember as (Measures.CurrentMember,[Date].[Calendar Year].&[2004]) -(Measures.CurrentMember,[Date].[Calendar Year].&[2003]) I can access this calculated member in Excel PivotTable (if I specify displaying OLAP calculated members in PivotTable options), while such calculated member: Create member [Adventure Works].[Date].[Calendar Year].[All Periods].MyMember as (Measures.CurrentMember,[Date].[Calendar Year].&[2004]) -(Measures.CurrentMember,[Date].[Calendar Year].&[2003]) I cannot find anywhere in Excel. The second one syntax is also correct - the following MDX Query works fine: Select {[Date].[Calendar Year].[All Periods].Children, [Date].[Calendar Year].[All Periods].MyMember, [Date].[Calendar Year].MyMember } on columns from [Adventure Works] 2. There are many troubles with printing PivotTable. The greatest problem I met is to set up PageBreaks correctly. I mean that if I place any group on columns on PivotTable, I want to deny PageBreak to break values in this group on different pages. AdventureWorks sample Excel file is attached. Look at it. You will discover, that Pacific region is placed on two pages - #2 and #3. In this case, I have to place manual pagebreak after North America (column Q) to make my printing useful. Of course, I have much more pages than 3 on my sheet, and manually placing page breaks is not an option. I write a macro to place pagebreaks automatically, but VBA objects that allow to determine, in what page my cell is contained, are very uncomfortable for my task - I have to iterate in HPageBreaks collection, and it works extremely slow. Best Regards Dmitry Jolobov |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
problem with pivotTable in Excel | Excel Discussion (Misc queries) | |||
Creating a PivotTable w/o selecting data in an existing PivotTable | Excel Discussion (Misc queries) | |||
PivotTable - PivotTable Field name is not valid - error! | Excel Programming |