Solved

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

Posted on 2010-09-24
5
130 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

617 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