Exception Reporting

­­General Discussions
1

I am trying to build an exception report to only show the Top 10 budget variances by department. I want the report to have 2 data sets - salaries and all other expenses. All other expenses was relatively easy to build from our Finance Cube. But I cannot get Personnel Planning to work. I created a Named Set within Employee dimension to capture the Top 10. But it provides no data. Formula uses same methodology as Finance Cube which works.

So I’m wondering - can the Employee dimension have a calculated Named Set with a MDX formula?

2

Hi Jennifer,

I mocked it up at my side and got it to work using the below MDX formula:
TopCount(Descendants([Employees].Default.[All],Leaves), 10, ([Account].[Default].CurrentMember, [Department].Default.[All], [Time].Default.&[2021], [Version].Default.&[PLAN01]))

The above expression, created as a Named Set under the Employees dimension, gives you the employees who have the top 10 amounts to the account selected, for the year 2021, version PLAN01 (budget) across all depts. If you want to set the named set to a specific account (say 4000), you would
replace
[Account].[Default].CurrentMember
with
[Account].Default.&[4000]

You can use this formula above to perhaps create a variance too, depending on your requirement.

Hope this helps.

Cheers,
Navin

2 Likes
3

It worked like a charm - thank you so much!

2 Likes
4

I’m very happy this worked out for you, @jennifer.walters! I have marked @navin.sadarangani.1’s response above as the solution.

1 Like
5

That’s awesome. You’re most welcome.

Thanks and Regards,
Navin

1 Like