Code from the epilog of the second TI is attached. This is basically where i dump out the data into a flat file to create a report and tidy all the cubes/views/subsets/dimensions up
Code: Select all
PCExportFile = SUBST(pExportFile,1, long(pExportFile) - 4) | 'PC.txt';
DetailExportFile = SUBST(pExportFile,1, long(pExportFile) - 4) | 'Detail.txt';
TotalElements = DIMSIZ(pMasterDim) - 1;
#MDX Returns the Current Approved MWE Parent Child Structure. Everything Except Liquidated Damages and Warranty Lines.
MDXString = '{FILTER({ORDER({TM1DRILLDOWNMEMBER({[Major Work Elements].[' | pCSR | ']},ALL,RECURSIVE)},[MWE Mapping].([Period].[' | pPeriod | '],[Vers
ion].[' | pVersion | '],[MWE Mapping Hierarchy].[Order]) ,BASC)},[MWE Mapping].([Period].[' | pPeriod | '],[Version].[' | pVersion | '],[MWE Mapping H
ierarchy].[Order])<>"")}';
MDXName = 'Sub' | pMasterDim;
SubsetCreatebyMDX(MDXName, MDXString);
TotalMWEPC = SubsetGetSize('Major Work Elements', MDXName) + 3;
# +3 as need LD, Warranty and CNA
IF(SubsetExists('Major Work Elements',MDXName) = 1);
SubsetDestroy('Major Work Elements',MDXName);
ENDIF;
#We need this for the Excel text import method
ASCIIOUTPUT(DetailExportFile, numbertostring(TotalElements));
ASCIIOUTPUT(PCExportFile, numbertostring(TotalMWEPC));
# We Need to Loop through each Parent and Then Child and Then CCN
MWELoop = 1 ;
MaxMWELoop = ElcompN(pMasterDim, pCSR);
WHILE (MWELoop <= MaxMWELoop);
#Each Parent
CurrentMWE = ELCOMP(pMasterDim, pCSR, MWELoop);
HrsinMonth = CellGetN(pMasterDim, 'Total Hours in Month', CurrentMWE);
CstinMonthL = CellGetN(pMasterDim, 'Costs in Month Labour', CurrentMWE);
CstinMonthN = CellGetN(pMasterDim, 'Costs in Month Non Labour', CurrentMWE);
CstinMonthT = CellGetN(pMasterDim, 'Costs in Month', CurrentMWE);
HrstoDate = CellGetN(pMasterDim, 'Total Hours to Date', CurrentMWE);
CsttoDateL = CellGetN(pMasterDim, 'Actual Costs to Date Labour', CurrentMWE);
CsttoDateN = CellGetN(pMasterDim, 'Actual Costs to Date Non Labour', CurrentMWE);
CsttoDateT = CellGetN(pMasterDim, 'Actual Costs to Date', CurrentMWE);
HrsinMonthL = CellGetN(pMasterDim, 'Actual Labour Hours', CurrentMWE);
HrsinMonthN = CellGetN(pMasterDim, 'Actual Non Labour Hours', CurrentMWE);
HrstoDateL = CellGetN(pMasterDim, 'Actual Labour Hours to Date', CurrentMWE);
HrstoDateN = CellGetN(pMasterDim, 'Actual Non Labour Hours to Date', CurrentMWE);
IF (Scan('Warranty', CurrentMWE) > 0);
MWEElemRef = 'Warranty';
ASCIIOUTPUT(PCExportFile, 'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(C
stinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring(CsttoDat
eT));
ASCIIOUTPUT(DetailExportFile, 'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostri
ng(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring(Cstt
oDateT));
IF (pReport @='False');
#Send Data into FD Core Financials Cube
CellPutN(CstinMonthL, 'FD Core Financials', 'Costs in Month Labour' ,pPeriod, pVersion, CurrentMWE);
CellPutN(CstinMonthN, 'FD Core Financials', 'Costs in Month Non Labour' ,pPeriod, pVersion, CurrentMWE);
CellPutN(CsttoDateL, 'FD Core Financials', 'Actual Costs to Date Labour' ,pPeriod, pVersion, CurrentMWE);
CellPutN(CsttoDateN, 'FD Core Financials', 'Actual Costs to Date Non Labour' ,pPeriod, pVersion, CurrentMWE);
CellPutN(HrsinMonthL, 'FD Core Financials', 'Actual Labour Hours' ,pPeriod, pVersion, CurrentMWE);
CellPutN(HrsinMonthN, 'FD Core Financials', 'Actual Non Labour Hours' ,pPeriod, pVersion, CurrentMWE);
CellPutN(HrstoDateL, 'FD Core Financials', 'Actual Labour Hours to Date' ,pPeriod, pVersion, CurrentMWE);
CellPutN(HrstoDateN, 'FD Core Financials', 'Actual Non Labour Hours to Date' ,pPeriod, pVersion, CurrentMWE);
ENDIF;
ELSEIF (Scan('LDs', CurrentMWE) > 0);
MWEElemRef = 'Liquidated Damages';
ASCIIOUTPUT(PCExportFile, 'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(C
stinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring(CsttoDat
eT));
ASCIIOUTPUT(DetailExportFile, 'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostri
ng(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring(Cstt
oDateT));
IF (pReport @='False');
#Send Data into FD Core Financials Cube
CellPutN(CstinMonthL, 'FD Core Financials', 'Costs in Month Labour' ,pPeriod, pVersion, CurrentMWE);
CellPutN(CstinMonthN, 'FD Core Financials', 'Costs in Month Non Labour' ,pPeriod, pVersion, CurrentMWE);
CellPutN(CsttoDateL, 'FD Core Financials', 'Actual Costs to Date Labour' ,pPeriod, pVersion, CurrentMWE);
CellPutN(CsttoDateN, 'FD Core Financials', 'Actual Costs to Date Non Labour' ,pPeriod, pVersion, CurrentMWE);
CellPutN(HrsinMonthL, 'FD Core Financials', 'Actual Labour Hours' ,pPeriod, pVersion, CurrentMWE);
CellPutN(HrsinMonthN, 'FD Core Financials', 'Actual Non Labour Hours' ,pPeriod, pVersion, CurrentMWE);
CellPutN(HrstoDateL, 'FD Core Financials', 'Actual Labour Hours to Date' ,pPeriod, pVersion, CurrentMWE);
CellPutN(HrstoDateN, 'FD Core Financials', 'Actual Non Labour Hours to Date' ,pPeriod, pVersion, CurrentMWE);
ENDIF;
ELSEIF (Scan('Costs Not Allocated From Mapping', CurrentMWE) > 0);
MWEElemRef = 'Costs Not Allocated From Mappings or Inputs';
ASCIIOUTPUT(PCExportFile, 'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(Cs
tinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring(CsttoDate
T));
ASCIIOUTPUT(DetailExportFile,'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring
(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring(CsttoD
ateT));
ELSE;
MWEElemRef = CellGetS('MWE Mapping', pPeriod, pVersion, CurrentMWE, 'DisplayRef');
ASCIIOUTPUT(PCExportFile, 'P', MWEElemRef, '', '', '', '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(CstinMont
hN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring(CsttoDateT));
ASCIIOUTPUT(DetailExportFile, 'P', MWEElemRef, '', '', '', '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(CstinM
onthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring(CsttoDateT));
ENDIF;
ChildLoop = 1;
MaxChildren = ElcompN(pMasterDim, CurrentMWE);
WHILE (ChildLoop <= MaxChildren);
IF (Scan('Warranty', CurrentMWE) > 0 % Scan('LDs', CurrentMWE) > 0 % Scan('Costs Not Allocated From Mapping', CurrentMWE) > 0);
#These will be CCN's
CCN = ELCOMP(pMasterDim, CurrentMWE, ChildLoop);
CCNStatus = CellGetS('CCN Status', pPeriod, CCN, 'Status');
HrsinMonth = CellGetN(pMasterDim, 'Total Hours in Month', CCN);
CstinMonthL = CellGetN(pMasterDim, 'Costs in Month Labour', CCN);
CstinMonthN = CellGetN(pMasterDim, 'Costs in Month Non Labour', CCN);
CstinMonthT = CellGetN(pMasterDim, 'Costs in Month', CCN);
HrstoDate = CellGetN(pMasterDim, 'Total Hours to Date', CCN);
CsttoDateL = CellGetN(pMasterDim, 'Actual Costs to Date Labour', CCN);
CsttoDateN = CellGetN(pMasterDim, 'Actual Costs to Date Non Labour', CCN);
CsttoDateT = CellGetN(pMasterDim, 'Actual Costs to Date', CCN);
ASCIIOUTPUT(DetailExportFile, 'L', '', '', '', '' , CCN, CCNStatus, numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring
(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring(CsttoD
ateT));
ELSE;
#These will be actual MWE Children
CurrentChild = ELCOMP(pMasterDim, CurrentMWE, ChildLoop);
ChildElemRef = CellGetS('MWE Mapping', pPeriod, pVersion, CurrentChild, 'DisplayRef');
HrsinMonth = CellGetN(pMasterDim, 'Total Hours in Month', CurrentChild);
CstinMonthL = CellGetN(pMasterDim, 'Costs in Month Labour', CurrentChild);
CstinMonthN = CellGetN(pMasterDim, 'Costs in Month Non Labour', CurrentChild);
CstinMonthT = CellGetN(pMasterDim, 'Costs in Month', CurrentChild);
HrstoDate = CellGetN(pMasterDim, 'Total Hours to Date', CurrentChild);
CsttoDateL = CellGetN(pMasterDim, 'Actual Costs to Date Labour', CurrentChild);
CsttoDateN = CellGetN(pMasterDim, 'Actual Costs to Date Non Labour', CurrentChild);
CsttoDateT = CellGetN(pMasterDim, 'Actual Costs to Date', CurrentChild);
HrsinMonthL = CellGetN(pMasterDim, 'Actual Labour Hours', CurrentChild);
HrsinMonthN = CellGetN(pMasterDim, 'Actual Non Labour Hours', CurrentChild);
HrstoDateL = CellGetN(pMasterDim, 'Actual Labour Hours to Date', CurrentChild);
HrstoDateN = CellGetN(pMasterDim, 'Actual Non Labour Hours to Date', CurrentChild);
ASCIIOUTPUT(PCExportFile, 'C', '', '', '', ChildElemRef , '', '', numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(Cs
tinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring(CsttoDate
T));
ASCIIOUTPUT(DetailExportFile, 'C', '', '', '', ChildElemRef , '', '', numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring
(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring(CsttoD
ateT));
IF (pReport @='False');
#Send Data into FD Core Financials Cube
CellPutN(CstinMonthL, 'FD Core Financials', 'Costs in Month Labour' ,pPeriod, pVersion, CurrentChild);
CellPutN(CstinMonthN, 'FD Core Financials', 'Costs in Month Non Labour' ,pPeriod, pVersion, CurrentChild);
CellPutN(CsttoDateL, 'FD Core Financials', 'Actual Costs to Date Labour' ,pPeriod, pVersion, CurrentChild);
CellPutN(CsttoDateN, 'FD Core Financials', 'Actual Costs to Date Non Labour' ,pPeriod, pVersion, CurrentChild);
CellPutN(HrsinMonthL, 'FD Core Financials', 'Actual Labour Hours' ,pPeriod, pVersion, CurrentChild);
CellPutN(HrsinMonthN, 'FD Core Financials', 'Actual Non Labour Hours' ,pPeriod, pVersion, CurrentChild);
CellPutN(HrstoDateL, 'FD Core Financials', 'Actual Labour Hours to Date' ,pPeriod, pVersion, CurrentChild);
CellPutN(HrstoDateN, 'FD Core Financials', 'Actual Non Labour Hours to Date' ,pPeriod, pVersion, CurrentChild);
ENDIF;
CCNLoop = 1;
MaxCCN = ElcompN(pMasterDim, CurrentChild);
WHILE (CCNLoop <= MaxCCN);
CCN = ELCOMP(pMasterDim, CurrentChild, CCNLoop);
CCNStatus = CellGetS('CCN Status', pPeriod, CCN, 'Status');
HrsinMonth = CellGetN(pMasterDim, 'Total Hours in Month', CCN);
CstinMonthL = CellGetN(pMasterDim, 'Costs in Month Labour', CCN);
CstinMonthN = CellGetN(pMasterDim, 'Costs in Month Non Labour', CCN);
CstinMonthT = CellGetN(pMasterDim, 'Costs in Month', CCN);
HrstoDate = CellGetN(pMasterDim, 'Total Hours to Date', CCN);
CsttoDateL = CellGetN(pMasterDim, 'Actual Costs to Date Labour', CCN);
CsttoDateN = CellGetN(pMasterDim, 'Actual Costs to Date Non Labour', CCN);
CsttoDateT = CellGetN(pMasterDim, 'Actual Costs to Date', CCN);
ASCIIOUTPUT(DetailExportFile, 'L', '', '', '', '' , CCN, CCNStatus, numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbert
ostring(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL), numbertostring(CsttoDateN), numbertostring
(CsttoDateT));
CCNLoop = CCNLoop + 1;
END;
ENDIF;
ChildLoop = ChildLoop + 1;
END;
MWELoop = MWELoop + 1;
END;
#Delete Temp Cube and Views/Subsets and Temp Dimension
IF (ViewExists('FD Cost Financials CCN',pMasterDim) = 1);
ViewDestroy('FD Cost Financials CCN',pMasterDim);
ENDIF;
IF (SubsetExists('Financial Variables', pMasterDim) = 1);
SubsetDestroy('Financial Variables', pMasterDim) ;
ENDIF;
IF (SubsetExists('Cum Periods', pMasterDim) = 1);
SubsetDestroy('Cum Periods', pMasterDim) ;
ENDIF;
IF (SubsetExists('Cost Breakdown GL', pMasterDim) = 1);
SubsetDestroy('Cost Breakdown GL', pMasterDim) ;
ENDIF;
IF (SubsetExists('CCN', pMasterDim) = 1);
SubsetDestroy('CCN', pMasterDim) ;
ENDIF;
IF (CubeExists(pMasterDim) = 1);
CubeDestroy(pMasterDim);
ENDIF;
IF (DimensionExists(pMasterDim) = 1);
DimensionDestroy(pMasterDim);
ENDIF;
pMasterDim is a Unique user time stamp and is used as the temp cube name/temp dimension name, temp subsets etc.