Migrate ActiveForm from Perspectives to PAfE / PAX

Post Reply
Mark2007
Posts: 35
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: PA 2
Excel Version: office 365

Migrate ActiveForm from Perspectives to PAfE / PAX

Post by Mark2007 » Mon Feb 22, 2021 2:16 pm

Hi there,

I tried an existing Report with ActiveForm (created with Perspectives and working great with Perspectives) in PAX, but in PAX / PAfE, it doesn't work.

What I tried right now:
The ranges in PAfE are differently named than in Perspectives: TM1RPTFMTIDCOL, PAX: TM1RPTFMTIDCOL1 - I did it
There are some new ranges in ActiveForms in PAfE: - TM1RPTQRYRNG1 and TM1RPTVIEWRNG1 - I defined them
There's a new Sheet (very hidden) in PAfE: Cognos_Office_Connection_Cache - but I think this is created automatically.

Any idea what else has to be done to make an activeForm work in PAfE?

PA-Server: PA 2.07
Excel Office 365 ProPlus, Version 2002 (Build 12527.21504)
PAfE: COR_APP_version=COR_APP-AW32-ML-RTM-11.0.58.7-0

Thanks!

Regards

Mark

User avatar
Steve Rowe
Site Admin
Posts: 2154
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Migrate ActiveForm from Perspectives to PAfE / PAX

Post by Steve Rowe » Mon Feb 22, 2021 5:48 pm

Hi Mark, In what manner doesn't it work?

Do you have back and forth to TM1 whilst determining references (i.e. things link Dimnm, DImix, DBS, etc etc)?
Cheers,
Technical Director
www.infocat.co.uk

User avatar
gtonkin
MVP
Posts: 884
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Migrate ActiveForm from Perspectives to PAfE / PAX

Post by gtonkin » Tue Feb 23, 2021 5:20 am

HI Mark,

There were one or two minor changes but have been able to get my old Active forms working in PAX with a few reviews and updates.

Where I have a TM1RPTROW formula, I try and reference a subset or if MDX, put the MDX in a cell to make it easy to view and update.
If you have used the set editor to create a set, PAfE will create the MDX and allocate it to a named range.
The named range is hidden and you would need to unhide it if you want to have sight of it.

In terms of the other identifiers e.g. TM1RPTFMTIDCOL1, the 1 is just the name of the Dynamic Report.
I usually edit the TM1RPTVIEW formula and update the name to something friendly i.e. =@TM1RPTVIEW("SDate:Sales Planning:Input"...) if this were an input sheet.
If you do change the name, edit the named ranges and update them accordingly too.
When you look at the Workbook tab on the PAfE Task Pane, at least the Dynamic reports will be meaningful too.

TM1RPTQRYRNG should be for your filter i.e. sorting, Top N, Bottom N etc. Previously I think you could just put the filter formula in to any random cell.

The Cognos_Office_Connection_Cache is used for the list of items tied to the data validations i.e. when you have a SUBNM or picklist cell, the members are returned to this sheet and linked to a named range called cafe_validation_temp

With the above in mind, I would first check that all the named ranges are valid for the Dynamic Report then check the TM1RPTROW to confirm that if you edit the set, it opens the editor etc.

Other issues may be TM1RPTTITLE references in the TM1RPTVIEWRNG that are invalid or are not necessary as they do not point to members that are in the "context area".

HTH

Mark2007
Posts: 35
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: PA 2
Excel Version: office 365

Re: Migrate ActiveForm from Perspectives to PAfE / PAX

Post by Mark2007 » Tue Feb 23, 2021 8:55 am

Steve Rowe wrote:
Mon Feb 22, 2021 5:48 pm
Hi Mark, In what manner doesn't it work?

Do you have back and forth to TM1 whilst determining references (i.e. things link Dimnm, DImix, DBS, etc etc)?
Cheers,
Hi Steve,

"it does not work" means: no lines in the ActiveForm are generated, and the first line keeps empty.
my report consists of two parts. In the upper, there is a summary with fixed lines and dbrw-formula. below, there is the activeform which dbrw-formulars are referencing to the same Subnm-Formulas as the fixed part.

If I open the report in Perspectives and actualize via <Alt> and <F9>, the lines are generated, so I think there's no general problem in the formulas.

Regards

Mark

Mark2007
Posts: 35
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: PA 2
Excel Version: office 365

Re: Migrate ActiveForm from Perspectives to PAfE / PAX

Post by Mark2007 » Tue Feb 23, 2021 9:44 am

Thanks for your help!

the good news was that there is no big issue I should focus. So I went through all TM1RPTRow-Formulas on the sheet (I have 5 of them).
four of the TM1RPTROW-Formulas included everything: A link to a subset (the cell is empty), a link to an element (the cell is empty) and a link to a mdx-statement (the cell is filled)

I thought that shouldn't be a problem - but it was.
I removed the link to the element, and now it works.

Might be a bug in PAfE, but now it woks.

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Migrate ActiveForm from Perspectives to PAfE / PAX

Post by Wim Gielis » Tue Feb 23, 2021 12:18 pm

Mark2007 wrote:
Tue Feb 23, 2021 9:44 am
four of the TM1RPTROW-Formulas included everything: A link to a subset (the cell is empty), a link to an element (the cell is empty) and a link to a mdx-statement (the cell is filled)
Actually, I have never ever done that. All my TM1RPTROW formulas always use only 1 option.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

Post Reply