Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format of calculated member in VBA
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format of calculated member in VBA
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format of calculated member in VBA
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format of calculated member in VBA
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format of calculated member in VBA
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/newsgroups...f-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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format of calculated member in VBA
Hello again,
Ive 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format of calculated member in VBA
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table, IF function, calculated item versus calculated field | Excel Discussion (Misc queries) | |||
Different format for Calculated Item in pivot table | Excel Discussion (Misc queries) | |||
SSAS Calculated Member in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Formulas Calculated Item / Calculated Field | Excel Programming | |||
PivotTable with Member Property - how to subtotal on Member Proper | Excel Programming |