Use Active Form but found duplicate Hierachy
-
- Posts: 1
- Joined: Wed May 25, 2011 8:30 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2010
Use Active Form but found duplicate Hierachy
Hi all,
We use TM1 9.5.2 with Excel 2010 and 2007. We prepare a report used Active Form and put the file in TM1 application . However, we found out that when we opened it from the perspective, it showed duplicated hieracy such as
Total current Asset 500
Cash on hand 100
AR 30
Input Vat 20
Cash on hand 100
AR 30
Input Vat 20
Data itself is correct. We deleted the duplicate hierachy in excel and saved. But when we opened it again, it happened again
Do you have any idea, how we can fix it
We use TM1 9.5.2 with Excel 2010 and 2007. We prepare a report used Active Form and put the file in TM1 application . However, we found out that when we opened it from the perspective, it showed duplicated hieracy such as
Total current Asset 500
Cash on hand 100
AR 30
Input Vat 20
Cash on hand 100
AR 30
Input Vat 20
Data itself is correct. We deleted the duplicate hierachy in excel and saved. But when we opened it again, it happened again
Do you have any idea, how we can fix it
-
- Posts: 32
- Joined: Wed May 28, 2008 11:41 am
- OLAP Product: TM1
- Version: 6 and 2.5 to 10.2.2
- Excel Version: 2007 2010
- Location: Northern England
- Contact:
Re: Use Active Form but found duplicate Hierachy
Hi there,
When you save you saved, did you first use the Active form option from the ribbon of "Rebuild Current Book", this will rebuild and redefine the ranges and formula for the Active form.
Secondly, there is a hidden sheet called "AR01" which holds the row information, if the hierarchy had become duplicated on this, then it would cause confusion on the active form itself.
Good luck
Neil
When you save you saved, did you first use the Active form option from the ribbon of "Rebuild Current Book", this will rebuild and redefine the ranges and formula for the Active form.
Secondly, there is a hidden sheet called "AR01" which holds the row information, if the hierarchy had become duplicated on this, then it would cause confusion on the active form itself.
Good luck
Neil
-
- Posts: 30
- Joined: Mon Jun 15, 2009 6:13 am
- OLAP Product: Cognos Express and TM1
- Version: 9.4 to 10.2
- Excel Version: Excel 02 to 10
- Location: Sydney
Re: Use Active Form but found duplicate Hierachy
I find when using active forms its best to drive column and row dimensions via subsets as this removes the hidden '{AR01}' related issues.Neil Watson wrote:Hi there,
When you save you saved, did you first use the Active form option from the ribbon of "Rebuild Current Book", this will rebuild and redefine the ranges and formula for the Active form.
Secondly, there is a hidden sheet called "AR01" which holds the row information, if the hierarchy had become duplicated on this, then it would cause confusion on the active form itself.
Good luck
Neil
It gives more flexibility as well
-
- Posts: 1
- Joined: Mon Mar 18, 2013 3:45 pm
- OLAP Product: TM1
- Version: 9 10
- Excel Version: 2003 2007
Re: Use Active Form but found duplicate Hierachy
Hello,
I have encountered the same issue. The report rows do use subsets, and I have recreated a new active form which is identical to the original active form which is duplicating. The new active form works perfectly fine and does not duplicate. Has anyone found a definitive cause for this? Using 9.5.2 and Excel 2007.
Thanks in advance!
I have encountered the same issue. The report rows do use subsets, and I have recreated a new active form which is identical to the original active form which is duplicating. The new active form works perfectly fine and does not duplicate. Has anyone found a definitive cause for this? Using 9.5.2 and Excel 2007.
Thanks in advance!
-
- Posts: 62
- Joined: Mon Aug 15, 2016 8:48 am
- OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
- Version: 10.2.0 FP3
- Excel Version: Excel 2013
- Location: Hamburg, DE, EU
- Contact:
Re: Use Active Form but found duplicate Hierachy
I encounter also to this problem, and also to an TM1RPTROW-Formula, which doesn't resolve the subset in Perspectives, but later in TM1Web it does.
My version indeed is 10.2.0 FP3 and my Excel is 2013 (for development clientside) resp. 2010 (on the server). Here the appearence:
I deleted all left {AR}xy sheets hidden from development, as actually all TM1RPTROW-formulas are driven by a named subset (Sprache means language and refers an Alias, which works as all labels are correctly in german): (this one appears empty above, "-")
(this one appears with it's duplicated hierarchy below; which is not found similarly in the subset itself)
Also I tried "rebuild the workbook" and "rebuild the sheet" in the TM1/Active Report - menu.
I found only the older postings above about this error, but nothing newer. Is there a known best practice I could have missed?
My version indeed is 10.2.0 FP3 and my Excel is 2013 (for development clientside) resp. 2010 (on the server). Here the appearence:
I deleted all left {AR}xy sheets hidden from development, as actually all TM1RPTROW-formulas are driven by a named subset (Sprache means language and refers an Alias, which works as all labels are correctly in german):
Code: Select all
=TM1RPTROW(ViewBlock4;"Servername:DIM_KPI";"Block4";"";Sprache;0)
Code: Select all
=TM1RPTROW(ViewBlock5;"Servername:DIM_KPI";"Block5";"";Sprache;0)
Also I tried "rebuild the workbook" and "rebuild the sheet" in the TM1/Active Report - menu.
I found only the older postings above about this error, but nothing newer. Is there a known best practice I could have missed?
-----------------------------------
Best regards,
Stefan
Best regards,
Stefan
-
- 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: Use Active Form but found duplicate Hierachy
Have you deleted all rows under the top row (which contains the TM1rptrow formula before saving the sheet?st2000 wrote: ↑Thu Sep 07, 2017 6:31 pm I encounter also to this problem, and also to an TM1RPTROW-Formula, which doesn't resolve the subset in Perspectives, but later in TM1Web it does.
My version indeed is 10.2.0 FP3 and my Excel is 2013 (for development clientside) resp. 2010 (on the server). Here the appearence:
I deleted all left {AR}xy sheets hidden from development, as actually all TM1RPTROW-formulas are driven by a named subset (Sprache means language and refers an Alias, which works as all labels are correctly in german):(this one appears empty above, "-")Code: Select all
=TM1RPTROW(ViewBlock4;"Servername:DIM_KPI";"Block4";"";Sprache;0)
(this one appears with it's duplicated hierarchy below; which is not found similarly in the subset itself)Code: Select all
=TM1RPTROW(ViewBlock5;"Servername:DIM_KPI";"Block5";"";Sprache;0)
Also I tried "rebuild the workbook" and "rebuild the sheet" in the TM1/Active Report - menu.
I found only the older postings above about this error, but nothing newer. Is there a known best practice I could have missed?
This is considered best practice as; in addition to improving performance upon opening - if not done you can end up with leftover rows that are technically under the active form and no longer attached to it.
Declan Rodger
-
- Posts: 62
- Joined: Mon Aug 15, 2016 8:48 am
- OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
- Version: 10.2.0 FP3
- Excel Version: Excel 2013
- Location: Hamburg, DE, EU
- Contact:
Re: Use Active Form but found duplicate Hierachy
Declan, thank you very much.
Simple and helpful, it is back in the desired condition regarding the duplicates. The missing headline "-" stays, but is filled in TM1web, which is the important thing.
Simple and helpful, it is back in the desired condition regarding the duplicates. The missing headline "-" stays, but is filled in TM1web, which is the important thing.
-----------------------------------
Best regards,
Stefan
Best regards,
Stefan
- 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: Use Active Form but found duplicate Hierachy
I've tried everything that You suggested here, by the way I wrote it on another post last week, and nothing helped.
I changed the MDX, I'm not using that AR stuff, or Subset (Static or Dinamic), I cleaned all the rows below TM1RPTROW, and nothing works.
In Excel It seems working pretty good, but When published to TM1Web, the rows drilled from another Row duplicate always!
Somebody can Help Me?
I changed the MDX, I'm not using that AR stuff, or Subset (Static or Dinamic), I cleaned all the rows below TM1RPTROW, and nothing works.
In Excel It seems working pretty good, but When published to TM1Web, the rows drilled from another Row duplicate always!
Somebody can Help Me?
-
- Regular Participant
- Posts: 173
- Joined: Sat Mar 20, 2010 3:03 pm
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2007-10
Re: Use Active Form but found duplicate Hierachy
I have seen this happen quite a few times and it is almost alway cured by deleting all of the workbook rows below the TM1Report row.
Can you rebuild your report from scratch to see if the problem happens in a second attempt. I do wonder sometimes if some corruption gets included into the Excel file and it is sometimes better to start again.
Can you rebuild your report from scratch to see if the problem happens in a second attempt. I do wonder sometimes if some corruption gets included into the Excel file and it is sometimes better to start again.
- 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: Use Active Form but found duplicate Hierachy
AmbPin,
Thanks for Your help, but in my case, I did that, but Doesn't Work.
See the other post: http://www.tm1forum.com/viewtopic.php?f=3&t=13563
Thanks.
Thanks for Your help, but in my case, I did that, but Doesn't Work.
See the other post: http://www.tm1forum.com/viewtopic.php?f=3&t=13563
Thanks.