Format of calculated member in VBA

[Whitebear]

Hello!
I want to create calculated member in my Pivot Table (connected to
SSAS'2005) programmatically. I use

..PivotTables(1).CalculatedMembers.Add

But .Add method have only two parameters: name of calculated member and MDX
expression to calculate it. How can I specify format string, color expression
and other parameters of calculated member? Also, I found a strange behavior
of Excel'2007: I can't find my calculated member if it is bound not to
Measures dimension except I specify [All] node of dimensions hierarchy
explicitly:

When I call

pvt.CalculatedMembers.Add "[Date].[Year].MyMember", "..."

I can't find MyMember nowhere in Pivot Table interface. But when I call

pvt.CalculatedMembers.Add "[Date].[Year].[All].MyMember", "..." - it is
there, among normal members of a hierarchy...

 

[Wei Lu [MSFT]]

Hello,

You could use the following to modify the string format

With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Date].[Calendar].[Calendar Year]")

PivotItems().Visible = "$#,##0.00"
End With

As for the coler expression, you need to modify the cell format.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

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

 

[Whitebear]

Hi!

Thank you for your answer. About color. I have the following calculated
member (MDX syntax):

With member <member name> AS <MDX Expression>,
FORMAT_STRING = <format>,
BACK_COLOR = <Color MDX Expression>

But in CalculatedMembers.Add method I can control only <member name> and
<MDX Expression>, and cannot specify <format> and <Color MDX Expression>. So,
since I cannot run custom MDX query in Excel'2007 PivotTable, I can't set
color and other properties of my member Is there any workaround, except
declaring calculated members on the server-side?

 

[Wei Lu [MSFT]]

Hello,

You could only specify the member name and mdx expression in the add method.

I will check for any workaround in internal.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

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

 

[Whitebear]

Hello.

There is a evident workaround - to declare all calculated members in SSAS
database with help of BI-studio. But in some cases it may be a real impact on
IT-infrastructure, because modifications of SSAS definition may be denied by
many reasons (security, maintainance, etc). I think it is a good idea to
define the .Add method more compatible with all SSAS'2005 MDX features in
future releases of PivotTable... I described this (and other) problems with
more details in my other post of it's newsgroup:

http://msdn.microsoft.com/newsgroup...ming&mid=3d24035c-9efb-4bbc-873f-72a0357c17e3

If you are interested in this problem, I can provide you with an Excel'2007
sample, based on AdventureWorks SSAS'2005 sample database...

Thank you

 

[Whitebear]

Hello again,

I’ve spend a little time to write detailed letter of my problems with Excel
2007 and SSAS 2005. All samples and letter you can download in archive from
my site:

http://www.bookworld.ru/articles/formatissue.zip

I will be very glad if somebody can help me.

Thank you
Dmitry Jolobov

 

[Wei Lu [MSFT]]

Hello Dmitry,

After consulting the internal team, I confirmed that you could not format
the calculate member in the client application.

You need to use this in the SSAS side.

I have sent this feature request to the product team. Thank you for your
understanding! If you have any question, please feel free to let me know!


Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

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