question about PAW MDX

Post Reply
mrdauduong
Posts: 52
Joined: Mon Sep 26, 2022 11:10 am
OLAP Product: Planning analytics
Version: PA 2.0.74.23
Excel Version: Excel 2016

question about PAW MDX

Post by mrdauduong »

Hi,

I want to insert the value of measure "Current_YEAR" of cube "FG_PARAM" to MDX to make the MDX change with my selector. Can some one give me 1 exemple?

for exemple : if i have 2021 in cube "FG_PARAM", the MDX take the value 2021
question MDX.png
question MDX.png (200.59 KiB) Viewed 515 times
Any help will be appreciated!!

Thanks a lot.

Code MDX before

Code: Select all

WITH MEMBER [FG_Mois].[FG_Mois].[TOTAL_ANNEE].[Services] AS [FG_Section].[FG_Section].CURRENTMEMBER.PROPERTIES("Services") 
SELECT TM1IGNORE_BADTUPLES {[FG_Mois].[FG_Mois].[Societe],[FG_Mois].[FG_Mois].[Services],[FG_Mois].[FG_Mois].[TOTAL_ANNEE],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M1],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M2],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M3],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M4],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M5],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M6],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M7],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M8],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M9],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M10],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M11],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M12]} ON 0, TM1IGNORE_BADTUPLES {TM1SubsetToSet([FG_Section].[FG_Section],"PAW - Liste section - Saisie ETP Externe","public")} 
ON 1 FROM [FG_REPORTING] WHERE ([FG_Rubrique].[FG_Rubrique].[ETP_EXTERNE], [FG_Version].[FG_Version].[Version de Saisie]
, [FG_Annee].[FG_Annee].[2023], [FG_Societe].[FG_Societe].[Total_Societe], [FG_Axe].[FG_Axe].[Total AXE], [FG_Type_Ecriture].[FG_Type_Ecriture].[Normale], [FG_MetierDAS].[FG_MetierDAS].[Total MetierDAS])
something that i want to, MDX change when i change the value of selector to 2021

Code: Select all

WITH MEMBER [FG_Mois].[FG_Mois].[TOTAL_ANNEE].[Services] AS [FG_Section].[FG_Section].CURRENTMEMBER.PROPERTIES("Services") 
SELECT TM1IGNORE_BADTUPLES {[FG_Mois].[FG_Mois].[Societe],[FG_Mois].[FG_Mois].[Services],[FG_Mois].[FG_Mois].[TOTAL_ANNEE],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M1],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M2],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M3],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M4],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M5],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M6],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M7],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M8],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M9],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M10],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M11],[FG_Mois].[FG_Mois].[TOTAL_ANNEE^M12]} ON 0, TM1IGNORE_BADTUPLES {TM1SubsetToSet([FG_Section].[FG_Section],"PAW - Liste section - Saisie ETP Externe","public")} 
ON 1 FROM [FG_REPORTING] WHERE ([FG_Rubrique].[FG_Rubrique].[ETP_EXTERNE], [FG_Version].[FG_Version].[Version de Saisie]
, [FG_Annee].[FG_Annee].[2021], [FG_Societe].[FG_Societe].[Total_Societe], [FG_Axe].[FG_Axe].[Total AXE], [FG_Type_Ecriture].[FG_Type_Ecriture].[Normale], [FG_MetierDAS].[FG_MetierDAS].[Total MetierDAS])
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: question about PAW MDX

Post by Steve Rowe »

Hi,

This should help you figure out what you need.

My front end selection cube is dimensioned by user which enables your PAW screens to support multiple users.

'{TM1Sort(
{[Funding Budget Group].[All],
TM1FILTERBYLEVEL(
Descendants({StrToMember("[Funding Budget Group].["+[Front End Selection].(StrToMember( "[}Clients].[" + USERNAME + "]" ) ,[Front End Selection].[Funding Budget Group]) +"]")}) , 0)},
ASC)}';

The bold piece is the reference to the front end cube.
The Italic piece converts the the text that is returned from the bold section into a member of the dimension concerned.
Technical Director
www.infocat.co.uk
Post Reply