TM1RPTROW Repeat Problem
- 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
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?
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?
- Attachments
-
- Contas-TM1RPTROW.png (10.67 KiB) Viewed 13547 times
-
- Contas-TM1RPTROW - Expand1.png (3.06 KiB) Viewed 13547 times
- gtonkin
- MVP
- Posts: 1203
- 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
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.
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]})}
- 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
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.
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.
-
- MVP
- Posts: 2832
- 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
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.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.
- 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
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
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
-
- MVP
- Posts: 2832
- 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
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]})}
- 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
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
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
-
- MVP
- Posts: 2832
- 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
Exactly how are you using the MDX? In the TM1RPTROW formula? Perhaps you should post what's in that formula.
- 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
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
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
-
- MVP
- Posts: 2832
- 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
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.
- 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
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!
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!
- 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
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!
Thanks again for the help!
- Attachments
-
- Contas-TM1RPTROW - Expand2.png (2.82 KiB) Viewed 13421 times
-
- MVP
- Posts: 3123
- 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
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 ?
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
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
- 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
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
Even in the Excel the active form doesn't act abnormally. It's something exclusivelly happen at the TM1Web.
Thanks for Your Help
-
- 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
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.
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.
- 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
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
I tried again just to be sure. (See attached)
Unfortunately Doesn't Work.
Thanks Anyway
- Attachments
-
- erro-tm1rptrow.png (43.86 KiB) Viewed 13344 times
-
- 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
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
- 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
No Declanr,
There's no Subset named like this! But Thank You Anyway
There's no Subset named like this! But Thank You Anyway
-
- 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
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?
{
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?
- 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
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
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
- Attachments
-
- erro-tm1rptrowT2v2.png (4.88 KiB) Viewed 13283 times
-
- erro-tm1rptrowT2.png (19.3 KiB) Viewed 13283 times