TM1RPTROW Repeat Problem

Post Reply
User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

TM1RPTROW Repeat Problem

Post by rafaewolie » Mon Sep 04, 2017 5:14 pm

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.

Image

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.

Image

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?
Attachments
Contas-TM1RPTROW.png
Contas-TM1RPTROW.png (10.67 KiB) Viewed 362 times
Contas-TM1RPTROW - Expand1.png
Contas-TM1RPTROW - Expand1.png (3.06 KiB) Viewed 362 times

User avatar
gtonkin
MVP
Posts: 496
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: TM1RPTROW Repeat Problem

Post by gtonkin » Mon Sep 04, 2017 6:19 pm

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:

Code: Select all

{ DRILLDOWNMEMBER( {[GRL.D.Contas_Contabeis].[TOTAL CONTAS ORÇAMENTÁRIAS]},{[GRL.D.Contas_Contabeis].[TOTAL CONTAS ORÇAMENTÁRIAS]})}
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.

User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: TM1RPTROW Repeat Problem

Post by rafaewolie » 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.

User avatar
tomok
MVP
Posts: 2316
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1RPTROW Repeat Problem

Post by tomok » Tue Sep 05, 2017 12:30 pm

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.

User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: TM1RPTROW Repeat Problem

Post by rafaewolie » Tue Sep 05, 2017 1:27 pm

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

User avatar
tomok
MVP
Posts: 2316
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1RPTROW Repeat Problem

Post by tomok » Tue Sep 05, 2017 1:56 pm

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:

Code: Select all

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

User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: TM1RPTROW Repeat Problem

Post by rafaewolie » Tue Sep 05, 2017 2:46 pm

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

User avatar
tomok
MVP
Posts: 2316
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1RPTROW Repeat Problem

Post by tomok » Tue Sep 05, 2017 2:57 pm

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

User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: TM1RPTROW Repeat Problem

Post by rafaewolie » Tue Sep 05, 2017 3:11 pm

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

User avatar
tomok
MVP
Posts: 2316
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1RPTROW Repeat Problem

Post by tomok » Tue Sep 05, 2017 4:36 pm

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.

User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: TM1RPTROW Repeat Problem

Post by rafaewolie » Tue Sep 05, 2017 5:58 pm

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!

User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: TM1RPTROW Repeat Problem

Post by rafaewolie » Tue Sep 05, 2017 9:49 pm

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

Image

Thanks again for the help!
Attachments
Contas-TM1RPTROW - Expand2.png
Contas-TM1RPTROW - Expand2.png (2.82 KiB) Viewed 236 times

Wim Gielis
MVP
Posts: 1514
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1RPTROW Repeat Problem

Post by Wim Gielis » Tue Sep 05, 2017 11:41 pm

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 ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: TM1RPTROW Repeat Problem

Post by rafaewolie » Wed Sep 06, 2017 1:51 pm

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

Mark RMBC
Posts: 74
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1RPTROW Repeat Problem

Post by Mark RMBC » Mon Sep 11, 2017 3:04 pm

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.

User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: TM1RPTROW Repeat Problem

Post by rafaewolie » Mon Sep 11, 2017 5:01 pm

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

Image
Attachments
erro-tm1rptrow.png
erro-tm1rptrow.png (43.86 KiB) Viewed 159 times

declanr
MVP
Posts: 1537
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: TM1RPTROW Repeat Problem

Post by declanr » Mon Sep 11, 2017 6:05 pm

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.

User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: TM1RPTROW Repeat Problem

Post by rafaewolie » Mon Sep 11, 2017 9:04 pm

No Declanr,

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

AmbPin
Regular Participant
Posts: 164
Joined: Sat Mar 20, 2010 3:03 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007-10

Re: TM1RPTROW Repeat Problem

Post by AmbPin » Tue Sep 12, 2017 7:47 am

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?

User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: TM1RPTROW Repeat Problem

Post by rafaewolie » Tue Sep 12, 2017 1:54 pm

Hi AmbPin,

I Tried the 2 sintaxes, and get this response:

Image
Image

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
Attachments
erro-tm1rptrowT2v2.png
erro-tm1rptrowT2v2.png (4.88 KiB) Viewed 98 times
erro-tm1rptrowT2.png
erro-tm1rptrowT2.png (19.3 KiB) Viewed 98 times

Post Reply