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 »

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 12871 times
Contas-TM1RPTROW - Expand1.png
Contas-TM1RPTROW - Expand1.png (3.06 KiB) Viewed 12871 times
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: TM1RPTROW Repeat Problem

Post by gtonkin »

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 »

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.
tomok
MVP
Posts: 2831
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 »

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
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 »

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
tomok
MVP
Posts: 2831
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 »

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]})}
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
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 »

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
tomok
MVP
Posts: 2831
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 »

Exactly how are you using the MDX? In the TM1RPTROW formula? Perhaps you should post what's in that formula.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
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 »

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
tomok
MVP
Posts: 2831
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 »

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 O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
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 »

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 »

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 12745 times
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1RPTROW Repeat Problem

Post by Wim Gielis »

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

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
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 »

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
Community Contributor
Posts: 292
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 »

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 »

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 12668 times
declanr
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: TM1RPTROW Repeat Problem

Post by declanr »

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.
Declan Rodger
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 »

No Declanr,

There's no Subset named like this! But Thank You Anyway
AmbPin
Regular Participant
Posts: 173
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 »

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 »

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 12607 times
erro-tm1rptrowT2.png
erro-tm1rptrowT2.png (19.3 KiB) Viewed 12607 times
Post Reply