TM1 Forum

I am new to TM1 and have a general question regarding the possibility to use dynamic parts of a dimension in a form.

For example we have a very large project dimension. In the projects dimension we have a consolidated level called Business Unit, so one Business Unit can have one or more projects as children. A project can be child to one and only one Business Unit. We also have several attributes on this dimension, for example one actuality year, so for example a projects is active for one year and one year only.

The problem I am trying to solve is that I want only the relevant projects to show in the form. For example if a user has chosen one Business Unit I only want the projects that are children to that Business Unit to show and if one year, for example 2010 has been chosen I only want the projects which have active_year=2010 as attribute to show.

I have solved the first part by using the ELCOMPN and DNEXT functions to first determine how many children the chosen BU has and then the Dnext function to display the next project after the first and so on, but I have no idea how to only include the projects that have 2010 in the active_year attibute so any help is veru much appreciated.

Just to summarize, what I'm locking for is a function or combination of function that are similar to dnext but instead of just returning the next element I want it to return the next element that meets a condition for an attribute value.

craig_karr wrote:Just to summarize, what I'm locking for is a function or combination of function that are similar to dnext but instead of just returning the next element I want it to return the next element that meets a condition for an attribute value.

In your version you can use Active forms. In an Active form you can define the row (and only the row) subset via MDX.
You can find several topics about the useage of Active forms and MDX in this forum and in TM1 guides.

HTH

Peter

You can also build a shorter dimension from your large project dimension with an paramaterized Action button.

Previously I had this large dimension as rows, but the users wanted it as columns instead so I can't use MDX active forms, I have to build it in a "normal" form. How does this concept with building a smaller dimension with the paramaterized Action button work?

craig_karr wrote:Previously I had this large dimension as rows, but the users wanted it as columns instead so I can't use MDX active forms, I have to build it in a "normal" form. How does this concept with building a smaller dimension with the paramaterized Action button work?

I do not know how this will work for you, it is only a idea.
I have not tried it out, but the concept is the following:

1.Create input fields for parameters in your form or on separate sheet
2.Create an action button and a process which creates the customized short dimension based on the parameters
If you have more than one user than you can combine the user name into the short dimension name like: project_jamesbond
3.Use this short dimension with DIMNM or DNEXT to specify the columns.

Maybe you get better input from elsewhere.

Peter

craig_karr wrote:Previously I had this large dimension as rows, but the users wanted it as columns instead so I can't use MDX active forms, I have to build it in a "normal" form. How does this concept with building a smaller dimension with the paramaterized Action button work?

Instead of building a bunch of separate dimensions why not create subsets, or better yet, one single subset that can filter,via MDX, based on a lookup cube that has the }Clients dimension in it. For example, if you created a lookup cub with the dimensions }Clients and Projects (or whatever) you could put a value of 1 in the intersection to indicate which projects are valid for each user. Then you could write an MDX based subset that returns the list of projets where the project/client intersections have a value of 1 (or greater than 0 to include parents as well). Put the SUBNM function (calling your MDX subset) in your columns and up the index number by one each time you go to the right. Each user will then have a dynamic report with only their valid projects along the columns.