Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 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