TM1 Forum

Hi Guys,

This is my first Topic here, so be patient...Lol

I built an Active Form Perspectives using MDX to control the lines that appear, showing a non-leaf group of elements, as the print below.



It's Showing correctly on Excel, but in TM1Web or Contributor, When someone expands(Drill Down) one line, the leaf level repeats all the elements below that consolidation twice or more.



The MDX that I'm using is :

{ DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SubsetBasis()},"TOTAL CONTAS ORÇAMENTÁRIAS")},{[GRL.D.Contas_Contabeis].[TOTAL CONTAS ORÇAMENTÁRIAS]})}

Sometimes It Repeats, sometimes it doesn’t, it's acting Weird.
Does anyone have any idea why?

As a first step, try replacing {TM1SubsetBasis()} with the relevant element that you want to start with e.g. {[GRL.D.Contas_Contabeis].Members} - TM1SubsetBasis() in my opinion will create problems.
If all you want to do is select the root element and expand it once, you could try: You are effectively then starting with TOTAL CONTAS ORÇAMENTÁRIAS as you specify this explicitly then drill it down. You would not need to try and filter by pattern. Hope this helps.

Gtonkin,

I'm really gratefull for Your Help. As It is an intermittent Problem, I'm still testing, but I think the issue is gone!

Thank You so much for Your Help.

rafaewolie wrote: ↑Mon Sep 04, 2017 8:46 pm Gtonkin,

I'm really gratefull for Your Help. As It is an intermittent Problem, I'm still testing, but I think the issue is gone!

Thank You so much for Your Help.

It's intermittent because you are referencing TM1SubsetBasis() in your MDX code. You need to replace that with TM1SubsetAll() in order for the MDX statement to work properly all the time.

Hi Tomok,

Thanks for Your Help, but I used the MDX sintax that Gtonkin gave me.

It works the imediate time I changed it, but now it's repeating again!

Any other tip?

Tks

I don't know why you are using DRILLDOWNMEMBER. If what you want is TOTAL CONTAS ORÇAMENTÁRIAS and all it's first level children I would use DRILLDOWNLEVEL:

Thanks Tom,

I tried Your Sintax, It works but still repeats some lines.

Another information: I reach this active form from an action button in another sheet.

The first time I Try to Drilldown the second level of "TOTAL CONTAS ORÇAMENTÁRIAS" some lines (children elements) are shown twice (or more).

If i collapse the consolidated element, and then expand it again, the hierarchy structure becomes correct without repeated lines.

Step 1) (opening Active Form, coming from an action button placed in another sheet of the same workbook)

>Consolidated Element


Step 2) (drilling down the consolidated element for the first time)

v Consolidated Element
- Children1
- Children2
- Children1
- Children2

Step 3) (collapsing the consolidated element)

>Consolidated Element

Step 4) (expanding the consolidation once again, now without the issue reported in step 2)

v Consolidated Element
- Children1
- Children2


Does it make any sense?

Thanks Again

Exactly how are you using the MDX? In the TM1RPTROW formula? Perhaps you should post what's in that formula.

Tom,

This is What my Action Form does:

=TM1RPTROW($A$29;"TM1Server:GRL.D.Contas_Contabeis";"";"";"Desc";;A28)

A28 = {DRILLDOWNLEVEL( {[GRL.D.Contas_Contabeis].[TOTAL CONTAS ORÇAMENTÁRIAS]})}

A29 =TM1RPTVIEW("TM1Server:OPX.150.OPEX_FINAL:teste"; 0; TM1RPTTITLE("TM1Server:GRL.D.Ano";$A$26); TM1RPTTITLE("TM1Server:GRL.D.Versoes";$A$25); TM1RPTTITLE("TM1Server:GRL.D.Centros_Custos";$D$23); TM1RPTTITLE("TM1Server:OPX.M.OPEX_FINAL";$A$24);TM1RPTFMTRNG;TM1RPTFMTIDCOL)

tks

Your syntax looks OK. What I think it might be is "bad" rows that were in the form when you published it. What I recommend is removing all the rows below the TM1RPTROW formula and then republishing it. This is pretty much standard practice for many of us you have been working with TM1 for a long time. If you set the report to auto-rebuild on open it will rebuild to whatever your MDX wants and the report will open faster and take up less space as far as file size goes.

Tom,

I already know about that best practice, but I didn't removed the rows the last time I edited the prespectives.
Now I did It and It seems that the issue is gone!

If the issue appears again, I will let You Know!

Thanks for All Your help!

Even When I Clean The lines below de TM1RPTROW the Children elements of the expanded row is repeated as attached



Thanks again for the help!

What happens if you recreate the active form in the cube viewer, with the same MDX and the same selections ? Does it work ?
Is the behavior the same as in TM1Web ?

Hi Win,

Even in the Excel the active form doesn't act abnormally. It's something exclusivelly happen at the TM1Web.

Thanks for Your Help

From what you have provided above it is clear you have played around with the active form a fair bit before publishing on the web (which is perfectly valid of course)

Have you tried to create a brand new active form without messing around with it too much, i.e. the exact steps below

1) Create the appropriate view in the cube viewer
2) From this create an active form by pressing the Active Form Icon
3) Add the {DRILLDOWNLEVEL( {[GRL.D.Contas_Contabeis].[TOTAL CONTAS ORÇAMENTÁRIAS]})} MDX into a blank cell on the active form
4) Edit the MDX parameter of the TM1RPTROW formula to reference the cell used in step 3

Then Test and see if the problem still occurs.

At least by doing this you can rule out anything you may have done in making changes to the active form.

Hi Mark, Thanks for the help, but I did that over and over again.

I tried again just to be sure. (See attached)

Unfortunately Doesn't Work.

Thanks Anyway

Just a quick though, do you have a subset called "TOTAL CONTAS ORÇAMENTÁRIAS" - unlikely as I think the MDX syntax you have would error but you reference a subset name in the same way as an element name in MDX so could possibly cause confusion.

No Declanr,

There's no Subset named like this! But Thank You Anyway

So difficult to know what to suggest without seeing your system. I doubt this will help but you could try:-
{
HIERARCHIZE(
{DRILLDOWNLEVEL( {[GRL.D.Contas_Contabeis].[TOTAL CONTAS ORÇAMENTÁRIAS]})}
)
}

If you replace the MDX with
{[GRL.D.Contas_Contabeis].[41006 PLR]}
what happens when you expand the element in the active form. Could you show us what happens when you use this MDX in the dimension browser and then expand the element there too?

Hi AmbPin,

I Tried the 2 sintaxes, and get this response:




The first Attachment is the PLR test that You asked me to do.
The second one is using the Hierarchize sintax

But as You can see, the issue persists.

It's curious that sometimes, When I expand the hierarchy the first second it's ok, but the next, appears the same line again, repeating. It seems like some intermittent issue!

Thanks Anyway