Picklist

[Jorge Rachid]

Hi guys,

I have a situation where in one picklist for a field the customer must have to choose between "yes" or "no".

But after that he would to filter only "yes".

Is it possible?

Best regards,

JR

[TomaszB]

Hi Jorge,

create an Active Form or View and use a MDX to filter the values

Code: Select all

{FILTER(
  {TM1FILTERBYLEVEL( {TM1SUBSETALL( [DIM1] )}, 0)},
  [YOUR CUBE].([DIM2].[ELEM1],[DIM3].[ELEM2])
) = "YES" )}

Best Regards
TomaszB

[Jorge Rachid]

Thanks!

But in this case all elements should be on the same dimension or I can manipulate the mdx code using another dimensions of the same cube?

Best regards,

JR

[TomaszB]

Hi Jorge,

You can filter a dimension base on cube values from a combination defined in the MDX statement.

http://www.bihints.com/book/export/html/68 -> Section Filter, by values, strings and attributes
or
http://users.skynet.be/fa436118/wim/tm1 ... nts_EN.htm

Best Regards
Tomasz Brzoza

[Wim Gielis]

Hello,

The website URL changed somewhat, and therefore all pages will change too.

http://www.wimgielis.com/tm1_mdxstatements_EN.htm

I mention this page as it is visited the most on my TM1 pages.

[PavoGa]

Wim,

You might want to consider this in passing parameters into the MDX. It something I've started doing to make the code easier to read over the ' | variable |' construct.

Code: Select all

# pCubeName = cube name passed in as parameter to TI

strMDX = EXPAND('UNION(
	{[}Dimensions].currentmember}
	, ORDER(
		FILTER( TM1SUBSETALL( [}Dimensions])
			, [}CubeDimensionUsage].([}Cubes].[%pCubeName%]) > "0")
		, [}CubeDimensionUsage].([}Cubes].[%pCubeName%])
		, ASC)
	, ALL)');

[lotsaram]

Wim,

You might want to consider this in passing parameters into the MDX. It something I've started doing to make the code easier to read over the ' | variable |' construct.

Code: Select all

# pCubeName = cube name passed in as parameter to TI

strMDX = EXPAND('UNION(
	{[}Dimensions].currentmember}
	, ORDER(
		FILTER( TM1SUBSETALL( [}Dimensions])
			, [}CubeDimensionUsage].([}Cubes].[%pCubeName%]) > "0")
		, [}CubeDimensionUsage].([}Cubes].[%pCubeName%])
		, ASC)
	, ALL)');

+1
Nice one. I've also adopted using Expand as my default way of managing string with variable concatenation over the pipe.

As an extra bonus this method means you also don't need to bother with numbertostring for any numeric variables since expand will only ever produce a string even for numeric variables.

[PavoGa]

It also greatly simplifies exporting a text file with a single process from cubes with variable numbers of dimensions vs. the Bedrock method of if nNumberOfDims = 2...
Build a string in the PROLOG and use a single ASCIIOUTPUT with EXPAND in the DATA tab.

[Wim Gielis]

Interesting comments and insights, thank you.

However, how would you do the following when vNrOfParameters is a number (1, 2, ...)

Code: Select all

CellPutS(vChoLine, 'CUBE', pChore, vProcess, vProcess_Order, EXPAND('Parameter %vNrOfParameters% value'));

I get as a result of Expand: "Parameter 2.000 value" while my element is called: "Parameter 2 value"

Obviously adding a variable with a NumberToString function will solve it but then I don't see a lot of value of Expand for numbers.

[lotsaram]

I get as a result of Expand: "Parameter 2.000 value" while my element is called: "Parameter 2 value"

Obviously adding a variable with a NumberToString function will solve it but then I don't see a lot of value of Expand for numbers.

Didn't say it was perfect but still useful. Depends what you use it for. For the use case of generating a text file to feed into command line to tm1runti where the numeric variable represents a numeric parameter it doesn't make any difference if this is represented as 2 or 2.000

It also greatly simplifies exporting a text file with a single process from cubes with variable numbers of dimensions vs. the Bedrock method of if nNumberOfDims = 2...
Build a string in the PROLOG and use a single ASCIIOUTPUT with EXPAND in the DATA tab.

I find this idea really interesting. If by "build a string in the prolog" you mean declaring all possible data source variables as empty strings so they exist (and the unused columns will just stay empty) then I think I get what you mean and yes it sure would have a lot of advantages in terms of less lines of code. With the only disadvantage being always generating the maximum number of columns in the output file (which would be a pretty minimal disadvantage in reality).

Thus something like
ElseIf( nDimensionCount = 4 );
AsciiOutput( cExportFile, pCube, v1, v2, v3, v4, sValue );
ElseIf( nDimensionCount = 5 );
AsciiOutput( cExportFile, pCube, v1, v2, v3, v4, v5, sValue );
ElseIf( nDimensionCount = blah blah ad infinitum to the max number of data source variables );

... would just become
AsciiOutput( cExportFile, Expand( '%pCube%,%v1%,%v2%,%v3%,%v4%,%v5%' ) );
or
AsciiOutput( cExportFile, Expand( '%pCube%%sDelim%%v1%%sDelim%%v2%%sDelim%%v3%%sDelim%%v4%%sDelim%%v5% ) );
or even
AsciiOutput( cExportFile, Expand( '%sQte%%pCube%%sQte%%sDelim%%sQte%%v1%%sQte%%sDelim%%sQte%%v2%%sQte%%sDelim%%sQte%%v3%%sQte%%sDelim%%sQte%%v4%%sQte%%sDelim%%sQte%%v5%%sQte%' ) );

Except that of course in the variants with expand there would be no need for the IF to test the number of export variables as it would just go to the maximum not to v5. Is this basically what you meant?

I think there will need to be a bedrock v4 at some point soon to support alternate hierarchies and temporary data sources etc. I like this idea and will pass it on. The only negative that might reject this approach might be performance impact of the expand evaluating so many variables as efficient to run wins out over efficient to write. Would have to do a head to head speed test and see which comes out on top as undoubtedly there's also a benefit to not having to evaluate the Booleans.

[failurehappening]

This is the perfect use of the famous DOUBLE EXPAND...

You need a cube with a large amount of dims to build/modify the TI, but this is the data tab, you can use the function Value_Is_String to find out if the cell you're processing is String or Numeric and then NumbertostringEX to format the numeric how you like

Code: Select all

 nRecordCount = nRecordCount + 1;

IF ( nRecordCount = 1 );
	Asciioutput( sOutputFile , sHeader );	
ENDIF;

sAllDimValues = '';
i = 1;

WHILE ( i <= nDimCount);
	j = NumberToString( i );
	sDimValue = EXPAND( '%V' | EXPAND ( '%j%' ) | '%');
	sAllDimValues = sAllDimValues  | ','  | sQuote | sDimValue | sQuote;
	i = i + 1; 
END;

sAllDimValues = SUBST( sAllDimValues , 3 , LONG( sAllDimValues ) -3 );

IF ( Value_Is_String = 0);
	sMeasureValue  =  NumbertostringEX ( nValue,'0.00000000000000000000','.','' ) ;
ELSE;
	sMeasureValue = sValue ;
ENDIF;
Asciioutput( sOutputFile  , sMeasureValue, pCubeName, sAllDimValues );

Unfortunately, as you can't use an EXPAND on the left hand side of the = sign you are restricted to if triangle on the import:

Code: Select all

	IF ( nDimCount = 2);
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V2 ) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 );
			ENDIF;
		ENDIF;

	ELSEIF ( nDimCount = 3);
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 , V3 ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V3 ) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 , V3 );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 , V3 );
			ENDIF;
		ENDIF;

	ELSEIF ( nDimCount = 4 );
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 , V3 , V4 ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V4) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 , V3 , V4  );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 , V3 , V4 );
			ENDIF;
		ENDIF;

	ELSEIF ( nDimCount = 5 );
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 , V3 , V4 , V5 ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V5 ) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 , V3 , V4 , V5   );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 , V3 , V4 , V5 );
			ENDIF;
		ENDIF;

	ELSEIF ( nDimCount = 6 );
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 , V3 , V4 , V5 , V6 ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V6 ) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 , V3 , V4 , V5 , V6  );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 , V3 , V4 , V5 , V6 );
			ENDIF;
		ENDIF;

	ELSEIF ( nDimCount = 7);
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V7 ) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7  );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 , V3 , V4 , V5 , V6 , V7 );
			ENDIF;
		ENDIF;

	ELSEIF ( nDimCount = 8);
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V8 ) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 );
			ENDIF;
		ENDIF;

	ELSEIF ( nDimCount = 9);
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V9) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 );
			ENDIF;
		ENDIF;

	ELSEIF ( nDimCount = 10);
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V10 ) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 );
			ENDIF;
		ENDIF;

	ELSEIF ( nDimCount = 11);
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 , V11  ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V11 ) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 , V11 );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 , V11 );
			ENDIF;
		ENDIF;

	ELSEIF ( nDimCount = 12);
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 , V11 , V12 ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V12 ) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 , V11 , V12 );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 , V11 , V12 );
			ENDIF;
		ENDIF;

	ELSEIF ( nDimCount = 13);
		IF ( CellIsUpdateable ( pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 , V11 , V12 , V13 ) = 1) ;
			IF (DTYPE ( sMeasureDimName , V13 ) @= 'N');
				CellIncrementN ( StringToNumber( vValue) , pCubeName , V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 , V11 , V12 , V13 );
			ELSE;
				CellPutS ( vValue , pCubeName, V1 , V2 , V3 , V4 , V5 , V6 , V7 , V8 , V9 , V10 , V11 , V12 , V13 );
			ENDIF;
		ENDIF;
	ENDIF;

ENDIF;

[lotsaram]

This is the perfect use of the famous DOUBLE EXPAND...

I love double Expand but actually no need for it in this use case. Courtesy of my hour train commute this morning I had a chance to try out modifying Bedrock.Cube.Data.Export to this approach and I finally got properly what I think Ty meant by "build the string on the prolog".

The standard TabDim loop on the cube dimensions only needs to be done ONCE on the prolog where you set a string which will be passed to Expand later on the Data tab. So the AsciiOutput on the Data tab just needs to be something like ..
AsciiOutput( cExportFile, Expand( sText ) );

This also means no need at all to have extra unused delimiters as this is all handled already building the sText string which is just a concatenation of the variable names with % and your delimiter and quote characters.

On an 8 dim cube the export was about 10% faster versus the triangular IF - ELSEIF monster but I kinda suspect that the larger the number of dimensions the greater the benefit as the greater the reduction in the number of boolean tests to be performed.

Unfortunately no way to avoid the IF branching on a generic import process due to Expand only working on the RHS. If only Expand could work on the LHS!

[Wim Gielis]

I think there will need to be a bedrock v4 at some point soon to support alternate hierarchies and temporary data sources etc. I like this idea and will pass it on. The only negative that might reject this approach might be performance impact of the expand evaluating so many variables as efficient to run wins out over efficient to write.

Good idea to update Bedrock.

I would include the 2 approaches in Bedrock, then depending on the case, the best approach can be chosen by the developer / admin person.

[PavoGa]

lotsaram wrote:

I find this idea really interesting. If by "build a string in the prolog" you mean declaring all possible data source variables as empty strings so they exist (and the unused columns will just stay empty) then I think I get what you mean and yes it sure would have a lot of advantages in terms of less lines of code. With the only disadvantage being always generating the maximum number of columns in the output file (which would be a pretty minimal disadvantage in reality).

Here is an example of what I've done. One bit of explanation: I have a cube with }Cubes and }Dimensions in it that is routinely updated with the dimension order for each cube and that factors in the code example below. If you do not have one of these, it turns out to be quite useful.

Code: Select all

#________________________ PROLOG
# not showing all the variable declarations, but they should be self-explanatory.
# create a subset of Dimensions to process.
strMDX = EXPAND('UNION(
	{[}Dimensions].currentmember}
	, ORDER(
		FILTER( TM1SUBSETALL( [}Dimensions])
			, [}CubeDimensionUsage].([}Cubes].[%pCubeName%]) > "0")
		, [}CubeDimensionUsage].([}Cubes].[%pCubeName%])
		, ASC)
	, ALL)');
SubsetCreateByMDX(subDimensions, strMDX);
SubsetElementDelete(dimDimensions, subDimensions, 1);


# now that we know the dimensions, let's build the datasource
ViewCreate(pCubeName, vwWorkingSource);

# get the position of the Version and Measure dimensions as they get a special subset built.
nVersionDimPos = NUMBR(CELLGETS(cubCubeDimUsage, pCubeName, 'Versions'));
nMeasureDimPos = NUMBR(CELLGETS(cubCubeDimUsage, pCubeName, pMeasureDim));

i = 1;
WHILE(i <= nNumberOfDims);
	# if the dimension is Versions, then create that subset using MDX for the attribute on Versions designating eligible versions for this conversion
	IF (i = nVersionDimPos);
		strMDX =  'UNION(
			{[Versions].currentmember}
			, FILTER( TM1SUBSETALL( [Versions])
				, [Versions].[OrgDataConversion] = ''Y'')
			, ALL)';
		ASCIIOUTPUT(strPrologErrLogFile, 'Version MDX: ', strMDX);
		SubsetCreateByMDX(subWorking, strMDX);
		SubsetElementDelete(dimVersions, subWorking, 1);
		ViewSubsetAssign(pCubeName, vwWorkingSource, dimVersions, subWorking);
	ELSEIF(i = nMeasureDimPos);
		# build the subset for the designated measure dimension. 
		SubsetCreate(pMeasureDim, subWorking);
		strMeasureParam = pMeasures;
		nIndex = 1;
		j = 1;
		# make sure the end character is not a delimiter.
		strMeasureParam = IF(SUBST(strMeasureParam, LONG(strMeasureParam), 1) @= '|', SUBST(strMeasureParam, 1, LONG(strMeasureParam)-1), strMeasureParam);
		WHILE(nIndex <> 0);
			nIndex = SCAN(strPipeDelim, strMeasureParam);
			IF (nIndex > 0);
				strMeasureAndFlag = SUBST(strMeasureParam, 1, nIndex - 1);
			ELSE;
				strMeasureAndFlag = strMeasureParam;
			ENDIF;
			
			nMeasureIndex = SCAN(strColonDelim, strMeasureAndFlag);
			IF (nMeasureIndex = 0);
				strMessage = 'Bad Measure parameter';
				PROCESSBREAK;
				nErrorFlag = 1;
			ENDIF;
			
			strMeasure = SUBST(strMeasureAndFlag, 1, nMeasureIndex - 1);
			strFlag = SUBST(strMeasureAndFlag, LONG(strMeasureAndFlag), 1);
			SubsetElementInsert(pMeasureDim, subWorking, strMeasure, j);
			strMeasureParam = SUBST(strMeasureParam, nIndex + 1, LONG(strMeasureParam) - nIndex);
			j = j + 1;
		END;
		nMeasureSubSize = SubsetGetSize(pMeasureDim, subWorking);
		IF (nMeasureSubSize = 0);
			strMessage = 'No measure elements provided';
			nErrorFlag = 1;
			PROCESSBREAK;
		ENDIF;
		ViewSubsetAssign(pCubeName, vwWorkingSource, pMeasureDim, subWorking);
	ELSE;
		# build subsets of leaf elements for other dimensions.
		dimWorking = SubsetGetElementName(dimDimensions, subDimensions, i);
		strMDX = EXPAND('UNION(
			{[%dimWorking%].currentmember}
			, TM1FILTERBYLEVEL( TM1SUBSETALL( [%dimWorking%]), 0)
			, ALL)');
		SubsetCreateByMDX(subWorking, strMDX);
		SubsetElementDelete(dimWorking, subWorking, 1);
		ViewSubsetAssign(pCubeName, vwWorkingSource, dimWorking, subWorking);
	ENDIF;
    # use NumberToString here to avoid the .000 that EXAND() will put in for i
	strOutputVariable = '%v' | NumberToString(i) | '%, ' ; 
	strOutputExecution = strOutputExecution | strOutputVariable;
	i = i + 1;
END;

strOutputExecution = strOutputExecution | '%strDataValue%';


#________________________ DATA tab

ASCIIOUTPUT(strOutputFile, EXPAND(strOutputExecution));

[PavoGa]

lotsaram wrote:
Unfortunately no way to avoid the IF branching on a generic import process due to Expand only working on the RHS. If only Expand could work on the LHS!

I have wondered if it is not best to go ahead and have a number of generic processes for various dimension counts. Then branch to that appropriately in the prolog of a control process and avoid the triangle altogether. Have not tested that, but it seems quite reasonable to expect better performance.

[Wim Gielis]

I have wondered if it is not best to go ahead and have a number of generic processes for various dimension counts. Then branch to that appropriately in the prolog of a control process and avoid the triangle altogether. Have not tested that, but it seems quite reasonable to expect better performance.

Let's start the process names with } so that they don't show up by default

[PavoGa]

Let's start the process names with } so that they don't show up by default

But of course!