OWC 11 - How to change #VALUE display

[Lucas]

Hi,
Is there any way to alter the way in which PT shows null values or values no
accessed due to security limitations? PT shows "#VALUE", but is there any
way to change this in order to show another thing? May be a blank or
something custom.

Thanks

Lucas

 

[Peter Huang]

Hi Lucas,

Based on my understanding, when you use the PivotTable of OWC11, you will
get the "#VALUE!" error message.

Can you tell me in which situation did you get the "#VALUE!" in PivotTable?
I will appreciate your efforts if you can describe your reproduce steps.

If you have any concern on this issue, please post here.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Lucas]

We identify two scenarios:
1. When you use Cell Level Security the #VALUE is shown in those cells that
you don't have permission to access
2. When you try to see the Total of a calculated measure related to a Cube
that uses Distinct count.

I think it's OK that PT doesn't show the data what I'd like to do is to
customize de #VALUE error displayed
In earlier versions of OWC, when you have scenario 1 (security limitations)
the cells was shown with a Blank The only possible value to show is #VALUE?
Can I customize it?

Thanks a lot

Lucas

 

[Peter Huang]

Hi Lucas,

I am researching the issue, if I have new information, I will update with
you ASAP.

If you have any concern on this issue, please post here.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Lori Turner [MSFT]]

Hi Lucas,

There's not a way to change the text for an error value, but you can use a
trick similar to Excel where you can change the number format to use colors
and specify that the cell text is white (to match the cell background color
which gives it the look of a blank cell).

For example, here's a code snippet where you can set the number format code
and color for [positive;negative;zero] values. The number format color
overrides the font color; so anything that's not a number, like an error,
will display in the font color.

To try this, modify the connection string to use your server and enter a
divisor (a divisor of zero will display an error in the calculated total):

PivotTable1.ConnectionString = _
"Provider=MSOLAP.2;Integrated Security=SSPI;Data
Source=YourServer;Initial Catalog=FoodMart 2000"
PivotTable1.DataMember = "Sales"

Dim pt As PivotTotal
Dim divisor As Integer
divisor = InputBox("Divisor")
Set pt = PivotTable1.ActiveView.AddCalculatedTotal("Test", "Test", "1/"
& divisor)
With PivotTable1.ActiveView
.RowAxis.InsertFieldSet .FieldSets("Customers")
.DataAxis.InsertTotal pt
pt.NumberFormat = "[Black]0.00;[Red]-0.00;[Black]0;"
.TotalFont.Color = "White"
End With

Hope this helps!

Lori Turner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

 

[Lucas]

Does this example hides the totals all the time? or just in case an Error
happens?

Thanks

Hi Lucas,

There's not a way to change the text for an error value, but you can use a
trick similar to Excel where you can change the number format to use colors
and specify that the cell text is white (to match the cell background color
which gives it the look of a blank cell).

For example, here's a code snippet where you can set the number format code
and color for [positive;negative;zero] values. The number format color
overrides the font color; so anything that's not a number, like an error,
will display in the font color.

To try this, modify the connection string to use your server and enter a
divisor (a divisor of zero will display an error in the calculated total):

PivotTable1.ConnectionString = _
"Provider=MSOLAP.2;Integrated Security=SSPI;Data
Source=YourServer;Initial Catalog=FoodMart 2000"
PivotTable1.DataMember = "Sales"

Dim pt As PivotTotal
Dim divisor As Integer
divisor = InputBox("Divisor")
Set pt = PivotTable1.ActiveView.AddCalculatedTotal("Test", "Test", "1/"
& divisor)
With PivotTable1.ActiveView
.RowAxis.InsertFieldSet .FieldSets("Customers")
.DataAxis.InsertTotal pt
pt.NumberFormat = "[Black]0.00;[Red]-0.00;[Black]0;"
.TotalFont.Color = "White"
End With

Hope this helps!

Lori Turner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.

phx.gbl

 

[Lori Turner [MSFT]]

Just errors, like #VALUE, #NUM.

Regards,
Lori Turner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------

From: "Lucas" <[email protected]>
References: <[email protected]>

Subject: Re: OWC 11 - How to change #VALUE display
Date: Thu, 8 Jan 2004 09:15:37 -0300
Lines: 89
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.office.developer.web.components
NNTP-Posting-Host: lan-152-107.easymail.net.ar 200.80.152.107
Path: cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.
phx.gbl!TK2MSFTNGP11.phx.gbl
Xref: cpmsftngxa07.phx.gbl microsoft.public.office.developer.web.components:8002
X-Tomcat-NG: microsoft.public.office.developer.web.components

Does this example hides the totals all the time? or just in case an Error
happens?

Thanks

Hi Lucas,

There's not a way to change the text for an error value, but you can use a
trick similar to Excel where you can change the number format to use colors
and specify that the cell text is white (to match the cell background color
which gives it the look of a blank cell).

For example, here's a code snippet where you can set the number format code
and color for [positive;negative;zero] values. The number format color
overrides the font color; so anything that's not a number, like an error,
will display in the font color.

To try this, modify the connection string to use your server and enter a
divisor (a divisor of zero will display an error in the calculated total):

PivotTable1.ConnectionString = _
"Provider=MSOLAP.2;Integrated Security=SSPI;Data
Source=YourServer;Initial Catalog=FoodMart 2000"
PivotTable1.DataMember = "Sales"

Dim pt As PivotTotal
Dim divisor As Integer
divisor = InputBox("Divisor")
Set pt = PivotTable1.ActiveView.AddCalculatedTotal("Test", "Test", "1/"
& divisor)
With PivotTable1.ActiveView
.RowAxis.InsertFieldSet .FieldSets("Customers")
.DataAxis.InsertTotal pt
pt.NumberFormat = "[Black]0.00;[Red]-0.00;[Black]0;"
.TotalFont.Color = "White"
End With

Hope this helps!

Lori Turner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
From: "Lucas" <[email protected]>
Subject: OWC 11 - How to change #VALUE display
Date: Fri, 2 Jan 2004 10:25:04 -0300
Lines: 11
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.office.developer.web.components
NNTP-Posting-Host: lan-152-107.easymail.net.ar 200.80.152.107
Path:

cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10

 

[Lucas]

We tried with:
[Black] #.000;[Red]-#.000;[Black]0;"#VALUE"[White]

in order not to see the #VALUE error. What do you think?

Thanks a lot

Lucas

Just errors, like #VALUE, #NUM.

Regards,
Lori Turner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Subject: Re: OWC 11 - How to change #VALUE display
Date: Thu, 8 Jan 2004 09:15:37 -0300
Lines: 89
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.office.developer.web.components
NNTP-Posting-Host: lan-152-107.easymail.net.ar 200.80.152.107
Path:

cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.

phx.gbl!TK2MSFTNGP11.phx.gbl
Xref: cpmsftngxa07.phx.gbl microsoft.public.office.developer.web.components:8002
X-Tomcat-NG: microsoft.public.office.developer.web.components

Does this example hides the totals all the time? or just in case an Error
happens?

Thanks

""Lori Turner [MSFT]"" <[email protected]> escribió en el mensaje

Hi Lucas,

There's not a way to change the text for an error value, but you can

use

a

trick similar to Excel where you can change the number format to use colors
and specify that the cell text is white (to match the cell background color
which gives it the look of a blank cell).

For example, here's a code snippet where you can set the number format code
and color for [positive;negative;zero] values. The number format color
overrides the font color; so anything that's not a number, like an error,
will display in the font color.

To try this, modify the connection string to use your server and enter a
divisor (a divisor of zero will display an error in the calculated total):

PivotTable1.ConnectionString = _
"Provider=MSOLAP.2;Integrated Security=SSPI;Data
Source=YourServer;Initial Catalog=FoodMart 2000"
PivotTable1.DataMember = "Sales"

Dim pt As PivotTotal
Dim divisor As Integer
divisor = InputBox("Divisor")
Set pt = PivotTable1.ActiveView.AddCalculatedTotal("Test", "Test", "1/"
& divisor)
With PivotTable1.ActiveView
.RowAxis.InsertFieldSet .FieldSets("Customers")
.DataAxis.InsertTotal pt
pt.NumberFormat = "[Black]0.00;[Red]-0.00;[Black]0;"
.TotalFont.Color = "White"
End With

Hope this helps!

Lori Turner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
From: "Lucas" <[email protected]>
Subject: OWC 11 - How to change #VALUE display
Date: Fri, 2 Jan 2004 10:25:04 -0300
Lines: 11
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.office.developer.web.components
NNTP-Posting-Host: lan-152-107.easymail.net.ar 200.80.152.107
Path:

cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10

 

[Lori Turner [MSFT]]

Hi Lucas,

Cool... that works too! And I like it, you don't have to set the font
color your way.

Best wishes,
Lori Turner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

From: "Lucas" <[email protected]>
References: <[email protected]>

<JloWh#[email protected]>

Subject: Re: OWC 11 - How to change #VALUE display
Date: Fri, 9 Jan 2004 16:09:56 -0300
Lines: 118
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.office.developer.web.components
NNTP-Posting-Host: lan-152-107.easymail.net.ar 200.80.152.107
Path:

cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA0
5.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl

Xref: cpmsftngxa07.phx.gbl microsoft.public.office.developer.web.components:8012
X-Tomcat-NG: microsoft.public.office.developer.web.components

We tried with:
[Black] #.000;[Red]-#.000;[Black]0;"#VALUE"[White]

in order not to see the #VALUE error. What do you think?

Thanks a lot

Lucas


""Lori Turner [MSFT]"" <[email protected]> escribió en el mensaje

Just errors, like #VALUE, #NUM.

Regards,
Lori Turner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: "Lucas" <[email protected]>
References: <[email protected]>
<JloWh#[email protected]>
Subject: Re: OWC 11 - How to change #VALUE display
Date: Thu, 8 Jan 2004 09:15:37 -0300
Lines: 89
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.office.developer.web.components
NNTP-Posting-Host: lan-152-107.easymail.net.ar 200.80.152.107
Path:

cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.

phx.gbl!TK2MSFTNGP11.phx.gbl
Xref: cpmsftngxa07.phx.gbl
microsoft.public.office.developer.web.components:8002
X-Tomcat-NG: microsoft.public.office.developer.web.components

Does this example hides the totals all the time? or just in case an Error
happens?

Thanks

""Lori Turner [MSFT]"" <[email protected]> escribió en el mensaje
Hi Lucas,

There's not a way to change the text for an error value, but you can use
a
trick similar to Excel where you can change the number format to use
colors
and specify that the cell text is white (to match the cell background
color
which gives it the look of a blank cell).

For example, here's a code snippet where you can set the number format
code
and color for [positive;negative;zero] values. The number format color
overrides the font color; so anything that's not a number, like an error,
will display in the font color.

To try this, modify the connection string to use your server and

enter
a

divisor (a divisor of zero will display an error in the calculated
total):

PivotTable1.ConnectionString = _
"Provider=MSOLAP.2;Integrated Security=SSPI;Data
Source=YourServer;Initial Catalog=FoodMart 2000"
PivotTable1.DataMember = "Sales"

Dim pt As PivotTotal
Dim divisor As Integer
divisor = InputBox("Divisor")
Set pt = PivotTable1.ActiveView.AddCalculatedTotal("Test", "Test",
"1/"
& divisor)
With PivotTable1.ActiveView
.RowAxis.InsertFieldSet .FieldSets("Customers")
.DataAxis.InsertTotal pt
pt.NumberFormat = "[Black]0.00;[Red]-0.00;[Black]0;"
.TotalFont.Color = "White"
End With

Hope this helps!

Lori Turner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no
rights.


--------------------
From: "Lucas" <[email protected]>
Subject: OWC 11 - How to change #VALUE display
Date: Fri, 2 Jan 2004 10:25:04 -0300
Lines: 11
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.office.developer.web.components
NNTP-Posting-Host: lan-152-107.easymail.net.ar 200.80.152.107
Path:

cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10