×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Pivot Table - Hide a Column Without Values

Excel Pivot Table - Hide a Column Without Values

Excel Pivot Table - Hide a Column Without Values

(OP)
Hi,

I have a Pivot Table in Excel that access a cube in Microsoft SSAS.

This cube has a time dimension with MONTH and YEAR.

In the cube some calculated members are evaluated to NULL for the MONTH level of the time dimension. This is half of the solution I need.

Now I have to find a way to hide the columns in Excel where the calculated members are NULL for the MONTH level of time dimension.

This is the Excel WorkSheet I have:



As you can see with a MDX expression I was abble to set the values of AVG SAMPLE to zero when the TIME dimension level is MONTH and all columns are empty.

My customer, however, is asking me to remove the columns for MONTH levels and I don't know how to do it.

I can't hide the MONTH level completely because there are some calculated members that should be shown in MONTH level, like PREÇO BASE.

Is it possible to do this?

Thank you again.

RE: Excel Pivot Table - Hide a Column Without Values

Hi,

Welcome to Tek-Tips. Hope your experience here is positive.

You stated...
"As you can see..."

Well I can't see what you are referring to. All that I see is 3 rows in Agosto having a value of ZERO. That's all.

Whenever you have a pivot aggregation, there is the possibility that some rows will contain ZERO. That's just a fact.

Furthermore, when you use source data that has already been processed, in this case a cube, your options for reporting can be severely reduced. There's a trade off of speed versus flexibility: a cube makes processing huge amounts of data faster, but then you give up other ways of grouping and aggregating your data.

From what you have presented here...
WYSIWYG...
What You See Is What You Get.

If your customer wants a report showing multiple months, then there's no getting around the possibility of ZERO data in some rows.

By the way, there should be no row having ZERO data in ALL columns.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Pivot Table - Hide a Column Without Values

(OP)
Hello, SkipVought

The AVG columns show zero for all the records because I wrote the following MDX expression in my cube:

SCOPE
({([DIM CALENDARIO - CRIACAO].[Hierarquia Tempo].[Mes])}, [Measures].[AVG]);
THIS = 0;
END SCOPE;

This is why the columns AVG are showing zero for all rows.

Do you think it would be possible to write a macro in Excel that hides all colmns with a specific label?

Thanks.

RE: Excel Pivot Table - Hide a Column Without Values

Under what conditions will Avg not be ALL ZEROS?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Pivot Table - Hide a Column Without Values

(OP)
When [DIM CALENDARIO - CRIACAO].[Hierarquia Tempo].[Ano]

For this level Excel is showing the correct value.

In the image I sent it's the last column (2019 AVG Sample).

RE: Excel Pivot Table - Hide a Column Without Values

If I understand you, then "at this level" perhaps you don't need Average in your pivot report. So just remove AVG.

But what I don't understand is that Aug & Sep DO sum to the 2019 P... Base, but the AVGs do NOT sum to the 2019 AVG Sample???

So something seems odd about the data in your cube or maybe (most likely) there's something I don't understand.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Pivot Table - Hide a Column Without Values

(OP)
Good morning, SkipVought

The image I sent you is just an approximation of what I really need.

The problem is a little more complex. These data come from an Analysis Services cube.

The columns PREÇO BASE and AVG are calculated members of this cube.

To calculate them I need the MONTH level of my time dimension. But my customer doesn't want the AVG column to be shown in the MONTH level because it would show the same value as PREÇO BASE as it would be calculated for just one month.

I managed to hide the values for this column in Excel using a MDX expression, but I can't find a way to remove the column when the data are loaded in Excel.

Thank you very much for you help.

RE: Excel Pivot Table - Hide a Column Without Values

So what analysis is done to your data by Analysis Services that couldn't be performed directly by a PivotTable aggregation that would not include AVG?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Pivot Table - Hide a Column Without Values

...in addition to what I stated in my previous post, if you can't provide a representative example, which according to your latest post you did not, then I cannot venture a cogent response, not knowing your data.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Pivot Table - Hide a Column Without Values

Here's how you can prevent Excel from hiding pivot items without data: source
  • Right-click any value within the target field. ...
  • Select Field Settings. ...
  • Select the Layout & Print tab in the Field Settings dialog box.
  • Select the Show Items with No Data option, as shown in this figure.
  • Click OK to apply the change.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close