Hi,
I have a project budget cube in which an end user will put in a start and end date of a project and then input the budget phased monthly. I wish to write a rule that only enables the user to enter budget data into the months that fall within the start and end date of the project. Sounds simple enough!
The rule that I have written does not work (for info, the full rule is at the bottom of the post). So I have set about breaking my rule down and seeing if the components all work. I have written the following rule:
IF(DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonth')))=0,3,2);
This DIMIX test = if the 'TimeMonth' ATTR of the Project_Measures dim DOES NOT exist as an element within the 'Time_Month' dim then 3, therwise 2. Within the cubes this returns "3"'s meaning that the DIMIX test failed - the ATTR do not exist. However, if I do the same test in excel with a DBRA and a DIMIX test I can prove that they do exist and the rule should return a "2".
I have tried making the element attribute text and numeric but I get the same result (note that I still used 'ATTRS' in the rule - as 'ATTRN' returned an error when trying to save the rule).
So my question is...can I use an element attribute as the element in a DIMIX test within a rule (as I am doing above) or does TM1 not allow this?
As always, any help or guidance would be much appreciated.
For info:
Within the small rule above I am just trying to determine the correct logic of teh rule. The full rule to achive what I wanted is this:
IF(
ELISCOMP('Project_Measures',!Project_Measures,'FY 2011')=1,
IF(
DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original Start Month'))>DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonth')))
0,
IF(
DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original End Month'))<DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonth'))),
0,
CONTINUE
)
),
CONTINUE);
Can you use an ATTRS within a DIMIX rules function?
-
- 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: Can you use an ATTRS within a DIMIX rules function?
Do you not simply want to check if the Attribute returned exists as an element?
If so the code would be as follows (without the DB):
IF(DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonth'))=0,3,2);
If so the code would be as follows (without the DB):
IF(DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonth'))=0,3,2);
Declan Rodger
-
- MVP
- Posts: 3651
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Can you use an ATTRS within a DIMIX rules function?
There is no issue with using ATTRS in a rule, or any function for that matter which you expect to return a string and embedding the function into a DB(). This should work, ... provided that the string exists a a member of the dimension at the appropriate location in the DB formula.tosca1978 wrote:IF(DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonth')))=0,3,2);
The ATTRN would fail with an error inside a DB as an element reference as elements by definition are defined as strings and the DB therefore expects string references only. My assumption would be that your "TimeMonth" attribute is actually defied as numeric type not string. Using ATTRS to call it is probably giving you a "silent fail" and returning a blank string as there is no string or alias type "TimeMonth" attribute defined (note that's a guess, albeit an informed one as I haven't explicitly tested this!). The reason this probably isn't failing in Excel is that the DBR formula automatically take the text value of any numeric arguments from a DBRA or another DBR and so are handling your type mismatch without you realizing.
-
- Community Contributor
- Posts: 147
- Joined: Mon Nov 29, 2010 6:30 pm
- OLAP Product: Cognos TM1
- Version: 10.1
- Excel Version: Office 2010
Re: Can you use an ATTRS within a DIMIX rules function?
This is a legal operation.
Are the month's inputted as numbers or words?
Are the element names in the Time_Month dimension numbers or words?
Is the attribute a string or a text? Are you comparing strings with strings and numbers with numbers?
To diagnose your issue, why not split your formula into further constituent parts and see if they work independently, then join them together step-by-step. i.e if they value returned by Attrs is invalid, then it won't work in any formula regardless.
I'm confused by the cube setup, but here are some items to look at.DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original Start Month'))>DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonth')))
Are the month's inputted as numbers or words?
Are the element names in the Time_Month dimension numbers or words?
Is the attribute a string or a text? Are you comparing strings with strings and numbers with numbers?
To diagnose your issue, why not split your formula into further constituent parts and see if they work independently, then join them together step-by-step. i.e if they value returned by Attrs is invalid, then it won't work in any formula regardless.
-
- Posts: 101
- Joined: Thu Oct 20, 2011 6:53 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2007
- Location: London, UK
Re: Can you use an ATTRS within a DIMIX rules function?
Hi Lotsaram/Chrostopher,
Thanks to you both for your replies. Christopher is some more info to help you understand teh cube set up (please be aware that I did not set up this cube - a consultant did):
The Original Budget cube consists of:
Project Code dim
Budget Line Item Dim
Project_Measures Dim (Start Date/End Date/Start Month/End Month/Profile (picklist)/Phased cost by month (Jan-11/Feb-11 etc)
The idea is that the end user types Start Date and End Date into Active Form eg 01/03/2011 and 01/06/2011. The Start Month is rule derived and produces "201103" and "201106". Now I only want the end user to be able to enter budget data into the months within the start and end dates of the project.
So, in the Project_Measures dim I have deleted my attributes that I build earlier (I had one as a text attribute and one as a numeric attribute to test if either worked), and I have created an alias called "TimeMonthValue". So against the numeric element 'Jan-11', the alias is '201101'.
In the Time_Month Dim the numerical element is '201101' and I have also created an alias called "MMM-YY". So against the numeric element '201101' the alias is 'Jan-11'.
I know that I only needed to create one alias but I doubles up to see if it helped.
My dimix test rule is:
IF(DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,!Project_Measures))=0,3,2);
This still places "3"'s in the cube so the dimix test is returning false. I have also tried:
DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue')))=0,3,2);
Still "3"'s so dimix test is still failing.
I must be missing something very simple!
Any further suggestions would be welcomed.
Many Thanks
Thanks to you both for your replies. Christopher is some more info to help you understand teh cube set up (please be aware that I did not set up this cube - a consultant did):
The Original Budget cube consists of:
Project Code dim
Budget Line Item Dim
Project_Measures Dim (Start Date/End Date/Start Month/End Month/Profile (picklist)/Phased cost by month (Jan-11/Feb-11 etc)
The idea is that the end user types Start Date and End Date into Active Form eg 01/03/2011 and 01/06/2011. The Start Month is rule derived and produces "201103" and "201106". Now I only want the end user to be able to enter budget data into the months within the start and end dates of the project.
So, in the Project_Measures dim I have deleted my attributes that I build earlier (I had one as a text attribute and one as a numeric attribute to test if either worked), and I have created an alias called "TimeMonthValue". So against the numeric element 'Jan-11', the alias is '201101'.
In the Time_Month Dim the numerical element is '201101' and I have also created an alias called "MMM-YY". So against the numeric element '201101' the alias is 'Jan-11'.
I know that I only needed to create one alias but I doubles up to see if it helped.
My dimix test rule is:
IF(DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,!Project_Measures))=0,3,2);
This still places "3"'s in the cube so the dimix test is returning false. I have also tried:
DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue')))=0,3,2);
Still "3"'s so dimix test is still failing.
I must be missing something very simple!
Any further suggestions would be welcomed.
Many Thanks
-
- Posts: 101
- Joined: Thu Oct 20, 2011 6:53 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2007
- Location: London, UK
Re: Can you use an ATTRS within a DIMIX rules function?
Declanr,
thanks for your post. Your suggestion of dropping the DB() worked. I thought that I was taking the "safer" approach using DB(). It was my understanding that whilst some rules require you to refer to internal elements within the DB() to work as opposed to the square brackets - this was not true of the opposite. I thought that the only downside of using DB() to refer to internal elements was that the rule would take longer to perform - but it was a safer option.
Anyway - I was clearly wrong as this DIMIX test using the ATTRS only worked when I removed the DB().
The full that I have used to stop the end user from entering budget data into any month outside of the project start and end date is below. It is not fully dynamic however! Because the Project_Measures dim has a mixture of set up elements and time elements I have used ELISCOMP to apply the rule to the months that have 2011 as a parent. I will have to repeat the rule for each year (not a big deal as trhere is only 10 years in the budget but still not tidy).
The parent of 2011/2012/2013 etc is "Phased_Cost". However, if I apply the ELISCOMP rule to Phased_Cost it does not apply to all n levels under the full years.
If anyone has a tip to get around this I would be very gratefull. If not, then I can live with 10 rules.
Declanr/Lotsaram/Christopher - many thanks for all of your help - it's much appreciated.
thanks for your post. Your suggestion of dropping the DB() worked. I thought that I was taking the "safer" approach using DB(). It was my understanding that whilst some rules require you to refer to internal elements within the DB() to work as opposed to the square brackets - this was not true of the opposite. I thought that the only downside of using DB() to refer to internal elements was that the rule would take longer to perform - but it was a safer option.
Anyway - I was clearly wrong as this DIMIX test using the ATTRS only worked when I removed the DB().
The full that I have used to stop the end user from entering budget data into any month outside of the project start and end date is below. It is not fully dynamic however! Because the Project_Measures dim has a mixture of set up elements and time elements I have used ELISCOMP to apply the rule to the months that have 2011 as a parent. I will have to repeat the rule for each year (not a big deal as trhere is only 10 years in the budget but still not tidy).
The parent of 2011/2012/2013 etc is "Phased_Cost". However, if I apply the ELISCOMP rule to Phased_Cost it does not apply to all n levels under the full years.
If anyone has a tip to get around this I would be very gratefull. If not, then I can live with 10 rules.
Declanr/Lotsaram/Christopher - many thanks for all of your help - it's much appreciated.
-
- Posts: 101
- Joined: Thu Oct 20, 2011 6:53 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2007
- Location: London, UK
Re: Can you use an ATTRS within a DIMIX rules function?
All,
sorry I forgot to add the ful rule that I used at the bottom of my post - here it is:
[]=N:
IF(
ELISCOMP('Project_Measures',!Project_Measures,'FY 2011')=1,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))<DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original Start Month')),
0,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))>DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original End Month')),
0,
CONTINUE)
),
CONTINUE
);
sorry I forgot to add the ful rule that I used at the bottom of my post - here it is:
[]=N:
IF(
ELISCOMP('Project_Measures',!Project_Measures,'FY 2011')=1,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))<DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original Start Month')),
0,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))>DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original End Month')),
0,
CONTINUE)
),
CONTINUE
);
-
- 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: Can you use an ATTRS within a DIMIX rules function?
With the ELISCOMP function not doing quite what you want, are you aware there is an ELISANC function which relates to ancestors rather than components so you could apply it even with 2 levels of separation.
Declan Rodger
-
- Posts: 101
- Joined: Thu Oct 20, 2011 6:53 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2007
- Location: London, UK
Re: Can you use an ATTRS within a DIMIX rules function?
Declanr,
Thank you VERY much - I'm now using ELISANC and it works great. Rule is:
[]=N:
IF(
ELISANC('Project_Measures','Phased_Cost',!Project_Measures)=1,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))<DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original Start Month')),
0,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))>DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original End Month')),
0,
CONTINUE)
),
CONTINUE
);
Thanks again!
Thank you VERY much - I'm now using ELISANC and it works great. Rule is:
[]=N:
IF(
ELISANC('Project_Measures','Phased_Cost',!Project_Measures)=1,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))<DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original Start Month')),
0,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))>DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original End Month')),
0,
CONTINUE)
),
CONTINUE
);
Thanks again!