TM1 Forum

Hello,

I was wondering. Can we already have a report in PAfE, just a slice with DBRW formulas.
There, 1 argument in a DBRW uses an element in an alternative (PA) hierarchy.
Does the software already support this ?
I would image that having more than 1 hierarchy from the same dimension would be difficult in the DBRW, but 1 such selection should be possible, no ?

Hi,

I am struggling to see the difference between "1 argument in a DBRW uses an element in an alternative (PA) hierarchy" and "having more than 1 hierarchy from the same dimension"

I would say no this isn't possible but don't take my word for it as I have not tested and don't understand what you are trying to do!

Maren

Hi

For a 5D cube, the DBRW has 6 arguments.
Even if we source from an alternative PA hierarchy it’s still 6.
However, if we involve more than 1 alternative hierarchy from the same dimension then the number of arguments increases.

I had hoped that as long as we don’t use more arguments than expected in the DBRW for that cube, PAfE would be able to do it.

Hi Wim,

I assume that when people say PAX does not support hierarchy aware formulas, they are talking about retrieving a value from only a single hierarchy at a time.

I can't even imagine what involving more than 1 alternative hierarchy from the same dimension in one formula even means.

What is the use case for involving more than 1 alternative hierarchy from the same dimension in the same formula?

Maren

Did you ever drag a hierarchy into a view in Workspace for instance ? That’s the use case and being able to update and retrieve cube values in that manner.

MarenC wrote: ↑Tue Feb 01, 2022 4:33 pm What is the use case for involving more than 1 alternative hierarchy from the same dimension in the same formula?

Consider a sku dimension where n-level elements are car models.
You want all automatic models from hierarchy 1, all SUV models from hierarchy 2 - this result returns only the sum of n-level elements that exist under both hierarchy consolidations (essentially an inner join.)

As to your question Wim, I assume DBRW is unaware of hierarchies altogether and even just trying to reference 1 wouldn’t work… that being said, I haven’t had the need to try or test it out.

declanr wrote: ↑Tue Feb 01, 2022 5:35 pmAs to your question Wim, I assume DBRW is unaware of hierarchies altogether and even just trying to reference 1 wouldn’t work… that being said, I haven’t had the need to try or test it out.

It confirms my thoughts but pretty bad situation after 6+ years in the land of PAW with alternative hierarchies...

PAW is too rigid in terms of formatting or extra logic (what we typically do in IF cells surrounding an Excel Perspectives / PAfE report).
So then we move to Excel but if we cannot use the alternative hierarchies, even not 1 per DBRW, that's nothing less then a shame.

Hi Wim,

The Dbrw aren't hierarchy aware. So if the consolidated element doesn't exist in the default hierarchy it won't recognise it and likewise any consolidation names that do exist in multiple hierarchies, then only the rollup value from the default hierarchy will be picked up.

This topic was re-raised on the IBM forum recently, see link below. Although the response wasn't great and I think the dqr (dynamic quick report) won't be until the next big release. What level of flexibility and level of interaction we'll get from this though, is anyone's guess

https://community.ibm.com/community/use ... 4eda43146b

Hi,

Did you ever drag a hierarchy into a view in Workspace for instance ?

Actually I never have, which explains my queries. Thanks to Declan for making it clear!

Maren

I can understand performance is a big concern from IBM if they develop this formula. That's why they want to replace it with a new report type. If that solves the problem, then everyone is happy.
Out of curiosity, how is DBRH performed in cubewise's slice? Has anyone use it to develop some monster report yet?

I would hope that IBM tackle the relevant and much needed things FIRST AND FOREMOST. Rather than what I read today in changes in the upcoming version 73.

https://www.ibm.com/docs/en/planning-an ... res_2_0_73

Trying to think of a use case for what those new formula might be?

They don't seem particularly relevant to TM1 and more generally in Excel, what would you use them for...?

Hi Steve,

Not sure I understood your comment. Anything that is done with PA alternative hierarchies would be used in all TM1 clients, including Excel. Meaning that, what would otherwise be SUMIF’s in Excel would be the result of a DBRW that calls 1 or more alternate hierarchies.

Sorry..
I meant these formula in the link you provided. Absolutely a DBRH would be the number one item on my wish list!

https://www.ibm.com/docs/en/planning-an ... ebook.html

TM1RESOLVEBOOK( ) - removes nested parentheses from each cell within a workbook
TM1RESOLVESHEET( ) - removes nested parentheses from each cell in the active worksheet
TM1RESOLVEFORMULAS( ) - removes nested parentheses within a cell range
TM1RESOLVENESTEDPARENS( ) - removes the nested parentheses and returns a string

I have no clue what these functions are about

Hi Wim,

This had been bugging me, too, and I now think I understand what these are meant do do after some help from Matt Hopkins @ QueBIT tracking down someone at IBM to get further clarification.

The short answer is that these are functions designed to help clean up existing TM1 Perspectives Excel reports that use a specific DBR/DBS nested formula argument syntax that most people probably have never seen and which IBM (I'd expect) wants to ultimately avoid supporting in PAfE because it's both confusing and unnecessary.

In the older days of Excel (2003 and earlier) we were, apparently, limited to 30 arguments to a function, so what TM1 Perspectives supported once we could have more than 16 dimensions was to allow nesting multiple elements within a single DBR/DBS argument to work around this 30 argument limitation. That means you could use this: Which looks to Excel like a function with 3 arguments, but is ultimately treated in the same way as this by Perspectives: So, I'd agree these should have been documented in much more detail.I also suspect that these should be classified as functions designed for VBA usage-- once you've called them to clean up this older, nested formula syntax you shouldn't need them anymore. I'm guessing those details didn't get through to the people that write the documentation the first time around (and I'll try to send some of that feedback along to support unless the IBM'ers here beat me to it!)

I also agree that DBRH + subset/user-defined-consolidation equivalent references in DBRx formulas would easily be top priority for PAfE.

Regards,
Mike

Thank you Mike. That seems very plausible but it is not something I’ve ever used before.

Thanks Mike.....you learn something new everyday! This is almost triggering me into rant but I've better things to do!

I've only seen DBR's with nested parentheses once "in the wild" and I remember wondering: what is this madness? I expect it's very, very uncommon out there.