very very new to mdx and have been generating my queries by setting up the query in an Excel Pivot table and then exporting the query to a txt doc.
My query is as follows:
SELECT NON EMPTY
Hierarchize(DrilldownMember({{DrilldownLevel({[Date].[Fiscal].[All].Children},,,INCLUDE_CALC_MEMBERS)}},
{[Date].[Fiscal].[Year].&[2024],[Date].[Fiscal].[Year].&[2023],
[Date].[Fiscal].[Year].&[2022]},,,INCLUDE_CALC_MEMBERS))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,
[Date].[Fiscal].[Period].[FiscalPeriodType],[Date].[Fiscal].[Period].[FiscalYear],[Date].[Fiscal].[Period].[IsLatestData] ON COLUMNS ,
NON EMPTY CrossJoin(Hierarchize(DrilldownMember({{DrilldownLevel({[Account].[CoA].Children},,,INCLUDE_CALC_MEMBERS)}},
{[Account].[CoA].&[14432]},,,INCLUDE_CALC_MEMBERS)),
Hierarchize({[Scenario].[Scenario].[Scenario Parent].AllMembers}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,
[Scenario].[Scenario].[Scenario Parent].[Parent Display Order],
[Scenario].[Scenario].[Scenario Parent].[Scenario Parent Description],[Account].[CoA].[AccountOrderKey],
[Account].[CoA].[CoA],[Account].[CoA].[CustomMemberFormula],[Account].[CoA].[Display Format String Billion],
[Account].[CoA].[Display Format String Million],[Account].[CoA].[Display Format String Thousand],
[Account].[CoA].[Display Format String Unit],[Account].[CoA].[IsVarianceInPoint],[Account].[CoA].[VarianceFlipSign] ON ROWS
FROM (SELECT ({[Date].[Fiscal].[Year].&[2024]}) ON COLUMNS ,
({[Account].[CoA].&[14436], [Account].[CoA].&[14433]},{[Scenario].[Scenario].[Prior Year], [Scenario].[Scenario].[Actuals]}) ON ROWS
FROM [CUBE])
WHERE ([Organization].[Management].[All],[Organization].[ServiceLine].[All],[PeriodType].[PeriodType].&[5],[Measures].[Local])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
I managed to remove All columns by changing ALLMEMBERS to Children where appropriate. The issue when running it in SSMS is that I can't remove the Billing field which is a level above Bills Issued and Cash Collected. I also can't work out how to remove 2024 which sits above all the periods given in the table below. The output in SSMS looks like this (some numbers replaced with randomly generated):
| 2024 | FY24 OpeningBal | FY24 July | FY24 August | FY24 September | FY24 October | FY24 November | FY24 December | FY24 January | FY24 February | FY24 March | FY24 April | FY24 May | FY24 June | FY24 Adjustment | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Billings | Actuals | 1.00E-05 | 1.00E+00 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 |
| Billings | Prior Year | 1.00E-05 | 1.00E+00 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 | 1.00E-05 |
| Bills Issued | Actuals | 0.571759 | (null) | 0.644204 | 0.342599 | 0.565033 | 0.049328 | 0.741076 | 0.767904 | 0.456827 | 0.086739 | 0.895016 | 0.16319 | 0.893175 | 0.974494 | 0.158187 |
| Bills Issued | Prior Year | 0.21984 | (null) | 0.708076 | 0.308315 | 0.995772 | 0.609942 | 0.085712 | 0.387499 | 0.6073 | 0.686889 | 0.789851 | 0.303484 | 0.867292 | 0.855121 | 0.636389 |
| Cash Collected | Actuals | 0.397955 | (null) | 0.928985 | 0.766025 | 0.415052 | 0.191884 | 0.069434 | 0.292659 | 0.886183 | 0.095258 | 0.363058 | 0.21286 | 0.926778 | 0.758365 | 0.779428 |
| Cash Collected | Prior Year | 0.334408 | (null) | 0.072637 | 0.562313 | 0.610455 | 0.247378 | 0.994229 | 0.747458 | 0.896372 | 0.898893 | 0.479051 | 0.923123 | 0.54981 | 0.97782 | 0.940315 |
Then when I use the same query in Python so I can use the data for some data visualization I have a table that looks nothing like the one in SSMS! The code I am using is:
with Pyadomd(conn) as conn:
with conn.cursor().execute(query) as cur:
yoy_table = pd.DataFrame(cur.fetchall(), columns=[i.name for i in cur.description])
Giving the following DataFrame which is not what I want at all. I want it to reflect the same output as SSMS with the Billing and 2024 levels removed. Again, random numbers generated:
| [Account].[CoA].[Level 01].[PARENT_UNIQUE_NAME] | [Account].[CoA].[Level 01].[HIERARCHY_UNIQUE_NAME] | [Account].[CoA].[Level 01].[AccountOrderKey] | [Account].[CoA].[Level 01].[CoA] | [Account].[CoA].[Level 01].[CustomMemberFormula] | [Account].[CoA].[Level 01].[Display Format String Billion] | [Account].[CoA].[Level 01].[Display Format String Million] | [Account].[CoA].[Level 01].[Display Format String Thousand] | [Account].[CoA].[Level 01].[Display Format String Unit] | [Account].[CoA].[Level 01].[IsVarianceInPoint] | [Account].[CoA].[Level 01].[VarianceFlipSign] | [Account].[CoA].[Level 02].[PARENT_UNIQUE_NAME] | [Account].[CoA].[Level 02].[HIERARCHY_UNIQUE_NAME] | [Account].[CoA].[Level 02].[AccountOrderKey] | [Account].[CoA].[Level 02].[CoA] | [Account].[CoA].[Level 02].[CustomMemberFormula] | [Account].[CoA].[Level 02].[Display Format String Billion] | [Account].[CoA].[Level 02].[Display Format String Million] | [Account].[CoA].[Level 02].[Display Format String Thousand] | [Account].[CoA].[Level 02].[Display Format String Unit] | [Account].[CoA].[Level 02].[IsVarianceInPoint] | [Account].[CoA].[Level 02].[VarianceFlipSign] | [Account].[CoA].[Level 03].[PARENT_UNIQUE_NAME] | [Account].[CoA].[Level 03].[HIERARCHY_UNIQUE_NAME] | [Account].[CoA].[Level 03].[AccountOrderKey] | [Account].[CoA].[Level 03].[CoA] | [Account].[CoA].[Level 03].[CustomMemberFormula] | [Account].[CoA].[Level 03].[Display Format String Billion] | [Account].[CoA].[Level 03].[Display Format String Million] | [Account].[CoA].[Level 03].[Display Format String Thousand] | [Account].[CoA].[Level 03].[Display Format String Unit] | [Account].[CoA].[Level 03].[IsVarianceInPoint] | [Account].[CoA].[Level 03].[VarianceFlipSign] | [Scenario].[Scenario].[Scenario Parent].[PARENT_UNIQUE_NAME] | [Scenario].[Scenario].[Scenario Parent].[HIERARCHY_UNIQUE_NAME] | [Scenario].[Scenario].[Scenario Parent].[Parent Display Order] | [Scenario].[Scenario].[Scenario Parent].[Scenario Parent Description] | [Date].[Fiscal].[Year].&[2024] | [Date].[Fiscal].[Period].&[2024000] | [Date].[Fiscal].[Period].&[2024001] | [Date].[Fiscal].[Period].&[2024002] | [Date].[Fiscal].[Period].&[2024003] | [Date].[Fiscal].[Period].&[2024004] | [Date].[Fiscal].[Period].&[2024005] | [Date].[Fiscal].[Period].&[2024006] | [Date].[Fiscal].[Period].&[2024007] | [Date].[Fiscal].[Period].&[2024008] | [Date].[Fiscal].[Period].&[2024009] | [Date].[Fiscal].[Period].&[2024010] | [Date].[Fiscal].[Period].&[2024011] | [Date].[Fiscal].[Period].&[2024012] | [Date].[Fiscal].[Period].&[2024998] |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| [Account].[CoA] | 4487 | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Account].[CoA].&[14392] | [Account].[CoA] | 17 | StatisticalAccounts | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Scenario].[Scenario] | 10 | Actuals | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | ||||||||||||||
| [Account].[CoA] | 4487 | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Account].[CoA].&[14392] | [Account].[CoA] | 17 | StatisticalAccounts | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Scenario].[Scenario] | 50 | Prior Year | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | 1e-05 | ||||||||||||||
| [Account].[CoA] | 4487 | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Account].[CoA].&[14392] | [Account].[CoA] | 17 | StatisticalAccounts | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Account].[CoA].&[14432] | [Account].[CoA] | 1 | Billings | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Scenario].[Scenario] | 10 | Actuals | 2405654600658.94 | nan | 325801981374.8 | 654084986749.85 | 992609332674.99 | 1415977517569.43 | 1881457636457.15 | 2354597431066.9 | 2405654600658.94 | 2405654600658.94 | 2405654600658.94 | 2405654600658.94 | 2405654600658.94 | 2405654600658.94 | 2405654600658.94 | ||||
| [Account].[CoA] | 4487 | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Account].[CoA].&[14392] | [Account].[CoA] | 17 | StatisticalAccounts | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Account].[CoA].&[14432] | [Account].[CoA] | 1 | Billings | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Scenario].[Scenario] | 50 | Prior Year | 4847109487854.47 | nan | 361776526533.39 | 697446208884.35 | 1039075952282.75 | 1360198428809.48 | 1796509307733.14 | 2484183407724.12 | 2797502022044.86 | 3063373428169.33 | 3474984450971.24 | 3866716314945.89 | 4339590828278.79 | 4847109487854.47 | 4847109487854.47 | ||||
| [Account].[CoA] | 4487 | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Account].[CoA].&[14392] | [Account].[CoA] | 17 | StatisticalAccounts | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Account].[CoA].&[14432] | [Account].[CoA] | 3 | Billings | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Scenario].[Scenario] | 10 | Actuals | 2432820164876.82 | nan | 432062213727.32 | 824116279516.18 | 1167631245677.22 | 1581419042955.97 | 1986055270473.5 | 2400827822041.97 | 2432820164876.82 | 2432820164876.82 | 2432820164876.82 | 2432820164876.82 | 2432820164876.82 | 2432820164876.82 | 2432820164876.82 | ||||
| [Account].[CoA] | 4487 | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Account].[CoA].&[14392] | [Account].[CoA] | 17 | StatisticalAccounts | 1e-05 | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Account].[CoA].&[14432] | [Account].[CoA] | 3 | Billings | #,0,,,.0;(#,0,,,.0);0; | #,0,,.0;(#,0,,.0);0; | #,0,;(#,0,);0; | #,0;(#,0);0; | 0 | 0 | [Scenario].[Scenario] | 50 | Prior Year | 4708345088279.18 | nan | 393235202258 | 749663471237.72 | 1136615456661.85 | 1488673181385.13 | 1779096047902.8 | 2218244064574.85 | 2694355903413.32 | 3084398990937.76 | 3471719057050.8 | 3883452909922.2 | 4283634695439.38 | 4708345088279.18 | 4708345088279.18 |
Any help greatly appreciated!