Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-09-24
5
Medium Priority
?
140 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 1000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

926 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