Friday, February 24, 2012

Calculations on members and aggregation

I'm designing a Profit And Loss report dimension that has the following attributes: Report Line, Cost Center and Account. Attribute relationships are defined between the attributes so that Report Line->Cost Center->Account.

A Report line is either A) A combination ofcost centers and accounts like:

Report Line A

- Cost Center 1

--Account 3000

--Account 3001

-Cost Center B

.. And so on

Or B) A calculation

I'm trying to handle calculations through mdx scripts by overwriting the value for those report lines that are calculations like:
scope (Report Line B);

this = Report Line A - Report Line C;

end scope

The problem is that the calculation report lines mess up the aggregation of the cube. I guess what I am really asking is wether its possible to turn off the members that are calculations in the aggregation of the cube. I realize i could use calculated measures for this but this doesnt fit my need for two reasons: Ease of use of the dimension and inability to drill down in reporting services on drillable members when calculated members are included.

Anyone have any ideas on this?

You can use the Freeze MDX statement to prevent changes to "Report Line B" from effecting the totals. To do this add simply apply Freeze to the all member of the hierarchy that contains Report Line B prior to updating Report Line B.

|||

Thanks!

Works like a charm, and as a bonus it helped me understand freeze

No comments:

Post a Comment