April 11, 2011 at 7:15 am
Hi,
i do have following problem (i try to simplify as much as possible):
There is first fact table DirectCosts
Account, department, Quantity,
There is second fact table CostReliefs:
Account, region, selling_price
Information about region is hidden in dimension called [Cost center] linked to first fact table.
I need to create calculated member which will calculate selling_price*quantity for each account and region.
My script looks like this:
CREATE MEMBER CURRENTCUBE.[Measures].[Simulated_revenue]
AS IIf(IsLeaf([Dim Account].currentmember),[Measures].[Selling Price]*[Measures].[Quantity],
Sum([Dim Account].currentmember.children,[Measures].[Simulated_revenue]))
It works perfect on leaf level but on higher levels not. Do you have any idea? I am really struggling with this:-(.
April 11, 2011 at 7:58 am
It would help those who want to help you, if you would post the table definition, some sample data and required results from the sample data.
To do so, please click on the first link in my signature block to learn a simple method of posting said information.
April 11, 2011 at 8:19 am
Ok, sorry i am really newbie here. i try it once again.
Dimension with accounts is created like this:
CREATE TABLE [dbo].[DimCostElements](
[ID_Cost_Element] [varchar](25) NOT NULL PRIMARY KEY,
[Parent] [varchar](25) NULL,
[Description] [varchar](80) NULL
)
Dimension with departments
CREATE TABLE [dbo].[DimCostCenter](
[ID_CC] [varchar](10) NOT NULL PRIMARY KEY,
[Description] [varchar](60) NULL,
[Parent] [varchar](10) NULL,
[ID_Manager] [varchar](10) NULL,
[Crest_Unit] [varchar](15) NULL,
[Crest_Function] [varchar](10) NULL,
[Company_Code] [varchar](10) NULL,
[ID_PC] [varchar](10) NULL,
[Lock_Act_PC] [numeric](1, 0) NULL,
[Lock_Act_SC] [numeric](1, 0) NULL,
[Lock_Act_R] [numeric](1, 0) NULL,
[Lock_Plan_PC] [numeric](1, 0) NULL,
[Lock_Plan_SC] [numeric](1, 0) NULL,
[Lock_Plan_R] [numeric](1, 0) NULL
)
[Company Code] means same as [Region] in [FactCostReliefs]
1st Fact table
CREATE TABLE [dbo].[FactDirectCosts](
[ID_Order] [numeric](10, 0) NULL,
[Cost_Element] [varchar](25) NULL,
[Scenario] [smallint] NULL,
[Version] [smallint] NULL,
[Item] [numeric](5, 0) NULL,
[ID_CC] [varchar](10) NULL,
[ID_Material] [varchar](20) NULL,
[Material_Group] [varchar](10) NULL,
[Date] [date] NULL,
[ID_PC] [varchar](10) NULL,
[Service_Category] [varchar](10) NULL,
[ID_Usage] [varchar](5) NULL,
[ID_LoB] [varchar](5) NULL,
[ID_Crest_Unit] [varchar](20) NULL,
[unit_of_measure] [varchar](10) NULL,
[value] [numeric](18, 2) NULL,
[Quantity] [numeric](18, 2) NULL
) ON [PRIMARY]
2nd fact table
CREATE TABLE [dbo].[FactCostReliefs](
[ID_Cost_element] [varchar](25) NULL,
[Role] [varchar](10) NULL,
[Region] [varchar](10) NULL,
[Activity] [varchar](50) NULL,
[Seniority] [varchar](20) NULL,
[Cost_Relief] [numeric](18, 2) NULL,
[Selling_Price] [numeric](18, 2) NULL
) ON [PRIMARY]
Because i want to have sample data to test i choose 4 regions:
1) US - selling price for all cost elements (accounts) 1.00
2) Asia - selling price for all cost elements (accounts) 2.00
3) Europe - selling price for all cost elements (accounts) 3.00
4) Africa - selling price for all cost elements (accounts) 4.00
SUMs by regions are correct but not by accounts. When i am not in leaf-level calculated member is calculated as SUM of all selling prices (1+2+3+4=10) * quantity.
Help me please and save my life:).
Thx a lot
April 11, 2011 at 8:38 am
No offence to BitBucket, but the TSQL and tables aren't going to help significantly with an MDX issue.
Sounds like you may want to investigate Measure Expressions. Christian Wade wrote a blog entry on this some time ago, I would imagine the approaches he outlines still hold true.
Steve.
April 11, 2011 at 9:43 am
Unfortunately it didnt help me:(.
What i dont understand is that total sums for regions are correct but not for accounts. Everything is correct on leaf-level. So, the problem is really in last part of current calculation formula..
CREATE MEMBER CURRENTCUBE.[Measures].[Simulated_revenue]
AS IIf(IsLeaf([Dim Account].currentmember),[Measures].[Selling Price]*[Measures].[Quantity],
Sum([Dim Account].currentmember.children,[Measures].[Simulated_revenue]))
April 11, 2011 at 12:25 pm
Have you tried AGGREGATE instead of SUM?
Steve.
April 12, 2011 at 1:43 am
Steve,
what do you mean? How do you think that script should look like?
Thx a lot
April 12, 2011 at 1:58 am
There is a message i cannot aggregate calculated member.
April 12, 2011 at 5:33 am
Finally working:
iif (
IsLeaf([Dim Cost Elements].[Cost Element].CurrentMember) AND
IsLeaf([Dim Cost Center].[Company Code].CurrentMember),
[Measures].[Quantity] * [Measures].[Selling Price],
Sum( Descendants( [Dim Cost Elements].[Cost Element].CurrentMember,, leaves) *
Descendants( [Dim Cost Center].[Company Code].CurrentMember,, leaves),
[Measures].[Simulated Revenue] )
)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply