Specifying the Location of a Calculated Member in a Hierarchy
-
I am working on building a hierarchy to facilitate the generate of financial statements.
I add attributes to my Account dimension to create the typical grouping and totals (Assets, Liabilities, Revenues, Expenses, etc). Each grouping attribute has a cooresponding "order by" attribute to keep them members in a logical order.In few cases I built calculated members to total or net regular members together. for example, in the below "net tuition and fees" is a calculated member of the hierarchy.
CREATE MEMBER CURRENTCUBE.[Account].[Account Structure].[Revenues].[Operating Revenues].[Net Tuition & Fees]
AS [Account].[Account Structure].[Tuition & Fees]
+ [Account].[Account Structure].[Scholarships and Grants],
VISIBLE = 1;
Currently the I am putting the calculated items in the correct spot of the hierarchy by using a named set calculation.
The problem is that if the user navigates the report, causing a new mdx query, they have broken out of the set so all the members go back to the order specified on the attribute and the calculated members appear at the end of the level... which cosmetically isnt what the customer wants...Does anyone know a way that as part of the definition of the calculated member I can also set the "Order By" attribute that goes with that member.
Or is this maybe not possible because the hierarchy order is established when the dimension is processed and the calculated members are just append when the dimension is associated with the cube?
Ideas?
- Bob.T - Thursday, September 13, 2018 4:23 PMI am working on building a hierarchy to facilitate the generate of financial statements.
I add attributes to my Account dimension to create the typical grouping and totals (Assets, Liabilities, Revenues, Expenses, etc). Each grouping attribute has a cooresponding "order by" attribute to keep them members in a logical order.In few cases I built calculated members to total or net regular members together. for example, in the below "net tuition and fees" is a calculated member of the hierarchy.
CREATE MEMBER CURRENTCUBE.[Account].[Account Structure].[Revenues].[Operating Revenues].[Net Tuition & Fees]
AS [Account].[Account Structure].[Tuition & Fees]
+ [Account].[Account Structure].[Scholarships and Grants],
VISIBLE = 1;
Currently the I am putting the calculated items in the correct spot of the hierarchy by using a named set calculation.
The problem is that if the user navigates the report, causing a new mdx query, they have broken out of the set so all the members go back to the order specified on the attribute and the calculated members appear at the end of the level... which cosmetically isnt what the customer wants...Does anyone know a way that as part of the definition of the calculated member I can also set the "Order By" attribute that goes with that member.
Or is this maybe not possible because the hierarchy order is established when the dimension is processed and the calculated members are just append when the dimension is associated with the cube?
Ideas?
As it is a member have you tried ordering the calculation itself by the set it lives in like:
CREATE MEMBER CURRENTCUBE.[Account].[Account Structure].[Revenues].[Operating Revenues].[Net Tuition & Fees]
AS
ORDER
([Account].[Account Structure].[Revenues].[Operating Revenues].MEMBERS,
[Account].[Account Structure].[Tuition & Fees]
+ [Account].[Account Structure].[Scholarships and Grants],
, ASC)
VISIBLE = 1;I'm unsure if that would work. Another approach would be to add a physical member to the hierarchy (in the a view, for example) and give it the correct "order by" attribute. Then just use SCOPE ([Account].[Account Structure].[Revenues].[Operating Revenues].[Net Tuition & Fees]);
THIS = [Account].[Account Structure].[Tuition & Fees]
+ [Account].[Account Structure].[Scholarships and Grants];
END SCOPE
I'm on LinkedIn -
Thank you for the response to my post.
I need to do more experimenting with the order() function. If this was just a query for a published content, order() would work similar to my template, but as this cube is being used with Pyramid Analytics and users would want to be able to expand and drill down hierarchies I believe I would run into similar cosmetic issues.The Scoping idea is somewhat of an option, but the value calculated by the Scope is included in the aggregation to the parent level.
I may be able to get around this by having a Scope set up for each level of the Account Structure hierarchy but that may be convoluted to maintain.As this is mostly a cosmetic thing I think the "template" named set idea may be the best I can do without creating a maintenance mess....
CREATE SET CURRENTCUBE.[SOA Template]
AS {[Account].[Account Structure].[Revenues],
[Account].[Account Structure].[Revenues].[Operating Revenues].[Tuition & Fees],
[Account].[Account Structure].[Revenues].[Operating Revenues].[Scholarships and Grants],
[Account].[Account Structure].[Revenues].[Operating Revenues].[Net Tuition & Fees], -- calculated member
[Account].[Account Structure].[Revenues].[Operating Revenues].[Interest Income],
[Account].[Account Structure].[Revenues].[Operating Revenues].[Investment Income],
[Account].[Account Structure].[Revenues].[Operating Revenues].[Sales and Services of Auxiliary Enterprises],
[Account].[Account Structure].[Revenues].[Operating Revenues].[Other Income],
descendants([Account].[Account Structure].[Expenses].[Operating Expenses], 1,SELF_AND_BEFORE),
[Account].[Account Structure].[All Accounts].[Net Operating Income], -- calculated member
descendants([Account].[Account Structure].[Non-Operating].[Non-Operating Revenues], 1,SELF_AND_BEFORE),
[Account].[Account Structure].[All Accounts].[Change in Net Assets], -- calculated member
[Account].[Account Structure].[All Accounts].[Net Assets Beg Bal] ,-- calculated member
[Account].[Account Structure].[All Accounts].[Net Assets YTD] -- calculated member
};Thanks again for your response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply