I have Created Some MDX that is supposed to do a bunch of custom calculations for cube view. All the Columns seem to be working perfectly fine. However, when I try to click down to drill into a consolidated element in my rows (clicking drop down arrow) it doesn't work at all. The issue isn't that anything breaks, it's that it doesn't want to drill down at all anymore. I have tried starting a new view and just opening the Cube view i want to look at (the basic version that still allows me to drill in my clicking the dropdown arrows) and copied the code for the rows and pasted it in the one that isn't working, but it still doesn't allow it to be dynamic for users.
I'm speculating it has something to do with the way I expressed my custom calculation but i haven't been able to find out what exactly it could be.
Here is my code MDX:
Code: Select all
WITH
MEMBER [Period].[Period].[2024.LTD].[Cost to Date Movement] AS ([Scenario].[Scenario].[Actual],[Period].[Period].[2024.01.LTD]) - ([Scenario].[Scenario].[Actual],[Period].[Period].[2023.12.LTD]), SOLVE_ORDER = 2, FORMAT_STRING = '#,##0.00;(#,##0.00)'
MEMBER [Period].[Period].[2023.LTD].[Revised Budget Movement] AS ([Scenario].[Scenario].[Revised Budget],[Period].[Period].[2024.01.LTD]) - ([Scenario].[Scenario].[Revised Budget],[Period].[Period].[2023.12.LTD]), SOLVE_ORDER = 2, FORMAT_STRING = '#,##0.00;(#,##0.00)'
MEMBER [Period].[Period].[2024.LTD].[Cost to Complete] AS ([Scenario].[Scenario].[Revised Budget],[Period].[Period].[2024.01.LTD]) - ([Scenario].[Scenario].[Actual],[Period].[Period].[2024.01.LTD]), SOLVE_ORDER = 3, FORMAT_STRING = '#,##0.00;(#,##0.00)'
MEMBER [Period].[Period].[2024.LTD].[Cost to Complete Prior Period] AS ([Scenario].[Scenario].[Revised Budget],[Period].[Period].[2023.12.LTD]) - ([Scenario].[Scenario].[Actual],[Period].[Period].[2023.12.LTD]), SOLVE_ORDER = 4, FORMAT_STRING = '#,##0.00;(#,##0.00)'
MEMBER [Period].[Period].[2024.LTD].[Estimated Final Cost] AS ([Scenario].[Scenario].[Actual],[Period].[Period].[2024.01.LTD]) + ([Period].[Period].[2024.LTD].[Cost to Complete]), SOLVE_ORDER = 5, FORMAT_STRING = '#,##0.00;(#,##0.00)'
MEMBER [Period].[Period].[2024.LTD].[Estimated Final Cost Prior Period] AS ([Scenario].[Scenario].[Actual],[Period].[Period].[2023.12.LTD]) + ([Period].[Period].[2024.LTD].[Cost to Complete Prior Period]), SOLVE_ORDER = 6, FORMAT_STRING = '#,##0.00;(#,##0.00)'
MEMBER [Period].[Period].[2024.LTD].[Gain/Loss on Adj Budget] AS ([Scenario].[Scenario].[Revised Budget],[Period].[Period].[2024.01.LTD]) - ([Period].[Period].[2024.LTD].[Estimated Final Cost]), SOLVE_ORDER = 7, FORMAT_STRING = '#,##0.00;(#,##0.00)'
MEMBER [Period].[Period].[2024.LTD].[Estimated Final Cost Movement] AS ([Period].[Period].[2024.LTD].[Estimated Final Cost]) - ([Period].[Period].[2024.LTD].[Estimated Final Cost Prior Period]), SOLVE_ORDER = 8, FORMAT_STRING = '#,##0.00;(#,##0.00)'
MEMBER [Period].[Period].[2024.LTD].[CTD by EFC Proportion] AS IIF([Period].[Period].[2024.LTD].[Estimated Final Cost] > 0, ([Scenario].[Scenario].[Actual],[Period].[Period].[2024.01.LTD]) / ([Period].[Period].[2024.LTD].[Estimated Final Cost]), 0 ), SOLVE_ORDER = 9, FORMAT_STRING = 'b:#.00%P|2|Y'
MEMBER [Period].[Period].[2024.LTD].[CTD by EFC Proportion Prior Period] AS IIF([Period].[Period].[2024.LTD].[Estimated Final Cost Prior Period] > 0, ([Scenario].[Scenario].[Actual],[Period].[Period].[2023.12.LTD]) / ([Period].[Period].[2024.LTD].[Estimated Final Cost Prior Period]), 0 ), SOLVE_ORDER = 10, FORMAT_STRING = 'b:#.00%P|2|Y'
SELECT NON EMPTY {
{[Scenario].[Scenario].[Budget]} * {[Period].[Period].[2024.01.LTD]},
{[Scenario].[Scenario].[Revised Budget]} * {
[Period].[Period].[2024.01.LTD],
[Period].[Period].[2023.12.LTD],
[Period].[Period].[Revised Budget Movement]
},
{[Scenario].[Scenario].[Actual]} * {
[Period].[Period].[2024.01.LTD],
[Period].[Period].[2023.12.LTD],[Period].
[Period].[Cost to Date Movement],
[Period].[Period].[CTD by EFC Proportion],
[Period].[Period].[CTD by EFC Proportion Prior Period] ,
[Period].[Period].[Cost to Complete],
[Period].[Period].[Estimated Final Cost],
[Period].[Period].[Gain/Loss on Adj Budget],
[Period].[Period].[Estimated Final Cost Prior Period],
[Period].[Period].[Estimated Final Cost Movement]
}