ASCIIOutput Elements in One Dimension Only

kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

ASCIIOutput Elements in One Dimension Only

Post by kenship »

Hi all,

I use AsciiOutput to export cube data without problem.

I have a new requirement that I need to export only the element of one dimension. The element is short listed based on the cube view.

This is a sample before zero suppression:

Project Dept Expenditure Revenue
123 A 0 0
456 B 10 5
789 C 15 10

After activating zero suppression, "123" would be gone.

Using AsciiOutput I can easily export a file that looks like this:

456 B Expenditure 10
456 B Revenue 5
789 C Expenditure 15
789 C Revenue 10

But, the prime usage of this view is to build a list of active projects with expenditures or revenues. What I am trying to accomplish is to export a file including only the Project dimension elements "456" and "789" only without repeating "456" and "789".

Thanks.

Kenneth
Last edited by kenship on Thu Sep 15, 2022 5:29 pm, edited 1 time in total.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOutput Elements in One Dimension Only

Post by Wim Gielis »

You have more than 180 posts here, I would assume that this task is not so difficult.
In the view that is the data source to the process, you need to exclude the 0 cells (which is what would do anyway, in almost all cases).
But take for example in all but the Project dimension, the top level element.
Or take level 0 but beware, when you do not exclude consolidated cells, the output can grow very large.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: ASCIIOutput Elements in One Dimension Only

Post by kenship »

Hi Wim, thanks for replying.

I noticed my original post wasn't the most intuitive in asking the question. I have now edited what I want to achieve better.

Kenneth
Wim Gielis wrote: Thu Sep 15, 2022 3:26 pm You have more than 180 posts here, I would assume that this task is not so difficult.
In the view that is the data source to the process, you need to exclude the 0 cells (which is what would do anyway, in almost all cases).
But take for example in all but the Project dimension, the top level element.
Or take level 0 but beware, when you do not exclude consolidated cells, the output can grow very large.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: ASCIIOutput Elements in One Dimension Only

Post by gtonkin »

Assuming you don’t want to repeat projects against multiple departments, you would need to look at a consolidation on department and on revenue and expenses.
If you have negatives where an aggregate could be zero, you may need to play with weightings or use a proxy for a valid “record”.
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: ASCIIOutput Elements in One Dimension Only

Post by kenship »

Consolidation for Dep is not the issue but consolidating Exp and Rev could be if there're offsetting negatives. My example shows Revenues in positive but in my data it's negative.

More than this, I would need to nest the columns with 12 months and 2 ledgers. That means my extraction file would have much more duplicated project entries.

Kenneth


gtonkin wrote: Thu Sep 15, 2022 5:55 pm Assuming you don’t want to repeat projects against multiple departments, you would need to look at a consolidation on department and on revenue and expenses.
If you have negatives where an aggregate could be zero, you may need to play with weightings or use a proxy for a valid “record”.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOutput Elements in One Dimension Only

Post by Wim Gielis »

Please can you provide a representative set of cube data and elements. Also, what the output should be based on the sample data. Then we can help you, otherwise we are wasting our time guessing what your requirements are.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: ASCIIOutput Elements in One Dimension Only

Post by kenship »

Apologize for confusion.

My regular AsciiOutput looks like this:

456 All Dpt Exp P01 Actuals 5
456 All Dpt Rev P01 Actuals -6
456 All Dpt Exp P01 Budget 5
456 All Dpt Rev P01 Budget -5
456 All Dpt Exp P02 Actuals 4
456 All Dpt Rev P02 Actuals -3
456 All Dpt Exp P02 Budget 5
456 All Dpt Rev P02 Budget -5
456 All Dpt Exp P03 Actuals 6
456 All Dpt Rev P03 Actuals -8
456 All Dpt Exp P03 Budget 5
456 All Dpt Rev P03 Budget -5
789 All Dpt Exp P01 Actuals 8
789 All Dpt Rev P01 Actuals -4
789 All Dpt Exp P02 Actuals 3
789 All Dpt Rev P02 Actuals -2

I can consolidate Dpt dimension; Exp/Rev is already consolidated and can't be combined; Period (P01, P02, P03) and Ledger (Actuals, Ledger) can't be combined due to the possibility of netting to zero then suppressed.

My ultimate goal is to extract "456", "789" once to a file.

I'm now looking at using MDX filter to create a subset that includes only the 2 projects for my extraction.

Hope this explains better.

Kenneth
Wim Gielis wrote: Thu Sep 15, 2022 8:12 pm Please can you provide a representative set of cube data and elements. Also, what the output should be based on the sample data. Then we can help you, otherwise we are wasting our time guessing what your requirements are.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOutput Elements in One Dimension Only

Post by Wim Gielis »

kenship wrote: Thu Sep 15, 2022 8:35 pm My ultimate goal is to extract "456", "789" once to a file.
What about the other contents of the file ? I’m sorry but I won’t ask you again and again.

Whether you determine the right projects in MDX or through the view, you need to do it somewhere.
You can also create temporary/fake consolidations with even random weights, to rule out the possibility of values netting each other out.
A CellGetN or series of CellGetNs can then be used to retrieve other cells but watch out for performance in big cubes.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: ASCIIOutput Elements in One Dimension Only

Post by declanr »

As I have understood your requirement:

You want to produce a file with 25 columns:
- Column 1 = Project Code
- Column 2>13 = Revenue for Month 1 through to 12
- Column 14 > 25 = Expenditure for Month 1 through to 12

And you want to exclude any Project/Row where all of the 24 values are zero.



Unless the cube is extremely large or rule intensive you can probably just do some simple loops of the entire dimension and still have it run perfectly quickly. The entire code below could just go on the prolog without any datasource specified for the TI.

Code: Select all

# Set Constants
cFileName = <Your Output File>;
cCubeName = <Your Cube Name>;
cDimProject = <Your Project Dimension>;
cDimTime = <Your Time Dimension>;
cSubsetTime = <A Subset with the relevant time periods for Export>;


DataSourceAsciiQuoteCharacter = '';

# Loop Project Dimension
iCount = 1;
iMax = DimSiz ( cDimProject );
While ( iCount <= iMax );
	sProjectName = DimNm ( cDimProject, iCount );
	If ( DType ( cDimProject, sProjectName ) @= 'N' );
		sString = sProjectName;
		nFlag = 0;
		# Find Revenue Figures for 12 Periods
		iTimeCount = 1;
		iTimeMax = SubsetGetSize ( cDimTime, cSubsetTime );
		While ( iTimeCount <= iTimeMax );
			sTimeName = SubsetGetElementName ( cDimTime, cSubsetTime, iTimeCount );
			nValue = CellGetN ( cCubeName, ..., ..., sTimeName, sProjectName, 'Revenue' );
			If ( nValue <> 0 );
				nFlag = 1;
			EndIf;
			sString = sString | ', ' | NumberToString ( nValue );
			iTimeCount = iTimeCount + 1;
		End;

		# Find Expenditure for 12 Periods
		iTimeCount = 1;
		iTimeMax = SubsetGetSize ( cDimTime, cSubsetTime );
		While ( iTimeCount <= iTimeMax );
			sTimeName = SubsetGetElementName ( cDimTime, cSubsetTime, iTimeCount );
			nValue = CellGetN ( cCubeName, ..., ..., sTimeName, sProjectName, 'Expenditure');
			If ( nValue <> 0 );
				nFlag = 1;
			EndIf;
			sString = sString | ', ' | NumberToString ( nValue );
			iTimeCount = iTimeCount + 1;
		End;

		# If any of the data points are not zero then export the data
		If ( nFlag <> 0 );
			TextOutput ( cFileName, sString );
		EndIf;

	EndIf;
	iCount = iCount + 1;
End;

For the CellGetNs you would obviously need to provide the correct order and relevant Consolidations for other dimensions in the cube.

It's a somewhat "brute force" approach to the requirement (if I have correctly understood the requirement), but it's simple and would get the job done in most cases. If it was slow, then I would start looking at taking similar code and moving it from the Prolog to the data tab with additional logic (as you were mentioning earlier) to create the datasource.
Even on a pretty big cube though, as long as the cube is set up to run efficiently then the process would be perfectly quick.


Usual caveat that the code has not been tested in a TI editor so probably contains some typos.
Declan Rodger
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: ASCIIOutput Elements in One Dimension Only

Post by kenship »

I don't need the other values at all.

The whole exercise is to output a list of project that is active, as defined by whether there's any value based on expenditure or revenue in any month, in actuals or budget ledger.

Doing a cube view is just super simple, if I take a snap shot then my project list is already in Excel.

Extracting the list of project using TI, definitely not straight forward.

Appreciate your time looking at this, sorry if I have wasted yours.

Kenneth
Wim Gielis wrote: Thu Sep 15, 2022 8:45 pm
kenship wrote: Thu Sep 15, 2022 8:35 pm My ultimate goal is to extract "456", "789" once to a file.
What about the other contents of the file ? I’m sorry but I won’t ask you again and again.

Whether you determine the right projects in MDX or through the view, you need to do it somewhere.
You can also create temporary/fake consolidations with even random weights, to rule out the possibility of values netting each other out.
A CellGetN or series of CellGetNs can then be used to retrieve other cells but watch out for performance in big cubes.
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: ASCIIOutput Elements in One Dimension Only

Post by kenship »

Thanks. I was hoping there's an easy way to use TI to extract something very similar to a snap shot, with many horizontal nested columns but first column giving me project without repeating it (which is why I nested the columns).

But I do appreciate your offer.

Kenneth
declanr wrote: Thu Sep 15, 2022 9:41 pm As I have understood your requirement:

You want to produce a file with 25 columns:
- Column 1 = Project Code
- Column 2>13 = Revenue for Month 1 through to 12
- Column 14 > 25 = Expenditure for Month 1 through to 12

And you want to exclude any Project/Row where all of the 24 values are zero.



Unless the cube is extremely large or rule intensive you can probably just do some simple loops of the entire dimension and still have it run perfectly quickly. The entire code below could just go on the prolog without any datasource specified for the TI.

Code: Select all

# Set Constants
cFileName = <Your Output File>;
cCubeName = <Your Cube Name>;
cDimProject = <Your Project Dimension>;
cDimTime = <Your Time Dimension>;
cSubsetTime = <A Subset with the relevant time periods for Export>;


DataSourceAsciiQuoteCharacter = '';

# Loop Project Dimension
iCount = 1;
iMax = DimSiz ( cDimProject );
While ( iCount <= iMax );
	sProjectName = DimNm ( cDimProject, iCount );
	If ( DType ( cDimProject, sProjectName ) @= 'N' );
		sString = sProjectName;
		nFlag = 0;
		# Find Revenue Figures for 12 Periods
		iTimeCount = 1;
		iTimeMax = SubsetGetSize ( cDimTime, cSubsetTime );
		While ( iTimeCount <= iTimeMax );
			sTimeName = SubsetGetElementName ( cDimTime, cSubsetTime, iTimeCount );
			nValue = CellGetN ( cCubeName, ..., ..., sTimeName, sProjectName, 'Revenue' );
			If ( nValue <> 0 );
				nFlag = 1;
			EndIf;
			sString = sString | ', ' | NumberToString ( nValue );
			iTimeCount = iTimeCount + 1;
		End;

		# Find Expenditure for 12 Periods
		iTimeCount = 1;
		iTimeMax = SubsetGetSize ( cDimTime, cSubsetTime );
		While ( iTimeCount <= iTimeMax );
			sTimeName = SubsetGetElementName ( cDimTime, cSubsetTime, iTimeCount );
			nValue = CellGetN ( cCubeName, ..., ..., sTimeName, sProjectName, 'Expenditure');
			If ( nValue <> 0 );
				nFlag = 1;
			EndIf;
			sString = sString | ', ' | NumberToString ( nValue );
			iTimeCount = iTimeCount + 1;
		End;

		# If any of the data points are not zero then export the data
		If ( nFlag <> 0 );
			TextOutput ( cFileName, sString );
		EndIf;

	EndIf;
	iCount = iCount + 1;
End;

For the CellGetNs you would obviously need to provide the correct order and relevant Consolidations for other dimensions in the cube.

It's a somewhat "brute force" approach to the requirement (if I have correctly understood the requirement), but it's simple and would get the job done in most cases. If it was slow, then I would start looking at taking similar code and moving it from the Prolog to the data tab with additional logic (as you were mentioning earlier) to create the datasource.
Even on a pretty big cube though, as long as the cube is set up to run efficiently then the process would be perfectly quick.


Usual caveat that the code has not been tested in a TI editor so probably contains some typos.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOutput Elements in One Dimension Only

Post by Wim Gielis »

kenship wrote: Thu Sep 15, 2022 11:28 pm I don't need the other values at all.

The whole exercise is to output a list of project that is active, as defined by whether there's any value based on expenditure or revenue in any month, in actuals or budget ledger.
If you want to have unique elements, you should have the reflex to think about a dimension. Why ?
In a dimension, an element can exist only once. Added bonus, the elements can be sorted by TM1.
Therefore, use a level 0 view of the data, and in the Metadata tab, populate a new temporary dimension.
That dimension is not part of any cube. It is created in the Prolog tab with DimensionCreate and
destroyed in the Epilog tab with DimensionDestroy.
However, before destroying the temporary dimension, in the Epilog tab you will do a simple While loop over the
elements in the new temporary dimension and do AsciiOutput.

This will be very quick, unless you have a giant cube.

About 1 month ago, I wrote a generic TI process that answers the question:
“Given a list of elements or all elements in a dimension/hierarchy, which elements do contain data and which don’t ? ”.
The ‘lookup’ can be done in any cube or just selected ones with wildcards. Data can be base data or rules-derived.
The process is optimized in that, once an element is detected, processing data on that element is stopped.
Simply put, the element Actual containing 10 million data points, will be detected after 1 Actuals cell and processing is immediately stopped for Actual.
We don’t need to go through 10 million cells if 1 cell is found.
The output is a text file, or a subset, or an attribute on the chosen dimension.

I can recommend anyone writing such a generic process. It can help a lot in deleting wrong or redundant elements,
or solving questions like the one you now have.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: ASCIIOutput Elements in One Dimension Only

Post by gtonkin »

Now imagine that TM1 supported array type variables...
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: ASCIIOutput Elements in One Dimension Only

Post by MarenC »

Hi,

You probably don't want another element in your measure dim, but you could add one and call it Active Flag, and then use ABS function on Revenue and expenditure in a rule, to be a 1 if ABS of rev and exp is not zero. Or have a cube called Project Information, and add the flag in there.

Then asciioutput any projects with a 1, using the project dimension subset as the data source, or a Project Information cube view.

Alternatively, you could use the projects dimension subset as a datasource, do a cellget ABS for revenue and expenditure and ascii if not zero.

All seems fairly simple to me.

Maren
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOutput Elements in One Dimension Only

Post by Wim Gielis »

MarenC wrote: Fri Sep 16, 2022 7:32 am Hi,

You probably don't want another element in your measure dim, but you could add one and call it Active Flag, and then use ABS function on Revenue and expenditure in a rule, to be a 1 if ABS of rev and exp is not zero. Or have a cube called Project Information, and add the flag in there.

Then asciioutput any projects with a 1, using the project dimension subset as the data source, or a Project Information cube view.

Alternatively, you could use the projects dimension subset as a datasource, do a cellget ABS for revenue and expenditure and ascii if not zero.

All seems fairly simple to me.

Maren
If a CellGetN is possible and you know the intersections in the cube to interrogate, why not have an MDX subset and forget all the hassle ?

Note that with some of your other suggestions you can still have multiple records in the output. For instance, what about the period dimension ?

If you want to have an extra measure included in TI output where the data source is a view, the measure needs to be fed. It’s an option but I’m not sure I would do that when there are other options and when we are taking about not so small cubes.

If I would go for an Active flag, I would do it at the time of loading the data from the source system. Just update a small 2D or 3D cube (or an attribute) and you are done. If it’s planning data with data input and rules, that might not be the best solution.
Last edited by Wim Gielis on Fri Sep 16, 2022 9:00 am, edited 1 time in total.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: ASCIIOutput Elements in One Dimension Only

Post by MarenC »

Hi,

The active flag could be held against a period element that isn't P01 to P12 but is an element used to hold full year measures. I mean there is so much we haven't been told, like when is the active period, spend within the current year, spend within the last 2 years? Who knows!

I did say that he probably wouldn't want to add new elements, therefore I was just posting that as an alternative. if you have a concept of active projects then I would expect that maybe this is something that is incorporated into the model, probably via a new cube which potentially holds other project information. Again we there is much we haven't been told!

There are certainly enough options to consider now :D , none of them exactly feats of lateral thinking, not like your delete consolidations idea! :D

Maren
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: ASCIIOutput Elements in One Dimension Only

Post by kenship »

Thanks to all, it's indeed a plentiful of suggestions.

I did have the Active flag on mind before going to the forum. I was hoping there's a way that I can output the view that gives me exactly what I want (I don't mind the extra columns) but I think this feature can only be a wishlist.

MDX and flag would be what I'll consider to go.

Thanks again!

Kenneth
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: ASCIIOutput Elements in One Dimension Only

Post by kenship »

I have implemented yours solution and modified to expand qualifying criteria.

In my case the process loop each project for if there's value based on:
Current year + each month + Revenue/Expense + Actuals/Budget ledger
Previous year + Full Year + Revenue/Expense + Actuals

Only the project is extracted in the csv file.

Works perfectly.

Kenneth

Code: Select all

cFilePath = '..\ExtractFiles\';
cFile = 'ActiveProject.csv';
cFullPath = cFilePath | cFile;
cCubeName = 'actuals';
cDimProject = 'Project';
cDimCurrentYear = CellGetS ('CTRL', 'Current Year', 'svalue');
cDimPreviousYear = CellGetS ('CTRL', 'Previous Year', 'svalue');
cDimPeriod = 'period';
cSubsetPeriod = 'P01-P12';

DataSourceAsciiQuoteCharacter = '';

# Loop Project Dimension
iCount = 1;
iMax = DimSiz ( cDimProject );
While ( iCount <= iMax );
	sProjectCode = DimNm ( cDimProject, iCount );
	If ( DType ( cDimProject, sProjectCode ) @= 'N' );
		sString = sProjectCode;
		nFlag = 0;
		# Find Revenue Figures for 12 Periods Actuals
		iTimeCount = 1;
		iTimeMax = SubsetGetSize ( cDimPeriod, cSubsetPeriod );
		While ( iTimeCount <= iTimeMax );
			sPeriodName = SubsetGetElementName ( cDimPeriod, cSubsetPeriod, iTimeCount );
			nValue = CellGetN (cCubeName, cDimCurrentYear, sPeriodName, sProjectCode, 'Revenue', 'ACTUALS');
			If ( nValue <> 0 );
				nFlag = 1;
			EndIf;
#			sString = sString | ', ' | NumberToString ( nValue );
			iTimeCount = iTimeCount + 1;
		End;

		# Find Expenditure Figures for 12 Periods Actuals
		iTimeCount = 1;
		iTimeMax = SubsetGetSize ( cDimPeriod, cSubsetPeriod );
		While ( iTimeCount <= iTimeMax );
			sPeriodName = SubsetGetElementName ( cDimPeriod, cSubsetPeriod, iTimeCount );
			nValue = CellGetN (cCubeName, cDimCurrentYear, sPeriodName, sProjectCode, 'Expenditure', 'ACTUALS');
			If ( nValue <> 0 );
				nFlag = 1;
			EndIf;
#			sString = sString | ', ' | NumberToString ( nValue );
			iTimeCount = iTimeCount + 1;
		End;

		# Find Revenue Figures for 12 Periods Budget
		iTimeCount = 1;
		iTimeMax = SubsetGetSize ( cDimPeriod, cSubsetPeriod );
		While ( iTimeCount <= iTimeMax );
			sPeriodName = SubsetGetElementName ( cDimPeriod, cSubsetPeriod, iTimeCount );
			nValue = CellGetN (cCubeName, cDimCurrentYear, sPeriodName, sProjectCode, 'Revenue', 'BUDGET');
			If ( nValue <> 0 );
				nFlag = 1;
			EndIf;
			iTimeCount = iTimeCount + 1;
		End;

		# Find Expenditure Figures for 12 Periods Budget
		iTimeCount = 1;
		iTimeMax = SubsetGetSize ( cDimPeriod, cSubsetPeriod );
		While ( iTimeCount <= iTimeMax );
			sPeriodName = SubsetGetElementName ( cDimPeriod, cSubsetPeriod, iTimeCount );
			nValue = CellGetN (cCubeName, cDimCurrentYear, sPeriodName, sProjectCode, 'Expenditure', 'BUDGET');
			If ( nValue <> 0 );
				nFlag = 1;
			EndIf;
			iTimeCount = iTimeCount + 1;
		End;

		# Find Revenue Figures for previous year
		nValue = CellGetN (cCubeName, cDimPreviousYear, 'All Periods', 'N04DPT-ALL', 'Revenue', 'ACTUALS');
		If ( nValue <> 0 );
			nFlag = 1;
		EndIf;

		# Find Expenditure Figures for previous year
		nValue = CellGetN (cCubeName, cDimPreviousYear, 'All Periods', 'N04DPT-ALL', 'Expenditure', 'ACTUALS');
		If ( nValue <> 0 );
			nFlag = 1;
		EndIf;

# If any of the data points are not zero then export the data
		If ( nFlag <> 0 );
			TextOutput ( cFullPath, sString );
		EndIf;

	EndIf;
	iCount = iCount + 1;
End;
declanr wrote: Thu Sep 15, 2022 9:41 pm As I have understood your requirement:

You want to produce a file with 25 columns:
- Column 1 = Project Code
- Column 2>13 = Revenue for Month 1 through to 12
- Column 14 > 25 = Expenditure for Month 1 through to 12

And you want to exclude any Project/Row where all of the 24 values are zero.



Unless the cube is extremely large or rule intensive you can probably just do some simple loops of the entire dimension and still have it run perfectly quickly. The entire code below could just go on the prolog without any datasource specified for the TI.

Code: Select all

# Set Constants
cFileName = <Your Output File>;
cCubeName = <Your Cube Name>;
cDimProject = <Your Project Dimension>;
cDimTime = <Your Time Dimension>;
cSubsetTime = <A Subset with the relevant time periods for Export>;


DataSourceAsciiQuoteCharacter = '';

# Loop Project Dimension
iCount = 1;
iMax = DimSiz ( cDimProject );
While ( iCount <= iMax );
	sProjectName = DimNm ( cDimProject, iCount );
	If ( DType ( cDimProject, sProjectName ) @= 'N' );
		sString = sProjectName;
		nFlag = 0;
		# Find Revenue Figures for 12 Periods
		iTimeCount = 1;
		iTimeMax = SubsetGetSize ( cDimTime, cSubsetTime );
		While ( iTimeCount <= iTimeMax );
			sTimeName = SubsetGetElementName ( cDimTime, cSubsetTime, iTimeCount );
			nValue = CellGetN ( cCubeName, ..., ..., sTimeName, sProjectName, 'Revenue' );
			If ( nValue <> 0 );
				nFlag = 1;
			EndIf;
			sString = sString | ', ' | NumberToString ( nValue );
			iTimeCount = iTimeCount + 1;
		End;

		# Find Expenditure for 12 Periods
		iTimeCount = 1;
		iTimeMax = SubsetGetSize ( cDimTime, cSubsetTime );
		While ( iTimeCount <= iTimeMax );
			sTimeName = SubsetGetElementName ( cDimTime, cSubsetTime, iTimeCount );
			nValue = CellGetN ( cCubeName, ..., ..., sTimeName, sProjectName, 'Expenditure');
			If ( nValue <> 0 );
				nFlag = 1;
			EndIf;
			sString = sString | ', ' | NumberToString ( nValue );
			iTimeCount = iTimeCount + 1;
		End;

		# If any of the data points are not zero then export the data
		If ( nFlag <> 0 );
			TextOutput ( cFileName, sString );
		EndIf;

	EndIf;
	iCount = iCount + 1;
End;

For the CellGetNs you would obviously need to provide the correct order and relevant Consolidations for other dimensions in the cube.

It's a somewhat "brute force" approach to the requirement (if I have correctly understood the requirement), but it's simple and would get the job done in most cases. If it was slow, then I would start looking at taking similar code and moving it from the Prolog to the data tab with additional logic (as you were mentioning earlier) to create the datasource.
Even on a pretty big cube though, as long as the cube is set up to run efficiently then the process would be perfectly quick.


Usual caveat that the code has not been tested in a TI editor so probably contains some typos.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOutput Elements in One Dimension Only

Post by Wim Gielis »

I would add a couple of:

If ( nFlag = 0);

To avoid processing useless loops.
If you already know that nFlag <> 0, why bother with other loops ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: ASCIIOutput Elements in One Dimension Only

Post by kenship »

I get it, I'll implement it.

Kenneth
Wim Gielis wrote: Mon Sep 19, 2022 5:27 pm I would add a couple of:

If ( nFlag = 0);

To avoid processing useless loops.
If you already know that nFlag <> 0, why bother with other loops ?
Post Reply