TM1 Forum

Posted: **Sat Aug 17, 2019 6:57 pm**

Hi,

I am looking for a solution to create a websheet which dynamically increases the rows when entering data. For example if you have an active form with a numbered list (elements 1, 2, 3 etc) as the row dimension. The active form should only show one empty row when opening it. As soon as the user enters data in the first row and recalculates, a second empty row appears and so on. Can anyone provide some hints how this can be achieved with MDX?

Thanks!
Mario

Posted: **Sat Aug 17, 2019 10:53 pm**

You can create an extra measure with a formula that results in 1 or 0. If you feed this measure it can be used for your zero suppression in the active form.

Posted: **Sun Aug 18, 2019 6:14 am**

You don’t need a helper measure and rules/feeding. You can do it with MDX which is better IMO. Both methods will require a refresh to get the extra row to appear.

For MDX assuming you have a "data entry index" type dimension with placelolder measures named somethign like row1, row2, ...row99 then it is very easy to use MDX. All you need to do is use the HEAD function combined with a FILTER of the entry cube with the "data entry index" elements being empty. Usually I allow for 5 data entry rows at a time. not just one. Then if a user wants t do multiple data entry they don't need to refresh the page each time to get the next row. Just adjust the HEAD function to the number of rows you need and job done. (Note: for the HEAD fnction to work properly the element ordering and leveling in the dimension is important!)

E.g. something like

Code: Select all

{Head(
   {Filter(
      {[Data Entry Row].[All Rows].Children},
      [Entry Cube].([Version].[Forecast],[Data Entry Row].CurrentMember,[Entry Cube Measure].[Entry]) = ""
   )},
   5
)}

Posted: **Mon Aug 19, 2019 8:27 pm**

Hi Wim and lotsaram,

Thanks a lot you for your answers.

@Wim: I knew I could solve it with an extra measure and rules. However I was sure it is somehow possible with MDX but I could not figure out how.

@lotsaram: Thank you for the code example, it works great! I had the wrong approach - I tried to solve it with Tail, Lead, NextMember functions. I could identify the last element with data using filter and the tail function. Then I tried to use Lead or NextMember and that is where I got stuck.

Thanks guys!
Mario

Posted: **Tue Aug 20, 2019 8:13 am**

Thank you Lotsaram, I forget about this one. In the past I applied this too.
I added this information to my MDX page

: 01.png (111.4 KiB) Viewed 6117 times

TM1 Forum

Active form with dynamic entry rows

Active form with dynamic entry rows

Re: Active form with dynamic entry rows

Re: Active form with dynamic entry rows

Re: Active form with dynamic entry rows

Re: Active form with dynamic entry rows