Excel Pivot Table - Hide a Column Without Values

[Benedito Almeida]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Benedito Almeida]

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.

 

[SkipVought]

Under what conditions will Avg not be ALL ZEROS?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Benedito Almeida]

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).

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Benedito Almeida]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

...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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[shrutib]

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