Hi
I am currently working on developing an asset model for Capital expenditure budgeting. I need to create a tool by which users can generate new assets numbers for their cost centre - from 1 to say 25 at a time - once the numbers are generated they will enter the names of the assets as attributes, and the $ amounts in the applicable months
I was thinking about using a While statement with a loop but I'm having trouble getting the loop to work.
Can anyone help, please!
thanks
Generating new asset numbers
- Alan Kirk
- Site Admin
- Posts: 6610
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Generating new asset numbers
Oh, I do love questions like "having trouble getting the loop to work".Quail wrote:Hi
I am currently working on developing an asset model for Capital expenditure budgeting. I need to create a tool by which users can generate new assets numbers for their cost centre - from 1 to say 25 at a time - once the numbers are generated they will enter the names of the assets as attributes, and the $ amounts in the applicable months
I was thinking about using a While statement with a loop but I'm having trouble getting the loop to work.
Request for assistance guidelines (PLEASE READ)
Probably the simplest way of doing this is to have the asset numbers in a dimension. The new asset numbers would be generated by a TI. Whether that would be triggered via server explorer or via an action button, depends on your own setup. The number of new asset numbers to be generated would have to be specified by a parameter of the TI process.Request for assistance guidelines wrote:...if you're getting unexpected results, specifics of what you're running, how, and what results you're getting will yield a more valuable response than "I'm running a T.I. but my code doesn't work properly".
The TI code would need to look up the last asset number in the dimension. Assuming that they're assigned sequentially, a combination of a DimNm formula and a DimSiz formula would be able to retrieve that.
Since it's an element name it would be in a string format, meaning that you'd have to convert it to a value using the Numbr() function.
Then it would simply be a case of doing a While() loop for n=1 to n= the number of new assets to be created, as passed via the parameter. Each asset number would be found by adding n to the converted value of the last existing asset, retrieved in the previous step.
That value would then need to be converted back to a string and inserted into the dimension via the DimensionElementInsert function.
If this (or something similar to it) isn't working for you, details of the specific problems that you're having would be useful to have.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Re: Generating new asset numbers
Hi Alan,
My apologies I should have been more specific - my first posting!
The TI actually creates 1 new asset but obviously I have not looped it appropriately and cannot create any more - here is the script
DimName = 'Asset';
vNum = CellGetS(‘Control', 'Current_Asset_Number', 'Assetcounter');
vQtr = CellGetS('Control', 'Current_Asset_Number', 'Timecode');
vNew = pNumber; (pNumber = the number of new assets to be created - entered by the user)
vNumIncrement = STR((NUMBR(vNum) + 1) , 4, 0);
iAssetNo = 1;
WHILE (iAssetNo <= vNew);
vAssetCode = vQtr|vNum;
DimensionElementInsert(DimName, '', vAssetCode,'N');
CellPutS(vNumIncrement, 'Control', 'Current_Asset_Number', 'Assetcounter');
iAssetNo = iAssetNo +1;
END;
thanks
My apologies I should have been more specific - my first posting!
The TI actually creates 1 new asset but obviously I have not looped it appropriately and cannot create any more - here is the script
DimName = 'Asset';
vNum = CellGetS(‘Control', 'Current_Asset_Number', 'Assetcounter');
vQtr = CellGetS('Control', 'Current_Asset_Number', 'Timecode');
vNew = pNumber; (pNumber = the number of new assets to be created - entered by the user)
vNumIncrement = STR((NUMBR(vNum) + 1) , 4, 0);
iAssetNo = 1;
WHILE (iAssetNo <= vNew);
vAssetCode = vQtr|vNum;
DimensionElementInsert(DimName, '', vAssetCode,'N');
CellPutS(vNumIncrement, 'Control', 'Current_Asset_Number', 'Assetcounter');
iAssetNo = iAssetNo +1;
END;
thanks
- Alan Kirk
- Site Admin
- Posts: 6610
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Generating new asset numbers
Quail wrote:Hi Alan,
My apologies I should have been more specific - my first posting!
The TI actually creates 1 new asset but obviously I have not looped it appropriately and cannot create any more - here is the script
DimName = 'Asset';
vNum = CellGetS(‘Control', 'Current_Asset_Number', 'Assetcounter');
vQtr = CellGetS('Control', 'Current_Asset_Number', 'Timecode');
vNew = pNumber; (pNumber = the number of new assets to be created - entered by the user)
vNumIncrement = STR((NUMBR(vNum) + 1) , 4, 0);
iAssetNo = 1;
WHILE (iAssetNo <= vNew);
vAssetCode = vQtr|vNum;
DimensionElementInsert(DimName, '', vAssetCode,'N');
CellPutS(vNumIncrement, 'Control', 'Current_Asset_Number', 'Assetcounter');
iAssetNo = iAssetNo +1;
END;
thanks
Your problem is that you're only incrementing outside the loop. That means that no matter how many assets the user asks for, they get only one.
This looks OK:
Code: Select all
DimName = 'Asset';
vNum = CellGetS(‘Control', 'Current_Asset_Number', 'Assetcounter');
vQtr = CellGetS('Control', 'Current_Asset_Number', 'Timecode');
vNew = pNumber; (pNumber = the number of new assets to be created - entered by the user)
Code: Select all
vNumIncrement = STR((NUMBR(vNum) + 1) , 4, 0);
It would need to be (untested):
Code: Select all
iAssetNo = 1;
WHILE ( iAssetNo <= vNew);
vNumIncrement = STR( (NUMBR(vNum) + iAssetNo) , 4, 0);
vAssetCode = vQtr|vNumIncrement;
DimensionElementInsert(DimName, '', vAssetCode,'N');
CellPutS(vNumIncrement, 'Control', 'Current_Asset_Number', 'Assetcounter');
iAssetNo = iAssetNo +1;
END;
(a) You could convert vNum to a number as soon as you read it from the cube so that you don't have to do it each loop;
(b) You could do the CellPutS to the control cube after the end of the loop since you don't need to update that for every iteration of the loop either.
The only reason that I didn't do that is that wanted to change your code as little as possible to help you get a better idea of where the problem is.
I'd also suggest doing a test to make sure that the number input to pNumber is valid; not <=0 or higher than the number of projects that they're allowed to create.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Re: Generating new asset numbers
Hi Alan
many thanks for your response- it has worked a treat! Your thorough explanation was really appreciated!
thanks
many thanks for your response- it has worked a treat! Your thorough explanation was really appreciated!
thanks