MDX to Convert Revenue To Positive

Question

Post reply

MDX to Convert Revenue To Positive

jasonclements32304

Ten Centuries

Points: 1122
More actions
October 20, 2016 at 1:38 pm

#151027

I need some help. Im new to MDX and SSAS. I need to convert every value for [FMSA Monthly Activity] under the [Dim FMS Heirarchy].[FMS Reporting Hierarchy].[FMSH Financial Page] starting under the Income Statement and all the levels below that member.
I have been trying this?? Im I on the right path?
-- Convert Revenue To Positive
SCOPE(descendants([Dim FMS Heirarchy].[FMS Reporting Hierarchy].[FMSH Financial Page].&[Income Statement],[Dim FMS Heirarchy].[FMS Reporting Hierarchy].[FMSH Financial Page],SELF_AND_AFTER),[Dim Date].[Current YTD]);
This =(([Measures].[FMSA Monthly Activity].CurrentMember)*(-1));
END SCOPE
Picture shared here

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

I'm thinking you could use ABS or IIF (ABS being the easiest and most efficient)

ABS:ABS(([Measures].[FMSA Monthly Activity].CurrentMember))

IIF:IIF

(

[Measures].[FMSA Monthly Activity].CurrentMember >= 0,

[Measures].[FMSA Monthly Activity].CurrentMember,

[Measures].[FMSA Monthly Activity].CurrentMember*(-1)

)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

jasonclements32304 Ten Centuries Points: 1122 More actions · Answer 2

OK but is the scoping correct? See link on post.

My values are not getting updated.

Bill Anton SSCrazy Points: 2417 More actions · Answer 3

jasonclements32304 (10/20/2016)
OK but is the scoping correct? See link on post.
My values are not getting updated.

Why have you included [Dim Date].[Current YTD] in your scope statement?

Also, a good method for troubleshooting scope statements is to use font color formatting...

SCOPE(

descendants(

[Dim FMS Heirarchy].[FMS Reporting Hierarchy].[FMSH Financial Page].&[Income Statement],

[Dim FMS Heirarchy].[FMS Reporting Hierarchy].[FMSH Financial Page],

SELF_AND_AFTER

),

[Dim Date].[Current YTD]

);

This = [Measures].[FMSA Monthly Activity] * (-1);

Fore_Color ( This ) = "255";

END SCOPE;

Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com

jasonclements32304 Ten Centuries Points: 1122 More actions · Answer 4

I guess I don't need it.

I want all of Dim Time to be updated.

SCOPE(descendants([Dim FMS Heirarchy].[FMS Reporting Hierarchy].[FMSH Financial Page].&[Income Statement],[Dim FMS Heirarchy].[FMS Reporting Hierarchy].[FMSH Financial Page],SELF_AND_AFTER));

This =(ABS(([Measures].[FMSA Monthly Activity].CurrentMember)));

Fore_Color ( This ) = "255";

END SCOPE;

This should get me there correct?

Bill Anton SSCrazy Points: 2417 More actions · Answer 5

gotcha - in that case, give this one a shot... scope to the leaf level below the [Income Statement] member and let the engine handle the aggregation back up through the levels between.