Hi,
I am having an issue with supress zeros in a dynamic report.
When the first column element is typed, supress zero’s works correctly.
When the first column element is selected via a SUBNM("PeriodMonthly";"Base";"202101") dropdown,
the other column elements are no longer taken into account.
How does the suppress zero functionality recognize column values?
Any Ideas?
Suppress zero PAX
-
- Posts: 18
- Joined: Thu Sep 03, 2020 1:59 pm
- OLAP Product: TM1
- Version: 2.0
- Excel Version: office 365
Suppress zero PAX
- Attachments
-
- Select period.png (3.71 KiB) Viewed 4736 times
-
- Type period.png (4.82 KiB) Viewed 4736 times
-
- Regular Participant
- Posts: 197
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: Suppress zero PAX
Columns don't get suppressed in an active form only rows. Is the subnm correct, I would have expected the commas , instead of semicolons ; to separate the conditions
I don't usually have a subnm directly on a column but I frequently have columns derived from a single subnm, so you can select one period and it displays say the next 12 periods.
After making the selection in the subnm are you simply refreshing or rebuilding the book
Just a thought tin the tm1rptview what are your title elements. And in the dbrw are you referencing the cell with tm1rptview or have you just named the cube name. If you just referenced the cube name that'll be a problem as the zero suppression.
It's a bit difficult to give too many pointers though without seeing the spreadsheet and what the formulae and named rangess are
I don't usually have a subnm directly on a column but I frequently have columns derived from a single subnm, so you can select one period and it displays say the next 12 periods.
After making the selection in the subnm are you simply refreshing or rebuilding the book
Just a thought tin the tm1rptview what are your title elements. And in the dbrw are you referencing the cell with tm1rptview or have you just named the cube name. If you just referenced the cube name that'll be a problem as the zero suppression.
It's a bit difficult to give too many pointers though without seeing the spreadsheet and what the formulae and named rangess are
-
- Posts: 18
- Joined: Thu Sep 03, 2020 1:59 pm
- OLAP Product: TM1
- Version: 2.0
- Excel Version: office 365
Re: Suppress zero PAX
Hi,
Thanks for the response.
Usually, I derive 12 periods from a single subnm selection cell.
The problem that I am having is that the column (period) information is not used as input, unless I explicitly type a period in one of the cells.
Some additional information:
- My ranges go far enough.
- The period dimension is not specified in the tm1rptview.
- The DBRW formulas are referencing the cell with tm1rptview
- I rebuild the book.
- The subnm works fine.
Thanks for the response.
Usually, I derive 12 periods from a single subnm selection cell.
The problem that I am having is that the column (period) information is not used as input, unless I explicitly type a period in one of the cells.
Some additional information:
- My ranges go far enough.
- The period dimension is not specified in the tm1rptview.
- The DBRW formulas are referencing the cell with tm1rptview
- I rebuild the book.
- The subnm works fine.
- Steve Rowe
- Site Admin
- Posts: 2410
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Suppress zero PAX
Hi,
How are you getting to the outer months from your first month?
I suspect there is an issue with the calculation order where your column headers are not fully resolved at the time the dynamic report is initially populated. Pafe uses Excels dirty cell flagging to determine what needs to be calculated versus perspectives where everything is just calculated. This can mean that approaches that worked in Perspectives don't in Pafe, this can usually be resolved with different approaches.
Try using Excel Validations to drive the pick list rather than the subnm. The Excel validtion list could be populated using Subnms against your Base subset.
HTH
How are you getting to the outer months from your first month?
I suspect there is an issue with the calculation order where your column headers are not fully resolved at the time the dynamic report is initially populated. Pafe uses Excels dirty cell flagging to determine what needs to be calculated versus perspectives where everything is just calculated. This can mean that approaches that worked in Perspectives don't in Pafe, this can usually be resolved with different approaches.
Try using Excel Validations to drive the pick list rather than the subnm. The Excel validtion list could be populated using Subnms against your Base subset.
HTH
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 18
- Joined: Thu Sep 03, 2020 1:59 pm
- OLAP Product: TM1
- Version: 2.0
- Excel Version: office 365
Re: Suppress zero PAX
Hi,
Your solution worked.
It must indeed have been an issue with the calculation order at the time the dynamic report is initially populated.
This list was then referencet by my first column header.
The other periods are just cell_on_left + 1.
Thanks a lot, for the support
Your solution worked.
It must indeed have been an issue with the calculation order at the time the dynamic report is initially populated.
I added an excel data validaiton that references a list created using the TM1ELLIST function.Steve Rowe wrote: ↑Tue Jun 08, 2021 10:47 am Try using Excel Validations to drive the pick list rather than the subnm. The Excel validtion list could be populated using Subnms against your Base subset.
This list was then referencet by my first column header.
The other periods are just cell_on_left + 1.
Thanks a lot, for the support