Use Active Form but found duplicate Hierachy

Post Reply
tsureerat
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

Post by tsureerat »

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
Neil Watson
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

Post by Neil Watson »

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
vaneagle
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

Post by vaneagle »

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
I find when using active forms its best to drive column and row dimensions via subsets as this removes the hidden '{AR01}' related issues.

It gives more flexibility as well ;)
Kris W
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

Post by Kris W »

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!
st2000
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

Post by st2000 »

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:
ActReportNoneOrDuplicate.png
ActReportNoneOrDuplicate.png (10.19 KiB) Viewed 7792 times

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)
(this one appears empty above, "-")

Code: Select all

=TM1RPTROW(ViewBlock5;"Servername:DIM_KPI";"Block5";"";Sprache;0)
(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?
-----------------------------------
Best regards,
Stefan
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: Use Active Form but found duplicate Hierachy

Post by declanr »

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:
ActReportNoneOrDuplicate.png

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)
(this one appears empty above, "-")

Code: Select all

=TM1RPTROW(ViewBlock5;"Servername:DIM_KPI";"Block5";"";Sprache;0)
(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?
Have you deleted all rows under the top row (which contains the TM1rptrow formula before saving the sheet?
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
st2000
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

Post by st2000 »

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.
-----------------------------------
Best regards,
Stefan
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: Use Active Form but found duplicate Hierachy

Post by rafaewolie »

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?
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: Use Active Form but found duplicate Hierachy

Post by AmbPin »

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.
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: Use Active Form but found duplicate Hierachy

Post by rafaewolie »

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.
Post Reply