Solved

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

Posted on 2010-09-24
5
117 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now