Use subsets in DBRW

Post Reply
AskAndAnswer
Posts: 39
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

Use subsets in DBRW

Post by AskAndAnswer » Thu Jun 27, 2019 5:48 pm

I have a report that used a dimension subset in DBRW formula. It worked in Perspectives, but it does not work in PAX custom report. Is there anything that can mimic the functionality besides creating an alternate hierarchy in the dimension? Virtual dimensions don't work in the custom reports, as far as I know.

lotsaram
MVP
Posts: 3329
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Use subsets in DBRW

Post by lotsaram » Thu Jun 27, 2019 8:52 pm

User Defined Consolidations aren't supported in PAX. So you can't use a subset name as a pseudo-consolidation. You need to use an actual consolidation.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

AskAndAnswer
Posts: 39
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

Re: Use subsets in DBRW

Post by AskAndAnswer » Fri Jun 28, 2019 5:15 pm

That's what I was trying to avoid, and in fact there is a work around - create a hidden dynamic report with the given subset in the TM1RPTROW and a sum underneath, then refer the sum.

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

Re: Use subsets in DBRW

Post by Steve Rowe » Mon Jul 01, 2019 11:12 am

I'm always curious about the desire for UDCs and avoiding setting up a true consolidation, can I ask what your use case is such that you don't want to set-up a consolidation?

Wim Gielis
MVP
Posts: 2287
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Use subsets in DBRW

Post by Wim Gielis » Mon Jul 01, 2019 11:39 am

Hi Steve,

Anything related to ad hoc analysis, or related to user choices, leading to elements / consolidations of which the sum needs to be shown.
A regular Excel pivot table can show the sum of the visible elements, so why not TM1 in an easy way ?
For more structural consolidations, I would create them in the dimension as regular consolidations.
But it's not always easy or foreseeable which totals will be needed afterwards.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Mark RMBC
Regular Participant
Posts: 204
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Use subsets in DBRW

Post by Mark RMBC » Mon Jul 01, 2019 12:08 pm

Hi,

I raised this issue here:

https://www.tm1forum.com/viewtopic.php?p=72800#p72800

I tend to agree with Wim, it was for simplicity, an easy way to retrieve a value.

But I guess the workaround is pretty straightforward!

cheers, Mark

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

Re: Use subsets in DBRW

Post by Steve Rowe » Tue Jul 02, 2019 8:56 am

The need to generate ad-hoc totals from a random lists of elements is a valid use-case, I just don't like the subset / UDC driven solution. If they are starting from scratch I think there are probably better ways we can deliver ad-hoc totals without the loss of control that subset as totals and other UDCs bring.

I think that just reproducing the legacy approach to ad-hoc totals is a bit "lazy", they ought to be going back to the requirements and thinking about the best way of delivering them...easy to say of course!

For example, the dynamic report use case seems to be the most common, wouldn't it be better if there was just a flag in the TM1RptRow formula that meant that a total was always generated?

All hypothetical I guess...

antcharwill
Posts: 1
Joined: Mon Nov 18, 2019 9:50 am
OLAP Product: IBM Cognos
Version: PAx
Excel Version: Excel 365

Re: Use subsets in DBRW

Post by antcharwill » Mon Nov 18, 2019 11:04 am

Sorry to bump an old thread, but perhaps better than starting a new one on the same topic!
AskAndAnswer wrote:
Fri Jun 28, 2019 5:15 pm
That's what I was trying to avoid, and in fact there is a work around - create a hidden dynamic report with the given subset in the TM1RPTROW and a sum underneath, then refer the sum.
Isn't this much more cumbersome than just allowing subsets in DBRW formulas?

We've just migrated to PAx I've got a load of pre-existing reports that make heavy use of subsets in cube reads, and having to add dynamic reports to all my workbooks then referencing the sums seems daft compared to just having a single DBRW("blah", "blah","blah","blah","Europe + CIS") formula that does all that for me.

This appears to be a real step backwards.

Post Reply