Solved

"PivotTable.DisplayEmptyRows  = false " in Excel pulling from Analysis Server 2008, doesn't work.

Posted on 2010-09-24
5
122 Views
Last Modified: 2016-02-15
We display financial information, using Excel pulling from Analysis Server 2008.

In vba,  "ptTemp.DisplayEmptyRow = false" works fine for hiding empty rows of data on most pivot tables, but if the "account numbers" parent/child dimension is in the row fields, it doesn't.

The row dimension look like this:

Balance Sheet
      Assets
            Current Assets
                  Cash
                  …
Total Balance Sheet

Income Statement
      Gross Profit
            Revenue
                  Item A
                  ….
Total Income Statement

Ratios
      Current Ratio
      Debt:Equity
      ….
Total Ratios

When  "ptTemp.DisplayEmptyRow = false" is used, all the rows with no values disappear as expected, except that the Section header "Ratios" disappears too and the ratios mash into the Income Statement section….

Is there a settings in SSAS that would prevent this, or if not, an elegant workaround in VBA?
0
Comment
Question by:WaldenSwanson
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:CSLARSEN
ID: 33756065
Hi,
Just trying to understand.
How is your "chart of account" dimension build, is it a parent -child dimension?
Are all members of the ratio "header" calculated members defined in the cube and assigned to the "row" dimension.

Cheers
cslarsen


0
 

Author Comment

by:WaldenSwanson
ID: 33757147
Good questions.

Yes....the Chart of Accounts dimension is parent/child.

All of the Ratio header children are calculated members defined in the cube.

In this pivot table, the "Chart of Accounts" Dimension is in the row field...
0
 
LVL 15

Accepted Solution

by:
CSLARSEN earned 250 total points
ID: 33768733
Hi again,
OK, thx, moving on...

Is "Ratio" a calculated member  as well?
If so, then->
Do you have anything entered in the setting for the "non-empty behavior"
on the parent member "Ratio"
on the Ratio children

Maybe the "non-empty behavior" could be set to handle this situation.
A guess would be to set the  "non-empty behavior" of the "Ratio" element.

If not,
my guess is that "Ratio" does not have any other normal children which the cube can use to control whether or not to show the "Ratio" element.
So you could try to establish "Ratio" as a calculated member and set "non-empty behavior" or
create a dummy element in the parent-child hierarchy to "Ratio".

Cheers
cslarsen
0
 

Author Comment

by:WaldenSwanson
ID: 33770431
Thanks...

Will try this later this week and let you know.

Appreciatively,

W
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem with a moving column in Excel 6 42
MS SQL Server select from Sub Table 14 26
SQL Recursion 6 20
excel table with over 25000 rows. 3 32
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question