MDX - Clear with nested accounts and exclude dyn calc members — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

MDX - Clear with nested accounts and exclude dyn calc members

edited Nov 16, 2020 4:25PM in Planning 1 comment

Summary

Nest multiple rollups of accounts dim in a clear script

Content

I have a clear script and this code works for all Balance Sheet accounts. This is part of my crossjoin:

 

{Filter(Descendants([BalanceSheet],Levels([Account],0)),
 [Account].CurrentMember.Member_Type <>2)}),

But now I need to add IncomeStmt accounts too.  I don't want to create 10 different clear scripts, I would rather find a way to nest IncomeStmt into existing code that works.  I have tried so many iterations, I give up

 

Is it possible or do I need separate clears?

Version

11.2

Code Snippet

alter database AppName.DbName clear data in region
'
CrossJoin(
CrossJoin(
CrossJoin(
CrossJoin(
CrossJoin(
CrossJoin(
CrossJoin(
CrossJoin( 
CrossJoin(

{[Actual]},
{[Input]}),

{Descendants([EMEA],Levels([Entity],0))}),

{Descendants([Total_Regions],Levels([Region],0))}),
{Descendants([Total_Products],Levels([Products],0))}),
{Descendants([Total_Series],Levels([Series],0))}),

{Filter(Descendants([BalanceSheet],Levels([Account],0)),
 [Account].CurrentMember.Member_Type <>2)}),

{[USD]}),
{[FY22]}),
{[Jan]})' Physical;

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!